Scenario: Extracting and synchronizing data changes using XStream mode - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This scenario describes a Job that inserts data into an empty source table PERSON in one Oracle database on which the XStream outbound server is configured, then extracts and synchronizes the data changes into an empty target table PERSON_BAK in another Oracle database on which the XStream inbound server is configured using Oracle CDC XStream mode, and finally displays the data in the source and target tables on the console.

Prerequisites: The Oracle database connections dedicated to the XStream outbound and inbound servers have been set up in the Repository tree view in the Studio, and the CDC has been added to the empty source and target tables. For more information about how to set up an Oracle database connection and configure CDC in Oracle XStream mode, see Talend Studio User Guide.

Dropping and linking the components

  1. Create a new Job and add the following components by typing their names in the design workspace or dropping them from the Palette: a tFixedFlowInput component, a tOracleOutput component, a tOracleCDC component, a tOracleCDCOutput component, two tOracleInput components, and two tLogRow components.

  2. Connect tFixedFlowInput to tOracleOutput using a Row > Main link.

  3. Do the same to connect tOracleCDC to tOracleCDCOutput, the first tOracleInput to the first tLogRow, and the second tOracleInput to the second tLogRow.

  4. Connect tFixedFlowInput to tOracleCDC using a Trigger > On Subjob Ok connection.

  5. Do the same to connect tOracleCDC to the first tOracleInput and the first tOracleInput to the second tOracleInput.

Configuring the components

Inserting data into the source table

  1. Double-click tFixedFlowInput to open its Basic settings view.

  2. Click the [...] button next to Edit schema to open the schema editor.

    Click four times the [+] button to add four columns: id and age of the integer type, name and sex of the string type.

    Click OK to close the schema editor and accept the propagation prompted by the pop-up dialog box.

  3. Select Use Inline Content(delimited file) in the Mode area, and then enter the following input data in the Content field:

    1;Ford;Male;25
    2;Rose;Female;30
    3;Sabrina;Female;28
    4;Teddy;Male;32
    5;Kate;Male;35
  4. Double-click tOracleOutput to open its Basic settings view.

  5. Set Property Type to Repository and select the connection metadata corresponding to the Oracle database on which the XStream outbound server is configured, Oracle12_XStreamOut in this example. The connection type, database version and other connection details information will be completed automatically.

  6. In the Table field, enter the name of the table into which the input data will be inserted. In this example, it is PERSON.

  7. Click the [...] button next to Edit schema to open the schema editor to ensure the schema has been synced up.

Extracting and synchronizing data changes to the target table

  1. Double-click tOracleCDC to open its Basic settings view.

  2. Set Property of the CDC connection to Repository and select the connection metadata corresponding to the Oracle database on which the XStream outbound server is configured, Oracle12_XStreamOut in this example. The connection type, database version and other connection details information will be completed automatically.

  3. Select LCR Object from the Generate LCR type drop-down list.

    In the Table using CDC field, enter the name of the source table from which changes made to data are to be captured. In this example, it is PERSON.

    In the Outbound server name field, enter the XStream outbound server name. In this example, it is xout.

  4. Click the [...] button next to Edit schema to open the schema editor, and you will see that the schema has been set by default.

  5. Click Advanced settings to open its view.

    In the Idle timeout(seconds) field, enter the number of seconds of idle time that the outbound server waits for an LCR before terminating the call to get or receive an LCR stream. Considering the network latency and the database response rate, the idle time is set to 60 seconds in this example.

  6. Double-click tOracleCDCOutput to open its Basic settings view.

    Set Property of the CDC connection to Repository and select the connection metadata corresponding to the Oracle database on which the XStream inbound server is configured, Oracle12_XStreamIn in this example. The database version and other connection details information will be completed automatically.

  7. In the Inbound server name field, enter the XStream inbound server name. In this example, it is xin.

    Select the Set data table schema check box and specify the schema of the CDC table. In this example, it is TALEND.

    Select the Set target table check box and specify the table to which data changes from the inbound server will be replicated. In this example, it is PERSON_BAK.

Retrieving data from the source and target table

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

  2. Set Property Type to Repository and select the connection metadata corresponding to the Oracle database on which the XStream outbound server is configured, Oracle12_XStreamOut in this example. The connection type, database version and other connection details information will be completed automatically.

    Set Schema to Repository and select the source table from the Repository. In this example, it is PERSON.

  3. In the Query field, enter the following SQL statement to retrieve all the data from the source table:

    select * from PERSON
  4. Double-click the second tOracleInput to open its Basic settings view.

  5. Set Property Type to Repository and select the connection metadata corresponding to the Oracle database on which the XStream inbound server is configured, Oracle12_XStreamIn in this example. The connection type, database version and other connection details information will be completed automatically.

    Set Schema to Repository and select the target table from the Repository. In this example, it is PERSON_BAK.

  6. In the Query field, enter the following SQL statement to retrieve all the data from the target table:

    select * from PERSON_BAK
  7. Double-click the first tLogRow to open its Basic settings view.

    In the Mode area, select Table (print values in cells of a table) for a better display of the result.

  8. Do the same to configure the second tLogRow.

Saving and executing the Job

  1. Press Ctrl + S to save the Job.

  2. Press F6 to run the Job.

    As shown above, the input data is inserted into the source table and then the data changes are synchronized into the target table.