| | |
|
Synchronize the Customers table in the DATA database with the one in the DWH database.
We will now update manually the CUSTOMERS table in your data warehouse. To do so, you will create a job handling three data flows, allowing you to update the CUSTOMERS table in the DWH database with the following operations:
Next
|
 |
| | |
 |
 | Step 1: Create the Job
|
In the Repository :
Right-click on Job Designs.
In the menu, click on Create job to open the New Job wizard. In the New Job wizard: In the Name field, fill in the name of the Job: SyncDWH. Click Finish to close the wizard and create the Job. The Job Designer opens an empty Job. |
 |
In the Repository: Click on the Metadata > Db Connections > CDC_Oracle. Click the CDCOracle metadata and drop it on the Job Designer. Select tOracleCDC in the [Components] dialog box and click OK. |
 |
In the Job Designer:
Double-click CDCOracle to show the corresponding Component view to define its Basic settings.
In the Component view:
The connection details are already filled in.
Check the Delete box to retrieve the deleted data.
|
 |
In the Repository: Click Metadata > Db Connections > CDC_Oracle > DWHOracle. Click the DWHOracle metadata and drop it on the Job Designer. Select tOracleOutput in the [Components] dialog box and click on OK. Click the name of the component and change it to CUSTOMERS. |
 |
In the Job Designer:
Double-click CUSTOMERS to show the corresponding Component view to define its Basic settings.
In the Component view:
The connection details to the CDC database are already filled in.
In the Action on Data field, select Delete to delete the rows in the CUSTOMERS table of the data warehouse.
|
 |
In the Palette: To add the tMap component, click the Proccessing family.
Click the tMap component and drop it on the Job Designer. |
 |
In the Job Designer:
To link the components, right-click on CDCOracle, hold and drag to the tMap. Click the link name to change it to delete.
Do the same thing to link the tMap to the tOracleOutput (CUSTOMERS). Rename the link customers2.
|
 |
In the Job Designer:
Double-click on the first tMap component (tMap_1) to open the tMap editor.
In the tMap editor:
Click the white arrow on customers2 table to display the filter fields.
In the customers2 table, press Ctrl+Space in the filter field, click delete.TALEND_CDC_TYPE in the autocompletion list and type the Java syntax .equals("D")
Click OK. |
 |
 | Step 2: Create a subjob for each action type
|
Create two identical subjobs and rename their links.
To do so, click on the name and change it:
Rename the second input link update, the second output link customers3. Rename the third link insert and the third output link customers1.
|
 |
In the Job Designer:
To link the subjobs, right-click the first tCDCOracle component, select Trigger > On SubjobOk, drag to the tCDCOracle below and click it.
Do the same thing to link the second subjob to the third subjob.
|
 |
In the Job Designer:
Double-click tOracleCDC_3 to show the corresponding Component view to define its Basic settings.
In the Component view:
The connection details to the CDC database are already filled in.
Check the Update box.
|
 |
In the Job Designer:
Double-click on the second tMap component (tMap_2) to open the tMap editor.
In the tMap editor:
Click the white arrow on customers3 table to display the filter fields.
In the customers3 table, press Ctrl+Space in the filter field, click update.TALEND_CDC_TYPE in the autocompletion list and type the Java syntax .equals("U")
Click OK. |
 |
In the Job Designer:
Double-click CUSTOMERS to show the corresponding Component view to define its Basic settings.
In the Component view:
The connection details to the DWH database are already filled in. In the Action on Data field, select Update to update the CUSTOMERS table.
|
 |
In the Job Designer:
Double-click tOracleCDC_4 to show the corresponding Component view to define its Basic settings.
In the Component view:
The connection details to the CDC database are already filled in.
Check the Insert box. |
 |
In the Job Designer:
Double-click the second tMap component (tMap_3) to open the tMap editor.
In the tMap editor:
Click the white arrow on customers3 table to display the filter fields.
In
the customers1 table, press Ctrl+Space in the filter field, click insert.TALEND_CDC_TYPE in
the autocompletion list and type the Java syntax .equals("I").
Click OK. |
 |
In the Job Designer:
Double-click CUSTOMERS to show the corresponding Component view to define its Basic settings.
In the Component view:
The connection details to the DWH database are already filled in.
In the Action on Data field, select Insert to add the row previously created.
|
 |
 | Step 3: Synchronization of data and consumption of changes
|
In the Run view, check the Statistics box and execute the Job to observe the number of lines that are inserted, deleted or updated. |
 |
The job has consumed the CDC - i.e. the TALEND_CDC_STATE field in the tCDC_Customers table have been changed from 0 to 1.
If you execute the job again, no rows should be added, since the CUSTOMERS tables in the DWH and DATA databases are synchronized.
You can check manually that the two tables are identical.
|
