在 AWS Athena 中的数组中提取 json

Question

我已经将日志从 kubernetes 发送到 S3 存储桶，并希望使用 Athena 进行查询

日志如下所示

[{      "date":1589895855.077230,
"log":"192.168.85.35 - - [19/May/2020:13:44:15 +0000] "GET /healthz HTTP/1.1" 200 3284 "-" "ELB-HealthChecker/2.0" "-"",
"stream":"stdout",
"time":"2020-05-19T13:44:15.077230187Z",
"kubernetes":{
"pod_name":"myapp-deployment-cd984ffb-kjfbm",
"namespace_name":"master",
"pod_id":"eace0175-99cd-11ea-95e4-0aee746ae5d6",
"labels":{
"app":"myapp",
"pod-template-hash":"cd984ffb"
},
"annotations":{
"cluster-autoscaler.kubernetes.io/safe-to-evict":"false",
"kubernetes.io/psp":"eks.privileged"
},
"host":"ip-1-1-1-1.eu-north-1.compute.internal",
"container_name":"myapp",
"docker_id":"cb2cda1ed46c5f09d15090fc3f654b1de35970001e366923287cfbd4a4abf4a1"
}
},
{      "date":1589995860.077230,
"log":"192.168.1.40 - - [20/May/2020:17:31:00 +0000] "GET /healthz HTTP/1.1" 200 3284 "-" "ELB-HealthChecker/2.0" "-"",
"stream":"stdout",
"time":"2020-05-20T17:31:00.077230187Z",
"kubernetes":{
"pod_name":"myapp-deployment-cd984ffb-kjfbm",
"namespace_name":"master",
"pod_id":"eace0175-99cd-11ea-95e4-0aee746ae5d6",
"labels":{
"app":"myapp",
"pod-template-hash":"cd984ffb"
},
"annotations":{
"cluster-autoscaler.kubernetes.io/safe-to-evict":"false",
"kubernetes.io/psp":"eks.privileged"
},
"host":"ip-1-1-1-1.eu-north-1.compute.internal",
"container_name":"myapp",
"docker_id":"cb2cda1ed46c5f09d15090fc3f654b1de35970001e366923287cfbd4a4abf4a1"
}
},]

所以基本上是一个包含 json 对象的数组。

我正在使用雅典娜中的创建外部表查询来创建表。 我尝试的是：

CREATE EXTERNAL TABLE IF NOT EXISTS athenadb.mytable (
`data` string
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://mybucket/testlog'
TBLPROPERTIES ('has_encrypted_data'='false');

这只会将数组中的第一项读取到表中，除非我指定了更多行，例如

数据1 字符串

数据2 字符串

数据3 字符串

但是，由于我不知道数组中有多少项，因此我需要更动态的东西。

然后我试了这个

CREATE EXTERNAL TABLE IF NOT EXISTS athenadb.mytable (
`data` string
)
LOCATION 's3://mybucket/testlog'
TBLPROPERTIES ('has_encrypted_data'='false');

现在，我将整个日志(两个条目(放在表中的一行中。

从这里我尝试使用 UNNEST，但我收到"无法取消嵌套类型：varchar"的错误

将每个 {} 放入表中自己的行的最简单方法是什么？也许从创建外部表完成，之后不需要任何额外的查询？

编辑：

现在也试过了

SELECT data
FROM mytable
CROSS JOIN UNNEST(CAST(json_parse(data) AS array)) AS data2

但是我得到"未知类型：数组">

我在这里发现了一个类似的问题：如何将数据数组导入到 Hive 表中的单独行中？

但似乎没有任何建议的解决方案可以产生想要的结果。

Answer 1

将unnest与强制转换json组合到array(json)：

SELECT data, e
FROM mytable
CROSS JOIN UNNEST(CAST(json_parse(data) AS array(json))) t(e)

注意：array<json>是array(json)类型定义的旧版本。后者符合SQL标准。