August 16, 2023

tJDBCSCDELT – Docs for ESB 6.x

  2. Talend for ESB Docs
  3. Docs 6.x
  4. tJDBCSCDELT – Docs for ESB 6.x

tJDBCSCDELT

Tracks data changes in a source database table using
SCD (Slowly Changing
Dimensions) Type
1 method
and/or Type 2
method
and writes both the current
and historical
data
into a specified SCD
dimension table.

tJDBCSCDELT Standard properties

These properties are used to configure tJDBCSCDELT running in
the Standard Job framework.

The Standard
tJDBCSCDELT component belongs to two families: Business Intelligence and Databases.

The component in this framework is generally available.

Basic settings

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 click the relevant connection component to reuse the connection
details you already defined.

JDBC URL

Specify 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 Select
Module wizard from which you can select the driver JAR of your interest.
For example, the driver jar RedshiftJDBC41-1.1.13.1013.jar for
the Redshift database.

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.
Action on table

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. The schema is either Built-In or stored remotely in the Repository.

  • Built-In: You create and store the
    schema locally for this component only. Related topic: see
    Talend Studio

    User Guide.

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

    User Guide.

Click Edit schema to make changes to the
schema. Note that if you make changes, the schema automatically becomes built-in.

  • 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 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 colulmn 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, 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 2 SCD.

Start date

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

End date

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

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.

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.

Mapping

Specify the metadata mapping file for the database
to be used. The metadata mapping file is used for the data type conversion
between database and Java. For more information about the metadata mapping,
see the related documentation for Type mapping.

Advanced settings

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.

Global Variables

ERROR_MESSAGE

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

Usage

Usage rule

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

Tracking data changes in a Snowflake table using the tJDBCSCDELT component

This scenario describes a Job that captures the employee data changes in a Snowflake
table using SCD (Slowly Changing Dimensions) Type 1 and Type 2 methods implemented by the
tJDBCSCDELT component, and writes both the current and historical data in a SCD dimension
table.

The input data contains various employee details including
name, role, salary, and
another id column is added to help ensuring the unicity of the input
data.

At first, the following employee data is inserted to a new Snowflake
table.

Later, the table is updated with the following renewed employee data.

You can see the role of Thomas Johnson is changed from
developer to tester, the role of Teddy
Brown is changed from tester to writer, and his
salary is raised from 16000.00 to 17000.00. Besides, a new
employee record with id 444 is inserted. In this scenario,

  • the
    existing
    name and role data will
    be
    overwritten
    by the
    new
    data, so
    SCD
    Type 1 method will be
    performed on them, and

  • the
    full history of the salary
    data will be
    retained, and
    a
    new record with the changed data
    will be always created
    and
    the previous
    record will be
    closed, so
    SCD
    Type 2 method will be
    performed on it.

For more information about SCD types, see SCD management methodology.

Creating a Job for tracking data changes in a Snowflake table using tJDBCSCDELT

  1. Create a new Job and add a tJDBCConnection component,
    two tJDBCRow components, two
    tFixedFlowInput components, two
    tJDBCOutput components, two
    tJDBCSCDELT components, two
    tJDBCInput components, and two
    tLogRow components to the Job.

    tjdbcscdelt_scenario_job.png

  2. Link the first tFixedFlowInput component to the first
    tJDBCOutput component using a Row > Main connection.
  3. Do the same to link the first tJDBCInput component to
    the first tLogRow component, the second
    tFixedFlowInput component to the second
    tJDBCOutput component, and the second
    tJDBCInput component to the second
    tLogRow component.
  4. Link the tJDBCConnection component to the first
    tJDBCRow component using a Trigger > On Subjob Ok connection.
  5. Do the same to link the first tJDBCRow component to the
    second tJDBCRow component, the second
    tJDBCRow component to the first
    tFixedFlowInput component, the first
    tFixedFlowInput component to the first
    tJDBCSCDELT component, the first
    tJDBCSCDELT component to the first
    tJDBCInput component, the first
    tJDBCInput component to the second
    tFixedFlowInput component, the second
    tFixedFlowInput component to the second
    tJDBCSCDELT component, the second
    tJDBCSCDELT component to the second
    tJDBCInput component.

Opening a connection to a Snowflake database

  1. Double-click the tJDBCConnection component to open its
    Basic settings view.

    tjdbcscdelt_scenario_tjdbcconnection.png

  2. In the JDBC URL field, enter the connection string to
    connect to Snowflake using the JDBC driver.

    For more information about how to use the JDBC driver to connect to Snowflake
    and how to specify the connection string, see JDBC Driver Connection String.

  3. Click the [+] button below the Driver
    JAR     table to add a line, and in the Jar
    name     cell of the new line, enter the name of the jar file for
    the Snowflake JDBC driver, snowflake-jdbc-3.2.2.jar in
    this example.
  4. In the Driver Class field, enter the Snowflake JDBC
    driver class, com.snowflake.client.jdbc.SnowflakeDriver
    in this example.
  5. In the Username and Password
    fields, enter the authentication information accordingly.

Creating a Snowflake table and a Snowflake sequence

  1. Double-click the first tJDBCRow component to open its
    Basic settings view.
  2. Select the Use an existing connection check box and from
    the Component List drop-down list displayed, select the
    connection component to reuse the connection created by it,
    tJDBCConnection_1 in this example.
  3. In the Query field, enter the SQL command used to create
    a new table.

    In this example, the SQL command is CREATE OR REPLACE TABLE
    employee (id INTEGER, name VARCHAR(50), role VARCHAR(50), salary DOUBLE,
    PRIMARY KEY(id))    , which creates a new table
    employee with four columns, id
    of INTEGER type as the primary key, name and
    role of VARCHAR type, and
    salary of DOUBLE type. This table will be used to
    store the employee data.
  4. Double-click the second tJDBCRow component to open its
    Basic settings view.
  5. Select the Use an existing connection check box and from
    the Component List drop-down list displayed, select the
    connection component to reuse the connection created by it,
    tJDBCConnection_1 in this example.
  6. In the Query field, enter the SQL command used to create
    a Snowflake sequence.

    In this example, the SQL command is create or replace sequence
    employee_sequence    , which creates a new sequence
    employee_sequence. This sequence will be used by the
    tJDBCSCDELT component to generate the surrogate key
    for SCD Type 2 method.

Inserting data into the new Snowflake table

  1. Double-click the first tFixedFlowInput component to open
    its Basic settings view.
  2. Click the […] button next to Edit
    schema     and in the pop-up dialog box, define the schema by adding
    four columns: id of Integer type as the primary key,
    name and role of String type,
    and salary of Double type.

    tjdbcscdelt_scenario_tfixedflowinput_schema.png

  3. Click OK to save the schema changes. In the pop-up
    dialog box, click Yes to propagate the
    schema to the next component.
  4. Select Use Inline Content in the
    Mode area. Then in the Content
    field displayed, enter the following employee data to be inserted.

  5. Double-click the first tJDBCOutput component to open its
    Basic settings view.
  6. Select the Use an existing connection check box and from
    the Component List drop-down list displayed, select the
    connection component to reuse the connection created by it,
    tJDBCConnection_1 in this example.
  7. In the Table field, enter the name of the table into
    which the employee data will be written, employee in this
    example.
  8. In the Action on data drop-down list, select
    Insert to insert the employee data transferred from
    the first tFixedFlowInput component.

Tracking data insertion changes and writing the changes into a SCD dimension
table

  1. Double-click the first tJDBCSCDELT component to open its
    Basic settings view.

    tjdbcscdelt_scenario_tjdbcscdelt.png

  2. Select the Use an existing connection check box and from
    the Component List drop-down list displayed, select the
    connection component to reuse the connection created by it,
    tJDBCConnection_1 in this example.
  3. In the Source table field, enter the name of the table
    whose data changes will be captured, employee in this
    example.
  4. In the Table field, enter the name of the SCD dimension
    table that will store both the current and historical employee data,
    employee_scd in this example.
  5. Select Drop table if exists and create from the
    Action on table drop-down list to create the SCD
    dimension table.
  6. Click the […] button next to Edit
    schema     and in the pop-up dialog box, define the schema by adding
    nine columns: sk and id of Integer
    type as the primary key, name and
    role of String type, salary of
    Double type, start_date and
    end_date of Date type with the Date Pattern
    yyyy-MM-dd, and active_status
    and version of Integer type. When done, click
    OK to save the changes and close the dialog
    box.

    tjdbcscdelt_scenario_tjdbcscdelt_schema.png

  7. From the Surrogate key drop-down list, select the name
    of the column that will be used as the primary key of the SCD dimension table,
    sk in this example.
  8. Select DB sequence from the
    Creation drop-down list and in the
    Sequence field displayed, enter the name of the
    Snowflake sequence used to generate the surrogate key for the SCD Type 2
    method.
  9. Click the [+] button below the Source
    keys     table to add a new line, and click the
    Name cell and select the key column of the source
    table from the drop-down list, id in this example.
  10. Select the Use SCD type 1 fields check box, click the
    [+] button below the SCD type 1
    fields     table twice to add two lines. Then click each cell and
    from the drop-down list, select the column on which the SCD Type 1 method will
    be performed. In this example, they are name and
    role.
  11. Select the Use SCD type 2 fields check box, click the
    [+] button below the SCD type 2
    fields     table to add a line. Then click the cell and select the
    column on which the SCD Type 2 method will be performed. In this example, it is
    salary.
  12. From the Start date and End date
    drop-down lists, select the columns used to hold the start date and end date
    values for the SCD Type 2 method respectively, start_date
    and end_date in this example.
  13. Select the Log active status check box and from the
    Active field drop-down list displayed, select the
    column used to hold the active status value for the SCD Type 2 method, which
    helps identify the active records, active_status in this
    example.
  14. Select the Log versions check box and from the
    Version field drop-down list, select the column used
    to hold the version number of the records for the SCD Type 2 method,
    version in this example.
  15. Select Mapping Snowflake from the
    Mapping drop-down list to use the Snowflake metadata
    mapping file.

Retrieving the data insertion updates from the SCD dimension table

  1. Double-click the first tJDBCInput component to open its
    Basic settings view.
  2. Select the Use an existing connection check box and from
    the Component List drop-down list displayed, select the
    connection component to reuse the connection created by it,
    tJDBCConnection_1 in this example.
  3. Click the […] button next to Edit
    schema     and in the pop-up dialog box, define the schema by adding
    nine columns: sk and id of Integer
    type as the primary key, name and
    role of String type, salary of
    Double type, start_date and
    end_date of Date type with the Date Pattern
    yyyy-MM-dd, and active_status
    and version of Integer type. When done, click
    OK to save the changes and close the dialog
    box.

    The schema of the first tJDBCInput component is the
    same as the schema of the tJDBCSCDELT component, you
    can just copy and paste it.

  4. In the Query field, enter the SQL command used to
    retrieve data from the SCD dimension table, select * from
    employee_scd     in this example.
  5. Double-click the first tLogRow component and in the
    Mode area on its Basic
    settings     view, select Table to display
    the retrieved data in a table.

Updating data in the Snowflake table

  1. Double-click the second tFixedFlowInput component to
    open its Basic settings view.
  2. Click the […] button next to Edit
    schema     and in the pop-up dialog box, define the schema by adding
    four columns: id of Integer type as the primary key,
    name and role of String type,
    and salary of Double type.

    This schema is the same as the schema of the first
    tFixedFlowInput component, you can just copy and
    paste it.

  3. Click OK to save the schema changes. In the pop-up
    dialog box, click Yes to propagate the
    schema to the next component.
  4. Select Use Inline Content in the
    Mode area. Then in the Content
    field displayed, enter the following employee data to update the existing
    data.

  5. Double-click the second tJDBCOutput component to open
    its Basic settings view.
  6. Select the Use an existing connection check box and from
    the Component List drop-down list displayed, select the
    connection component to reuse the connection created by it,
    tJDBCConnection_1 in this example.
  7. In the Table field, enter the name of the table, in
    which the data will be updated, employee in this
    example.
  8. Select Insert or update from the Action on
    data     drop-down list.

Tracking data update changes and writing the changes into the SCD dimension table

  1. Double-click the second tJDBCSCDELT component to open
    its Basic settings view.
  2. Repeat 2 through 15 in the procedure Tracking data insertion changes and writing the changes into a SCD dimension table to configure the second tJDBCSCDELT component.

Retrieve the data update changes from the SCD dimension table

  1. Double-click the second tJDBCInput component to open its
    Basic settings view.
  2. Repeat 2 through 4 in the procedure Retrieving the data insertion updates from the SCD dimension table to configure the second tJDBCInput component.
  3. Double-click the second tLogRow component and in the
    Mode area on its Basic
    settings     view, select Table to display
    the retrieved data in a table.

Executing the Job to track data changes in a Snowflake table using tJDBCSCDELT

  1. Press Ctrl + S to save
    the Job.
  2. Press F6 to execute the
    Job.

    tjdbcscdelt_scenario_result.png

    As shown above, the old role developer for
    Thomas Johnson is overwritten directly by the new
    role tester because SCD Type 1 is performed on the
    role column, and a new record with the surrogate
    key value set to 4 is created for Teddy
    Brown    ‘s salary update from 16000.00
    to 17000.00 because SCD Type 2 is performed on the
    salary column.

Document get from Talend https://help.talend.com
Thank you for watching.
Docs 6.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x