如何通过UNLOAD语句删除S3桶中未被源查询覆盖的分区文件？

问题根源 #

你当前使用的PARTITION BY (customerId) CLEANPATH PARALLEL OFF参数组合中，CLEANPATH仅会清理本次UNLOAD要写入的分区目录——也就是说，只有源查询返回的customerId对应的分区会被清空并写入新数据；对于源查询中不存在的customerId，UNLOAD不会触及对应的S3分区目录，导致旧分区文件留存。

解决方案 #

UNLOAD本身没有单一参数可以直接删除源数据中不存在的分区，以下是两种符合要求的实现方式：

方式1：双UNLOAD语句配合（纯UNLOAD操作） #

通过先清空整个目标前缀，再写入当前有效分区的方式，间接清理旧分区：

1. 执行空查询的UNLOAD清空S3目标路径：

UNLOAD ('SELECT 1 WHERE 1=0')
TO 's3://your-bucket/your-target-prefix/'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-iam-role'
CLEANPATH
PARALLEL OFF;

这条语句会删除目标前缀下所有文件和子目录，但因查询无返回结果，不会写入任何数据。
2. 执行原有UNLOAD语句写入当前有效分区：

UNLOAD ('SELECT your_columns FROM your_source_table')
TO 's3://your-bucket/your-target-prefix/'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-iam-role'
PARTITION BY (customerId)
PARALLEL OFF;

（此处可省略CLEANPATH，因为前缀已被清空，新分区目录会重新创建；保留也不会产生负面影响）

方式2：Redshift Spectrum外部表辅助清理 #

若已配置Redshift Spectrum，可通过外部表映射S3分区，直接删除无效分区：

1. 创建映射S3分区的外部表：

CREATE EXTERNAL TABLE spectrum.customer_sync_data (
    -- 与源表列定义保持一致
    column1 INT,
    column2 VARCHAR(255),
    -- 其他列...
)
PARTITIONED BY (customerId VARCHAR(100))
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION 's3://your-bucket/your-target-prefix/';

2. 刷新外部表的分区信息：

ALTER TABLE spectrum.customer_sync_data RECOVER PARTITIONS;

3. 删除源表中不存在的分区（同步清理S3对应目录）：

ALTER TABLE spectrum.customer_sync_data DROP PARTITION (customerId)
WHERE customerId NOT IN (SELECT DISTINCT customerId FROM your_source_table);

4. 执行原有UNLOAD语句更新有效分区：

UNLOAD ('SELECT your_columns FROM your_source_table')
TO 's3://your-bucket/your-target-prefix/'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-iam-role'
PARTITION BY (customerId)
CLEANPATH
PARALLEL OFF;

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