Working with databases

Etlworks Integrator works equally well with all relational and NoSQl 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

The following connectors are available out of the box:

Create database connection to connect to the relational database.

Connecting to Oracle

To create a connection to the Oracle database, open Connections window, click + button, and type in oracle. Choose connection type from the list of the available Oracle connections.

Enter connection parameters.

The following properties are available for the Oracle connection's URL.

Connecting to MS SQL Server

To create a connection to the Microsoft SQL Server database, open Connections window, click + button, and type in sql server. Select Microsoft SQL Server.

Enter connection parameters.

The following properties are available for the Microsoft Sql Server connection's URL.

Connecting to IBM DB2

To create a connection to the IBM DB2 database, open Connections window, click + button, and type in db2. Select IBM DB2.

Enter connection parameters.

The following properties are available for the IBM DB2 connection's URL.

Connecting to PostgreSQL

To create a connection to the PostgreSQL database, open Connections window, click + button, and type in postres. Select PostgreSQL.

Enter connection parameters.

The following properties are available for the PostgreSQL connection's URL.

Connecting to MySQL

To create a connection to the MySQL database, open Connections window, click + button, and type in mysql. Select MySQL.

Enter connection parameters.

The following properties are available for the MySQL connection's URL.

Connecting to Informix

To create a connection to the Informix database, open Connections window, click + button, and type in informix. Select one of the available Informix connection types.

Enter connection parameters.

If Informix using DB2 driver connection type is selected, the following properties are available for the Informix connection's URL.

Otherwise, the following URL properties are available.

Connecting to Sybase ASE

To create a connection to the Sybase ASE database, open Connections window, click + button, and type in sybase. Select Sybase ASE.

Enter connection parameters.

The following properties are available for the Sybase ASE connection's URL.

Connecting to Snowflake

To create a connection to the Snowflake database, open Connections window, click + button, and type in snowflake. Select Snowflake.

Enter connection parameters.

The following properties are available for the Snowflake connection's URL.

Connecting to Amazon RDS

Amazon RDS is a fully managed database service. The following RDS connectors are available in Etlworks Integrator:

Connecting to Amazon Aurora PostgreSQL

Amazon Aurora PostgreSQL is essentially a fully managed PostgreSQL cluster, available on AWS. Use the following guideline to configure Aurora PostgreSQL cluster.

To create a connection to the Amazon Aurora PostgreSQL database, open Connections window, click + button, and type in aurora. Select Amazon Aurora PostgreSQL.

Enter connection parameters.

The following properties are available for the Amazon Aurora PostgreSQL connection's URL.

Connecting to Amazon Aurora MySQL

Amazon Aurora MySQL is essentially a fully managed MySQL cluster, available on AWS. Use the following guideline to configure Aurora MySQL cluster.

To create a connection to the Amazon Aurora MySQL database, open Connections window, click + button, and type in aurora. Select Amazon Aurora MySQL.

Enter connection parameters.

The following properties are available for the Amazon Aurora MySQL connection's URL.

Connecting to Amazon Redshift

Amazon Redshift is a fully managed data warehouse available on AWS.

To create a connection to the Amazon Redshift database, open Connections window, click + button, and type in redhsift. Select Amazon Redshift.

Enter connection parameters.

The following properties are available for the Amazon Redshift connection's URL.

Connecting to Google Bigquery

Google Bigquery is not a typical relational database (it is actually a service), but Google partnered with the third-party companies to provide JDBC driver that leverage the power of BigQuery's Standard SQL.

To create a connection to the Google Bigquery, open Connections window, click + button, and type in bigquery. Select Google Bigquery.

Enter connection parameters.

The following properties are available for the Google Bigquery connection's URL.

At this time the Google service account is required to connect to the Bigquery.

This guideline explains how to create a new Google service account.

When creating a new service account two parameters are important:

Use the following URL to connect to the Bigquery using Google service account.

jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=<Project>;OAuthType=0;
OAuthServiceAcctEmail={user};OAuthPvtKeyPath={password}

Enter Google service account email in the User field.

Upload the key in the JSON or P12 format into the server storage.

Enter path to the key file in the Password field. Use token {app.data} to define the path to the key. Example: {app.data}/key.json.

Connecting to Derby

Derby (Java DB) is a compact and embeddable database implemented entirely in Java and available under the Apache License, Version 2.0.

To create a connection to the Derby database, open Connections window, click + button, and type in derby. Select Client Java DB (Derby).

Enter connection parameters.

The following properties are available for the Derby connection's URL.

Connecting to SQLite

SQLite is available in Etlworks Integrator as a temporary staging database.

In the dedicated instance it is also possible to create a connection to the actual, not temporary (in memory) SQLite database.

Step 1. Open Connections window, click + button, and type in jdbc. Select Generic JDBC.

Step 2. Enter org.sqlite.JDBC in the Driver field.

Step 3. Enter connection URL as described here.

Step 4. If needed, enter user name and password.

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.

Inserting images from files into database table

Read how to insert image files into the database table.

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.