如何将S3分区Parquet文件导入Redshift临时表并填充分区日期列

有两种可行的方案可以将分区路径中的date值填充到Redshift临时表的date列中：

方案一：使用COPY命令的PARTITION COLUMNS参数（推荐） #

Redshift的COPY命令支持通过PARTITION COLUMNS子句自动从S3分区路径中提取key=value格式的分区值，直接填充到表的对应列中，无需额外处理。

修改你的COPY语句，添加PARTITION COLUMNS指定date列的类型：

DROP TABLE IF EXISTS table;
CREATE TEMP TABLE table 
(
var1 bigint,            
var2 bigint,        
date timestamp
);

COPY table
FROM 's3://mybucket/myfolder/'
access_key_id 'id'
secret_access_key 'key'
PARQUET
FILLRECORD
PARTITION COLUMNS (date timestamp);

注意事项： #

• Redshift会自动解析路径中的date=YYYY-MM-DD部分，将字符串转换为timestamp类型填充到date列
• 建议使用IAM角色替代硬编码的access_key和secret_key，提升安全性（将access_key_id和secret_access_key替换为IAM_ROLE 'arn:aws:iam::你的账号ID:role/你的角色名'）

方案二：利用Redshift虚拟列$PATH提取分区值 #

如果你的Redshift版本不支持PARTITION COLUMNS，可以先将数据导入不含date列的临时表，再通过虚拟列$PATH提取文件路径中的date值：

1. 创建临时表存储原始数据（不含date列）：

DROP TABLE IF EXISTS tmp_raw_data;
CREATE TEMP TABLE tmp_raw_data 
(
var1 bigint,            
var2 bigint
);

COPY tmp_raw_data
FROM 's3://mybucket/myfolder/'
access_key_id 'id'
secret_access_key 'key'
PARQUET FILLRECORD;

2. 将数据插入目标临时表，同时提取date值：

DROP TABLE IF EXISTS table;
CREATE TEMP TABLE table 
(
var1 bigint,            
var2 bigint,        
date timestamp
);

INSERT INTO table (var1, var2, date)
SELECT
  var1,
  var2,
  -- 从$PATH中提取date值，需根据实际路径调整split_part的索引
  regexp_replace(split_part("$PATH", '/', 5), 'date=', '')::timestamp AS date
FROM tmp_raw_data;

注意事项： #

• $PATH是Redshift内置的虚拟列，记录每条数据对应的S3文件路径
• split_part("$PATH", '/', 5)中的数字5需根据你的路径结构调整：比如路径s3://mybucket/myfolder/date=2022-01-01/file.parquet按/分割后，date=2022-01-01是第5个元素（索引从1开始）
• regexp_replace用于移除路径中的date=前缀，再通过::timestamp转换为时间戳类型

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