Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Cannot load simple Json file with AWS Athena

I have a simple JSON file:

{'oldname':'mau'}

In AWS Athena I wish to read this file and I create the matching table t

CREATE EXTERNAL TABLE IF NOT EXISTS stats_json.t (
  `oldname` string 
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1'
) LOCATION 's3://mybucket/stats/';

then I try to query:

select * from t limit 10;

and get an error:

Query bceb274d-309f-40d5-a893-570de5f4ca4e failed with error code HIVE_CURSOR_ERROR: Row is not a valid JSON Object - JSONException: Missing value at 1 [character 2 line 1]

Where do I go wrong?

like image 542
maurizio Avatar asked Dec 18 '16 00:12

maurizio


1 Answers

I got it to work, so to answer my own question, the problem was with the format of the JSON file. It seems AWS Athena (well org.openx.data.jsonserde.JsonSerDe) is rather picky re: format of the JSON file it reads.

Each JSON record must be entirely on 1 line of text with no spaces between keys and values.

In Python, I generated the JSON records as follows:

import json
dStatsRecord = {} # a valid json dict
with open('myfile.json', 'r') as oFile:
  json.dump(dStatsRecord, oFile, separators=(',', ':'))
like image 111
maurizio Avatar answered Sep 19 '22 18:09

maurizio