Redshift UNLOAD操作出现冗余数据问题求助

I’ve dealt with this exact frustrating inconsistency in Redshift UNLOAD jobs before, so I know how annoying it is when your "overwrite" logic fails randomly and leaves duplicate data. Let’s break down what’s probably causing this and how to fix it.

The Problem Recap #

You’re using UNLOAD to transform data from an S3-based external table and write it as PARQUET to another S3 bucket, with ALLOWOVERWRITE and PARTITION BY enabled. Most of the time it works as expected—overwriting existing files in the target partitions—but occasionally, instead of replacing files like 0000_part_00.parquet, it creates a new file 0000_part_01.parquet, doubling your data volume and causing duplicates in the external table. The kicker? If you empty the partition first and re-run the UNLOAD, it works perfectly.

Your UNLOAD command looks like this:

unload (<simple select statement>) 
to 's3://<s3 bucket>/<prefix>/' 
iam_role '<iam-role>' 
allowoverwrite 
PARQUET 
PARTITION BY (partition_col1, partition_col2);

Why This Happens #

Redshift’s ALLOWOVERWRITE logic with PARTITION BY relies on matching the exact partition paths and file patterns to overwrite existing content. Here are the most common triggers for this duplicate file issue:

• Case-sensitive partition paths: If your S3 bucket uses case-sensitive object names (default in many regions), a mismatch in case for partition values (e.g., partition_col1=US vs partition_col1=us) will make Redshift treat them as separate partitions, so it won’t overwrite—instead, it adds new files.
• Interrupted or retried UNLOAD jobs: If a UNLOAD job gets interrupted mid-execution (network blips, cluster resource limits, etc.), partial files might be left in S3. When you retry the job, ALLOWOVERWRITE doesn’t always clean up these incomplete files, leading to duplicates.
• S3 Versioning is enabled: If your target S3 bucket has versioning turned on, ALLOWOVERWRITE just adds a new version of the file instead of deleting the old one. External tables scan all versions by default, so you’ll see duplicate data.
• Mismatched partition column data types: If the data type of your partition columns in the SELECT statement doesn’t match the external table’s partition definition (e.g., returning a string for an integer partition), Redshift generates unexpected partition paths that don’t match existing ones, so no overwrite occurs.

Fixes to Try #

Let’s go through actionable fixes based on the root causes above:

1. Standardize partition path case

   • Explicitly format partition columns in your SELECT statement to enforce consistent case. For string columns, use LOWER(partition_col1) or UPPER(partition_col1) to ensure the generated partition paths are identical every time.
   • Note: You can’t change an S3 bucket’s case sensitivity after creation, so fixing this in your SQL is the most reliable approach.

2. Clean partitions before UNLOAD
   Since you know emptying the partition first works, automate this step. You can:

   • Use the AWS CLI to delete files in the target partition before running UNLOAD:

     aws s3 rm --recursive s3://<s3 bucket>/<prefix>/partition_col1=<value>/partition_col2=<value>/
     

   • Wrap this cleanup and the UNLOAD in a single script or orchestration task (like Airflow) to ensure atomicity.

3. Disable S3 Versioning (if unnecessary)
   Check if your target bucket has versioning enabled. If you don’t need to retain old file versions, turn it off—this will make ALLOWOVERWRITE replace existing files instead of adding new versions.

4. Validate partition column data types
   Double-check that the data types of the partition columns in your SELECT query exactly match the external table’s partition schema. For example, if the external table defines partition_col1 as INT, don’t return a VARCHAR representation of the number (this would create a path like partition_col1='123' instead of partition_col1=123, which Redshift sees as a different partition).

5. Use the MANIFEST option
   Add MANIFEST to your UNLOAD command to generate a JSON file that lists all the files written. You can use this manifest to:

   • Verify exactly which files were created after each UNLOAD
   • Update your external table to only load files from the manifest, avoiding any unexpected duplicate files left in S3
     Example updated command:

   unload (<simple select statement>) 
   to 's3://<s3 bucket>/<prefix>/' 
   iam_role '<iam-role>' 
   allowoverwrite 
   PARQUET 
   PARTITION BY (partition_col1, partition_col2)
   MANIFEST;
   

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