Hive ETL: Loading JSON, XML, Text Data Examples

Hive as an ETL and data warehousing tool on top of the Hadoop ecosystem provides functionalities like data modeling, data manipulation, data processing and data querying. Data extraction in Hive means the creation of tables in Hive and loading structured and semi-structured data as well as querying data based on the requirements.

For batch processing, we are going to write custom defined scripts using custom map and reduce scripts in a scripting language. It provides an SQL like environment and support for easy querying.

Working with Structured Data using Hive

Structured data means that the data is in the proper format of rows and columns. This is more like RDBMS data with proper rows and columns.

Here we are going to load structured data present in text files in Hive.

Step 1) In this step we are creating table “employees_guru” with column names such as Id, Name, Age, Address, Salary and Department of the employees with data types.

From the above screenshot, we can observe the following:

1. Creation of table “employees_guru”
2. Loading data from Employees.txt into table “employees_guru”

Step 2) In this step we are displaying the contents stored in this table by using the “Select” command. We can observe the table contents in the following screenshot.

Sample code snippet

Queries to be performed

1) Create table employees_guru(Id INT, Name STRING, Age INT, Address STRING, Salary FLOAT, Department STRING)
> Row format delimited
> Fields terminated by ',';
2) load data local inpath '/home/hduser/Employees.txt' into TABLE employees_guru;
3) select * from employees_guru;

Working with Semi-structured Data using Hive (XML, JSON)

Hive performs ETL functionalities in the Hadoop ecosystem by acting as an ETL tool. It can be difficult to perform MapReduce in some types of applications; Hive can reduce that complexity and provides the best solution to IT applications in terms of the data warehousing sector.

Semi-structured data such as XML and JSON can be processed with less complexity using Hive. First we will see how we can use Hive for XML.

XML to Hive Table

In this, we are going to load XML data into Hive tables, and we will fetch the values stored inside the XML tags.

Step 1) Creation of table “xmlsample_guru” with a str column of string data type.

From the above screenshot, we can observe the following:

1. Creation of table “xmlsample_guru”
2. Loading data from the test.xml into table “xmlsample_guru”

Step 2) Using the XPath method, xpath(), we will be able to fetch the data stored inside XML tags.

From the above screenshot, we can observe the following:

1. Using the xpath() method we are fetching the values stored under /emp/esal/ and /emp/ename/
2. Values present inside XML tags. In this step, we are displaying actual values stored under XML tags in table “xmlsample_guru”

Note that xpath() returns an array of strings; xpath_string(), xpath_int() and xpath_double() return a single typed value.

Step 3) In this step, we will fetch and display the raw XML of table “xmlsample_guru”.

From the above screenshot, we can observe the following:

• The actual XML data displaying with tags
• If we observe a single tag, it is with “emp” as the parent tag with “ename” and “esal” as child tags.

Code snippet:

Queries to be performed

1) create table xmlsample_guru(str string);                                                                                                   2) load data local inpath '/home/hduser/test.xml' overwrite  into table xmlsample_guru;
3) select xpath(str,'emp/ename/text()'), xpath(str,'emp/esal/text()') from xmlsample_guru;

JSON (JavaScript Object Notation)

Social media and website data is commonly stored in JSON format. Whenever we try to fetch data from online servers it will return JSON files. Using Hive as the data store we can load JSON data into Hive tables by creating schemas.

JSON to Hive Table

In this, we are going to load JSON data into Hive tables, and we will fetch the values stored in the JSON schema.

Step 1) In this step, we are going to create a JSON table named “json_guru”. Once created, we load and display the contents of the actual schema.

From the above screenshot, we can observe the following:

1. Creation of table “json_guru”
2. Loading data from test.json into table “json_guru”
3. Displaying the actual schema of the JSON file stored in the json_guru table

Step 2) Using the get_json_object() method we can fetch the data values stored in the JSON hierarchy.

From the above screenshot, we can observe the following:

1. Using get_json_object(str,’$.ecode’) it can fetch ecode values from table json_guru. Similarly, using get_json_object(str,’$.ename’) and get_json_object(str,’$.sal’) it will fetch the ename and sal values from table json_guru
2. Values stored inside of the JSON hierarchy in json_guru

Because get_json_object() parses the document once per call, json_tuple() is cheaper when several keys are needed, and a JSON SerDe maps the document onto typed columns at load time.

Code snippet

Queries to be performed

1) create table json_guru(str string);
2) load data inpath 'home/hduser/test.json' into table json_guru;
3) select * from json_guru;
4) select get_json_object(str,'$.ecode') as ecode, get_json_object(str,'$.ename') as ename ,get_json_object(str,'$.sal') as salary from json_guru;

Complex JSON to Hive Table

In this, we are going to load complex JSON data into Hive tables, and we will fetch the values stored in the JSON schema.

Step 1) Creating complexjson_guru with a single column field.

From the above screenshot, we can observe the following:

1. Creation of table complexjson_guru with a single column field as string data type
2. Loading data into complexjson_guru from the emp.json complex JSON file

Step 2) By using get_json_object we can retrieve the actual content that is stored inside the JSON file hierarchy.

From the following screenshot, we can see the output of the data stored in complexjson_guru.

Step 3) In this step, by using the “Select” command we are able to see the complex JSON data stored inside table “complexjson_guru”.

Sample code snippet

Queries to be performed

1) create table complexjson_guru(json string);
2) load data inpath 'home/hduser/emp.json' into table complexjson_guru;
3) select get_json_object(json,'$.ecode') as ecode ,get_json_object(json,'$.b') as code, get_json_object(json,'$.c') from complexjson_guru;
4) select * from complexjson_guru;

Hive in Real-time Projects – When and Where to Use

When and where to use Hive in the Hadoop ecosystem:

When

• When working with strong and powerful statistical functions on the Hadoop ecosystem
• When working with structured and semi-structured data processing
• As a data warehouse tool with Hadoop
• Real-time data ingestion with HBase, where Hive can be used

Where

• For ease of use as an ETL and data warehousing tool
• To provide an SQL type environment and to query like SQL using HiveQL
• To use and deploy custom specified map and reducer scripts for specific client requirements

FAQs