Redshift从S3更新表报语法错误，求技术解决方案

错误原因 #

你的SQL语法存在问题，Redshift不支持直接在FROM子句中把S3文件路径当作数据源使用。必须通过临时表或者Redshift Spectrum查询读取S3中的CSV数据，再关联目标表执行更新操作。

解决方案一：通过临时表更新 #

这是最常用的实现方式，步骤如下：

1. 创建与CSV文件、目标表结构匹配的临时表：

CREATE TEMP TABLE temp_s3_data (
    bid [对应数据类型],
    user_id [对应数据类型],
    username [对应数据类型],
    total_sum [对应数据类型],
    amount_currency [对应数据类型],
    allowance_name [对应数据类型],
    ledgerentrytype [对应数据类型],
    transaction_timestamp_group VARCHAR, -- 对应CSV中的字段类型，后续再转换为TIMESTAMP
    employer [对应数据类型],
    taxreportingstatus [对应数据类型]
);

2. 将S3中的CSV数据导入临时表：

COPY temp_s3_data
FROM 's3://xx-xx-xx/xx-xx-x/x-x-xx/xxx.csv'
IAM_ROLE 'arn:aws:iam::xxxxx:role/service-role/xxxx-xx-xx-xx'
FORMAT AS CSV 
IGNOREHEADER 1; -- 如果CSV包含表头，添加此参数忽略第一行

3. 关联临时表更新目标表：

UPDATE "red"."shift"."table" t
SET 
    bid = s.bid,
    user_id = s.user_id,
    username = s.username,
    total_sum = s.total_sum,
    amount_currency = s.amount_currency,
    allowance_name = s.allowance_name,
    ledgerentrytype = s.ledgerentrytype,
    transaction_timestamp_group = CAST(s.transaction_timestamp_group AS TIMESTAMP),
    employer = s.employer,
    taxreportingstatus = s.taxreportingstatus
FROM temp_s3_data s
WHERE s.bid = t.bid; -- 通过主键关联，仅更新匹配的已有数据行

解决方案二：直接通过Redshift Spectrum读取S3数据更新 #

若已配置外部Schema，可直接在FROM子句中查询S3数据：

UPDATE "red"."shift"."table" t
SET 
    bid = s.bid,
    user_id = s.user_id,
    username = s.username,
    total_sum = s.total_sum,
    amount_currency = s.amount_currency,
    allowance_name = s.allowance_name,
    ledgerentrytype = s.ledgerentrytype,
    transaction_timestamp_group = CAST(s.transaction_timestamp_group AS TIMESTAMP),
    employer = s.employer,
    taxreportingstatus = s.taxreportingstatus
FROM (
    SELECT 
        bid,
        user_id,
        username,
        total_sum,
        amount_currency,
        allowance_name,
        ledgerentrytype,
        transaction_timestamp_group,
        employer,
        taxreportingstatus
    FROM S3Object
    LOCATION 's3://xx-xx-xx/xx-xx-x/x-x-xx/xxx.csv'
    IAM_ROLE 'arn:aws:iam::xxxxx:role/service-role/xxxx-xx-xx-xx'
    FORMAT AS CSV
    IGNOREHEADER 1 -- 按需添加表头忽略参数
) s
WHERE s.bid = t.bid;

注意事项 #

• 确保临时表或S3查询的字段数据类型与目标表匹配，避免类型转换错误
• 验证S3路径和IAM角色权限正确，确保Redshift具备访问该S3资源的权限

内容的提问来源于stack exchange，提问作者Alfredo Suárez