to use tMySQLCDC component - does a CDC db need to be installed?
Hello, I am new to Talend Enterprise Data Integration. I get an error msg: "Unknown column 'TALEND_CDC_STATE' in 'where clause' " when trying to use tMySQLCDC component in a job. I do not see TALEND_CDC_STATE column anywhere in the tables that I want to capture CDC. Does a CDC database need to be installed prior to using tMySQLCDC component in jobs? If yes, please point me to documentation describing how to install the CDC database on MySQL. Is it easy to install and to configure?
I have provided snapshots of the job flow and the error message that I am getting - as you have asked, but haven't heard back from anyone. Could you please assist? This is quite urgent. I nwould like to resolve the issue to be able to execute a job flow with CDC successfully.
Thank you,
Lenny
Hi,
So you are saying that I do need to create a CDC database to be able to use CDC component in a job - it is required, right? I have seen the tutorial that you have provided - thank you. However, it does not specify what type of schema, tables, etc. needs to be created in the "CDCOracle" database. Is it only a matter of simply creating a database and that is it? If not, could you please provide more specific details? As of now, I still do not know what exactly needs to be done to be able to run a job using CDC components.
Thank you,
Lenny
Is it only a matter of simply creating a database and that is it?
Sorry for that, we don't have much more tutorial about CDC component. Once we have, will inform you asap! I will consult to our component team and Documentation team for further info.
Best regards
Sabrina
If it is only a matter of simply creating a CDC database, then do I need to know what type of schema, tables, etc. needs to be created in the "CDCOracle" database? Otherwise, I am unable to use the CDC component in jobs. Could someone from your team provide this information? Thank you, Lenny
Hi,
Sorry for the delay. For your confusion, i have create a demo job using mysql DB to explain how to use the CDC connenction .
Step 1: Populating a data warehouse
The following Java scenario creates a three-component Job that populates a data warehouse. A tMysqlInput component reads your test data stored in the test base. A tMap component allows you to modify this data and the modifications are transmitted to the crm table in the CRM database through a tMysqlOutput component.
Drop the following components from the Palette onto the design workspace: tMysqlInput, tMap, and tMysqlOutput.
Connect the three components using Row Main links. see pic1
In the design workspace, select tMysqlInput and click the Component tab to define its basic settings. pic2
Set Property Type to Repository and then select the connection to the test database that holds the information about your clients. The connection details will display automatically in the corresponding fields.
Note
If you have not stored the DB connection details in the Metadata entry in the Repository, select Built-in in the property type list and set the connection details manually.
Set Schema to Repository and click the three-dot button to select the schema of the test database stored in the Metadata entry.
In the Table Name field, enter the name of the table holding the information you want to modify, in this example: test
Click Guess Query to retrieve all data from your table.
Double-click the tMap component to open the Map Editor. Notice that the Input area is already filled with the metadata of the input component. pic3
drag the fields in the input zone to the fields in the test table in the output zone. For more information regarding data mapping
Click OK to validate the operation.
In the design workspace, select tMySqlOutput and click the Component tab to define its basic settings.pic4
Waiting, i will send the step 2 and step3
Best regards
Sabrina
Hi,
Step 2: Configuring CDC
Before being able to retrieve modified data from the CRM data warehouse, you must:
first set up the DB connection dedicated to CDC,
second, set up a DB connection to the source data and identify the table to catch,
finally, set the connection between the CDC and the data.
To do that:
In the Repository tree view and under Metadata, create a connection to your database dedicated to CDC, in this scenario CDC_connection.
Note
Ensure that the DB connection for CDC is on the same server with the source data to which changes are to be captured.
In the Repository tree view and under Metadata, create a connection to the source data warehouse and identify the table to catch, in this scenario CRM.
Right-click the CRM and select Retrieve schema from the drop-down menu to retrieve the schema of the table to catch.
Right-click CDC Foundation of CRM and select Create CDC in the drop-down menu.
The dialog box displays
In the Set link Connection field, select CDC_connection.
Click Create Subscriber. The dialog box displays.
Click Execute and then Close.
Click Finish to validate the creation of the subscriber table.
In the CDC Foundation folder, the relevant subscriber table displays.
You must specify which table the subscriber wants to subscribe to and then activate the subscription. To do that:
Right-click the "crm" schema in the source CRM and select Add CDC in the drop-down list. The dialog box displays.
In the Events to catch check boxes, select Insert, Update and Delete to catch inserted, updated or deleted data.
In the Subscriber Name field, enter the name of the subscriber that will have access to the modifications, in this scenario Sub_Mktg for the Marketing department.
Click Execute and then Close to validate the subscription.
In the CDC Foundation folder, the two created tables display and the schema node of the catched table is marked with a green CDC symbol.
Hi,
Step3:
Modify the data of your customers in your CRM, for example, convert all customer names to upper case.
Double-click the tMap component and enter row1.CUST_NAME.toUpperCase()in front of the CustomerName column to convert all customer names to upper case.
Click Ok.
Double-click the tMysqlOutput component.
In the Action on table field, select None.
In the Action on data field, select Insert or update to insert or update table data.
Save your job and press F6 to execute the job.
To view all changes done on data, right-click the CRM table and select View All Changes to open the relevant dialog box.
After setting up the CDC environment, you can now design a job using the Mysql CDC component to incrementally extract the change data from the Leadfact table. To do that:
From the Palette, drop the tMysqlCDC and tLogRow components to the design workspace.
Link the two components using a Row Main link.
Double-click the tMysqlCDC component to define its properties.
Set Property Type to Repository and then select the select the schema corresponding to your Mysql DB table, CDC_connection in this scenario. The connection details will display automatically in the corresponding fields
Note
If you have not stored the CRM data warehouse connection details in the Metadata entry in the Repository, select Built-in in the property type list and set the connection details manually.
In the Schema using CDC field, select Repository and then select the schema of the crm table stored in the Metadata entry.
In the Table using CDC field, enter the name of the table captured by the CDC, in this scenario crm .
In the Subscriber field, enter the name of the subscriber that will extract modified data, sub-crm
Double-click the tLogRow component to set is properties.
The customer names are converted to upper case and the modification type displays here is U to stand for Update.
Once these modifications are extracted, they are no more available in the modified table. To verify their extraction, right-click the crm table catched by the CDC and then select Views All Changes. The extracted changes do not display anymore.
Hope that will help you!
Best regards
Sabrina
So you are saying that I do need to create a CDC database to be able to use CDC component in a job - it is required, right? I have seen the tutorial that you have provided - thank you. However, it does not specify what type of schema, tables, etc. needs to be created in the "CDCOracle" database. Is it only a matter of simply creating a database and that is it? If not, could you please provide more specific details? As of now, I still do not know what exactly needs to be done to be able to run a job using CDC components.
The schema and table depend on your needs. for example, my job is :
CUST_ID;CUST_NAME
1;john;
2;mary;
The table name is created by yourself without any limitation.
Best regards
Sabrina
