Import data into MySQL using Talend Open Studio

Question

I have been trying to import CSV data about 60 MB; it contains about 400,000 rows, which should not be too much. I tried these ways to import the data:

1. MySQL Workbench - it took about two hours!!!
2. Using MySQL LOAD INFILE command - it took about 15 seconds!
3. Using Talend Open Studio - almost as long as Workbench.

Why are the GUI tools working so badly? I want to use GUI-based tools, instead of writing scripts.

With Talend Open Studio, is there a way to speed things up? I heard that there is possibly another Talend product that deals with Big Data, will this do it faster than Talend Open Studio?

Hope someone out there knows!

I know that in Hadoop using Pig, I can get the the CSV from HDFS into Pig memory very quickly.

• Rick James: I can't explain the specifics you are seeing, but ... Serious programming demands serious tools, not convenience tools sudy as UIs.
• Palu: Hi Rick, thanks for your reply. BUT isn't Talend Open Studio considered a powerful tool for this kind of task. Its one of the big ETL tools from the Open Source world. If that isn't a serious tool, then what would be the proper 'serious' tool?
• a_vlad: @RickJames - Talend really one of the best ETL tools
• Nawaz Sohail: please go through hope it helps..nwazsohail.blogspot.com/2016/10/…

Answer 1

What parameters are You use for Talend tMySQLOutput? What MySQL settings? It could not be faster then INFILE, but still really depend from settings.

You can post Your question on TelndForge forum with screen short of Job components, will try to help

For example one of my regular Talend-MySQL Jobs (billing prepare) - transfer from->to mysql 2M of records, 400Mb, it take 6-15 minutes depending from servers (we have 2 configuration)

edit, because always better 1 time to show, than 100 times explain

2.3M rows, 300Mb file, speed of tMySQL with 100 rows per insert: 100 rows per insert

same with 10 000 rows per insert 10 000 rows per insert

tBulkOutputExec: twice faster than previous

but then total time: original 10 000 rows: original

bulk: bulk just because after execution it wait commit transaction, single transaction

• Palu: Hi a_vlad, thanks for your response and also being a user of Talend. I just started using Talend, so I am not sure what parameters would need to be set. In the TMap I just mapped the fields between the CSV and mysql database. In the tMySQLOutput I was able to verify the connection to the MySQL database. Other then that I don't know what other settings I would need.
• a_vlad: Hi Palu, first of all - in tMySQL output - did You use Insert or Insert/Update? second - what size of Batch (commit every), third - settings of MySQL also could be affected (InnoDB redo log size for example)
• Palu: Hi there, I was away for a while. Thanks for all your analysis and suggestions. I will go back and try things out and get back on this. Thanks for your super efforts both you Vlad and Dillon.

Answer 2

First of all what output component are you using? The speeds you are suggesting seem like you are using Tmysqloutput. This is very slow because it is designed to write line by line. Try using Tmysqloutputbulkexecute. This collects the data in a bulk file then uploads the whole thing at once and then commits it.

enter image description here

• a_vlad: BulkExec is faster, but even with normal - just check last Job - 300Mb, 2M of rows, 95sec by normal tMySQLInput (10 000 rows batch, 300 sec with 100 in batch), with BulkExec 80sec, in Our case - not important and it exactly not more than 2Hr as described, problem somewhere other place
• Dillon Wright: we won't know until he troubleshoots the issue or at least tells us more about his issue my answer gives him some options on where he can continue to test and at least identify the issue is not with the particular output component he is using just because something works for you and your use case does not mean it would work for his in particular a data base on a high latency connection will do very poorly on the first component but one with a low latency connection will perform much better
• a_vlad: that easy could be network issues or any other, but not tMySQL component issue - see pictures above (taken from every day operation)
• Palu: Hi, I know its late. But I did tryout using the command line way of importing text into MySQL and took less than a minute. SO not sure where the overhead is in TalendStudio. Now I have a question, can talend studio be used to execute a batch script instead, that would be one way to use a GUI tool and not have to execute things at the command line.
• Dillon Wright: you can always use the TSystem component to run via cmd prompt although i am puzzled as to the overhead on your job did you attempt to use the tMysqloutputbulkexec component?