Moving Redshift data between databases

Ben Lee
April 23, 2014

I recently had need to transfer some data between Redshift Databases. Coming from an Oracle background I started my search for the Redshift equivalent to import/export. I found was that there wasn’t one. However using the unload and copy commands you can achieve the desired results. There are quite a few flags options with the commands but here are the ones that worked for me on large set of tables.
To extract the data from a table called PRODUCT in the XYZ schema run the following against the source database. You need to use S3 to store the export files.

unload ('select * from xyz.product') to 's3://my-bucket/product' CREDENTIALS
'aws_access_key_id=<your key here>;aws_secret_access_key=<your key here>

In your S3 bucket this will create a manifest file called productmanifest which contains a list of all the export files created and the export data files (eg product0001_part_00 & product0001_part_00).
On the target database issue the following command to load the data into the database.

copy xyz.product from 's3://my-bucket/productmanifest' CREDENTIALS
'aws_access_key_id=<your key here>;aws_secret_access_key=<your key here>'

If there are any errors you can check for errors in the STL_LOAD_ERRORS table.
All the code, all the fun – Ben

Ben writes blogs on the technical side of BI, the code, all the code and not much other than the code.

Connect with Ben on LinkedIn or read some of his other blogs here.

Copyright © 2019 OptimalBI LTD.