如何解决AWS Athena将完整JSON对象加载到单个字段的问题

问题背景 #

尝试将S3中的JSON数据加载到Athena表中，但返回结果不符合预期。

原始JSON数据格式 #

[{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":1, "e":2}},
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":2, "e":3}},
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":3, "e":4}},
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":4, "e":5}},
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":5, "e":6}}]

建表语句 #

CREATE EXTERNAL TABLE IF NOT EXISTS `my_db`.`my_table`(
    `a` string,
    `b` string,
    `my_data` STRUCT< 
        `c`: STRING,
        `d`: INT, 
        `e`: INT>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'ignore.malformed.json' = 'FALSE',
  'dots.in.keys' = 'FALSE',
  'case.insensitive' = 'TRUE',
  'mapping' = 'TRUE'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://myjsondata/'
TBLPROPERTIES ('classification' = 'json');

当前错误结果 #

• 表包含a、b、my_data三列
• 仅返回一行数据
• 列a的值为{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":1, "e":2}
• 列b的值为{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":2, "e":3}
• 列my_data的值为{"c":null, "d":null, "e":null}

期望结果 #

问题原因 #

1. JSON格式不兼容：Athena默认要求JSON数据采用JSON Lines格式（每行一个独立JSON对象），而当前数据是一个完整的JSON数组。TextInputFormat会把整个数组当作单行处理，导致SerDe无法正确拆分解析每个对象。
2. SerDe解析逻辑限制：org.openx.data.jsonserde.JsonSerDe默认只能解析单个JSON对象，无法直接处理数组结构，因此出现字段映射混乱、嵌套字段为空的情况。

解决方法 #

方法1：修改JSON为JSON Lines格式 #

将原数组拆分为每行一个JSON对象，修改后的文件内容如下：

{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":1, "e":2}}
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":2, "e":3}}
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":3, "e":4}}
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":4, "e":5}}
{"a":"a_value", "b":"b_value", "my_data":{"c":"c_value", "d":5, "e":6}}

重新执行查询后，即可正确解析数据。若需要展开my_data中的字段，可使用点符号查询：

SELECT a, b, my_data.c, my_data.d, my_data.e FROM my_db.my_table;

方法2：通过临时表+UNNEST处理原数组 #

如果无法修改S3中的原始数据，可创建临时表读取整个数组，再通过UNNEST展开：

1. 创建临时表：

CREATE EXTERNAL TABLE IF NOT EXISTS `my_db`.`temp_table`(
    data ARRAY<STRUCT<
        a: STRING,
        b: STRING,
        my_data: STRUCT<c: STRING, d: INT, e: INT>
    >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://myjsondata/'
TBLPROPERTIES ('classification' = 'json');

2. 查询时展开数组：

SELECT 
    item.a, 
    item.b, 
    item.my_data.c, 
    item.my_data.d, 
    item.my_data.e 
FROM my_db.temp_table
CROSS JOIN UNNEST(data) AS t(item);

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