Hive with JSON: getting EOF exception while querying

0 votes

I am facing some problem while querying HIVE

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 similar to this

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'
Aug 30, 2018 in Big Data Hadoop by Frankie
• 9,570 points
51 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

You are missing the commas in your query, 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;
answered Aug 30, 2018 by Neha
• 6,140 points

Related Questions In Big Data Hadoop

+3 votes
1 answer

Getting Connection Error while loading data into table using cloudera hive

Hey Nafeesa, Itseems that Hive is not able ...READ MORE

answered Oct 3, 2018 in Big Data Hadoop by Vardhan
• 10,560 points
40 views
0 votes
1 answer

Getting error while loading data into hive table

In the command you have used, the ...READ MORE

answered Jan 30 in Big Data Hadoop by Omkar
• 65,840 points
59 views
+1 vote
1 answer
0 votes
1 answer
0 votes
1 answer

Hadoop Mapreduce word count Program

Firstly you need to understand the concept ...READ MORE

answered Mar 16, 2018 in Data Analytics by nitinrawat895
• 9,030 points
1,649 views
0 votes
10 answers

hadoop fs -put command?

copy command can be used to copy files ...READ MORE

answered Dec 7, 2018 in Big Data Hadoop by Sujay
7,990 views
0 votes
1 answer

Hadoop dfs -ls command?

In your case there is no difference ...READ MORE

answered Mar 16, 2018 in Big Data Hadoop by kurt_cobain
• 9,260 points
555 views
0 votes
1 answer
0 votes
1 answer

How to convert a string to timestamp with milliseconds in Hive?

 concatenation of substrings using the following code: select ...READ MORE

answered Oct 31, 2018 in Big Data Hadoop by Neha
• 6,140 points
715 views
0 votes
1 answer

How to pause and resume hive job?

Practically speaking, it's difficult/impossible to pause and resume ...READ MORE

answered Jul 17, 2018 in Big Data Hadoop by Neha
• 6,140 points
34 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.