amazon web services - aws athena - Create table by an array of json object

Question

Can I get help in creating a table on AWS Athena. For a sample example of data :

[{"lts": 150}]

AWS Glue generate the schema as :

 array (array<struct<lts:int>>)

When I try to use the created table by AWS Glue to preview the table, I had this error:

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

The message error is clear, but I can't find the source of the problem!

Answer 1

Hive running under AWS Athena is using Hive-JSON-Serde to serialize/deserialize JSON. For some reason, they don't support just any standard JSON. They ask for one record per line, without an array. In their words:

The following example will work.

{ "key" : 10 }
{ "key" : 20 }

But this won't:

{
  "key" : 20,
}

Nor this:

[{"key" : 20}]