simple JSON file analysing in Hive-0.14 using serde

Question

I am trying to execute hive commands on json file using jsonserde's,but I am always getting null values ,but not actual data. I have used serde's provided in "code.google.com/p/hive-json-serde/downloads/list" link. I have tried multiple ways but all of the attempts were not successful. Please can some one help me with the exact steps to be followed and serde's to be used in order to work with json files in apache hive latest version (0.14)

BR, San

Answer 1

Here are the simple steps to play around with JSON in Hive

1. Create a hive table

   CREATE EXTERNAL TABLE IF NOT EXISTS json_table (
       field1 string COMMENT 'This is a field1',
       field2 int COMMENT 'This is a field2',
       field3 string COMMENT 'This is a field3',
       field4 double COMMENT 'This is a field4'
   )
   ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
   Location '/path/to/json_table';
   

2. Sample data for your table. Copy the below content into a json file and store into a file location pointed by json_table.

   {"field1":"data1","field2":100,"field3":"more data1","field4":123.001}
   {"field1":"data2","field2":200,"field3":"more data2","field4":123.002}
   {"field1":"data3","field2":300,"field3":"more data3","field4":123.003}
   {"field1":"data4","field2":400,"field3":"more data4","field4":123.004}
   

3. Make sure JSON Serde Jar file is added in the HIVE class path. For this example we have used openx json serde. It can be downloaded from here

   Command to add the jar

    ADD JAR /path-to/json-serde-1.3.6-jar-with-dependencies.jar;
   

4. Now we can query the entries from json_table

   select * from json_table;