将CSV时间戳插入Redshift timestamp列时遇格式错误求助

问题场景 #

尝试将UTF-8编码的CSV导入Redshift，向timestamp类型列插入数据时报错。错误提示shiftstarttime列的原始值“2023-01-17 09:01:34”第4位存在无效字符“-”。

示例CSV数据 #

employeeId,employeeDept,employeeName,shiftStartTime,shiftEndTime,onPremises
KL214691,John Smith,operations,2023-01-17 09:01:34,2023-01-17 16:52:41,1
KL214692,Samantha Kennedy,operations,2023-01-17 08:31:54,2023-01-17 16:09:10,1

目标表DDL #

create table historical_metrics_agent_status_time_on_status
(
    employeeid       varchar(10),
    employeename     varchar(100),
    employeedept     varchar(50),
    shiftstarttime   timestamp encode az64,
    shiftendtime     timestamp encode az64,
    onpremises       boolean,
    importdatetime   timestamp encode az64
)
    sortkey (employeeid);

问题原因 #

1. 列顺序不匹配：CSV的列顺序（employeeId,employeeDept,employeeName,...）与表的列顺序（employeeid,employeename,employeedept,...）不一致，且表中包含CSV没有的importdatetime列。
2. 未指定列映射：导入时未明确指定CSV列与表列的对应关系，Redshift默认按表列顺序解析CSV，导致非时间字段被错误映射到shiftstarttime列，进而触发格式错误。

解决方法 #

1. 在COPY命令中明确指定列映射 #

执行COPY时，显式声明CSV列与表列的对应关系，跳过表中不存在于CSV的importdatetime列：

COPY historical_metrics_agent_status_time_on_status (employeeid, employeedept, employeename, shiftstarttime, shiftendtime, onpremises)
FROM 's3://your-bucket/path/to/your-file.csv'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-redshift-copy-role'
FORMAT CSV
DELIMITER ','
HEADER
ENCODING 'UTF8';

2. 为importdatetime列设置默认值（可选） #

如果需要自动填充导入时间，修改表结构给该列添加默认值：

ALTER TABLE historical_metrics_agent_status_time_on_status
ALTER COLUMN importdatetime SET DEFAULT CURRENT_TIMESTAMP;

此后导入时，该列会自动填充当前时间，无需从CSV读取数据。

3. 确认时间格式兼容性 #

你的CSV时间格式YYYY-MM-DD HH:MI:SS符合Redshift timestamp类型的默认解析规则，无需调整格式。

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