TECH SOLUTIONS

To load JSON Data in Hive , you would need to use JsonSerde .

Download JsonSerde source code from following link and build it using Maven. Advantage of using this JsonSerde is that you can also handle Hive Array and Maps which will not be handled if you use default JSonSerde.

https://github.com/foursquare/Hive-JSON-Serde-1

Once you have build it ,add this jar to Hive

hive>add jar /home/impadmin/hive/hive-0.8.1/jsonserde/Hive-JSON-Serde-1/target/json-serde-1.1-jar-with-dependencies.jar;

Now, for example you have following Json file on HDFS or your local system

$ cat file.json

 { "id": "1",  "name": "emp1",  "contact":"9999999999",  "dept":"IT", "sal":"10000" }

 { "id": "2",  "name": "emp2",  "contact":"9999999929",  "dept":"IT", "sal":"10000" }

 { "id": "3",  "name": "emp4",  "contact":"9999999989",  "dept":"IT", "sal":"10000" }

 { "id": "4",  "name": "emp4",  "contact":"9999999979",  "dept":"IT", "sal":"10000" }

Now create a Hive Table using JsonSerde as follows  :

hive> create table test_jsontable (id string, name string,contact string,dept string,sal string) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

OK

Time taken: 0.057 seconds

Now, load the JSON Data into the table as follows:

hive> load data local inpath '/patht/to/file.json' into table test_jsontable;                                                                

Copying data from file:'/patht/to/file.json'

Copying file: file:'/patht/to/file.json'

Loading data to table default.test_jsontable

OK

Time taken: 0.18 seconds

hive> select * from test_jsontable;                                                                                                                    

OK

id    name    contact    dept    sal

1    emp1    9999999999    IT    10000

2    emp2    9999999929    IT    10000

3    emp4    9999999989    IT    10000

4    emp4    9999999979    IT    10000

Time taken: 0.081 seconds

If the file resides on HDFS , you can load the data as follows:

hive> load data  inpath '/patht/to/file.json' into table test_jsontable;

Also, you can create external table as follows:

hive>create external table test_jsontable (id string, name string,contact string,dept String,sal string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' location '/path/to/dir/of/jsonfile';

Click here for Other topics of BigData Technologies