topic large csv file import in Talend Studio

large csv file import

Anonymous — Thu, 19 Sep 2019 11:01:15 GMT

I have many large files, each one contains more than 2 Mil rows of .CVS data.

I need to import gradually into MySQL table because I cannot load data in CVS file into memory.

Could you guide me which should use to do that?

I try to use tFileInputDelimited component, but it take out all file data and consume all my lap memory

Re: large csv file import

manodwhb — Thu, 19 Sep 2019 11:09:12 GMT

@phancongphuoc ,if you are getting memory error ,you can increase the JVM by using the below link.

https://community.talend.com/t5/Installing-and-Upgrading/Configure-to-use-a-JVM/td-p/112893

you can try to use the tMySqlBulkExec component to improve the performance. to know more about tMySqlBulkExec find the below link.

https://help.talend.com/reader/jomWd_GKqAmTZviwG_oxHQ/YhYqawgnulVXpdzE1lJ6cg

Re: large csv file import

TRF — Thu, 19 Sep 2019 12:52:42 GMT

You may also have a loop over your tFileInputDelimited with the Limit parameter setted to the max value your are able to manage at the same time (for example 250,000) and set the Header parameter dynamically based on the loop indice to ignore previously imported rows.

You may also split your big file into small chunck files and iterate over the list of created files.

If you connect tFileInputFullRow to tFileOutputDelimited set the advanced parameter "Split output in several files" to the number of lines your are able to manage at once, it should be an easier solution.

Re: large csv file import

Anonymous — Fri, 20 Sep 2019 02:56:00 GMT

Hello Manohar,

I have just start with Talend around 3 hours, could you please tell me which one should I use between TOS_DI or TOS_BD for my case?

Re: large csv file import

Anonymous — Fri, 20 Sep 2019 03:17:10 GMT

Dear TFR,

It seem your suggestion an very appropriate to my case.

Could you guide me some steps to do that? I am very new to TOS

Great thanks

Re: large csv file import

Anonymous — Fri, 20 Sep 2019 07:59:33 GMT

Dear TRF

Screenshot is what I am trying to do (connect tFileInputFullRow to tFileOutputDelimited)

But how to config the tFileInputFullRow because it require the File Name?

Capture1.PNG

Re: large csv file import

TRF — Fri, 20 Sep 2019 08:13:32 GMT

The job design should be like this:

tFileInputFullRow --> tFileOutputDelimited (just to create small files - set schema to a single field)

onSubJobOK

tFileList --> tFileInputDelimited (with the real schema) --> tMap --> IHSDatabase

Re: large csv file import

Anonymous — Fri, 20 Sep 2019 08:24:58 GMT

Dear TRF

I got the OutOfMemmories error message

I still wonder tFileInputFullRow should take row by row in the tFileInputDelimited should be more sense. Am I right?

Capture1.PNG

Re: large csv file import

manodwhb — Fri, 20 Sep 2019 08:35:07 GMT

@phancongphuoc ,increase the JVM and see.

follow the below link to set JVM.

https://community.talend.com/t5/Installing-and-Upgrading/Configure-to-use-a-JVM/td-p/112893

Re: large csv file import

Anonymous — Fri, 20 Sep 2019 09:59:21 GMT

Hi Manohar ,

I am using TOS 64 bit and I have no problem with Java as in your link

Still confuse on what you mentioned about

Re: large csv file import

manodwhb — Fri, 20 Sep 2019 10:25:10 GMT

@phancongphuoc ,check the below link .

https://community.talend.com/t5/Design-and-Development/Memory-issues-when-profiling-large-data-sets-using-indicators/ta-p/24132

Re: large csv file import

Anonymous — Fri, 20 Sep 2019 11:35:21 GMT

Hi Manohar,
Thanks for your suggestion. I try it now.
But do we have a solution for increasing import as TRF mentioned above? Mean that we don't load all rows in file to RAM, instead we insert a number of rows then continue loading other rows.

Re: large csv file import

TRF — Sat, 21 Sep 2019 17:09:34 GMT

@phancongphuoc your job design doesn't make sense.

Here is what I suggest to you:

With 1rst subjob, you'll split your big file into smallest CSV file, thanks to the "Split output in several files" option.

For example, you can generate files of 100,000 records.

tFileInputFullRow consider the input file with 1 single field called "line". Use the same for the tFileOutputDelimited and don't include header for the output files.

For the 2nd subjob, use tFileList component to iterate over the list of previously generated CSV files.

tFileInputDelimited let you read each CSV file one by one with the desired schema.

You have to use the following expression for the filename:

((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))

The content of the current file is pushed to the database by the tMysqlOutput component.

Re: large csv file import

Anonymous — Sun, 22 Sep 2019 05:17:22 GMT

Hi TRF,

I try your approach, but it seem that TFileInputFullRow component read all rows in the file before it seperate into several files.

This is mean that it is load all the file content into RAM

So that, I still got the error of "Exception in thread "main" java.lang.OutOfMemoryError: Java heap space " in even that first step of seperating into serveral files

I also try to increase the JAVA heap to > 3GH

All those thing cannot be done

Re: large csv file import

Tarun2 — Thu, 02 Apr 2020 22:01:30 GMT

"If you connect tFileInputFullRow to tFileOutputDelimited set the advanced parameter "Split output in several files" to the number of lines your are able to manage at once, it should be an easier solution."

Thanks! Your suggestion was useful in my case. Do you know of any limitation or shortcoming of this process?

What I am doing is reading a big XML (1GB) as a flat file, updating it and saving it with the same name. I was able to achieve this using the attached flow. But not aware, if there is any shortcoming , that I might face in future due to this.

Read_Big_XML.png

Re: large csv file import

Tarun2 — Thu, 02 Apr 2020 23:21:04 GMT

Update to the previous comment. Only 350MB file processed successfully with that approach. The 1GB file failed with Java Heap error. It processed only after bumping the JVM to 12GB. So, this is not an ideal solution.