MySQL Data Loading – Column Map vs INSERT SELECT INTO ETL

importMySQL

I've got 125 csv files to upload into a database. The files are typically ~200K rows and 30 columns.

In order to make the data more relational I was going to column map during LOAD DATA INFILE process (specific columns into specific tables) via bash script. But now I'm wondering if it makes more sense to LOAD all the files and use INSERT SELECT INTO to do my ETL'ing.

Any recommendations?

Best Answer

Since you have CSV files, it makes a lot more sense to use LOAD DATA. Put them into 125 tables. After that, you can do the massaging that you suggest doing with bash.

Related Solutions

Mysql – How much will computer hardware affect the speed of a “load data local infile” query

I would like to recommend something with regard to LOAD DATA INFILE:

You need to increase bulk_insert_buffer_size (such as 256M) because a special tree structure is made to accommodate the loading of the data. Even with LOAD DATA INFILE, the tree structure is used when loading data into non-empty tables.

According to the MySQL Docs on bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

Mysql – Importing CSV file to MySQL via PHPMyAdmin

What you're looking for is...

ESCAPED BY '"'

In CSV, one way of including literal quotation marks inside a single field is by doubling them up.

In the example file you posted, that's exactly what's happening at line 44.

The rest of your statement looks correct. Replace the ESCAPED BY part with what I've shown above, and you should be good.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY character are interpreted as part of the current field value.

...which is exactly what you're needing.

Best Answer

Related Solutions

Mysql – How much will computer hardware affect the speed of a “load data local infile” query

Mysql – Importing CSV file to MySQL via PHPMyAdmin

Related Question