CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbenchinnodb
Ed_
Ed_

Reputation: 69

What is the best method for uploading large (40 GB) .csv files into mysql tables

I am attempting to set up a large database on my desktop (~100GB) and one of the csv files is about 40 GB. My MySQL workbench executes the query for about 30-60 minutes then loses connection, with a report of error code 2013.

What is the typical upload time per GB ? Do I need to modify my INNODB Option File or other parameters? I cannot seem to figure out the perfect settings...below I have listed my LOAD DATA code for reference.

LOAD DATA LOCAL INFILE '/Users/ED/desktop/mirror2/CHARTEVENTS.csv'                          
INTO TABLE CHARTEVENTS
FIELDS TERMINATED BY ',' ESCAPED BY '\\' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@ROW_ID,@SUBJECT_ID,@HADM_ID,@ICUSTAY_ID,@ITEMID,@CHARTTIME,@STORETIME,@CGID,@VALUE,@VALUENUM,@VALUEUOM,@WARNING,@ERROR,@RESULTSTATUS,@STOPPED)
SET
ROW_ID = @ROW_ID,
SUBJECT_ID = @SUBJECT_ID,
HADM_ID = IF(@HADM_ID='', NULL, @HADM_ID),
ICUSTAY_ID = IF(@ICUSTAY_ID='', NULL, @ICUSTAY_ID),
ITEMID = @ITEMID,
CHARTTIME = @CHARTTIME,
STORETIME = IF(@STORETIME='', NULL, @STORETIME),
CGID = IF(@CGID='', NULL, @CGID),
VALUE = IF(@VALUE='', NULL, @VALUE),
VALUENUM = IF(@VALUENUM='', NULL, @VALUENUM),
VALUEUOM = IF(@VALUEUOM='', NULL, @VALUEUOM),
WARNING = IF(@WARNING='', NULL, @WARNING),
ERROR = IF(@ERROR='', NULL, @ERROR),
RESULTSTATUS = IF(@RESULTSTATUS='', NULL, @RESULTSTATUS),
STOPPED = IF(@STOPPED='', NULL, @STOPPED);

Upvotes: 0

Views: 257

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

I don't know the details of the connection between your local machine and the MySQL server, but the connection could be getting cut off for any number of reasons. One simple workaround here would be to just upload the 40GB file directly to the same remote machine running MySQL, and then use LOAD DATA (without LOCAL). With this approach, the LOAD DATA statement should take orders of magnitude less time to parse the input file, there no longer being any network latency to slow things down.

Upvotes: 1

Related Questions