Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive query to get data from json records

Hive query to get data from json records

New Contributor

Hive query giving null values when trying to pull the data from json which has @date column.below is the sample data and the query iam using.

{"name":"jai","@date":"2015-06-15"}

{"name":"pri","@date":"2017-08-25"}


CREATE TABLE json_obj( json string)

LOCATION '/user/*************/'


select

get_json_object(json_obj.json,'$.name') as name,

get_json_object(json_obj.json,'$.@date') as date

from json_obj;


ouput:

name date

jai null

pri null


expected output:

name date

jai 2015-06-15

pri 2017-08-25

1 REPLY 1

Re: Hive query to get data from json records

Super Guru

@srini

This is Hive bug reported here HIVE-2927.

Possible Workarounds would be:

Hive >=1.3.+

Then use replace function in hive with get_json_object

Example:

hive> select get_json_object(replace(jsn,'@',''),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

(or)

hive> select get_json_object(replace(jsn,'@date','date'),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

Hive <1.3:

Use regexp_replace function:

hive> select get_json_object(regexp_replace(jsn,'@',''),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

(or)

hive> select get_json_object(regexp_replace(jsn,'@date','date'),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
2015-06-15

-->Final query would be:

hive> select get_json_object(jsn,'$.name'),get_json_object(regexp_replace(jsn,'@',''),"$.date") from (select string('{"name":"jai","@date":"2015-06-15"}')jsn)t;
jai     2015-06-15

-

If the answer is helpful to resolve the issue, Login and Click on Accept button below to close this thread.This will help other community users to find answers quickly :-)