在Athena中读取结构不一致的嵌套JSON的技术咨询

我之前处理过类似的Athena嵌套JSON解析问题，正好能帮你解决这两个疑问：

问题1：能否让嵌套JSON中缺失的字段自动填充为null？ #

当然可以！关键是正确定义表的嵌套结构，并使用支持嵌套JSON解析的SerDe（序列化/反序列化器）。Athena默认的CSV SerDe不适合处理嵌套JSON，你需要用org.openx.data.jsonserde.JsonSerDe（或者AWS官方的com.amazonaws.athena.connectors.json.JsonSerDe），只要在DDL中将所有可能出现的字段都定义到STRUCT层级里，缺失的字段就会自动填充为null。

举个适配你数据结构的DDL例子：

CREATE EXTERNAL TABLE IF NOT EXISTS your_scene_data (
  id STRING,
  date STRING,
  data STRUCT<
    a: STRING,
    b: STRING,
    body: STRUCT<
      sid: STRUCT<
        uif: STRING,
        sidd: STRING,
        state: STRING
      >,
      persona: STRUCT<
        one: STRUCT<
          movement: STRING
        >
      >
    >,
    category: STRING
  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true', -- 跳过格式错误的行，避免全表解析失败
  'dots.in.keys' = 'false'
)
LOCATION 's3://your-bucket/path/to/your/json/files/'
TBLPROPERTIES ('has_encrypted_data' = 'false');

在这个结构里，不管你的JSON里只存在sid、只存在persona，还是两者都有，Athena都会正确解析存在的字段，缺失的字段自动设为null。比如只包含sid的行，data.body.persona的值就是null，反之亦然。

问题2：能否直接指定JSON键与Athena字段名的对应关系？ #

完全可以，分两种情况：

1. 字段名与JSON键同名（大多数情况） #

这种情况下不需要额外配置，只要你在STRUCT中定义的字段名和JSON的键完全一致，Athena会自动映射。就像上面的DDL例子，data.a对应JSON里的data.a，data.body.sid对应JSON里的data.body.sid，缺失的键自动对应null。

2. 字段名与JSON键不同名（需要自定义映射） #

如果你想把JSON里的某个键映射到不同的Athena字段名，可以通过SerDe的mapping属性配置。比如你想把JSON的sid映射到Athena的session_id，可以在SERDEPROPERTIES里添加：

'mapping.session_id' = 'sid'

对应的DDL片段：

WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'true',
  'dots.in.keys' = 'false',
  'mapping.session_id' = 'sid' -- 自定义JSON键与Athena字段的映射
)

为什么你之前的data列是空？ #

大概率是这两个原因：

• 用了错误的SerDe：比如默认的CSV SerDe无法解析嵌套JSON，导致整个data字段解析失败为空；
• 结构定义不匹配：比如你定义的data结构体没有覆盖所有子字段，或者层级错误，导致Athena无法正确识别嵌套结构。

换用上面的DDL应该就能解决这个问题啦！

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