Import data from CSV file to Amazon Web Services RDS MySQL database

Question

I have created a Relational Database (MySQL) hosted on Amazon Web Services. What I would like to do next is, import the data in my local CSV files into this database. I would really appreciate if someone provides me an outline on how to go about it.Thanks!

Answer 1

This is easiest and most hands-off by using MySQL command line. For large loads, consider spinning up a new EC2 instance, installing MySQL CL tools, and transferring your file to that machine. Then, after connecting to your database via CL, you'd do something like:

mysql> LOAD DATA LOCAL INFILE 'C:/upload.csv' INTO TABLE myTable;

Also options to match your file's details and ignore header (plenty more in the docs)

mysql> LOAD DATA LOCAL INFILE 'C:/upload.csv' INTO TABLE myTable FIELDS TERMINATED BY ','
ENCLOSED BY '"' IGNORE 1 LINES;

If you're hesitant to use CL, download MySQL Workbench. It connects no prob to AWS RDS.

Closing thoughts:

• MySQL LOAD DATA Docs
• AWS' Aurora RDS is MySQL-compatible so command works there too
• "LOCAL" flag actually transfers the file from your client machine (where you're running the command) to the DB server. Without LOCAL, the file must be on the DB server (not possible to transfer it there in advance with RDS)
• Works great on huge files too! Just sent a 8.2GB file via this method (260 million rows). Took just over 10 hours from a t2-medium EC2 to db.t2.small Aurora
• Not a solution if you need to watch out for unique keys or read the CSV row-by-row and change the data before inserting/updating