How to save large amounts of data by reading from a CSV file

Question

How to save large amounts of data by reading from a CSV file (say, 20 million lines). Until now, it was approaching 1 1/2 days and saving only 10 million lines, how can I do this to make it faster, and it is possible to run it in parallel.

I am using CSV reader code here, I would like to know if there is a better way to achieve this.

Refer: to large CSV files (20G) in ruby

Answer 1

You can first split the file into several smaller files, then you can process multiple files in parallel.

It is likely that the user will be able to use a tool faster, such as split

split -l 1000000 ./test.txt ./out-files- 

Then, while you are processing each of the files and assuming that you are inserting records, rather than pasting them one by one, you can combine them into batches and make voluminous inserts. Sort of:

 INSERT INTO some_table VALUES (1,'data1'), (2, 'data2') 

To improve performance, you need to create an SQL statement yourself and execute it:

 ActiveRecord::Base.connection.execute('INSERT INTO <whatever you have built>') 

Answer 2

Since you want to save data in MySQL for further processing, using Load Data Infile from MySQL will be faster. something like the following with your circuit:

sql = "LOAD DATA LOCAL INFILE 'big_data.csv' INTO TABLE FIELD TESTS TESTED ',' CONCLUDED '\" LINES INTERRUPTED' \ n '(foo, foo1) "

con = ActiveRecord :: Base.connection

con.execute (SQL)

Answer 3

Key points:

• If you use MySQL InnoDB, my advice is to always define auto-increment PRIMARY KEY , InnoDB uses a clustered index to store data in a table. A clustered index defines the physical order of the data in a table.
  refer: http://www.ovaistariq.net/521/understanding-innodb-clustered-indexes/
• Configure MySQL server settings, the most important of them are (1) close mysql binlog
  (2) innodb_buffer_pool_size.
  (3) innodb_flush_log_at_trx_commit
  (4) bulk_insert_buffer_size
  You can read this: http://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/
• You should use the producer-consumer scenario.

Sorry for the bad english.