Home / server / Questions / 212224
Accepted
jimkont
Asked: 2010-12-14 14:37:41 +0800 CST

Import a 260GB csv file into MySQL

  • 772

I have a really big csv ~260GB and I want to import it into MySQL. I use the following mysql script in MacOS

DROP TABLE IF EXISTS tmp_catpath_5;

create table tmp_catpath_5( a1 BIGINT(20),a2 BIGINT(20),a3 BIGINT(20),a4 BIGINT(20),a5 BIGINT(20), c1 BIGINT(20),c2 BIGINT(20),c3 BIGINT(20),c4 BIGINT(20),c5 BIGINT(20), INDEX (a5) );

load data local infile '/Volumes/Time Machine Backups 1/tmp_catpath_5.csv' into table tmp_catpath_5 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ;

It works for smaller files e.g. 150GB, but for this size the database disappears immediately after the load command

ERROR 1049 (42000): Unknown database

Is there a MySQL size limit? Is it something else? I use the MySQL from MAMP application (LAMP for MacOS), could it be different from the official MySQL application?

mysql files csv large-data

2 Answers

  1. Best Answer

    I don't know if there are limits for particular DB engines but I've met with some Department of Defense admins who manage a MySQL system which imports 10TB every day and they are not experiencing this kind of problem.

    Rather than use macros I recommend writing a script to parse the input file and create insert statements from it, which can be fed straight to MySQL. That way you are not dealing with the entire file in one hit, just one line at a time. I like to use Perl for such things.

    You may also like to consider using truncate instead of dropping he table and recreating it each time.

    • 5

  2. While this doesn't really answer your question, is the possibility of splitting the file into smaller chunks something you've considered? The unix utility "split" can handle this easily.

    When you say "crashes", do you mean the MySQL client or the server? Is any error displayed?

    • 1