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
Hive commands are similar to that of SQL which is a data warehousing tool similar to Hive.
It is an extensible framework which supports different file and data formats.
We can easily plug-in map reduce code in the language of our choice using user-defined functions.
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.
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 Components
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.
HIVE Architecture
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.
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.
HivePrimitive data types
Type
Size
Literal Syntax Example
TINYINT
1-byte signed integer, from -128 to 127
30
SMALLINT
2-byte signed integer, from -32,768 to 32,767
30
INT
4-byte signed integer
30
BIGINT
8-byte signed integer
30
FLOAT
4-byte single precision floating point number
8.16
DOUBLE
8-byte double precision floating point number
8.16
BOOLEAN
Boolean Ture or False
TRUE
TIMESTAMP
Only available starting with Hive 0.8.0
2016-02-02 11:41:56′
DATE
DATE values describe a particular year/month/day, in the form YYYY-MM-DD
‘2013-01-01’
STRING
Sequence of characters with either single quotes (‘) or double quotes (“)
‘Learn HIVE’
VARCHAR
Varchar types are created with a length specifier (between 1 and 65355)
‘Learn HIVE’
CHAR
Char types are similar to Varchar but they are fixed-length
‘Learn HIVE’
BINARY
Array of Bytes
11101
Hive Collection Data Types
Type
Expression
Literal Syntax Example
ARRAY
It is the collection of similar type of elements that are indexed. It is similar to arrays in Java.
array(‘mak’,’mani’);The second element is accessed with array[1]
MAP
Collection of key, value pair where fields are accessed by array notation of keys.
MAP(‘first’,’Kumar’,’last’,’Mak’).’first’ and ‘last’ is the column name in the table and ‘Kumar’ and ‘Mak’ are the values. The last name can be referenced using name[‘last’]
STRUCT
It is similar to STRUCT in C language where fields can be accessed using “dot” notation.
For a column c of type STRUCT {a INT; b INT} the a field is accessed by the expression c.a
Below table structure demonstrates the Hive data types and their uses.
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:
A file format is a way in which information is stored in a computer file. Below is the default delimiters table for the fields:
Default Delimiter Table
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 below post we are going to understand about HIVE Partition and Concepts of HIVE partitioning in detail.
Partitions are essentially horizontal slices of data that divides the larger set of data into small manageable chunks.
In HIVE, partitioning is supported for both managed and external table. Partitioning can be done based on one or more than one columns to impose multi-dimensional structure on directory storage.
Partition column is a virtual column that does not exist on the file as a column. HIVE queries can take advantage of the partitioned data for better performance.
HIVE Partition – Managed Table Partitioning
Let us discuss the HIVE partition concept for the managed table first.
Our human resource (HR) team might be interested in looking the data country-wise and then state-wise. Therefore, let us first partition the data first county-wise and then state-wise.
CREATE TABLE IF NOT EXISTS financial.EMP
(
Ename STRING,
EmpID INT,
Salary FLOAT,
Tax MAP<STRING, FLOAT>,
Subordinates ARRAY<STRING>,
Address STRUCT<city: STRING, state: STRING, zip: INT>
)
PARTITIONED BY (country STRING, state STRING);
Partitioning tables changes how HIVE structures the data storage. HIVE will create sub-directories to reflect the partition structure. For Example:
If the table data and the number of partitions are large then a HIVE query across all partitions it can trigger enormous MapReduce jobs.
In such case, we put the HIVE into “strict” mode by setting up the below parameter.
hive> set hive.mapred.mode=strict;
The above parameter prohibits the HIVE queries on partitioned tables to run without a WHERE clause.
SHOW PARTITION Syntax
hive> SHOW PARTITIONS EMP;
HIVE Partition – External Table Partitioning
Partitioning external table has the added advantage of sharing the data with other tools, while still optimizing the query performance.
CREATE EXTERNAL TABLE IF NOT EXISTS log_tracking
(
ID STRING,
SERVER STRING,
MESSAGE STRING,
)
PARTITIONED BY (YEAR INT,MONTH INT,DAY INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n';
When we created a non-partitioned external table, we need the “LOCATION” as the mandatory clause.
But for the partitioned external table, the clause is not mandatory. We use ALTER table statement to add each partition separately.
ALTER TABLE log_tracking ADD PARTITION(YEAR=2016, MONTH=12, DAY=3)
LOCATION ‘hdfs://server_name/data/log_tracking/2016/12/03’;
SHOW PARTITIONS log_tracking;
…
Year=2016/month=12/day=3
…
…
Similarly, DESCRIBE EXTENDED log_tracking will show the detailed information of the partition keys.
