to use tMySQLCDC component - does a CDC db need to be installed?

One Star

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?
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi,
Does a CDC database need to be installed prior to using tMySQLCDC component in jobs?

You don't need to install a CDC db. please give us your job flow and the related screenshot. Maybe something wrong with your setting.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

How do I upload images? I do not see any buttons or links to upload images on the Post Reply page.
Please advise. Thank you.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

You need to be logged in to upload images.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

When I hit 'Log In' at the top right hand corner of Talendforge page, it then shows my userID - lenfinkel (View/Edit mode). But when I go to view posts, do a search, etc... the msg at the top (right under Index, Tags, Timeline, etc... says "You are not logged in'. Is this a bug? What is the next step to be able to upload images?
Please advise. Thank you.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Could anyone assist with the issue?
Thank you.
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi,
This issue is known and confirmed, our colleagues from IT team are working on resolving the problem.

Best regards
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Community Manager

Re: to use tMySQLCDC component - does a CDC db need to be installed?

When I hit 'Log In' at the top right hand corner of Talendforge page, it then shows my userID - lenfinkel (View/Edit mode). But when I go to view posts, do a search, etc... the msg at the top (right under Index, Tags, Timeline, etc... says "You are not logged in'. Is this a bug? What is the next step to be able to upload images?
Please advise. Thank you.

It was a bug right now. This could be a solution, it works for other users who have same login issue.
Don't use IE to open Talend, go to Talendforge page
http://www.talendforge.org/
and login, then open Exchange page.
http://www.talendforge.org/exchange/index.php
return back to Talendforge page, and then Talend forum.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Sabrina,
Here are the snapshots of the CDC job flow, including error messages. Another question - does a "TCDC_" table need to be created prior to executing the job? Please see error msgs in the attached screenshots. Please let me know as soon as you can if you have enough information in attached screenshots to assist me so that I will be able to run CDC jobflows successfully. I would like to be able to execute this job today.
The error msg in the snapshot is:
2012-11-05 10:24:23|wegvks|wegvks|wegvks|ANALYTICS|Lenny_LoadStagingCurrency_Today2012Oct25_WithCDCs|Default|6|Java Exception|tMysqlCDC_7|com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Table 'tca_staging.TCDC_stagingcurrency' doesn't exist|1
disconnected
Job Lenny_LoadStagingCurrency_Today2012Oct25_WithCDCs ended at 10:24 05/11/2012.

Thank you,
- Lenny
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Here is a snapshot of the error message in the job flow - a more clear, readable image.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi,
From your job and error info, we doubt that you don't add cdc to table completely, please follows this tutorial to learn how to use cdc.http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=41. Sorry for the delay!
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi,
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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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.

Best regards
Sabirna
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

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.

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Thank you very much for the detailed information on using the CDC component. I will review it and let you know.
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi,
Welcome, if you need, i will send the zipped job to you by e-mail.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: to use tMySQLCDC component - does a CDC db need to be installed?

could you please send me demo you explained above. thank you.
Moderator

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi, umeshrakhe
I had tried to send a E-mail but it has been forbidden by organization of gmail-smtp-in.l.google.com. So would you mind providing other E-mail address or giving me a way to get rid of it?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: to use tMySQLCDC component - does a CDC db need to be installed?

Hi Sabrina,

Could you please send me demo job you explained above.

Thank you.


@xdshi wrote:
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