One Star

How to trigger a Talend job based on the mysql insertion/updation

Hi All,
How to trigger a Talend job based on the mysql insertion/updation on a particular table.And Talend job will fetch those new/updated records and sync those into another database.
Can any once please suggest me on this.

28 REPLIES
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
Could you please take a look at talend CDC feature TalendHelpCenter:CDC+architectural+overview to see if it is satisfying your needs?
https://help.talend.com/display/KB/Change+Data+Capture+%28CDC%29+is+easy+with+Talend+Studio+-+v5.3.
Note: CDC feature is only available in Talend Enterprise Subscription Version(we have provided community user with a free trial of Talend Enterprise Data Integration: http://www.talend.com/enterprise-data-integration).
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.
Two Stars

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sayagoud,
For TOS workaround could be - Assume you are using some query at source to identify new insertion and another query to find updates to existing database table.
Use tReplicate component to replicate the insertion which will give another flow and use your second job/subjob within the job to insert data into the another database.
Carry same process for Update as well.
Thanks
Vaibhav
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
I followed the articles that you have suggested but I have few questions on CDC :
If any change made at source table,then CDC function will copy those changes into changes table.Now the changed data available to the subscribers.
1.But how talend job will identify those changes ?
2.how talend job will trigger automatically to sync those records into target system.
3.Do we need to manually run the talend job to fetch those updated records from the changes table? or is there any other way to achieve this?
Please suggest me on this.
Thanks in advance.
Sayagoud Ravelly.
Hi,
Could you please take a look at talend CDC feature TalendHelpCenter:CDC+architectural+overview to see if it is satisfying your needs?
https://help.talend.com/display/KB/Change+Data+Capture+%28CDC%29+is+easy+with+Talend+Studio+-+v5.3.
Note: CDC feature is only available in Talend Enterprise Subscription Version(we have provided community user with a free trial of Talend Enterprise Data Integration: http://www.talend.com/enterprise-data-integration).
Best regards
Sabrina
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
As matter of fact, talend CDC architecture is based on a publisher/subscriber model. The publisher captures the data changes and makes them available to the subscribers (Talend Jobs). Subscribers utilize the data changes obtained from the publisher.
It is a trigger or a DB log depends on what DB you are using, it closes to 100% real time. The changed data can be recorded in table when you take some action on you DB.
There is a sign-bit in subscriber table which will be set to 0 after reading record.
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: How to trigger a Talend job based on the mysql insertion/updation

Hi Vaibhav,
Thanks for your reply.
My question is how to identify the new changes at source table and sync those records into target table?
Talend job should automatically trigger and sync those new/updated records into target table.
Because running cron job for every minute will not work with my requirement for new changes.The moment when we create/update any record at source table then same changes needs to update at destination table also.
Thanks in advance,
Sayagoud Ravelly.
Hi Sayagoud,
For TOS workaround could be - Assume you are using some query at source to identify new insertion and another query to find updates to existing database table.
Use tReplicate component to replicate the insertion which will give another flow and use your second job/subjob within the job to insert data into the another database.
Carry same process for Update as well.
Thanks
Vaibhav
Two Stars

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
I think I have given some idea for this... isn't it useful???
>>>>
Use tReplicate component to replicate the insertion which will give another flow and use your second job/subjob within the job to insert data into the another database. Use same method for update as well.
This is one way to do it, there could be another ways to do same. If you don't like above one or have found some issue with this one, we can try to identify other ways.
Vaibhav
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
Can u please suggest me how talend job will trigger whenever a new change made in changes table?
If possible can you please provide me a workaround or a sample on this.
Thanks in advance
Sayagoud Ravelly
Hi,
As matter of fact, talend CDC architecture is based on a publisher/subscriber model. The publisher captures the data changes and makes them available to the subscribers (Talend Jobs). Subscribers utilize the data changes obtained from the publisher.
It is a trigger or a DB log depends on what DB you are using, it closes to 100% real time. The changed data can be recorded in table when you take some action on you DB.
There is a sign-bit in subscriber table which will be set to 0 after reading record.
Best regards
Sabrina
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
I want to trigger my job for every 10min. I'm using Ubuntu OS and Talend 5.5 version.
 My job which has to be scheduled is as follows
tFilelist---->tFileInputDelimited(Repository)---->tUnite--->tSortRow--->tAggregateRow--->tMysqlOutput
When new file is adding to tFilelist after trigger used the fieds from new file should not override the existing record, but should be update with it.
Could u please provide the solution to me.
Thanks in advance.
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Metta Soujanya,

With talend open source, you can set "Action on data" option as " Update or Insert" with one key in tMysqlOutput to update the existing record then export the job script, and then schedule it with third scheduler tool, such as crontab command on Linux, there are so many topics about scheduler in forum, just search them with the key word 'scheduler', you will find the answers and solutions.

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: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
Thanks for your reply
Ya I export the job and scheduled it with  crontab command as mentioned in forum.But I couldn't see the output.
And whem used crontab -l command I could not get list of scheduled job. It means My job scheduled or not.
1)If scheduled so where my output will be stored?
2) I actually wants to update the job output...Is it works?
Thank you
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Metta Soujanya,
Is your exported job working well when you execute .sh file directly in linux? Is there any update in your MySql table?

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: How to trigger a Talend job based on the mysql insertion/updation

HI Sabrina,
Thanks for reply
I execute .sh file (the exported job file) using crontab -e command and there is no update in my output
Note:I used tFileOutputDelimited, I'm not yet used tMysqlOutput(update/insert).
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
Is everything going well in studio(update works well)? Do your new file has the same schema structure with existed one? Would you mind uploading your processing workflow screenshots into forum so that we can see if your workflow works normally(all files can be listed by tfilelist).
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: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
My new file has the same schema structure with the existed files.
All files listed into the tFilelist only and used append also in tFileOutputDelimited.
If I run my job directly in the Talend studio I would get the output but while executing by job scheduling, could not get.
(Iam trying on sample files before directly trying on real time data)
Please solve my problem as soon as possible so that I'll work on real time data.
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
If job works well in studio, the work flow is correct. Is there any error info printed on commandline when execute exported job.sh file? What's your JDK version in Ubuntu OS?
Did you follow up document TalendHelpCenter:How+to+build+Jobs to export your job script? We cannot open the screenshot link you provided. Could you please check 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.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
In comand prompt also not getting any error. I used command in crontab as
8 16 23 7 2 /home/Desktop/SAMPLE_run.sh
And after when used command crontab -l,it shows like follows
# m h  dom mon dow   command
8 16 23 7 2 /home/user/SAMPLE/SAMPLE_run.sh
Does it scheduled or not?
I have to done my work by tomorrow..I'm trying a lot thats why posting in different links for your help.
Thank you.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

I followed Talend Help Center Exporting+a+Job+script+and+executing+it+outside+of+Talend+Studio) to export my job and schedule. Even though I'm unable to get output.
One thing, there is no option like  current time stamp in tInputDelimited Built In settings as mentioned in the tFixedFlowInput. 
Please provide the solution
Thank you
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Metta Soujanya,
What's your JDK version in Ubuntu OS? Regardless the cron trigger tool, is your .sh file working well for you(execute it directly without cron tool).
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: How to trigger a Talend job based on the mysql insertion/updation

My JDK version is "1.7.0_60". I execute .sh file using following commands
cd /path/to/file
chmod +x filename.sh
./filename.sh

It is not working well,error occuring.
Thank you.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
 
Please kindly reply to the my previous post..
Thanks in advance.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Metta Soujanya,
What is the error when you execute ./filename.sh ? Did you try to execute using ksh as ksh filename.sh ? Which shell are you on your Ubuntu server?
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
I'm using BASH shell. I'm executing SAMPLE_run.sh,
Error: Could not find or load main class beeline.sample_0_1.SAMPLE
Thank you.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sunil,
Thanks for your reply.
I'm using BASH shell. When executing SAMPLE_run.sh in command prompt the error is like be
Error: Could not find or load main class beeline.sample_0_1.SAMPLE
How could it avoid?
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Metta Soujanya,
Sorry for delay.
The error Could not find or load main class is usually caused the blank space in the installation path or wrong java environment variable setting. But you are not in this case, for your job run successfully in studio.
Could you please send your .zip file to us by emai(the address under my avatar) so that we can take a testing on 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.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
Thanks for your reply.
I couldn't find out option to send my .zip file to you from mail. Where that option resides to browse my file?
Please reply me...
Moderator

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi,
I have sent an email to your gmail Inbox, could you please check if it is available and give me a reply with your exported .zip file?
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: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
I've sent my .sh file to your mail...did u check it? if so reply me please. 
Thank you.
One Star

Re: How to trigger a Talend job based on the mysql insertion/updation

Hi Sabrina,
I send an mail to you and I'm waiting for your reply...
thank you,