MySQL and bulk

One Star

MySQL and bulk

Hello,
i've got some troubles with Mysql in talend 5.0.1 Data integration Professional Edition.
I would like to insert or update my database. The tMySQLOutput components is awesome for that. But when you've got 100 k lines to "update or insert" (or "insert or update") it's kinda slow. I tried wit "Replace" and it duplicate lines :/
So i tried tMySQLOutputBulk and then tMySQLBulkExec. Faster!
But in "Advanced settings > Action on data" we have a set of intresting choices and... still no good.
- When i use a replace it act like an simple insert and duplicate lines
- When i use an update, it only update existing lines and ignores new ones
Any advices for speeding up that process?
Community Manager

Re: MySQL and bulk

Hi
tMysqlBulkExec component don't support the insert and update action at the same time, if you stick to use tMysqlBulkExec component, as a workaround, you can read the source file and do an inner join with target table on primary key, get the rows which need to be insert, and the rows which need to updated, and use two tMysqlBulkExec components for each action. For example:
tFileInputDelimited(read source file)--main--tMap--main--tMysqlBulkExec_1(for inserting)
| --main--tMysqlBulkExec_1(for updating)
lookup
|
tMysqlInput(read primary key from target table)
on tMap component, do an inner join on primary key, get the matched rows that need to be updated, get the unmatched rows that need to be inserted.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MySQL and bulk

Thank you!
That's a good idea. I'm going to try this.
I was thinking of that... Thanks to you it's a bit clear now.
One Star

Re: MySQL and bulk

Hi ,
The job which i have configured has to load 6 crore and contains 43 col into a mysql table ,the flow is as follows
tFileInputDelimited--->tmap--->tSort--->tUniq---->tMysqlOutputBulkExec
The job takes around 3 and half hours to load into table . Can anyone tell how to speed up the job and reduce time of execution.
Community Manager

Re: MySQL and bulk

Hi ,
The job which i have configured has to load 6 crore and contains 43 col into a mysql table ,the flow is as follows
tFileInputDelimited--->tmap--->tSort--->tUniq---->tMysqlOutputBulkExec
The job takes around 3 and half hours to load into table . Can anyone tell how to speed up the job and reduce time of execution.

A quick question, what about the performance if you remove tSort--->tUniq from your job?
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MySQL and bulk

hi shong,

The time it took to load is almost same without tSort and tUniq
One Star

Re: MySQL and bulk

Hi
Any suggestions on reducing time of execution of job while loading into mysql table

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads