Working with relational databases

Integrator works equally well with all relational databases, as long as there is a JDCB driver for the database and database can be accessed from the server where Integrator is running.

Connecting to database

You can connect to the following relation databases from the Integrator:

Create database connection to connect to the relational database.

Connecting to database if it is not on the list

If database is not on the list of the supported databases, you can still connect to it using this instruction.

Testing database connection

Click the Test Connection button in the connection screen to test the database connection.

Test connection

Browsing database objects and executing SQL

Use Integrator's add-on Explorer to browser database objects, such as tables, views, columns, etc. and execute SQL queries.

Browsing database objects

To browse database objects select and expand database connection on the left navigational bar.

Writing and executing SQL queries

To write and execute SQL queries use build-in SQL editor.

Database is a source

Integrator can extract data from database objects, such as tables, views, synonyms, user-defined SQL, functions, etc., transform it and load into any destination, for example another database, files, API endpoints, etc.

Step 1 If database is a source in transformation, start creating data integration flow by opening Flow Builder page, clicking the + button, selecting Databases in the left navigational bar and selecting flow type, where database is a source:

database is a source

Step 2 Continue by creating one or multiple transformations where the source is a database

db is a source of the transformation

Step 3 Modify transformation by selecting database object to read data from (table, view, etc.) or defining a source query.

Step 4 Continue by defining mapping and parameters.

Database is a destination

Integrator can extract data from files, API endpoints, social web sites, databases, etc., transform it and load into any database. When loading data into database Integrator either automatically generates and executes SQL DML statement (INSERT, UPDATE, DELETE or MERGE) or executes user-created SQL.

Step 1 If database is a destination in transformation, start creating data integration flow by opening Flow Builder page, clicking the + button, selecting Databases in the left navigational bar and selecting flow type, where database is a destination:

database is a destination

Step 2 Continue by creating one or multiple transformations where the destination is a database

db is a destination of the transformation

Step 3 Modify transformation by selecting database object to load data into (table, view, etc.) or defining a destination query.

Step 4 Continue by defining mapping and parameters.

Automatically create destination table

Integrator can automatically create a destination table if it does not exist. It uses fields in the source to create and execute create table SQL.

This behavior is enabled by default.

create table

Conditionally update database table using MERGE

By default, when the destination is a database Integrator generates and executes INSERT SQL statement. In many cases it makes more sense to check if record already exists and UPDATE it instead. A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether condition matches. Integrator supports MERGE (upsert) for the following databases:

Step 1 Create a flow where destination is a database.

Step 2 Click the MAPPING button between FROM and TO.

Step 3 Goto Parameters tab.

Step 4 Select MERGE as an Action.

Step 5 Enter coma separated list of the fields in the Lookup Fields text box. These fields will be used to match the record in the destination table.

merge action

Important: PostgreSQL and MySQL require an unique index, which includes all the fields defined in the Lookup Fields text box.

Conditionally update database table without MERGE

The alternative to MERGE is IfExist action. Instead of relying on the native MERGE, included in the target database engine, Integrator will execute SQL statement to check if record already exist, and if it does, will then execute UPDATE. If record does not exist Integrator will execute INSERT.

Use IfExist if:

Step 1. Create flow with at least one transformation where database is a destination.

Step 2. When configuring transformation click MAPPING and then select Parameters tab.

Step 3. Select IfExist for action and enter coma separated list of field names, used to identify unique record.

IfExist action

Conditionally update database table (JavaScript)

You can use JavaScript to define what kind of DML statement (INSERT, UPDATE, DELETE or MERGE) will be executed when updating target table.

Other common use cases

Change replication

In Integrator it is possible to create flows, which track changes in database and load them into another database or API. It is called change replication.

Examples

Using temporary database

In Integrator it is possible to create a connection to the temporary, in memory database and use it as a staging area for the data.

Work with temporary database just like you would with any other relational database, just keep in mind that it will be automatically deleted once the connection that created it closed.

Usage examples:

Automatically create staging tables

Integrator can automatically create (and drop) staging tables for you, practically from any data source: files, API endpoints, etc.

Step 1 Start creating data integration flow by opening Flow Builder page. Click the + button and type create staging tables in the "Select Flow Type" box.

create staging tables

Step 2 Continue by defining FROM and TO. Select a database connection for the staging table(s) in the TO connection field. Note TO field. It it will be used as a name for the staging table.

create staging tables flow

Step 3. Configure flow to drop existing staging table if it is already exist.

drop staging tables

Execute any SQL

In Integrator it is possible to execute any SQL statement (including DDL) or a list of the ; separated SQL statements, outside of the regular source to destination transformation.

Read more about SQL flow.

Read records from the database and execute flow for each record

In Integrator it is possible to execute the same flow in a loop for each record returned by SQL statement.

Usage Examples

Expose database object as an API endpoint

In Integrator is is possible to build a REST API that can be used to access some controlled portion of the data from the other side of the firewall. For example, you can create a flow which executes a SQL query that reads data from your corporate database and exposes result set as an Integrator's API endpoint.

Read more about building REST API for accessing database objects.

Tips and tricks

Read about tips and tricks when working with databases.

Performance tips

Read about performance tips when working with databases.

Also read about common performance tips.