topic Re: Confused about tMysqlOutputBulkExec in Talend Studio

Confused about tMysqlOutputBulkExec

Anonymous — Fri, 31 Jan 2014 15:34:20 GMT

Hi all!
The tMysqlOutputBulkExec has the following settings under advanced:
Action on data:
- Insert records into table
- Replace records in table
- Update records in table
- Ignore records in table
I came across this component to fill my fact tables, because tMysqlOutput's performance was not satisfying. I expected, that when I use "Update records in table", the component would do a Update or Insert like tMysqlOutput does. But it seems that it does not insert any data unless I use "Insert records into table".
Now my questions:
1. Is tMysqlOutputBulkExec the right component at all for this job? Updates in fact-table are rare, but possible in our case...
2. What is the difference between these tMysqlOutput modes: "Insert or Update", "Update or Insert", "Insert or Update on dublicated key or unique index"?
Kind regards

Re: Confused about tMysqlOutputBulkExec

Anonymous — Fri, 31 Jan 2014 22:23:01 GMT

This component reflects only the capabilities of the MySQL function bulk load, nothing else. You can use this bulk load but I would never try to write into a table with existing data. Bulk load has also a very weak error handling, you have to check log files and even the content in the table.
I suggest loading you data into a staging table with the replace records option and move these data with SQL methods into your target tables.

Re: Confused about tMysqlOutputBulkExec

Anonymous — Mon, 03 Feb 2014 10:48:17 GMT

This component reflects only the capabilities of the MySQL function bulk load, nothing else. You can use this bulk load but I would never try to write into a table with existing data. Bulk load has also a very weak error handling, you have to check log files and even the content in the table.
I suggest loading you data into a staging table with the replace records option and move these data with SQL methods into your target tables.

Hi jlolling,
thanks for your feedback.
Do you think using a MysqlOutput with UpdateOrInsert can be considered as "save"? It might be much slower but at least one does not has to implement the Loading-Step from a staging Table to the DWH.
So actually, one of my Jobs to fill a fact-table looks like this now:

I get all the data from a sybase table, then in each tMap component I replace some columns by joining against a dimension table until only the facts and dimension keys remain. These I load into a Facttable by using Update or Insert.
Do you think this is good practise?