AWS Athena JSON多维数组结构 [英] AWS Athena JSON Multidimentional Array Structure

问题描述

JSON文件的结构如下：

The JSON file has a structure like this:

"otherstuff" : "stuff",
"ArrayofArrays" : {
   "Array-1" : {
      "type" : "sometype",
      "is_enabled" : false,
      "is_active" : false,
      "version" : "version 1.1"
   },
   "Array-2" : {
      "type" : "sometype",
      "is_enabled" : false,
      "is_active" : false,
      "version" : "version 1.2"
   }
   ...
}

使用以下命令运行查询

CREATE EXTERNAL TABLE IF NOT EXISTS test2.table14 (
`otherstuff` string,
`ArrayofArrays` array<array<struct<version:string>>>
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
     'serialization.format' = '1' ) LOCATION 's3://bucket/folder/' TBLPROPERTIES ('has_encrypted_data'='false')

但是当我运行基本p复查表查询我得到以下错误。

However when I run a basic preview table query I get the following error.

HIVE_BAD_DATA：解析字段＃的字段值时出错：org.openx.data.jsonserde.json.JSONObject无法转换为org.openx .data.jsonserde.json.JSONArray

HIVE_BAD_DATA: Error parsing field value for field #: org.openx.data.jsonserde.json.JSONObject cannot be cast to org.openx.data.jsonserde.json.JSONArray

显然数组数组存在问题。我无法弄清楚应该是什么结构。您知道如何为该结构声明多维数组吗？

There is clearly an issue with the array of array. I am unable to figure out what the structure should be. Do you know how to declair the multidimentional array for this structure?

推荐答案

您的JSON不包含任何数组元素。因此，必须使用map列类型而不是数组来访问此结构。

Your JSON does not contain any array elements. So instead of an array, you have to use the map column type, to access this structure.

CREATE EXTERNAL TABLE test14 (
  otherstuff string,
  ArrayofArrays map<string,struct<
     is_enabled:boolean,
     is_active:boolean,
     type:string,
     version:string
  >>
) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('serialization.format' = '1' ) 
LOCATION 's3://bucket/'

您可以通过以下查询语法访问地图：

You can access the map through the following query syntax:

select t.ArrayofArrays['array-1'] from test14 t

这篇关于AWS Athena JSON多维数组结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！