I’ve got a big table (~500m rows) in mysql RDS and I need to export specific columns from it to csv, to enable import into questDb.
Normally I’d use into outfile
but this isn’t supported on RDS as there is no access to the file system.
I’ve tried using workbench to do the export but due to size of the table, I keep getting out-of-memory issues.
3
Answers
Finally figured it out with help from this: Exporting a table from Amazon RDS into a CSV file
This solution works well as long as you have a sequential column of some kind, e.g. an auto incrementing integer PK or a date column. Make sure you have your date column indexed if you have a lot of data!
A slightly different approach which may be faster depending on indexing you have in place is step through the data month by month:
The above scripts will output a
import.sql
containing all the sql statements you need to import your data. See: https://questdb.io/docs/guides/importing-data/Edit: this solution would work only if exporting the whole table, not when exporting specific columns
You could try using mysqldump with extra params for CSV conversion. AWS documents how to use mysqldump with RDS and you can see at this stackoverflow question how to use extra params to convert into CSV.
I am quoting here the relevant part from that last link (since there are a lot of answers and comments)
You can use the
SELECT ... INTO OUTFILE
syntax to export the data to a file on the server.You can then use the
mysql
command line client to connect to the RDS instance and retrieve the file from the server.The only slight snag is that
mysql
won’t connect to the RDS instance unless the instance is in a VPC, so if it isn’t you’ll need to connect to a bastion host first, then connect to the RDS instance from there.SELECT * FROM mydb.mytable INTO OUTFILE '/tmp/mytable.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n';
You can then get the file from the server:
mysql -uusername -p -hmyrds.rds.amazonaws.com -P3306
When you have a prompt from the
mysql
command line client you can retrieve the file using theSELECT
command:SELECT LOAD_FILE('/tmp/mytable.csv');
You can then pipe the output to a file using:
SELECT LOAD_FILE('/tmp/mytable.csv') INTO OUTFILE '/tmp/mytable_out.csv';
You can then use the
mysql
command line client to connect to your questDB instance and load the data.If you want to retrieve a specific column then you can specify the column name in the
SELECT
command when creating the file on the RDS server:SELECT column1, column2, column3 FROM mydb.mytable INTO OUTFILE '/tmp/mytable.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY 'n';