如何基于S3存储的JSON事件文件创建AWS Athena表并解决格式解析问题

AWS Athena解析外层带数组括号JSON的解决方案 #

针对当前无转义符、仅外层包裹数组括号[...]的JSON格式，可通过以下两种方案实现正常解析： #

方案1：调整表结构适配数组格式 #

将表根字段定义为数组嵌套结构体类型，查询时通过UNNEST展开数组元素即可，建表语句如下：

CREATE EXTERNAL TABLE events (
  event array<struct<
    String1:int,
    String2:string,
    String3:string
  >>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://events/';

查询示例：

SELECT 
  e.String1,
  e.String2,
  e.String3
FROM events
CROSS JOIN UNNEST(event) AS t(e)

方案2：配置SerDe参数自动展开根数组 #

无需调整查询逻辑，通过添加SerDe配置参数让解析器直接读取数组内的元素，建表语句如下：

CREATE EXTERNAL TABLE events (
  String1 int,
  String2 string,
  String3 string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  "root.array.path" = "$",
  "ignore.malformed.json" = "true"
)
LOCATION 's3://events/';

root.array.path="$"参数会指定解析器将根路径的数组直接按元素拆分为行，可直接按普通字段查询数据。 #

旧带转义符JSON格式兼容方案 #

如果需要处理带有转义符\的历史数据，在上述SerDe配置中额外添加参数 "escape.delim" = "\\" 即可正确识别转义的双引号。

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