Question
NTT DATA Co Ltd
JP
Last activity: 18 Jul 2023 13:06 EDT
inserting a 5 million record CSV file to local storage.
We would like to ask for best practices and implementation steps when inserting a 5 million record CSV file to local storage.
The following are the candidates we are considering here, but we think it is difficult to take (1) because it takes too much time when massively inserting.
(1) Use Obj-Save to submit each row.
(2) Create SQL by java step and batch insert (No improvement from (1).
(3) Data submission using DataFlow （Not started yet)
Please let us know if there are other effective approaches.
***Edited by Moderator Marije to add Capability tags***
JPMorgan Chase
IN
Leverage Data flow to insert records it is more performance effective.
JPMC
IN
@SriHarsha Anika Could you please elaborate on the steps as it would help others.
Masking Technology
NL
How many columns does the CSV has, and what is roughly the size of the file?
I've had a requirement to insert a 3 mln file into a data table and I used the import functionality of the data type landing page. Then you can upload the file (skip validation, make sure the data always fits*), and import it.
The import took about 2 hours to complete. But the import runs on the background, so the user uploading the data doesn't have a locked session.
*) any pre-import formatting needs to be done in some other tool (visual studio code, notepad++, etc.)
Incessant Technologies
GB
@KentaroA9143 If you are looking insert 5 Million records from CSV file to a local SQL Table then you can use SQL Loader Control File, this will be far quicker then using other options.
Masking Technology
NL
You're right, but only if you have access to the database. When your application is hosted on the Pega Cloud, there is no option to directly access the database.