Hi,
a newbie in Talend need your help. I am using TOS 3.2.2 to import data from xml files in a table in MSSQL. I want to do following:
1/ if the data does not exist in table, do an insert
2/ otherwise, do an update if and ony if any values have been changed.
By using tMap component and Lookup i can distinguish between insert and update (Looking up for primary keys of the table). But how can i tell Talend to do an update only if the values of fields have been changed. Thank you very much for any advise.
Hi, thongMobi..
In the tMSSQLOutput there are options for table (Action on table) and rows (Action on data).
From your post i assumed you use inner join on the primary key fields and create 2 outputs (inner join data & inner join rejects data). The matched joins data shall be updated and the unmatched data shall be inserted into the table right?
So, you can use the options i mentioned earlier. For matched data, use Action on data = update and for unmatched data, use Action on data = insert. The Action on data = update will check the primary keys and then check for any changes in each fields of the row. It will then update the changed fields only. It's advisable that you add an extra field in you table (date_updated) and use getCurrentDate functions so you can have a better tracking on your updated data..
Hope this will help you mate.. (^_^)
Hi, thongMobi.. In the tMSSQLOutput there are options for table (Action on table) and rows (Action on data). From your post i assumed you use inner join on the primary key fields and create 2 outputs (inner join data & inner join rejects data). The matched joins data shall be updated and the unmatched data shall be inserted into the table right? So, you can use the options i mentioned earlier. For matched data, use Action on data = update and for unmatched data, use Action on data = insert. The Action on data = update will check the primary keys and then check for any changes in each fields of the row. It will then update the changed fields only. It's advisable that you add an extra field in you table (date_updated) and use getCurrentDate functions so you can have a better tracking on your updated data.. Hope this will help you mate.. (^_^)
Hi alucard,
thanks for your post. It helps a lots, really.
> The Action on data = update will check the primary keys and then check for any changes in each fields of the row. It will then update the changed fields only.
How could i do it? Should i do it in tMap component or on the tMSSQLOutput?. Could you please upload an simple example? Thank you
Hello thongMobi
No the "action on data" option wont change only the updated values, actually it will update the whole row even if no change occured.
I f you want to update only the updated records, you should set up a CDC (stand for Change Data Capture) process in your job. To sum it up : all the change in your source database will be save in a log DB (column X : new value : ...) or log file. Then your jobs will read that log DB and update the target database only with the changes.
Take a look on
http://en.wikipedia.org/wiki/Change_data_capture to have a good summary about CDC
Depending on your software but many databases vendors have integrated CDC system in their products.
Hello camsellem,
thank you for your nice post. Well, i have heard about CDC but they are not part of TOS, aren't they. In this case i don't have a source database. I just have a xml file and it is no way to use CDC. Anyway it is interesting to know if there is any workaround to solve this problem in TOS?
Hello thongMobi No the "action on data" option wont change only the updated values, actually it will update the whole row even if no change occured. I f you want to update only the updated records, you should set up a CDC (stand for Change Data Capture) process in your job. To sum it up : all the change in your source database will be save in a log DB (column X : new value : ...) or log file. Then your jobs will read that log DB and update the target database only with the changes. Take a look on http://en.wikipedia.org/wiki/Change_data_capture to have a good resume about CDC Depending on your software but many databases vendors have integrated CDC system in their products.
A workaround would be to make a job to compare your file and the last one which was loaded in the database. The delta will contain the data to update or insert. But honestly this kind of comparaison is really hard to do...
Edit : take a look on this OpenSource projet JXyDiff it might help you
http://potiron.loria.fr/projects/jxydiff
A workaround would be to make a job to compare your file and the last one which was loaded in the database. The delta will contain the data to update or insert. But honestly this kind of comparaison is really hard to do... Edit : take a look on this OpenSource projet JXyDiff it might help you http://potiron.loria.fr/projects/jxydiff
Hi,
thank you for the info. I have tried to solve this with tMap components and LookUp (see screenshot). In tMap_1 i have to look up for the primary keys. The unmatched datas will be inserted into table. The matched datas should go through the tMap_2 component which are looked up for all fields. For the unmached datas an update should be done. Well, that all is just my idea. The problem why i could not connect the tReplicate component to the tMap_2 to do a lookup. Is it impossible in TOS? I hope to hear something from Talend experts. Thank you in advance.
Hi all,
i have solved this problem by setting the filter expression for the update output. Now it does an update only if any values have been changed. TOS is really powerful. Thank you for all yourd helps
Best regards
Thong
Hi thongMobi,
Can you share some snapshots so that we can see how you have implemented it?
Other members who have any other suggestions to above problem, please reply. Thanks.
Best regards
Kshitij
