从S3 Bucket向Redshift复制文件时的日期格式问题排查

为什么时间部分会导致报错？ #

Redshift的DATE类型仅存储日期，但执行COPY导入时会严格校验输入字符串的格式：

• 指定DATEFORMAT 'yyyy-MM-dd'时，Redshift要求输入字符串完全匹配该格式，而你的CSV中是带时分秒的完整时间串（如2024-05-20 14:30:00），多余的时间部分会触发格式不匹配错误。
• 不指定DATEFORMAT时，Redshift使用默认日期解析规则，但默认规则无法自动识别带时间的字符串，导致解析失败，抛出的“Invalid Date Format - length must be 10 or more”提示有误导性，本质是无法解析带时间的字符串。

解决方法 #

方法1：指定完整格式让Redshift自动截断 #

在COPY命令中定义完整的时间戳格式，Redshift会自动提取日期部分存入DATE列：

COPY your_target_table
FROM 's3://your-bucket/your-file.csv'
IAM_ROLE 'arn:aws:iam::your-account-id:role/redshift-access-role'
CSV
TIMESTAMPFORMAT 'yyyy-MM-dd HH:MI:SS'
;

也可以分开指定日期和时间格式：

COPY your_target_table
FROM 's3://your-bucket/your-file.csv'
IAM_ROLE 'arn:aws:iam::your-account-id:role/redshift-access-role'
CSV
DATEFORMAT 'yyyy-MM-dd'
TIMEFORMAT 'HH:MI:SS'
;

方法2：COPY时手动提取日期部分 #

通过COLUMNS参数将原始带时间的列当作字符串处理，截取前10位后转换为DATE类型：

COPY your_target_table 
(col1, col2, date_col)
FROM 's3://your-bucket/your-file.csv'
IAM_ROLE 'arn:aws:iam::your-account-id:role/redshift-access-role'
CSV
COLUMNS (col1, col2, raw_date_str, date_col AS CAST(SUBSTRING(raw_date_str FROM 1 FOR 10) AS DATE))
;

方法3：预处理CSV文件（可选） #

若有权限修改S3中的CSV文件，可通过Lambda、Python脚本等工具批量移除日期列的时间部分，仅保留yyyy-MM-dd格式，之后执行常规COPY即可。

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