MySQL's dirty little secret - CSV tables

Perhaps i'm late to the party on this, but MySQL allows you to have CSV as a storage engine, which is very handy when you're importing lots of CSV. Slides from my lightening talk at the Melbourne Rails meetup.

Transcript

1. SQLserver ‛ MySQL ★ Seed the new server ★ Periodic “syncing”1 ★ Servers are not on same network 1 four - ve years later it’s still doing it
2. nce upon a time there was a grumpy old NT server that lived under the stairs… It spat out CSV les all day long
3. Loading large CSV les uses a lot of memory ★ large = ~750,000 rows2 ★ when it crashed… and it did, wasn’t always obvious how far the import had progressed 2 e server had 512Mb of RAM
4. MySQL CSV tables3 ★ Dump CSV directly into the db ★ Use familiar tools for dealing with the data ★ Can query the table, so ActiveRecord is happy ★ Can step through the rows can delete each row after processing ★ Feels less volatile ★ No indexes 3 http://dev.mysql.com/doc/refman/5.1/en/csv-storage-engine.html
5. Create your CSV table Via ActiveRecord migration by de ning the engine. Note that everything is a string db_connection.create_table('import_table', :options => 'ENGINE=CSV', :id => false) do |t| # regular migration t.string :id t.text :name t.string :version t.string :platform end
6. Load/dump the CSV into the csv table LOAD DATA LOW_PRIORITY INFILE 'import-stuff.csv' REPLACE INTO TABLE import_stuff FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' STARTING BY ''
7. Credits http://www.dailydropcap.com