August 15, 2023

Scenario: Merging data directly on the DBMS – Docs for ESB 6.x

Scenario: Merging data directly on the DBMS

This scenario describes a simple Job that opens a connection to a MySQL database,
merges data from a source table into a target table according to customer IDs, and
displays the contents of the target table before and after the merge action. A WHERE
clause is used to filter data during the merge operation.

Use_Case_tSQLTemplateMerge1.png
  • Drop a tMysqlConnection component, a
    tSQLTemplateMerge component, two tMysqlInput components and two tLogRow components from the Palette onto the design workspace.

  • Connect the tMysqlConnection component to the
    first tMysqlInput component using a Trigger > OnSubjobOK connection.

  • Connect the first tMysqlInput component to
    the first tLogRow component using a Row > Main
    connection. This row will display the initial contents of the target table on
    the console.

  • Connect the first tMysqlInput component to
    the tSQLTemplateMerge component, and the
    tSQLTemplateMerge component to the second
    tMysqlInput component using Trigger > OnSubjobOK connections.

  • Connect the second tMysqlInput component to
    the second tLogRow component using a Row > Main
    connection. This row will display the merge result on the console.

  • Double-click the tMysqlConnection component
    to display its Basic settings view.

Use_Case_tSQLTemplateMerge2.png
  • Set the database connection details manually or select Repository from the Property
    Type
    list and select your DB connection if it has already been
    defined and stored in the Metadata area of the
    Repository tree view.

    For more information about Metadata, see
    Talend Studio User
    Guide
    .

  • Double-click the first tMysqlInput component
    to display its Basic settings view.

Use_Case_tSQLTemplateMerge3.png
  • Select the Use an existing connection check
    box. If you are using more than one DB connection component in your Job, select
    the component you want to use from the Component
    List
    .

  • Click the three-dot button next to Edit
    schema
    and define the data structure of the target table, or
    select Repository from the Schema list and select the target table if the schema
    has already been defined and stored in the Metadata area of the Repository
    tree view.

    In this scenario, we use built-in schemas.

Use_Case_tSQLTemplateMerge4.png
  • Define the columns as shown above, and then click OK to propagate the schema structure to the output component and
    close the schema dialog box.

  • Fill the Table Name field with the name of
    the target table, customer_info_merge in this
    scenario.

  • Click the Guess Query button, or type in
    "SELECT * FROM customer_info_merge" in the Query area, to retrieve all the table columns.

  • Define the properties of the second tMysqlInput component, using exactly the same settings as for
    the first tMysqlInput component.

  • In the Basic settings view of each tLogRow component, select the Table option in the Mode area
    so that the contents will be displayed in table cells on the console.

Use_Case_tSQLTemplateMerge5.png
  • Double-click the tSQLTemplateMerge component
    to display its Basic settings view.

Use_Case_tSQLTemplateMerge6.png
  • Type in the names of the source table and the target table in the relevant
    fields.

    In this scenario, the source table is new_customer_info,
    which contains eight records; the target table is
    customer_info_merge, which contains five records, and
    both tables have the same data structure.

Note:

The source table and the target table may have different schema structures. In
this case, however, make sure that the source column and target column specified in
each line of the Merge ON table, the UPDATE Columns table, and the INSERT Columns table are identical in data type and the target
column length allows the insertion of the data from the corresponding source
column.

  • Define the source schema manually, or select Repository from the Schema list
    and select the relevant table if the schema has already been defined and stored
    in the Metadata area of the Repository tree view.

    In this scenario, we use built-in schemas.

Use_Case_tSQLTemplateMerge7.png
  • Define the columns as shown above and click OK to close the schema dialog box, and do the same for the
    target schema.

  • Click the green plus button beneath the Merge
    ON
    table to add a line, and select the ID
    column as the primary key.

use_case-tsqltemplatemerge8half.png
  • Select the Use UPDATE check box to update
    existing data during the merge operation, and define the columns to be updated
    by clicking the green plus button and selecting the desired columns.

    In this scenario, we want to update all the columns according to the customer
    IDs. Therefore, we select all the columns except the ID
    column.

Warning:

The columns defined as the primary key CANNOT and MUST NOT be made subject
to updates.

  • Select the Specify UPDATE WHERE clause check
    box and type in customer_info_merge.ID >= 4 within double
    quotation marks in the WHERE clause field so
    that only those existing records with an ID equal to or greater than 4 will be
    updated.

use_case-tsqltemplatemerge8half2.png
  • Select the Use INSERT check box and define
    the columns to take data from and insert data to in the INSERT Columns table.

    In this example, we want to insert all the records that do not exist in the
    target table.

use_case-tsqltemplatemerge8half3.png
  • Select the SQL Template view to display and
    add the SQL templates to be used.

    By default, the SQLTemplateMerge component
    uses two system SQL templates: MergeUpdate and
    MergeInsert.

Note:

In the SQL Template tab, you can add system SQL
templates or create your own and use them within your Job to carry out the coded
operation. For more information, see tSQLTemplateFilterColumns Standard properties.

Use_Case_tSQLTemplateMerge9.png
  • Click the Add button to add a line and select
    Commit from the template list to commit the
    merge result to your database.

    Alternatively, you can connect the tSQLTemplateMerge component to a tSQLTemplateCommit or tMysqlCommit component using a Trigger > OnSubjobOK
    connection to commit the merge result to your database.

  • Save your Job and press F6 to run it.

    Both the original contents of the target table and the merge result are
    displayed on the console. In the target table, records No. 4 and No. 5 contain
    the updated information, and records No.6 through No. 8 contain the inserted
    information.

Use_Case_tSQLTemplateMerge10.png


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