UNLOAD Redshift Table to S3 and Local

Amazon Redshift unload command exports the result or table content to one or more text or Apache Parquet files on Amazon S3. It uses Amazon S3 server-side encryption. You can unload the result of an Amazon Redshift query to your Amazon S3 data lake in Apache Parquet, an efficient open columnar storage format for analytics. In this article, we will check how to unload Redshift table to Amazon S3 and later download it to the local system using Amazon AWS command line interface (CLI).

Unload Redshift Table

Unload command unloads query results to Amazon S3. It does not unload data to a local system. You will have to use AWS CLI commands to download created file.

Following is the unload command syntax.

UNLOAD ('select-statement')
TO 's3://S3-path/name-prefix'
[CREDENTIALS| AWS IAM user]
[ option [ ... ] ]

Following are the options. You can provide one or many options to unload command.

{ [ FORMAT [ AS ] ] CSV | PARQUET
| PARTITION BY ( column_name [, ... ] ) [ INCLUDE ]
| MANIFEST [ VERBOSE ] 
| HEADER           
| DELIMITER [ AS ] 'delimiter-char' 
| FIXEDWIDTH [ AS ] 'fixedwidth-spec'   
| ENCRYPTED [ AUTO ]
| BZIP2  
| GZIP 
| ZSTD
| ADDQUOTES 
| NULL [ AS ] 'null-string'
| ESCAPE
| ALLOWOVERWRITE
| PARALLEL [ { ON | TRUE } | { OFF | FALSE } ]
| MAXFILESIZE [AS] max-size [ MB | GB ] 
| REGION [AS] 'aws-region' }

Unload Redshift Table Example

Following is the example to unload warehouse table to S3. The default delimiter is pipe (‘|’). However, you can always use DELIMITER option to override default delimiter.

unload ('SELECT * from warehouse')
to 's3://product/test/file'
iam_role 'arn:aws:iam::123456789012:role/myRedshiftRole';

The command will unload the warehouse table to mentioned Amazon S3 location. As unload command export the results in parallel, you may notice multiple files in the given location.

Unload Redshift Query Results to a Single File

In order to unload results to a single file, you should set PARALLEL to FALSE. However, It is recommended to set PARALLEL to TRUE.

For example,

unload ('SELECT * from warehouse')
to 's3://product/test/file'
 PARALLEL FALSE
iam_role 'arn:aws:iam::123456789012:role/myRedshiftRole';

Unload Redshift Query Results with Header

You should provide option HEADER to export results with header. i.e. unloads results with column names.

For example,

unload ('SELECT * from warehouse')
to 's3://product/test/file'
 PARALLEL FALSE
 HEADER
iam_role 'arn:aws:iam::123456789012:role/myRedshiftRole';

Unload Redshift Table to Local System

You cannot use unload command to export file to local, as of now it supports only Amazon S3 as a destination. As an alternative you can use psql command line interface to unload table directly to the local system.

For more details, follow my other article, Export Redshift Table Data to Local CSV format.