如何在AWS Athena中创建支持解析匿名JSON数组的外部表

你之前的两种配置都存在问题：

1. 第一种配置strip.outer.array=true的作用是将根级JSON数组直接展开，数组内的每个对象对应表的一行，此时不需要再额外定义外层struct字段，你多套了一层data struct导致仅能匹配到数组第一个对象的结构，剩余内容被丢弃。
2. 第二种配置strip.outer.array=false时，org.openx.data.jsonserde.JsonSerDe处理根级匿名数组的逻辑存在限制，仅会读取数组的第一个元素写入行数据，所以你定义的array字段永远只有1个元素，UNNEST后也只能拿到第一个结果。

以下是两种可行的解决方案： #

方案1：调整建表结构配合strip.outer.array参数 #

这种方案不需要修改查询逻辑，建表后直接查询即可拿到所有行：

CREATE EXTERNAL TABLE IF NOT EXISTS testdata.trynum85 (
  `key1` string,
  `key2` string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'strip.outer.array'='true'
) LOCATION 's3://mys3/new-json-structure/'
TBLPROPERTIES ('has_encrypted_data'='false');

建表完成后直接执行查询即可返回所有结果：

SELECT key1 FROM testdata.trynum85

方案2：整行读取后用内置JSON函数解析 #

这种方案兼容性更强，不受SerDe功能限制，适合JSON结构复杂的场景：

第一步：建表将整行数据读取为原始字符串 #

CREATE EXTERNAL TABLE IF NOT EXISTS testdata.trynum85_raw (
  raw_json string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'field.delim' = '|' -- 选择源数据中不存在的字符作为分隔符，保证整行数据都被读取到raw_json字段
) LOCATION 's3://mys3/new-json-structure/'
TBLPROPERTIES ('has_encrypted_data'='false');

第二步：查询时解析JSON数组并展开 #

SELECT stuff.key1
FROM testdata.trynum85_raw
CROSS JOIN UNNEST(CAST(json_parse(raw_json) AS ARRAY<STRUCT<key1:string, key2:string>>)) AS t(stuff)

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