How to unload a table on RedShift to a single CSV file?

ciphor picture ciphor · Dec 2, 2013 · Viewed 30.9k times · Source

I want to migrate a table from Amazon RedShift to MySQL, but using "unload" will generate multiple data files which are hard to imported into MySQL directly.

Is there any approach to unload the table to a single CSV file so that I can import it to MySQL directly?

Answer

Dan Ciborowski - MSFT picture Dan Ciborowski - MSFT · Jun 24, 2014

In order to send to a single file use parallel off

unload ('select * from venue')
to 's3://mybucket/tickit/unload/venue_' credentials 
'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
parallel off;

Also I recommend using Gzip, to make that file even smaller for download.

unload ('select * from venue')
to 's3://mybucket/tickit/unload/venue_' credentials 
'aws_access_key_id=<access-key-id>;aws_secret_access_key=<secret-access-key>'
parallel off
gzip;