使用UNLOAD命令导出AWS Redshift数据至S3时输出异常

Hey there! Since you're new to Redshift and hitting issues with your UNLOAD command output not matching expectations, let's walk through the most common fixes and checks to get this sorted:

1. Explicitly define output format for PostgreSQL compatibility #

By default, Redshift's UNLOAD uses pipe (|) separators and minimal formatting, which might not align with what PostgreSQL expects for smooth imports. Try adding format-specific parameters to your command to make the output CSV-friendly (PostgreSQL's go-to for bulk imports):

UNLOAD ('select * from date') 
TO 's3://sample-dwh-data/date_' 
credentials 'aws_access_key_id=******;aws_secret_access_key=*************' 
PARALLEL OFF
FORMAT CSV
HEADER
ADDQUOTES;

• FORMAT CSV: Ensures the output uses comma separators
• HEADER: Includes column names at the top (critical for matching PostgreSQL table schema)
• ADDQUOTES: Wraps string values in quotes to avoid issues with commas or special characters inside data

2. Validate your source query first #

Before blaming the UNLOAD command, confirm the data in your Redshift date table is what you expect. Run:

SELECT * FROM date LIMIT 20;

Compare this result directly to the content of the S3 file you exported. If the raw query output doesn't match your expectations, the issue is with your source data, not the UNLOAD process.

3. Fix data type mismatches between Redshift and PostgreSQL #

While Redshift is based on PostgreSQL, some data types behave differently (e.g., TIMESTAMPTZ formatting, fixed-length CHAR columns). If you're seeing garbled or unexpected values for specific columns, explicitly convert them to a PostgreSQL-compatible format in your UNLOAD query:

UNLOAD ('
  SELECT 
    id,
    to_char(date_column, ''YYYY-MM-DD HH24:MI:SS'') AS date_column,
    varchar_column
  FROM date
') 
TO 's3://sample-dwh-data/date_' 
credentials 'aws_access_key_id=******;aws_secret_access_key=*************' 
PARALLEL OFF
FORMAT CSV
HEADER;

4. Inspect the raw S3 file content #

Download the exported file from S3 and open it in a text editor to pinpoint exactly what's wrong:

• Are columns split incorrectly? Check if you used the right delimiter.
• Are special characters (like quotes or newlines) causing line breaks in the middle of rows? Use ADDQUOTES or ESCAPE to handle these.
• Is data truncated? Redshift has default length limits for UNLOAD; if you have large columns, add MAXFILESIZE to allow larger output files.

5. Align with PostgreSQL's import requirements #

Once your S3 file is formatted correctly, make sure your PostgreSQL COPY command matches the UNLOAD settings. For example, if you exported with CSV headers and quotes, your import command should look like:

COPY date FROM '/local/path/to/date_file.csv'
DELIMITER ','
CSV HEADER
QUOTE '"';

内容的提问来源于stack exchange，提问作者Technext