tJDBCSCDELT Standard properties

Property Type

Select the way the connection details will be set.

• Built-In: The connection details will be set locally for this component. You need to specify the values for all related connection properties manually.

• Repository: The connection details stored centrally in Repository > Metadata will be reused by this component. You need to click the [...] button next to it and in the pop-up Repository Content dialog box, select the connection details to be reused, and all related connection properties will be automatically filled in.

Use an existing connection

Select this check box and in the Component List drop-down list, select the desired connection component to reuse the connection details you already defined.

JDBC URL

The JDBC URL of the database to be used. For example, the JDBC URL for the Amazon Redshift database is jdbc:redshift://endpoint:port/database.

Driver JAR

Complete this table to load the driver JARs needed. To do this, click the [+] button under the table to add as many rows as needed, each row for a driver JAR, then select the cell and click the [...] button at the right side of the cell to open the Module dialog box from which you can select the driver JAR to be used. For example, the driver jar RedshiftJDBC41-1.1.13.1013.jar for the Redshift database.

For more information, see Importing a database driver.

Driver Class

Enter the class name for the specified driver between double quotation marks. For example, for the RedshiftJDBC41-1.1.13.1013.jar driver, the name to be entered is com.amazon.redshift.jdbc41.Driver.

Username and Password

The database user authentication data.

To enter the password, click the [...] button next to the password field, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.

DB Schema

Specify the name of the database schema. For the database that does not support schema, just leave this field empty.

Source table

Specify the name of the source input table whose data changes will be captured by SCD.

Table

Specify the name of the dimension table into which the data captured by SCD will be written.

Select an operation to be performed on the table defined.

• None: No operation is carried out.

• Drop and create table: The table is removed and created again.

• Create table: The table does not exist and gets created.

• Create table if does not exist: The table is created if it does not exist.

• Drop table if exist and create: The table is removed if it already exists and created again.

• Clear table: The table content is deleted. You have the possibility to rollback the operation.

• Truncate table: The table content is deleted. You do not have the possibility to rollback the operation.

Schema and Edit schema

A schema is a row description. It defines the number of fields (columns) to be processed and passed on to the next component. When you create a Spark Job, avoid the reserved word line when naming the fields.

• Built-In: You create and store the schema locally for this component only.

• Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs.

• View schema: choose this option to view the schema only.

• Change to built-in property: choose this option to change the schema to Built-in for local changes.

• Update repository connection: choose this option to change the schema stored in the repository and decide whether to propagate the changes to all the Jobs upon completion. If you just want to propagate the changes to the current Job, you can select No upon completion and choose this schema metadata again in the Repository Content window.

Surrogate key

Set the column where the generated surrogate key will be stored. A surrogate key can be generated based on a method selected on the Creation list.

Creation

Select any of the following methods to be used for generating the surrogate key.

• Auto increment: an auto-incrementing integer will be used.

• DB sequence: a sequence will be used.

Source keys

Specify one or more columns used as the key(s) that ensure the unicity of the incoming data.

Use SCD type 0 fields

Select this check box and in the SCD type 0 fields table displayed, specify the column(s) whose value changes will be tracked using Type 0 SCD.

This option only supports these databases: Exasol, MySql, MSSql, Oracle, Postgresql, and Snowflake.

Use SCD type 1 fields

Select this check box and in the SCD type 1 fields table displayed, specify the column(s) whose value changes will be tracked using Type 1 SCD.

SCD type 1 fields

Click the [+] button to add as many rows as needed, each row for a column, then click the right side of the cell and from the drop-down list displayed select the column whose value changes will be tracked using Type 1 SCD.

Use SCD type 2 fields

Select this check box and in the SCD type 2 fields table displayed, specify the column(s) whose value changes will be tracked using Type 2 SCD.

SCD type 2 fields

Click the [+] button to add as many rows as needed, each row for a column. Click the arrow on the right side of the cell and select the column whose value changes will be tracked using Type 2 SCD from the drop-down list displayed .

This table is available only when the Use SCD type 2 fields option is selected.

Start date

Specify the column that holds the start date for type 2 SCD.

This list is available only when the Use SCD type 2 fields option is selected.

End date

Specify the column that holds the end date for type 2 SCD.

This list is available only when the Use SCD type 2 fields option is selected.

End Date Field Type: Specify the end date value, which can be NULL or Fixed Date.

Log active status

Select this check box and from the Active field drop-down list displayed, select the column that holds the true or false status value, which helps to spot the active record for type 2 SCD.

This option is available only when the Use SCD type 2 fields option is selected.

Log versions

Select this check box and from the Version field drop-down list displayed, select the column that holds the version number of the record for type 2 SCD.

This option is available only when the Use SCD type 2 fields option is selected.

Mapping

Source fields value include Null

Select this check box to allow the source columns to have Null values. The source columns here refer to the fields defined in the SCD type 1 fields and SCD type 2 fields tables.

Debug mode

Select this check box to display each step during processing the data in a database.

tStatCatcher Statistics

Select this check box to gather the Job processing metadata at the Job level as well as at each component level.

ERROR_MESSAGE

The error message generated by the component when an error occurs. This is an After variable and it returns a string.

Usage rule

This component can be used as a standalone component of a Job or subJob.