使用Redshift COPY命令从DynamoDB加载数据时处理嵌套字段的方案问询

问题背景 #

DynamoDB中的数据包含嵌套的M类型字段，尝试直接加载到Redshift时遇到两种问题：

1. 扁平化表加载后嵌套字段值为NULL
2. 使用SUPER类型时触发"Unsupported Data Type"错误

DynamoDB数据结构：

"Status": {
    "S": "queued"
},
"optional": {
    "M": {
      "file_size": {
        "N": "1.884"
      },
      "records_count": {
        "N": "1276"
      },
      "submission_timestamp": {
        "N": "1716764400"
      }
    }
}

可行解决方案 #

方案1：使用JSONPaths文件配合COPY命令直接映射嵌套字段 #

Redshift的COPY命令支持通过JSONPaths文件定义DynamoDB字段到Redshift表的映射路径，无需提前扁平化数据到S3。

步骤1：创建JSONPaths文件

在S3存储桶中创建一个JSONPaths文件（例如s3://your-bucket/paths.json），内容如下：

{
    "jsonpaths": [
        "$.Status.S",
        "$.optional.M.file_size.N",
        "$.optional.M.records_count.N",
        "$.optional.M.submission_timestamp.N"
    ]
}

该文件定义了从DynamoDB的嵌套结构中提取对应字段的路径。

步骤2：执行COPY命令

使用包含JSONPaths参数的COPY命令加载数据：

COPY my_redshift_table
FROM 'dynamodb://ddp-dev-euwest1-bookkeeper-v2'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-redshift-copy-role'
JSON 's3://your-bucket/paths.json';

此命令会按照JSONPaths定义的路径直接解析嵌套字段，填充到扁平化的Redshift表中。

方案2：通过Redshift Spectrum查询DynamoDB并解析嵌套字段 #

利用Redshift Spectrum创建DynamoDB外部表，支持直接解析嵌套的M类型结构，然后可以直接查询或插入到Redshift本地表。

步骤1：创建外部Schema

首先创建关联到Glue Data Catalog的外部Schema：

CREATE EXTERNAL SCHEMA dynamodb_ext_schema
FROM DATA CATALOG
DATABASE 'your-glue-db-name'
IAM_ROLE 'arn:aws:iam::your-account-id:role/your-redshift-spectrum-role';

步骤2：创建DynamoDB外部表

定义外部表结构，匹配DynamoDB的嵌套类型：

CREATE EXTERNAL TABLE dynamodb_ext_schema.my_dynamodb_table (
    status STRING,
    optional STRUCT<file_size: DOUBLE, records_count: BIGINT, submission_timestamp: BIGINT>
)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES (
    "dynamodb.table.name" = "ddp-dev-euwest1-bookkeeper-v2",
    "dynamodb.column.mapping" = "status:Status,optional:optional"
);

步骤3：查询并插入到Redshift本地表

直接从外部表提取嵌套字段，插入到目标表：

INSERT INTO my_redshift_table (status, optional_file_size, optional_records_count, optional_submission_timestamp)
SELECT 
    status,
    optional.file_size,
    optional.records_count,
    optional.submission_timestamp
FROM dynamodb_ext_schema.my_dynamodb_table;

原方案失败原因说明 #

• 扁平化表加载返回NULL：未指定字段映射规则，Redshift无法自动识别DynamoDB的嵌套M类型结构，无法提取对应字段值。
• SUPER类型报错：部分Redshift版本的DynamoDB COPY命令暂不支持直接将复杂嵌套类型加载到SUPER字段，而Spectrum或JSONPaths方式可绕过此限制。

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