CODEWITHSUNDEEP
sqljsonhadoophive
Lukas Müller
Lukas Müller

Reputation: 101

EOF Exception querying Hive on JSON

I'm new to HIVE and need some help querying a table which i created from a JSON file. 

CREATE TABLE json_table( json string);
LOAD DATA INPATH  '/path/to/file.json' 
INTO TABLE json_table;

When I query the created json_table.json with:

SELECT * FROM json_table;

This returns:

{"id":243379853,"sampling_rate":null,"timestamp":"2017-08-06 20:05:02","location":{"id":1296,"latitude":"49.863","longitude":"8.651","country":"DE"},"sensor":{"id":2573,"pin":"7","sensor_type":{"id":9,"name":"DHT22","manufacturer":"various"}},"sensordatavalues":[{"id":559959584,"value":"19.00","value_type":"temperature"},{"id":559959585,"value":"86.00","value_type":"humidity"}]}

I'm trying to get a result like

id        | timestampt |         | [...] | 
==========|======================|=======| 
243379853 | 2017-08-06 20:05:02  | [...] |

with the query 

SELECT
  GET_JSON_OBJECT(json_table.json,'$.id'),
  GET_JSON_OBJECT(json_table.json,'$.timestamp')
  GET_JSON_OBJECT(json_table.json,'$.sampling_rate')
  GET_JSON_OBJECT(json_table.json,'$.location.latitude')
  GET_JSON_OBJECT(json_table.json,'$.location.longitude')
FROM json_table;

But i get the following Hive SQL Exception

java.lang.Exception: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 4:17 missing EOF at '(' near 'GET_JSON_OBJECT'

Upvotes: 2

Views: 216

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You need to add commas:

SELECT
  GET_JSON_OBJECT(json_table.json,'$.id'),
  GET_JSON_OBJECT(json_table.json,'$.timestamp'),   --here
  GET_JSON_OBJECT(json_table.json,'$.sampling_rate'), --here
  GET_JSON_OBJECT(json_table.json,'$.location.latitude'), -- and here
  GET_JSON_OBJECT(json_table.json,'$.location.longitude')
FROM json_table;

I would also add aliases for columns using expr AS alias.

Upvotes: 1

Related Questions