How to use Change Data Capture in Oracle (Step 2/4)
Languages available:
Learn how to implement a Change Data Capture (CDC) in Oracle
In this tutorial, you will learn how to design a Job to generate the data representing the customers (ID, Name, Forename, Age) and to simultaneous load the data into the DATA and DWH databases to simulate the data synchronization.
Configure the tOracleOutput components as the screenshot, modifying the connection data for your environment (Username =DATA, Table = customers, Action on table = Drop table if exists and create...).
Click Sync Columns button to retrieve the tRowGenerator schema.
Configure the tOracleOutput components as the screenshot, modifying the connection data for your environment (Username =DWH, Table = customers, Action on table = Drop table if exists and create...).
Click Sync Columns button to retrieve the tRowGenerator schema.
We will now implement the CDC, creating a subscriber.
Click Metadata > Db Connections > CDC_Oracle > DATAOracle. Right-click CDC Foundation and select Create CDC.
In the Set Link Connection field, select DB (ORACLE_SID): CDCOracle, it allows you to create a link between the tables in the DATA database and those in the CDC database.
Click Metadata > DB Connections > CDC_Oracle > DATAOracle > Table schemas > CUSTOMERS and select Create CDC.
In the new window, check the Insert, Update and Delete boxes and name the subscriber dwh-customers.
Click Execute. This allows you to add a row to the TSUBSCRIBERS table of the CDC databasecontaining the name of the subscriber, the table to be monitored and the creation date.
The tcdc_customers table has been created in the Oracle database. It contains four columns, plus the column which represent the primary key.
- TALEND_CDC_SUSCRIBERS_NAME represents the DWH_Customers subscriber that you just created. - TALEND_CDC_STATE is set to "0" if the modification has been consumed by the subscriber, and "1" if not. - TALEND_CDC_TYPE represents the type of modification that has been carried out: "I" for Insert, "U" for Update, "D" for Delete. - TALEND_CDC_CREATION_DATE represents the date of the modification. - ID represents the data which has been modified, and is identified by its primary key.
The schema of the tcdc_view_customers view
contains the columns:
TALEND_CDC_TYPE, TALEND_CDC_SUSCRIBERS_NAME, TALEND_CDC_CREATION_DATE, ID, NAME, FIRSTNAME and AGE.
The first three columns have the same role as before. The four others represent the Customers table. It allows you to take a snapshot of the modifications at a given moment.
