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 consists of Json Array

$ cat file1.json

 { "id": "1",  "name": "emp1",  "contact":"9999999999",  "depts":["IT","Electronics"], "sal":"10000" }

 { "id": "2",  "name": "emp2",  "contact":"9999999929",  "depts":["IT","Mechanical"], "sal":"10000" }

 { "id": "3",  "name": "emp4",  "contact":"9999999989",  "depts":["IT","Electronics"], "sal":"10000" }

 { "id": "4",  "name": "emp4",  "contact":"9999999979",  "depts":["IT","Electronics"], "sal":"10000" }

 { "id": "5",  "name": "emp5",  "contact":"9999999969",  "depts":["IT","Mechanical"], "sal":"10000" }

Now create a Hive Table using JsonSerde as follows  :

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

OK

Time taken: 0.417 seconds

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

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

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

Copying file: file:'/path/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    depts    sal

1    emp1    9999999999    ["IT","Electronics"]    10000

2    emp2    9999999929    ["IT","Mechanical"]    10000

3    emp4    9999999989    ["IT","Electronics"]    10000

4    emp4    9999999979    ["IT","Electronics"]    10000

5    emp5    9999999969    ["IT","Mechanical"]    10000

Time taken: 0.089 seconds

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

hive> load data  inpath '/path/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,depts array<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