2

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

Example JSON format:

{  
   "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 website enter link description here

Xplora
  • 837
  • 3
  • 12
  • 24
Arthis
  • 53
  • 1
  • 8

2 Answers2

6

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
jens walter
  • 13,269
  • 2
  • 56
  • 54
3

Thank you for your reply

now ,i know this problem cause of query i think it's a sample SQL query

yesterday, I created table syntax below

  CREATE external TABLE monlyreport (
        Tapes array<struct< Status:string,
      Used:double,
      Barcode:string,
      SizeGB:double,
      UsedGB:double,
      Date:date >>
       )
    ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
    LOCATION 's3://bucket/test';

and Preview table get Output

tapes

    [{status=AVAILABLE, used=0.0, barcode=TEST5257F7, sizegb=107.0, usedgb=0.0, date=null}, {status=AVAILABLE, used=0.0, barcode=TEST5257F7, sizegb=107.0, usedgb=0.0, date=null}]

I try to query, but it doesn't work

now, i understand query for this problem 

select n.status,n.used,n.barcode,n.gateway,n.usedgb,n.date,n.sizegb from test
cross join UNNEST(test.Tapes) as t (n)

thank you very much

Arthis
  • 53
  • 1
  • 8