如何创建表AWS Athena->映射Json数组?

本文介绍了如何创建表AWS Athena->映射Json数组?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何为Json Array格式创建表Athena(AWS)?

How to create table Athena(AWS) for Json Array format?

JSON格式示例:

{
   "Tapes":[
      {
         "Status":"AVAILABLE",
         "Used":0.0,
         "Barcode":"TEST1217F7",
         "Gateway":"Test_Report",
         "UsedGB":0.0,
         "Date":"06-11-2017",
         "SizeGB":107.0
      },
      {
         "Status":"AVAILABLE",
         "Used":0.0,
         "Barcode":"TEST1227F7",
         "Gateway":"Test_Report",
         "UsedGB":0.0,
         "Date":"06-11-2017",
         "SizeGB":107.0
      }
   ]
}

我想在下面获取输出格式:
在此处输入图片描述

I want to get output format below:
enter image description here

我已经尝试根据该网站解决问题在此处输入链接描述

I have tried to solve the problem according to this websiteenter link description here

推荐答案

从示例JSON派生，您可以创建下表.

Derived from your sample JSON, you can create the following table.

create external table test(
  Tapes array<struct<
        Status:string,
        Used:string,
        Barcode:string,
        Gateway:string,
        UsedGB:string,
        Date:string,
        SizeGB:string>>
) ROW FORMAT  serde 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://bucket/test'

有了该表，您可以通过以下方式查询所有数组元素.

With that table, you can query all array elements the following way.

select t1.* from test
cross join UNNEST(test.Tapes) as t1

这篇关于如何创建表AWS Athena->映射Json数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！