Apache Hive

i
Apache Hive

About the Tutorial

Hive is a data warehouse infrastructure tool to process structured data in Hadoop.
It resides on top of Hadoop to summarize Big Data, and makes querying and
analyzing easy.

This is a brief tutorial that provides an introduction on how to use Apache Hive
HiveQL with Hadoop Distributed File System. This tutorial can be your first step
towards becoming a successful Hadoop Developer with Hive.

Audience
This tutorial is prepared for professionals aspiring to make a career in Big Data
Analytics using Hadoop Framework. ETL developers and professionals who are into
analytics in general may as well use this tutorial to good effect.

Prerequisites
Before proceeding with this tutorial, you need a basic knowledge of Core Java,
Database concepts of SQL, Hadoop File system, and any of Linux operating system
flavors.

Disclaimer & Copyright

 Copyright 2014 by Tutorials Point (I) Pvt. Ltd.

All the content and graphics published in this e-book are the property of Tutorials
Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy,
distribute or republish any contents or a part of contents of this e-book in any
manner without written consent of the publisher. We strive to update the contents
of our website and tutorials as timely and as precisely as possible, however, the
contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides
no guarantee regarding the accuracy, timeliness or completeness of our website
or its contents including this tutorial. If you discover any errors on our website or
in this tutorial, please notify us at [email protected].

i
Apache Hive

Table of Contents
About the Tutorial ····································································································································· i

Audience ··················································································································································· i

Prerequisites ············································································································································· i

Disclaimer & Copyright ······························································································································ i

Table of Contents ····································································································································· ii

1. INTRODUCTION ···················································································································· 1

Hadoop ···················································································································································· 1

What is Hive? ··········································································································································· 2

Features of Hive ······································································································································· 2

Architecture of Hive ································································································································· 2

Working of Hive ······································································································································· 4

2. HIVE INSTALLATION ·············································································································· 6

Step 1: Verifying JAVA Installation ··········································································································· 6

Step 2: Verifying Hadoop Installation ······································································································· 8

Step 3: Downloading Hive ······················································································································ 15

Step 4: Installing Hive ····························································································································· 15

Step 5: Configuring Hive ························································································································· 16

Step 6: Downloading and Installing Apache Derby ················································································· 17

Step 7: Configuring Metastore of Hive ···································································································· 19

Step 8: Verifying Hive Installation ·········································································································· 20

3. HIVE DATA TYPES················································································································ 22

Column Types ········································································································································· 22

Literals ··················································································································································· 24

Null Value ··············································································································································· 24

Complex Types ······································································································································· 24

ii
Apache Hive

4. CREATE DATABASE ············································································································· 25

Create Database Statement ··················································································································· 25

5. DROP DATABASE ················································································································ 28

Drop Database Statement ······················································································································ 28

6. CREATE TABLE ···················································································································· 30

Create Table Statement·························································································································· 30

Load Data Statement······························································································································ 33

7. ALTER TABLE······················································································································· 36

Alter Table Statement ···························································································································· 36

Rename To… Statement ························································································································· 36

Change Statement ·································································································································· 38

Add Columns Statement························································································································· 40

Replace Statement ································································································································· 41

8. DROP TABLE ······················································································································· 44

Drop Table Statement ···························································································································· 44

9. PARTITIONING ···················································································································· 47

Adding a Partition ·································································································································· 48

Renaming a Partition ······························································································································ 48

Dropping a Partition ······························································································································· 48

10. BUILT-IN OPERATORS ········································································································· 50

Relational Operators ······························································································································ 50

Arithmetic Operators ····························································································································· 52

Logical Operators ··································································································································· 53

Complex Operators ································································································································ 54

11. BUILT-IN FUNCTIONS ·········································································································· 55

iii
Apache Hive

Built-In Functions ··································································································································· 55

Aggregate Functions ······························································································································· 57

12. VIEWS AND INDEXES ·········································································································· 59

Creating a View ······································································································································ 59

Example ················································································································································· 59

Dropping a View ····································································································································· 60

Creating an Index ··································································································································· 60

Example ················································································································································· 61

Dropping an Index ·································································································································· 61

13. HIVEQL SELECT…WHERE ····································································································· 62

Syntax ···················································································································································· 62

Example ················································································································································· 62

14. HIVEQL SELECT…ORDER BY································································································· 66

Syntax ···················································································································································· 66

Example ················································································································································· 66

15. HIVEQL GROUP BY ·············································································································· 70

Syntax ···················································································································································· 70

Example ················································································································································· 70

16. HIVEQL JOINS ····················································································································· 74

Syntax ···················································································································································· 74

Example ················································································································································· 74

JOIN ······················································································································································· 75

LEFT OUTER JOIN ···································································································································· 76

RIGHT OUTER JOIN ································································································································· 76

FULL OUTER JOIN ··································································································································· 77

iv
1. INTRODUCTION Apache Hive

The term ‘Big Data’ is used for collections of large datasets that include huge volume, high
velocity, and a variety of data that is increasing day by day. Using traditional data
management systems, it is difficult to process Big Data. Therefore, the Apache Software
Foundation introduced a framework called Hadoop to solve Big Data management and
processing challenges.

Hadoop
Hadoop is an open-source framework to store and process Big Data in a distributed
environment. It contains two modules, one is MapReduce and another is Hadoop Distributed
File System (HDFS).

 MapReduce: It is a parallel programming model for processing large amounts of

structured, semi-structured, and unstructured data on large clusters of commodity
hardware.
1.
 HDFS: Hadoop Distributed File System is a part of Hadoop framework, used to store
and process the datasets. It provides a fault-tolerant file system to run on commodity
hardware.

The Hadoop ecosystem contains different sub-projects (tools) such as Sqoop, Pig, and Hive
that are used to help Hadoop modules.

 Sqoop: It is used to import and export data to and fro between HDFS and RDBMS.

 Pig: It is a procedural language platform used to develop a script for MapReduce

operations.

 Hive: It is a platform used to develop SQL type scripts to do MapReduce operations.

Note: There are various ways to execute MapReduce operations:

 The traditional approach using Java MapReduce program for structured, semi-
structured, and unstructured data.

 The scripting approach for MapReduce to process structured and semi structured data
using Pig.

 The Hive Query Language (HiveQL or HQL) for MapReduce to process structured data
using Hive.

What is Hive?
Hive is a data warehouse infrastructure tool to process structured data in Hadoop. It resides
on top of Hadoop to summarize Big Data, and makes querying and analyzing easy.

5
Apache Hive

Initially Hive was developed by Facebook, later the Apache Software Foundation took it up
and developed it further as an open source under the name Apache Hive. It is used by
different companies. For example, Amazon uses it in Amazon Elastic MapReduce.

Hive is not
 A relational database
 A design for OnLine Transaction Processing (OLTP)
 A language for real-time queries and row-level updates

Features of Hive
Here are the features of Hive:

 It stores schema in a database and processed data into HDFS.

 It is designed for OLAP.
 It provides SQL type language for querying called HiveQL or HQL.
 It is familiar, fast, scalable, and extensible.

Architecture of Hive
The following component diagram depicts the architecture of Hive:

This component diagram contains different units. The following table describes each unit:

Unit Name Operation

6
Apache Hive

User Interface Hive is a data warehouse infrastructure software that can create
interaction between user and HDFS. The user interfaces that Hive
supports are Hive Web UI, Hive command line, and Hive HD Insight
(In Windows server).

Meta Store Hive chooses respective database servers to store the schema or
Metadata of tables, databases, columns in a table, their data
types, and HDFS mapping.

HiveQL Process HiveQL is similar to SQL for querying on schema info on the
Engine Metastore. It is one of the replacements of traditional approach for
MapReduce program. Instead of writing MapReduce program in
Java, we can write a query for MapReduce job and process it.

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

HDFS or HBASE Hadoop distributed file system or HBASE are the data storage
techniques to store data into file system.

Working of Hive
The following diagram depicts the workflow between Hive and Hadoop.

7
Apache Hive

The following table defines how Hive interacts with Hadoop framework:

Step No. Operation

1 Execute Query

The Hive interface such as Command Line or Web UI sends query to Driver
(any database driver such as JDBC, ODBC, etc.) to execute.

2 Get Plan

The driver takes the help of query compiler that parses the query to check
the syntax and query plan or the requirement of query.

3 Get Metadata

The compiler sends metadata request to Metastore (any database).

4 Send Metadata

Metastore sends metadata as a response to the compiler.

5 Send Plan

8
Apache Hive

The compiler checks the requirement and resends the plan to the driver.
Up to here, the parsing and compiling of a query is complete.

6 Execute Plan

The driver sends the execute plan to the execution engine.

7 Execute Job

Internally, the process of execution job is a MapReduce job. The execution

engine sends the job to JobTracker, which is in Name node and it assigns
this job to TaskTracker, which is in Data node. Here, the query executes
MapReduce job.

7.1 Metadata Ops

Meanwhile in execution, the execution engine can execute metadata

operations with Metastore.

8 Fetch Result

The execution engine receives the results from Data nodes.

9 Send Results

The execution engine sends those resultant values to the driver.

10 Send Results

The driver sends the results to Hive Interfaces.

9
2. HIVE INSTALLATION Apache Hive

All Hadoop sub-projects such as Hive, Pig, and HBase support Linux operating system.
Therefore, you need to install any Linux flavored OS. The following simple steps are executed
for Hive installation:

Step 1: Verifying JAVA Installation

Java must be installed on your system before installing Hive. Let us verify java installation
using the following command:

$ java –version

If Java is already installed on your system, you get to see the following response:

java version "1.7.0_71"

Java(TM) SE Runtime Environment (build 1.7.0_71-b13)
Java HotSpot(TM) Client VM (build 25.0-b02, mixed mode)

If java is not installed in your system, then follow the steps given below for installing java.

Installing Java
Step I:
Download java (JDK <latest version> - X64.tar.gz) by visiting the following link
http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html.

Then jdk-7u71-linux-x64.tar.gz will be downloaded onto your system.

Step II:
Generally, you will find the downloaded java file in the Downloads folder. Verify it and extract
the jdk-7u71-linux-x64.gz file using the following commands.

$ cd Downloads/

$ ls

jdk-7u71-linux-x64.gz

10
Apache Hive

$ tar zxf jdk-7u71-linux-x64.gz

$ ls

jdk1.7.0_71 jdk-7u71-linux-x64.gz

Step III:
To make java available to all the users, you have to move it to the location “/usr/local/”. Open
root, and type the following commands.

$ su

password:

# mv jdk1.7.0_71 /usr/local/

# exit

Step IV:
For setting up PATH and JAVA_HOME variables, add the following commands to ~/.bashrc
file.

export JAVA_HOME=/usr/local/jdk1.7.0_71

export PATH=PATH:$JAVA_HOME/bin

Now apply all the changes into the current running system.

$ source ~/.bashrc

Step V:
Use the following commands to configure java alternatives:

# alternatives --install /usr/bin/java java usr/local/java/bin/java 2

# alternatives --install /usr/bin/javac javac usr/local/java/bin/javac 2

# alternatives --install /usr/bin/jar jar usr/local/java/bin/jar 2

# alternatives --set java usr/local/java/bin/java

11
Apache Hive

# alternatives --set javac usr/local/java/bin/javac

# alternatives --set jar usr/local/java/bin/jar

12
Apache Hive

End of ebook preview

If you liked what you saw…
Buy it from our store @ https://store.tutorialspoint.com

13