JDBC Incremental Load

JDBC Incremental Load


This article is part of the series on Incremental Load Tools and Shared Jobs.

Overview

The JDBC Incremental Load component is a tool designed to allow users to easily set up a Shared Job that will incrementally load from JDBC-compliant databases, rather than having to manually create such a job, which would require significantly more expertise.

In the Components panel, type "JDBC incremental load" to locate the component and add it to the canvas. The search bar is not case-sensitive. The wizard will load once a user drag-and-drops this component onto the canvas.

JDBC Incremental Load


JDBC Incremental Load Setup (Redshift)

1. Database Selection

Page 1 of the wizard gives users an explanation of the wizard, as well as requiring database connection details.

Database Type

Select the type of database to connect to. The available database types are:

  • IBM DB2
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL


Connection URL

Pass your database's connection URL. Once you have selected a database type, Matillion ETL will automatically load a template URL relative to the database type. For example, selecting PostgreSQL as the database type provides this template: jdbc:postgresql://<host>/<database>.


Username

Provide the username for the database.


Password name

Here, users can select a configured password entry from the dropdown list. To add a new password entry, or edit or remove existing entries, click the Manage button. Read Manage Passwords for more information.

Click Next.

Database selection


2. Connection Options

Page 2 of the wizard is for setting connection options. These are defined in parameter=value pairs. To add a new connection option, click .

Below are links to the connection options for each available database type.

Schema

Select the source schema. Depending on the selected database type and the selected user, this property may be hidden.

Click Next.

Connection Options


3. Data Sources

Page 3 of the wizard focuses on the data sources (tables) to load. If the database setup and connection options were applied successfully, page 3 will show a success message.

Data Sources

Use the arrow buttons to select which data sources to add to the incremental load. Data sources in the left column are not set to be included; data sources in the right column will be included in the incremental load.

By default, the right column is empty, and users must manually add the columns they wish to load. Use the text fields above the columns to refine your searches.

In this example, the data source "numerictest" has been selected for the incremental load.

Click Next.

Data Sources


4. Data Selection

Page 4 of the wizard requires users to confirm the columns to be loaded from each selected data source.

Click to set any columns as incremental, as well as to define the primary key.

Data Sources

Click Next to move on to the final page of the wizard.

Confirm columns


5. Configuration

Page 5 of the wizard requires users to specify data warehouse details.

Property Setting Description
Staging Bucket Select Select the S3 bucket from the dropdown list for data staging. The avaliable buckets depend on the selected Redshift cluster.
Staging Table Prefix String Specify a prefix to be added to all tables that are staged.
Stage Schema Select Select the Redshift schema via which tables will be staged.
Target Table Prefix String Specify a prefix to be added to all tables in the load.
Target Schema Select Select the Redshift schema into which tables will be loaded.
Target Distribution Style Select Select the distribution style.
  1. All Copy rows to all nodes in the Redshift Cluster.
  2. Even Distribute rows around the Redshift cluster evenly. This is the default setting.
Concurrency Select Select whether to load data in a concurrent or sequential method.

Click Create & Run to finish the setup; or else click Back to cycle back through the wizard.

Configuration


JDBC Incremental Load Setup (Snowflake)

1. Database Selection

Page 1 of the wizard gives users an explanation of the wizard, as well as requiring database connection details.

Database Type

Select the type of database to connect to. The available database types are:

  • IBM DB2
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL


Connection URL

Pass your database's connection URL. Once you have selected a database type, Matillion ETL will automatically load a template URL relative to the database type. For example, selecting PostgreSQL as the database type provides this template: jdbc:postgresql://<host>/<database>.


Username

Provide the username for the database.


Password name

Here, users can select a configured password entry from the dropdown list. To add a new password entry, or edit or remove existing entries, click the Manage button. Read Manage Passwords for more information.

Click Next.

Database selection


2. Connection Options

Page 2 of the wizard is for setting connection options. These are defined in parameter=value pairs. To add a new connection option, click .

Below are links to the connection options for each available database type.

Schema

Select the source schema. Depending on the selected database type and the selected user, this property may be hidden.

Click Next.

Connection Options


3. Data Sources

Page 3 of the wizard focuses on the data sources (tables) to load. If the database setup and connection options were applied successfully, page 3 will show a success message.

Data Sources

Use the arrow buttons to select which data sources to add to the incremental load. Data sources in the left column are not set to be included; data sources in the right column will be included in the incremental load.

By default, the right column is empty, and users must manually add the columns they wish to load. Use the text fields above the columns to refine your searches.

In this example, the data source "numerictest" has been selected for the incremental load.

Click Next.

Data Sources


4. Data Selection

Page 4 of the wizard requires users to confirm the columns to be loaded from each selected data source.

Click to set any columns as incremental, as well as to define the primary key.

Data Sources

Click Next to move on to the final page of the wizard.

Confirm columns


5. Staging Configuration

Page 5 of the wizard requires users to specify details for data staging.

Property Setting Description
Staging Table Prefix String Specify a prefix to be added to all tables that are staged.
Staging Warehouse Select Select the staging warehouse.
Staging Database Select Select the staging database.
Staging Schema Select Select the staging schema.

Click Next.

Staging configuration


6. Target Configuration

Page 6 of the wizard requires users to specify target data warehouse details.

Property Setting Description
Target Table Prefix String Specify a prefix to be added to all tables in the load.
Target Warehouse Select Select the target warehouse.
Target Database Select Select the target database.
Target Schema Select Select the target schema.
Concurrency Select Select whether to load data in a concurrent or sequential method.

Click Create & Run to finish the setup; or else click Back to cycle back through the wizard.

Configuration