CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-athena
rajeswari
rajeswari

Reputation: 309

Create table and query json data using Amazon Athena?

I want to query JSON data of format using Amazon Athena:

[{"id":"0581b7c92be",
  "key":"0581b7c92be",
  "value":{"rev":"1-ceeeecaa040"},
  "doc":{"_id":"0581b7c92be497d19e5ab51e577ada12","_rev":"1ceeeecaa04","node":"belt","DeviceId":"C001"}},
 {"id":"0581b7c92be49",
  "key":"0581b7c92be497d19e5",
  "value":{"rev":"1-ceeeecaa04031842d3ca"},
  "doc":{"_id":"0581b7c92be497","_rev":"1ceeeecaa040318","node":"belt","DeviceId":"C001"}
 }
]

Upvotes: 1

Views: 2380

Answers (1)

belostoky
belostoky

Reputation: 974

Athena DDL is based on Hive, so u will want each json object in your array to be in a separate line: 

{"id": "0581b7c92be", "key": "0581b7c92be", "value": {"rev": "1-ceeeecaa040"}, "doc": {"_id": "0581b7c92be497d19e5ab51e577ada12", "_rev": "1ceeeecaa04", "node": "belt", "DeviceId": "C001"} }
{"id": "0581b7c92be49", "key": "0581b7c92be497d19e5", "value": {"rev": "1-ceeeecaa04031842d3ca"}, "doc": {"_id": "0581b7c92be497", "_rev": "1ceeeecaa040318", "node": "belt", "DeviceId": "C001"} }

You might have problems with the nested fields ("value","doc"), so if you can flatten the jsons you will have it easier. (see for example: Hive for complex nested Json)

Upvotes: 4

Related Questions