error when loading json data in hive

Question

JSON data looks like :

{"id":"U101", "name":"Rakesh", "place":{"city":"MUMBAI","state":"MAHARASHTRA"}, "age":20, "occupation":"STUDENT"}
{"id":"","name":"Rakesh", "place":{"city":"MUMBAI","state":"MAHARASHTRA"}, "age":20, "occupation":"STUDENT"}
{"id":"U103", "name":"Rakesh", "place":{"city":"","state":""}, "age":20, "occupation":"STUDENT"}

I get the following error while trying to select the data from the table :

hive (ecom)> select * from users_info_raw; 
OK 
Failed with exception java.io.IOException:org.apache.hadoop.hive.serde2.SerDeException:
org.codehaus.jackson.JsonParseException: Unexpected character ('2'
(code 50)): was expecting comma to separate OBJECT entries  at
[Source: java.io.StringReader@15b0734; line: 1, column: 222] 
Time taken: 0.144 seconds

Create Table DDL Query:

CREATE TABLE users_info_raw(
       > id string,
       > name string,
       > place struct<city:string,state:string>,
       > age INT,
       > occupation string
       > )
       > ROW FORMAT SERDE
       > 'com.cloudera.hive.serde.JSONSerDe'
       > STORED AS INPUTFORMAT
       > 'org.apache.hadoop.mapred.TextInputFormat'
       > OUTPUTFORMAT
       > 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

Place age 20 in double quotes "20", hope this will solve your problem. — deepakrajbhar
– deepakrajbhar, Commented Sep 19, 2017 at 10:54

Dipak Shaw · Accepted Answer · 2017-09-19 13:53:21Z

2

I have used hive hcatalog serde and it is working fine with your input data.

CREATE TABLE info_raw( id string, name string, place struct<city:string,state:string>, age INT, occupation string ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

answered Sep 19, 2017 at 13:53

Dipak Shaw

4013 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

error when loading json data in hive

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related