Unloading from redshift to s3 with headers

Question

I already know how to unload a file from redshift into s3 as one file. I need to know how to unload with the column headers. Can anyone please help or give me a clue?

I don't want to manually have to do it in shell or python.

Answer 1

As of cluster version 1.0.3945, Redshift now supports unloading data to S3 with header rows in each file i.e.

UNLOAD('select column1, column2 from mytable;') TO 's3://bucket/prefix/' IAM_ROLE '<role arn>' HEADER; 

Note: you can't use the HEADER option in conjunction with FIXEDWIDTH.

https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html

Answer 2

If any of your columns are non-character, then you need to explicitly cast them as char or varchar because the UNION forces a cast.

Here is an example of the full statement that will create a file in S3 with the headers in the first row.

The output file will be a single CSV file with quotes.

This example assumes numeric values in column_1. You will need to adjust the ORDER BY clause to a numeric column to ensure the header row is in row 1 of the S3 file.

    ******************************************      /* Redshift export to S3 CSV single file with headers - limit 6.2GB */      UNLOAD ('          SELECT \'column_1\',\'column_2\'        UNION           SELECT              CAST(column_1 AS varchar(255)) AS column_1,           CAST(column_2 AS varchar(255)) AS column_2           FROM source_table_for_export_to_s3          ORDER BY 1 DESC        ;        ')      TO 's3://bucket/path/file_name_for_table_export_in_s3_' credentials      'aws_access_key_id=<key_with_no_<>_brackets>;aws_secret_access_key=<secret_access_key_with_no_<>_brackets>'        PARALLEL OFF        ESCAPE       ADDQUOTES       DELIMITER ','       ALLOWOVERWRITE       GZIP       ;       ****************************************