Confused about tMysqlOutputBulkExec

One Star

Confused about tMysqlOutputBulkExec

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
Seventeen Stars

Re: Confused about tMysqlOutputBulkExec

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.
One Star

Re: Confused about tMysqlOutputBulkExec

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?

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads