How to import/export data from Amazon Redshift

Amazon Redshift is fully managed Date Warehouse solution from Amazon. Many times we need to export/import data into it using CSV for testing or for data migration. Let’s see how to import/export data from Amazon Redshift.

This post assumes Postgres Amazon Redshift

Import/Expot Data

Setting password

There are other ways of managing password, but my preferred way while using a terminal is to export it for the session. Here is how to do it

$export PGPASSWORD=<password>

Export Data from Amazon Redshift

$psql -h *****.eu-west-1.redshift.amazonaws.com -p 5439 -U test -d test -F, -A -c 'select * from  mytable' > mytable.csv

Where
p – port where Redshift is listening
U – User name
d – Database name
mytable – Name of table

Importing Data into Amazon Redshift

$psql -h ****.eu-west-1.rds.amazonaws.com -p 5432 -U test -d test -c '\copy mytable from mytable.csv with CSV HEADER'

This shall import data into mytable from mytable.csv