R bulk upload data to MYSQL database

Question

there is the package: RMySQL

How can I bulk upload lots of data to mysql from R? I have a csv with around 1 million lines and 80 columns.

Would something like this work?

dbWriteTable(con, "test2", "~/data/test2.csv") ## table from a file

I fear this inserts line by line...

Answer 1

Since you have lots of data consider using LOAD DATA. It's the fastest method of importing data from a file according to mysql docs.

LOAD DATA INFILE
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

Speed of INSERT Statements
When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements. See Section 13.2.6, “LOAD DATA INFILE Syntax”.
...
INSERT is still much slower for loading data than LOAD DATA INFILE, even when using the strategies just outlined.

LOAD DATA INFILE '/path/to/your/file.csv' 
INTO TABLE contacts 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' -- or '\r\n'
IGNORE 1 LINES; -- use IGNORE if you have a header line in your file