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.

Querying JSON Data in Hive - returning null

Solved Go to solution
Highlighted

Querying JSON Data in Hive - returning null

Contributor

Content of stock2.json file

{"myid":"0001","mytype":"donut"}

stock2.json file is in the path of '/warehouse/tablespace/external/hive/haijintest.db/stock2';

Below is the table creation script.

CREATE EXTERNAL TABLE stock4_json (myjson struct<myid:string, mytype:string>)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION '/warehouse/tablespace/external/hive/haijintest.db/stock2';

Below is the query that returned null value.

0: jdbc:hive2://> select myjson.myid from stock4_json;
19/05/27 20:20:18 [103d2343-eb59-4072-8a07-5469b2112093 main]: WARN optimizer.SimpleFetchOptimizer: Table haijintest@stock4_json is external table, falling back to filesystem scan.
OK
+-------+
| myid  |
+-------+
| NULL  |
+-------+
1 row selected (0.232 seconds)
0: jdbc:hive2://> select * from stock4_json;
OK
+---------------------+
| stock4_json.myjson  |
+---------------------+
| NULL                |
+---------------------+
1 row selected (0.242 seconds)

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Querying JSON Data in Hive - returning null

Expert Contributor

Hi @Haijin Li

The input file you are using is incorrect stock2.json

{"myid":"0001","mytype":"donut"}

command is correct

CREATE EXTERNAL TABLE stock4_json (myjson struct<myid:string, mytype:string>)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION '/warehouse/tablespace/external/hive/haijintest.db/stock2';

myjson struct define it is having further list and map.

You have to place correct input file stock3.json

{"myjson":{"myid":"0001","mytype":"donut"}}

and create table with same command and check.

Let me know if still you are facing issue.


Regards,

Vinay K

2 REPLIES 2

Re: Querying JSON Data in Hive - returning null

Expert Contributor

Hi @Haijin Li

The input file you are using is incorrect stock2.json

{"myid":"0001","mytype":"donut"}

command is correct

CREATE EXTERNAL TABLE stock4_json (myjson struct<myid:string, mytype:string>)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' LOCATION '/warehouse/tablespace/external/hive/haijintest.db/stock2';

myjson struct define it is having further list and map.

You have to place correct input file stock3.json

{"myjson":{"myid":"0001","mytype":"donut"}}

and create table with same command and check.

Let me know if still you are facing issue.


Regards,

Vinay K

Re: Querying JSON Data in Hive - returning null

Contributor

@Vinay

Your are right. The issue is resolved.

Thank you!

Don't have an account?
Coming from Hortonworks? Activate your account here