Hive - Reading JSON using Serde

Reading JSON in Hive is quite trickier but JSON serde has made our life easier.The JSON serde can be downloaded from any third party website.The JSON serde for JSON file is available in Hive 0.12 and later version.

We can download JSON serde from any of the sites like :-

https://code.google.com/archive/p/hive-json-serde/downloads

We have downloaded a sample JSON file  from https://support.oneskyapp.com/hc/en-us/articles/208047697-JSON-sample-files


Step 1.) Download the Serde from the above link and store it in any of the required folder .



Step 2.) Once the JSON serde has been downloaded , kindly add it to the Hive using below command.

ADD path/to/jar Jarfile.


Step 3.) Create the required table format using the below syntax .

create external table serde_fruit(fruit string,size string,color string) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.JsonSerde' location ‘/serde_ex’;



Step 4. ) validate the data by using the select query.

Hive - Complex Data Type (Array)

The complex data types contains :

Array

Map

Structs

Arrays :- The array contains elements of same data type and can be accessed using the index. For example :- if we have an array called animal , it can contain animal like [‘dog’,’cat’,’lion’].These animals can be accessed via index like animal[1] =cat

Let us understand this in four simple steps :-

Step 1.) We will create a sample file called ‘array_exm.txt’

We are having three fields namely id, name and goals .Goals is an array datatype.

Step 2.) we will create the table .

Create table player(id int,name varchar(20),goals array<int>) row format delimited fields terminated by ‘\t’ collection items terminated by ‘,’;

Step 3.) We will load the data into the table.

Load data local inpath ‘pathname’ into table player;

Step 4.) Select the data from the table .

We can also select the specified array position value data from the table .