Querying JSON Data in Hive - returning null

[#1]

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?

[#2 · Accepted solution]

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

[#3]

@Vinay

Your are right. The issue is resolved.

Thank you!