使用Redshift COPY通过JSON PATH加载驼峰式JSON至小写列遇NULL问题求助

场景与问题详情 #

需要将S3中存储的DynamoDB序列化JSON（驼峰式结构）复制到Redshift，Redshift表列名均为全小写格式：

• S3中的JSON示例：

{"accountId":{"S":"1acb4"}}

• 使用的JSON PATH文件内容：

{"jsonpaths": ["$.accountId.S"]}

• 执行的COPY语句：

COPY destination.table
FROM 's3://mybucket/myfile'
IAM_ROLE 'myarn'
JSON 's3://path/to/jsonpathsfile.json'

执行后Redshift表中得到NULL值，且因必须用JSON PATH解析DynamoDB JSON，无法启用ignorecase参数。

解决方法 #

1. 严格匹配JSON PATH数组与表列顺序 #

Redshift的COPY命令使用JSON PATH文件时，数组中路径的顺序必须和目标表的列顺序完全一致。

• 例如：若目标表结构为CREATE TABLE destination.table (account_id VARCHAR);，JSON PATH数组仅需包含["$.accountId.S"]，且位置对应唯一列即可；若表有多列，需按表列定义的先后顺序依次写入对应的JSON路径。

2. 验证JSON PATH文件的访问权限 #

确保Redshift使用的IAM角色拥有JSON PATH文件所在S3桶的访问权限，需配置至少s3:GetObject权限，若涉及桶列表操作还需s3:ListBucket权限。

3. 核对数据类型匹配性 #

目标表列的数据类型必须与JSON中提取的值类型匹配：

• 示例中accountId.S是字符串类型，Redshift列需定义为VARCHAR（或等价字符串类型），若定义为INT等不兼容类型会导致数据无法解析，返回NULL。

4. 启用COPY日志排查问题 #

在COPY语句中添加日志参数，查看具体错误信息定位问题：

COPY destination.table
FROM 's3://mybucket/myfile'
IAM_ROLE 'myarn'
JSON 's3://path/to/jsonpathsfile.json'
LOG 's3://mybucket/copy_logs/'

日志会记录诸如路径错误、权限不足、数据格式不匹配等详细问题。

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