JDBC Incremental Load
-
DarkLight
JDBC Incremental Load
-
DarkLight
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.
Users should schedule their incremental load jobs to run periodically for the job to continually update the created tables. To learn more about scheduling, read Manage Schedules.
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.
|
| 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
JDBC Incremental Load Setup (BigQuery)
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.
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 |
|---|---|---|
| Cloud Storage Area | Select | Select the Cloud Storage bucket to stage the data. |
| Staging Table Prefix | String | Specify a prefix to be added to all tables that are staged. |
| Staging Project | Select | Select the staging project. |
| Staging Dataset | Select | Select the staging dataset. |
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 Project | Select | Select the target BigQuery Project. |
| Target Dataset | Select | Select the target dataset. |
| 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.
Target Configuraton