如何通过COPY命令将S3的JSON文件加载至Redshift并拆分嵌套字段

没问题！完全可以实现把S3上带嵌套结构的JSON文件加载到Redshift，同时将嵌套的information块拆分为独立列。我帮你梳理下具体操作步骤，还有需要注意的细节：

第一步：确保JSON文件格式合法 #

你给出的示例JSON键名没有加双引号，这不符合标准JSON规范，Redshift的COPY命令会无法解析。S3上的JSON文件必须是标准格式，比如：

{ 
  "message": 3, 
  "time": 1521488151, 
  "user": 39283, 
  "information": { 
    "bytes": 2342343, 
    "speed": 9392, 
    "location": "CA" 
  } 
}

第二步：创建目标Redshift表 #

我们直接把嵌套的information字段拆成独立列，另外注意：user是Redshift的保留关键字，直接用会报错，所以我这里改成user_id，你也可以用双引号包裹（比如"user"），但推荐改名更方便后续操作：

CREATE TABLE user_activity (
    message INT,
    time BIGINT,
    user_id INT,
    bytes BIGINT,
    speed INT,
    location VARCHAR(20)
);

第三步：使用COPY命令加载数据 #

这里有两种常用方法，你可以根据自己的场景选择：

方法1：使用JSON路径文件（适合复杂结构、可复用） #

先创建一个JSON路径文件，比如上传到s3://your-bucket/paths/json-paths.json，内容是每个表列对应的JSON路径：

{
  "jsonpaths": [
    "$.message",
    "$.time",
    "$.user",
    "$.information.bytes",
    "$.information.speed",
    "$.information.location"
  ]
}

然后执行COPY命令，指定路径文件的位置：

COPY user_activity
FROM 's3://your-bucket/json-files/'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3-Access-Role'
FORMAT JSON 's3://your-bucket/paths/json-paths.json';

方法2：直接在COPY命令中指定映射（适合简单结构、无需额外文件） #

如果你的结构比较简单，不想单独维护路径文件，可以直接在COPY命令里定义每个列对应的JSON路径：

COPY user_activity
FROM 's3://your-bucket/json-files/'
IAM_ROLE 'arn:aws:iam::123456789012:role/Redshift-S3-Access-Role'
FORMAT JSON AS '{"message": "$.message", "time": "$.time", "user_id": "$.user", "bytes": "$.information.bytes", "speed": "$.information.speed", "location": "$.information.location"}';

几个关键注意事项 #

• IAM权限：确保Redshift关联的IAM角色拥有S3目标桶的s3:GetObject权限，否则会加载失败。
• 压缩文件：如果你的JSON文件是gzip压缩的，只需在COPY命令末尾加上GZIP参数即可。
• 空值处理：如果某些字段可能缺失，可以添加NULL AS 'null'（根据你的空值标识调整）来避免加载错误。
• 验证数据：加载完成后，执行SELECT * FROM user_activity LIMIT 10;检查数据是否正确映射到对应列。

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