Database schemas and data are imported in hale»studio using JDBC. hale»studio offers support for Microsoft SQL Server, PostgreSQL and PostGIS databases out of the box, support for other databases can be added through plug-ins.
The database import currently has the following known limitations:
To load the schema or data from the database, you can either use the From Database (JDBC) tab in the import dialog, or use a JDBC URL in From URL. For the first option, you only need to select the database driver and specify the database host and name:
Next you will be prompted for a user and password to use for connecting with the database. Be aware that this information will be saved in the alignment project as plain text, so you should only share this project where it does not pose a security risk.
The database schema is represented as individual types for each
database table or view. The namespace for each type depends on the
database type, the database name and if applicable the name of the
database schema the table is defined in.
When you have loaded the
database schema you should explicitly specify which tables are
relevant for your mapping. Reducing the types in this way has the
consequence that only for this types data will be loaded from the
database.
To load the data from the database you have to provide the same
information as for the schema. Data will only be loaded for those
types displayed in the source schema in the Schema Explorer, i.e.
those types that are marked as mapping relevant.
Before
loading the data it is recommended to enable the selection of a
sub-set as sample data (First n instances per type), so a
potentially big database is not loaded completely into hale»studio just for
the analysis and mapping. This can be enabled in the tool bar or the
project's source data settings.
In relational databases tables usually reference other tables through
foreign keys. If in a mapping you need to combine information from two
or more of such tables, use the Join mapping function. The join
wizard will automatically add Join conditions between the foreign and
primary keys of the tables, if they could be detected from the schema
and you defined the Join order correctly.
An alternative is
to externally create a view in the database and use it for the
mapping, usually with the Merge function.
