In this post, we will discuss Hive data types and file formats.

Hive Data Types

Hive supports most of the primitive data types that we find in relational databases. It also supports three collection data types that are rarely supported by relational databases.

Hive Primitive data types

Hive Collection Data Types

Below table structure demonstrates the Hive data types and their uses.

CREATE TABLE EMP

(
Ename STRING,

EmpID INT,

Salary FLOAT,

Tax MAP<STRING, FLOAT>,

Subordinates ARRAY<STRING>,

Address STRUCT<city: STRING, state: STRING, zip: INT>

)

The ename column is a simple string which is the name of each employee, EmpID is the number assigned to each employee to uniquely identify the employee working for an organization and float is for employee’s salary.

Tax column is a map that holds the key-value pair for every deduction that will be taken out from employee’s salary.

For example, Tax can be (“professional tax”) where value can be a percentage value or an absolute number. In traditional databases, there might be a separate table which will be holding the Tax type and the corresponding values.

Data examples of the above create table statement can be a JSON (JavaScript Object Notation) file with below data:

{

“Ename”: “Kumar Mak”,

“EmpID”: 1234,

“Salary”: 5000.0,

“Tax”: {

“Professional Tax”: .3,

“State Tax”:05

“Fedral Tax”:.1

},

“Subordinates”: [“Smith”, ”John”, ”Carley”],

“Address”: {

“city”: “London”,

“State”: “UK”

“Zip”:560076

}
}

A file format is a way in which information is stored in a computer file. Below is the default delimiters table for the fields:

Below is the same “EMP” table with the format specified:

CREATE TABLE EMP

(

Ename STRING,

EmpID INT,

Salary FLOAT,

Tax MAP<STRING, FLOAT>,

Subordinates ARRAY<STRING>,

Address STRUCT<city: STRING, state: STRING, zip: INT>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE;

The “ROW FORMAT DELIMITED” keyword must be specified before any other clause, with an exception of the “STORED AS” clause.

It specifies the format of data rows. ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\001’ meaning that HIVE will use “^A” character to separate fields. ‘\001’ is the octal code for “^A”. Similarly, ‘\002’ is the octal code for “^B” and so on.

“STORED AS” specifies the type of file in which data is to be stored. The file can be a TEXTFILE, SEQUENCEFILE, RCFILE, or BINARY SEQUENCEFILE.

Schema on Read

When we write data into traditional databases either by INSERT, UPDATE operation or through loading external data etc. The database has control on its storage. It can enforce schema as the data is written. This is called schema on write.

Hive has no control over the underlying storage. There are many ways to create, update or delete the data from the HDFS storage through the Hive Query. Therefore,

Therefore, Hive can only enforce queries on read. Hence, this is called as schema on read.

In the following post, we will cover Hive Introduction and key principles of Hive.

Hive Introduction – Benefits and Limitations

Hive is a data warehouse tool developed on top of Hadoop to process structured data. This is basically a wrapper written on top of map reduce programming layer that makes querying and analyzing easy.

It facilitates analysis of large data sets, ad-hoc queries, and easy data summarization through a query processing language named HQL (Hive Query Language) for the data residing on HDFS.

Due to SQL-like language, Hive is a popular choice for Hadoop Analytics. Hive’s SQL gives users multiple places to integrate their own functionality to do custom analysis, such as User Defined Functions (UDFs).

It provides massive scale-out and fault tolerance capabilities for data storage and processing of commodity hardware.

Hive was originally developed by Facebook in 2007 to handle massive volumes of data, and later the Apache Software Foundation took it up and developed it further as an open source under the name Apache Hive.

It is nowadays used by many companies. For Example, Amazon uses it for Elastic MapReduce.

It is important to note that Hive is not a relational database which does not support low-level insert, update or delete operations.

It is not used for real-time data processing. Hive is not designed for online transaction processing. However, it is best suited for traditional data warehousing.

Relying on MapReduce for execution, Hive is batch-oriented and has high latency for query execution. Therefore, it uses the concept of MapReduce for execution and HDFS for storage and retrieval of data.

Principles of Hive

1. Hive commands are similar to that of SQL which is a data warehousing tool similar to Hive.
2. It is an extensible framework which supports different file and data formats.
3. We can easily plug-in map reduce code in the language of our choice using user-defined functions.
4. Performance is better in Hive since Hive engine uses the best built-in script to reduce the execution time while enabling high output.

Some of the key Hive components that we are going to learn in this post are UI, Driver, Compiler, Metastore, and Execution engine. Let us understand these Hive components one by one in detail below.

Apache Hive components

Hive User Interfaces (UI)

The user interface is for users to submit queries and other operations to the system. Hive includes mainly three ways to communicate to the Hive drivers.

• CLI (Command Line Interface)

This is the most common way of interacting with Hive where we use Linux terminal to issue queries directly to Hive drivers.

• HWI (Hive Web Interface)

It is an alternative to the CLI where we use the web browser to interact with Hive.

• JDBC/ODBC/Thrift Server

This allows the remote client to submit the request to HIVE and retrieve the result. HIVE_PORT environment variable needs to be specified with the available port number to let the server listen on.

It is important to note that CLI is a fat client which requires a local copy of all the HIVE components as well as the Hadoop client and configurations.

Hive Driver

This component receives the queries from user interfaces (UI) and provides execute and fetch API’s modeled on JDBC/ODBC drivers.

Hive Compiler

This very component parses the query, does semantic analysis on different query blocks and finally generates the execution plan.

This is done with the help of tables and partitioned metadata that needed to be looked up into Metastore.

Hive Metastore

A Metastore is a component that stores the system catalog and metadata about tables, columns, partitions and so on.

For example – A create table definition statement is stored here. Metastore uses a relational database to store its metadata.

Apache Hive uses Derby database by default. However, this database has limitation such as multi-user access.

Any JDBC compliant database such as MySQL, Oracle can be used for Metastore. The key attributes that should be configured for Hive Metastore are given below:

Hive Execution Engine

This component is responsible for executing the execution plan created by the compiler.

The conjunction part of HiveQL process Engine and MapReduce is Hive Execution Engine. It processes the query and generates results same as MapReduce results. It basically uses the flavor of MapReduce.

The below diagram represents Hadoop Hive Architecture and typical query that flows through the HIVE system.

The UI calls the execute query interface to the driver. The driver creates a session handle for the query and sends the query to the compiler to generate an execution plan.

The compiler needs the metadata to send a request for “getMetaData” and receives the “sendMetaData” request from metastore.

This metadata does the typecheck of the query expression and prunes the partitions based on query predicates.

The plan generated by the compiler is a sequence of steps where each step is either a MapReduce job, a metadata operation or an operation on HDFS.

The execution engine submits these stages to appropriate components (steps 6, 6.1, 6.2 and 6.3). Once the output is generated it is written to a temporary HDFS file through serializer.

The content of the file is read by execution engine directly from HDFS and displayed to UI clients.