Using mysqldump on RDS to export a large file in TSV format

Question

I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica.

However, RDS doesn't provide a way to use mysqldump with the --tab=path option, so we need to parse/convert the INSERT statements to TAB seperated files.

I would like to convert lines from the dump file like this:

INSERT INTO MyTAble VALUES ('data_1','data_2', 'date_3'); 

to this 'data_1' 'data_2' 'data_3'

Since I need to parse through a 30G file, I would like to find the most efficient way of doing this?

Thanks for any help.

Answer 1

Assuming RDS with MySQL 5.6 is being used, mydumper may be a better option. The newer versions have the RDS grants limitation into account and you will get all the advantages of mydumper over mysqldump (compression, parallel execution, overall faster speed, etc).

This wouldn't provide TSV data, but by using the --chunk-filesize option you can split the data into multiple files in parallel and then process them in parallel afterwards

option:: --chunk-filesize -F Split tables into chunks of this output file size. This value is in MB 

Answer 2

However, RDS doesn't provide a way to use mysqldump with the --tab=path option, so we need to parse/convert the INSERT statements to TAB seperated files

Try the mysqltotsv pypi module:

pip3 install --user mysqltotsv
python3 mysql-to-tsv.py --file dump.sql --outdir out1

This will produce multiple .tsv files in the out1 directory (one .tsv file for each table found in the MySQL dump).

Answer 3

Assuming you're using Unix of some sort, I would use sed.

Using your data

INSERT INTO MyTAble VALUES ('data_1','data_2', 'date_3');

and putting this line into a file called testfile.

Run this

sed -i s/"INSERT INTO MyTAble VALUES ("//g testfile

sed -i is inplace.

more testfile
'data_1','data_2', 'date_3');

Then

sed -i s/);//g testfile 
sed -i s/,//g testfile

And then voila!

more testfile
'data_1''data_2' 'date_3'

So, there you have it.

Answer 4

If you table has a date field (with an index), I would use:

select * from table into OUTFILE 'filename' WHERE date between x and y ;

I would dump data per year. Allow you to test the export / import with a smaller data set.