Export Redshift Table Data to Local CSV format

You can export the Redshift table in many ways. Redshift export table is done using either UNLOAD command, COPY command or PostgreSQL command.

Using UNLOAD or COPY command is fasted way to export Redshift table, but with those commands you can unload table to S3 bucket. You have to use the PostgreSQL or psql to export Redshift table to local CSV format.

Export Redshift Table Data to Local CSV format

To export Redshift table to local directory, you must install the PostgreSQL in your machine. You can use the psql to connect to Redshift from local machine.

Read:

Steps to connect to Redshift using PostgreSQL – psql.

PostgreSQL or psql supports many command line options that you can use to format your table output. You can use ‘-F’ for delimiter and ‘-o’ for Redshift output file name.

Below is the example to export Redshift data to local CSV format:

psql -h <host> -d <db> -U <user> -p 5439 -a -c "select * from <table>" -F '<delimiter>' -o /path/filename.csv

You have to provide password at the run time to enable data export. You can even set psql variables to avoid entering password every time you try to export tables:

postgres@vithal-Inspiron-3558:~$ psql -h vithalxxxx.xxxxxxxxx.us-east-2.redshift.amazonaws.com -U vithaljs -d training -p 5439 -A -t -c "select * from tr_test" -F ',' -o tr_test.csv
Password for user vithaljs:
postgres@vithal-Inspiron-3558:~$

Now, verify the exported data

postgres@vithal-Inspiron-3558:~$ more tr_test.csv
2,bcd
3,CDE
5,EFG
1,ABC
4,def
postgres@vithal-Inspiron-3558:~$

Enclose Redshift Table column output in double quotes

You can use QUOTE_IDENT string function when selecting records form Redshift table. The QUOTE_IDENT function returns the specified column value as a double quoted string.

Below is the example:

training=# Select QUOTE_IDENT(id) as id, QUOTE_IDENT(name) as name from tr_test;
 id | name
-----+-------
 "2" | bcd
 "3" | "CDE"
 "5" | "EFG"
 "1" | "ABC"
 "4" | def
(5 rows)

training=#

You can use similar concept if you want to export Redshift table data in while all columns are enclosed in double quotation mark.

Read:

postgres@vithal-Inspiron-3558:~$ psql -h vithalxxxx.xxxxxxx.us-east-2.redshift.amazonaws.com -U vithaljs -d training -p 5439 -A -t -c "select QUOTE_IDENT(ID), QUOTE_IDENT(name) from tr_test" -F ',' -o tr_test.csv
Password for user vithaljs:
postgres@vithal-Inspiron-3558:~$

Now verify the file for data:

postgres@vithal-Inspiron-3558:~$ more tr_test.csv
"1","ABC"
"4",def
"2",bcd
"3","CDE"
"5","EFG"
postgres@vithal-Inspiron-3558:~$