AWS Athena中如何映射JSON的List<List<Object>>数据类型？

问题背景 #

我正在将以下JSON格式的数据上传至AWS S3存储桶：

{
  "time": 1663090620000,
  "data": [
    [
      1,
      [
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0,
        0
      ]
    ]
  ]
}

该JSON字段对应的自定义数据类型如下：

time - Long
data - List<List<Object>>

我尝试通过指向S3存储桶并使用分区投影功能在Athena中创建了如下表结构，但无法将JSON中的data字段映射到AWS Athena支持的数据类型：

CREATE EXTERNAL TABLE `test_data_db`.`test_data_table`(
  `time` bigint COMMENT 'from deserializer', 
  `data` array<array<double>> COMMENT 'from deserializer')
PARTITIONED BY ( 
  `id` string, 
  `creation_date` date
)
ROW FORMAT SERDE 
  'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-data-bucket/test-data'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.creation_date.format'='yyyy-MM-dd', 
  'projection.creation_date.range'='2010-01-01,2050-12-31', 
  'projection.creation_date.type'='date', 
  'projection.enabled'='true', 
  'projection.id.type'='injected', 
  'storage.location.template'='s3://test-data-bucket/test-data/id=${id}/creation_date=${creation_date}', 
  'transient_lastDdlTime'='1663357524')

问题分析 #

你的data字段是嵌套数组结构，且内层数组包含两种不同类型：数值和另一个数组，而你定义的array<array<double>>仅支持双层数值数组，类型不匹配导致映射失败。Athena基于Hive生态，需要用更灵活的类型或SerDe来适配这种混合嵌套结构。

解决方法 #

方案1：改用OpenX JSON SerDe + JSON类型 #

OpenX的JSON SerDe比Hive原生版本更适配复杂嵌套结构，配合Athena的json类型可直接存储任意JSON结构：

CREATE EXTERNAL TABLE `test_data_db`.`test_data_table`(
  `time` bigint COMMENT 'from deserializer', 
  `data` array<json> COMMENT 'from deserializer')
PARTITIONED BY ( 
  `id` string, 
  `creation_date` date
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-data-bucket/test-data'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.creation_date.format'='yyyy-MM-dd', 
  'projection.creation_date.range'='2010-01-01,2050-12-31', 
  'projection.creation_date.type'='date', 
  'projection.enabled'='true', 
  'projection.id.type'='injected', 
  'storage.location.template'='s3://test-data-bucket/test-data/id=${id}/creation_date=${creation_date}', 
  'transient_lastDdlTime'='1663357524')

查询时可通过JSON函数解析数据：

SELECT 
  time,
  json_extract_array_element_text(data[1], 0) AS first_value,
  json_extract_array_element(data[1], 1) AS nested_array
FROM test_data_db.test_data_table

方案2：使用UnionType强类型约束 #

如果需要严格的类型定义，可以用Hive的uniontype适配混合类型：

CREATE EXTERNAL TABLE `test_data_db`.`test_data_table`(
  `time` bigint COMMENT 'from deserializer', 
  `data` array<array<uniontype<bigint, array<double>>>> COMMENT 'from deserializer')
PARTITIONED BY ( 
  `id` string, 
  `creation_date` date
)
ROW FORMAT SERDE 
  'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-data-bucket/test-data'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.creation_date.format'='yyyy-MM-dd', 
  'projection.creation_date.range'='2010-01-01,2050-12-31', 
  'projection.creation_date.type'='date', 
  'projection.enabled'='true', 
  'projection.id.type'='injected', 
  'storage.location.template'='s3://test-data-bucket/test-data/id=${id}/creation_date=${creation_date}', 
  'transient_lastDdlTime'='1663357524')

查询时通过get_union_type提取对应类型的值：

SELECT 
  time,
  get_union_type(data[1][0], 0) AS first_value,
  get_union_type(data[1][1], 1) AS nested_array
FROM test_data_db.test_data_table

方案3：先存为字符串，查询时解析 #

如果上述方案不适用，可先将data存为字符串，再在查询时解析：

CREATE EXTERNAL TABLE `test_data_db`.`test_data_table`(
  `time` bigint COMMENT 'from deserializer', 
  `data` string COMMENT 'from deserializer')
PARTITIONED BY ( 
  `id` string, 
  `creation_date` date
)
ROW FORMAT SERDE 
  'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-data-bucket/test-data'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.creation_date.format'='yyyy-MM-dd', 
  'projection.creation_date.range'='2010-01-01,2050-12-31', 
  'projection.creation_date.type'='date', 
  'projection.enabled'='true', 
  'projection.id.type'='injected', 
  'storage.location.template'='s3://test-data-bucket/test-data/id=${id}/creation_date=${creation_date}', 
  'transient_lastDdlTime'='1663357524')

查询示例：

SELECT 
  time,
  json_extract_array_element_text(json_parse(data)[0], 0) AS first_value,
  json_extract_array_element(json_parse(data)[0], 1) AS nested_array
FROM test_data_db.test_data_table

总结 #

优先推荐方案1，OpenX SerDe配合JSON类型既保留了数据结构的灵活性，又能方便地在查询时解析；如果需要强类型约束，可选择方案2的UnionType方案。

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