Select query in json serde returns Null value

Question

My log file contains a part of log in input_to_log.txt as below:

{"agent":"Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.101 Safari/537.36",
"context":{"course_id":"edx/AN101/2014_T1","module":{"display_name":"Multiple Choice Questions"},"org_id":"edx","user_id":9999999}}

My query in hive shell:

ADD JAR /usr/lib/hive/apache-hive-0.13.0-bin/lib/hive-serde-0.13.0.jar;

CREATE EXTERNAL TABLE edx_lg ( agent STRING,context STRUCT<course_id:STRING,module:STRUCT<display_name:STRING>,org_id:STRING,user_id:INT>)ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.JsonSerde' WITH SERDEPROPERTIES("agent"="$.agent","context"="$.context.course_id,$.context.module.display_name,$.context.org_id,$.context.user_id");

load data local inpath '/home/hduser/input_to_log.txt' into table edx_lg;

select agent,context.course_id,context.module.display_name,context.org_id,context.user_id from edx_lg;

My output:

Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201406102148_0019, Tracking URL = http://x.x.x.x:50030/jobdetails.jsp?jobid=job_201406102148_0019
Kill Command = /usr/local/hadoop/libexec/../bin/hadoop job  -kill job_201406102148_0019
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-06-12 03:00:42,300 Stage-1 map = 0%,  reduce = 0%
2014-06-12 03:00:44,311 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 0.72 sec
2014-06-12 03:00:46,324 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 0.72 sec
MapReduce Total cumulative CPU time: 720 msec
Ended Job = job_201406102148_0019

MapReduce Jobs Launched:

Job 0: Map: 1   Cumulative CPU: 0.72 sec   HDFS Read: 467 HDFS Write: 30 SUCCESS
Total MapReduce CPU Time Spent: 720 msec
OK
NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL
Time taken: 11.278 seconds, Fetched: 2 row(s)

Select statement is returning Null values.What should i do to get the data in my table?

Community · Accepted Answer · 2017-05-23 12:27:25Z

0

What does this query returns?

select * from edx_lg

Check if this helps: HIVE Query returning null values after import data from local stored file

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jun 12, 2014 at 10:29

Ramanan

9901 gold badge7 silver badges20 bronze badges

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

3 Comments

Mitali Over a year ago

It is returning Null values also

Ramanan Over a year ago

Check this stackoverflow.com/questions/11659976/sql-explode-an-array/… stackoverflow.com/questions/14705858/…

Mitali Over a year ago

Still it is returning Null values,even if tried with some other examples but still not working..

Collectives™ on Stack Overflow

Select query in json serde returns Null value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related