- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Hive query to get data from json records
- Labels:
-
Apache Hive
Created 06-26-2019 01:29 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created 06-26-2019 04:36 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 🙂