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.