topic Re: Performing insert/update/delete together for a target table in Talend Studio

Performing insert/update/delete together for a target table

Anonymous — Thu, 02 Mar 2017 07:45:05 GMT

Hi,
I have a job wherein I'm transferring data from sql server to mysql. Job runs every 10 minutes. I want the rows to be deleted/inserted/updated in mysql as per the changes in sql server.
Do I have to make 2 separate outputs from tmap to tmsqloutput? One for 'Insert/Update' and the other one for 'Delete'? If yes, then would there not be lock in the table due to simultaneous insert/update/delete?
Any idea on this would be helpful.
Thanks in advance
Rathi

Re: Performing insert/update/delete together for a target table

Anonymous — Fri, 03 Mar 2017 03:25:59 GMT

Hi,
With community version, there is a only way to achieve your goal. Matching the table and comparing all records to conclude the needed inserted records, updated records, and deleted records.

If you want to capture the changed data and only load these changed data into target table to achieve table sync, you can compare tables by using tMap.
The work flow should be: Target DB-->tMap(make inner join on your input and set the "Catch lookup inner join reject" as true)-->output
Source DB-->
The output will be the changed data.

With Talend Enterprise Subscription Version, there is a CDC (Change Data Capture) feature which can achieve data sync, however, it only take action in one DB.

Best regards
Sabrina

Re: Performing insert/update/delete together for a target table

Anonymous — Mon, 06 Mar 2017 05:54:50 GMT

Hi Sabrina,
Thanks for the response. I am still unable to understand how do I do a insert/update/delete in one shot? Because there is 'Insert/update' and 'Delete'. How do I put these together?
I understand Enterprise, gives CDC. But I am supposed to do this in TOS itself
Thanks
Rathi

Re: Performing insert/update/delete together for a target table

vapukov — Mon, 06 Mar 2017 06:15:04 GMT

Hi Sabrina,
Thanks for the response. I am still unable to understand how do I do a insert/update/delete in one shot? Because there is 'Insert/update' and 'Delete'. How do I put these together?
I understand Enterprise, gives CDC. But I am supposed to do this in TOS itself
Thanks
Rathi

You can not do this in one step, or parallel as mentioned in original question
You must do this as sequence as combination main and lookup flows + tMap JOIN
or You can load data to temporary table and manage all by SQL code

Re: Performing insert/update/delete together for a target table

Anonymous — Mon, 06 Mar 2017 06:29:17 GMT

Yes I understand it must be done in sequence. So, the flow,
tmssqlinput --> tmap --> tmysqloutput(insertupdate)
|--> tmysqloutput (delete)
My question is tmap is outputting, both insert/update and delete at the same time. How will the table handle this simultaneously?
Thanks
Rathi

Re: Performing insert/update/delete together for a target table

vapukov — Mon, 06 Mar 2017 06:41:53 GMT

Yes I understand it must be done in sequence. So, the flow,
tmssqlinput --> tmap --> tmysqloutput(insertupdate)
|--> tmysqloutput (delete)
My question is tmap is outputting, both insert/update and delete at the same time. How will the table handle this simultaneously?
Thanks
Rathi

do not know - You can test
I use always other ways:

collect changes (when it possible), and then apply to target - it is best method. In my case it possible when we have 2 timestamps on database and soft delete (like 'deleted' == true), by timestamp very easy to understand - new records or edited from last iteration
use table on target database as mean above and call stored procedure to do this, benefits - use indexes and fast.
make a 3 separate step (not from 1 tMap, but 3 separate tMap and 3 query), if data not huge:

check records for UPDATE - and update all what possible
check new records - INSERT them
check what need delete - and delete them

why 3?
because in this case You can use batch mode for UPDATE/INSERT/DELETE and total speed much faster even after 3 query over databases

Re: Performing insert/update/delete together for a target table

Anonymous — Mon, 06 Mar 2017 07:04:37 GMT

Ok. Maybe I can use 'Update/Insert' and then 'Delete'. How do I design the job to run 'Delete' after 'Update/Insert'?
I know sql procedures can accomplish this.
But, I want to know how to do it in Talend. To call Delete after insert/update?
Thanks