APACHE HIVE

CIS 612
SUNNIE CHUNG
APACHE HIVE IS

Data warehouse infrastructure built on top of
Hadoop enabling data summarization and ad-hoc

Sunnie Chung CIS 612 Lecture Notes

queries.

Initially developed by Facebook.

Hive stores data in Hadoop Distributed File

System

Supports SQL like Query Language : HiveQL

Hive complied Hive Query Language statements

are broken down by the Hive service into
MapReduce jobs and executed across a Hadoop
cluster.
2
HOW HIVE WORKS ?

Hive structures data into well-understood
database concepts such as tables, rows, columns,

Sunnie Chung CIS 612 Lecture Notes

and partitions.

It supports primitive types, as well as Associative
Arrays, Lists, Struct.

HQL supports DDL and DML.

HQL has limited equality and join predicates,

and has no inserts on existing tables. (It can
override tables)

Users can embed Custom Map-Reduce scripts.

3
HIVE

Data in Hive is organized into Tables

Provides structure for unstructured Big Data

Sunnie Chung CIS 612 Lecture Notes

Work with data inside HDFS

Tables

Data : File or Group of Files in HDFS

Schema : In the form of metadata stored in
Relational Database

Have a corresponding HDFS directory

Data in a table is Serialized

Supports Primitive Column Types and Nestable

Collection Types: Array and Map(Key Value pair) 4
HIVE DATABASE

Data Model

Tables

Analogous to tables in relational database

Each table has a corresponding HDFS directory

Hive provides built-in serialization formats which exploit compression
and lazy-serialization

Partitions

Each table can have one or more partitions (Horizontal Partitions)

Example:
Table T in the directory : /wh/T.
If Tis partitioned on columns ds = ‘20090101’, and ctry = ‘US’, will be
stored /wh/T/ds=20090101/ctry=US.

Buckets

Data in each partition may in turn be divided into buckets based on the
hash of a column in the table

Each bucket is stored as a file in the partition directory
 TABLE SCHEMA EXAMPLE
CREATE TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,

Sunnie Chung CIS 612 Lecture Notes

friends ARRAY<BIGINT>, properties MAP<STRING, STRING>
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '1'
COLLECTION ITEMS TERMINATED BY '2'
MAP KEYS TERMINATED BY '3'
STORED AS SEQUENCEFILE;

6
HIVE QUERY LANGUAGE

SQL like language: HiveQL

DDL : to create tables with specific serialization

Sunnie Chung CIS 612 Lecture Notes

formats

DML : load and insert to load data from external
sources and insert query results into Hive tables

Do not support updating and deleting rows in
existing tables

Supports Multi-Table insert

Supports Select, Project, Join, Aggregate,

Supports Union all and Sub-queries in the From

clause 7
 HIVEQL: UDTF, UDAF

Can be extended with custom functions (UDFs)

User Defined Transformation Function(UDTF)
https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide+UDTF

User Defined Aggregation Function (UDAF)

Users can embed custom map-reduce scripts written in

any language using a simple row-based streaming
interface
WHAT HIVE DOES ?

Hive allows SQL developers to write Hive Query
Language (HQL) statements that are similar to

Sunnie Chung CIS 612 Lecture Notes

SQL statements, but with limited in the
commands.

It therefore allows developers to explore and
structure massive amounts of data, analyze it
then turn into business insight.

Hive queries have very high latency because it is
based on Hadoop.

Hive is read-based and not appropriate for write
operation.
9
HIVEQL When Facebook
users update their
status, the updates
are logged into flat
files in an NFS

Running time example: Status Meme directory
/logs/status_updates

Compute daily
statistics on the
frequency of status
updates based on
gender and school
ADVANTAGES OF HIVE

Familiar: hundreds of unique users can
simultaneously query the data using a language
familiar to SQL users.

Sunnie Chung CIS 612 Lecture Notes

Fast Response: times are typically much faster than
other types of queries on the same type of huge
datasets.

Scalable and extensible: as data variety and volume
grows, more commodity machines can be added to the
cluster, without a corresponding reduction in
performance.

Informative Familiar JDBC and ODBC drivers: allow
many applications to pull Hive data for seamless
reporting. Hive allows users to read data in arbitrary
formats, using SerDes and Input/Output formats.
(SerDes: serialized and deserialized API is used to
move data in and out of tables) 11
HIVE ARCHITECTURE

External Interfaces:

Web UI : Management

Hive CLI : Run Queries, Browse Tables, etc

API : JDBC, ODBC

Sunnie Chung CIS 612 Lecture Notes

Metastore :

System catalog which contains metadata about Hive tables

Driver :

manages the life cycle of a Hive-QL statement during compilation,
optimization and execution

Compiler :

translates Hive-QL statement into a plan which consists of a DAG of
map-reduce jobs

Database: is a namespace for tables

Table: metadata for table contains list of columns and their
types, owner, storage and SerDe information. Also contains
any user supplied key and value data.

Partition: each partition can have it own columns and SerDe
and storage information. 12
 Sunnie Chung CIS 612 Lecture Notes
13
HIVE ARCHITECTURE
 Sunnie Chung CIS 612 Lecture Notes
14
HIVE ARCHITECTURE
HIVE ARCHITECTURE
External interface:
Both user interface
like command line
(cli)
and web UI
Thrift is a framework
for cross-language
services, where a
server written in one
language (like Java)
can also support
clients in other
languages.
Metastore is the system
catalog. All other
components of Hive
interact with metastore

The Driver manages the

life cycle (statistics) of a
HiveQL statement
during compilation,
optimization and
execution

Figure 1: Hive Architecture

COMMAND LINE INTERFACE

There are several ways to interact with Hive, including some
popular graphical user interface but CLI is sometimes
preferable. CLI allows creating, inspecting schema and query
tables, etc.

Sunnie Chung CIS 612 Lecture Notes

All commands and queries go to the Driver, which complies,
optimizes and executes queries usually with MapReduce jobs.

Hive doesn’t generate MapReduce programs, it uses generic

Mapper and Reducer modules. Hive communicates with Job
Tracker to initiate the MapReduce job.

Data files to be processed are usually in HDFS, managed by

NameNode.

Hive uses Hive Query Language HQL, which is similar to

SQL.
16
HIVE ARCHITECTURE

MetaStore

The system catalog which contains metadata about the
tables stored in Hive

This data is specified during table creation and reused very
time the table is referenced in HiveQL

Contains the following objects:

database : the namespace for tables

table : metadata for table contains list of columns

and their types, owners, storage and SerDe

information

Partition: each partition can have its own columns

and SerDe and storage information

HIVE ARCHITECTURE

Bottom

Top
Figure 2: Query plan with 3 map-reduce
jobs for multi-table insert query
HIVE ARCHITECTURE

Compile

The compiler converts the string(DDL/DML/query statement)
to a plan.

The parser transforms a query string to a parse tree
representation

The semantic analyzer transforms the parse tree to a block-based

internal query representation

The logical plan generator converts the internal query
representation to a logical plan

The optimizer performs multiple passes over the logical plan and

rewrites it in several ways

Combined multiple joins which share the join key into a single multi-
way join, and hence a single map-reduce job

adds repartition operators

Prunes columns early and pushes predicates closer to the table scan
operators
…
HIVE ARCHITECTURE

Compile (continue..)

The optimizer performs multiple passes over the logical plan and
rewrites it in several ways

Combined multiple joins which share the join key into a single multi-
way join, and hence a single map-reduce job

adds repartition operators

Prunes columns early and pushes predicates closer to the table scan
operators

In case of partitioned tables, prunes partitions that are not needed by
the query

In case of sampling queries, prunes buckets that are not needed
Users can also provide hints to the optimizer to

Add partial aggregation operators to handle large cardinality grouped
aggregation

Add repartition operators to handle skew in grouped aggregations

Perform joins in the map phrase instead of the reduce phase

The Physical Plan generator converts the logical plan into physical
plan, consisting a directed-acyclic graph(DAG)of map-reproduce
jobs
INPUT DATA

Hive has no row-level insert, update or delete operations. The

only way to put data into a table is to use one of load
operations.

Sunnie Chung CIS 612 Lecture Notes

There are four file formats supported in Hive, which are
TEXTFILE, SEQUENCEFILE, ORC and RCFILE.

Example: ’NASDAQ_daily_prices_B.csv’ a log file of stocks

record of NASDAQ.

exchange,stock_symbol,date,stock_price_open,stock_price_hig
h,stock_price_low,stock_price_close,stock_volume,stock_price_
adj_close

NASDAQ,BBND,2010-02-08,2.92,2.98,2.86,2.96,483800,2.96

NASDAQ,BBND,2010-02-05,2.85,2.94,2.79,2.93,884000,2.93

NASDAQ,BBND,2010-02-04,2.83,2.88,2.78,2.83,1333300,2.83 21
….
CREATE TABLE TO HOLD THE DATA:

hive> CREATE TABLE IF NOT EXISTS stocks (

exchange STRING,

Sunnie Chung CIS 612 Lecture Notes

symbol STRING,
ymd STRING,
price_open FLOAT,
price_high FLOAT,
price_low FLOAT,
price_close FLOAT,
volume INT,
price_adj_close FLOAT)
ROW FORMAT DELIMITED FIELDS
TERMINATED BY ','; 22
HIVE QUERY LANGUAGE: HIVEQL

Create a database:
hive> CREATE DATABASE financials;
or

Sunnie Chung CIS 612 Lecture Notes

hive> CREATE DATABASE IF NOT EXISTS financials;

Describe table:
hive> DESCRIBE DATABASE financials;
OK
Financials
hdfs://localhost:54310/user/hive/warehouse/financials.db

Use database:
hive> USE financials;

Drop database: 23
hive> DROP DATABASE IF EXISTS financials;
HOW TO LOAD DATA INTO HIVE TABLE

Use LOAD DATA to import data into a Hive
table

Sunnie Chung CIS 612 Lecture Notes

Hive>Load Data LOCAL INPATH
'/home/sunny/EmployeeDetails.txt ' INTO
TABLE Employee

Use the word OVERWRITE to write over a file of
the same name

We can Load data from Local file system by using
LOCAL keyword as above Example

Inserting Data into new table by using SELECT
statement

For Example, INSERT OVERWRITE
24
<table_name> SELECT * FROM Employee
MANAGING TABLES

Operation Command Syntax

See current tables Hive>Show TABLES

Sunnie Chung CIS 612 Lecture Notes

Check the table name Hive>Describe <Table_Name>

Change the table name Hive>Alter Table <table_Name>

Rename to mytab

Add a column Hive> Alter Table <table_Name> ADD

COLUMNS (MyID String)

Drop a partition Hive>Alter Table <table_Name>

DROP PARTITION (Age>70)

25
HIVE SUPPORTS THE FOLLOWINGS:

WHERE Clause

UNION All and DISTINCT

Sunnie Chung CIS 612 Lecture Notes

GROUP BY and HAVING

LIMIT Clause

Hive Supports Sub-Queries but only in FROM

Clause

JOINS , ORDER BY, SORT BY

26
OUTPUT DATA

Output data produced by Hive is structured,

typically stored in a relational database.

Sunnie Chung CIS 612 Lecture Notes

For cluster, MySQL or similar relational
database is required.

The result tables then can be manipulated using

HiveQL in the similar way of SQL to relational
database.

27
LOAD FILE INTO TABLE:

hive> LOAD DATA LOCAL INPATH

'/Users/nqt289/Desktop/NASDAQ_daily_prices_B.csv'
> OVERWRITE INTO TABLE stocks;

Sunnie Chung CIS 612 Lecture Notes

Copying data from
file:/Users/nqt289/Desktop/NASDAQ_daily_prices_B.csv
Copying file:
file:/Users/nqt289/Desktop/NASDAQ_daily_prices_B.csv
Loading data to table mydb.stocks
Deleted
hdfs://localhost:54310/Users/nqt289/Desktop/NASDAQ_
daily_prices_B.csv
OK
Time taken: 0.231 seconds
28
EXAMPLE OF OUTPUT OF HIVE

hive> SELECT * FROM STOCKS WHERE price_open='2.92';

Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201403311509_0003, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201403311509_0003

Sunnie Chung CIS 612 Lecture Notes

Kill Command = /Users/nqt289/hadoop-0.20.2/bin/../bin/hadoop job -Dmapred.job.tracker=localhost:54311 -kill job_201403311509_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2014-03-31 15:39:20,577 Stage-1 map = 0%, reduce = 0%
2014-03-31 15:39:23,597 Stage-1 map = 100%, reduce = 0%
2014-03-31 15:39:26,625 Stage-1 map = 100%, reduce = 100%
Ended Job = job_201403311509_0003
MapReduce Jobs Launched:
Job 0: Map: 1 HDFS Read: 21998523 HDFS Write: 5166 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
NASDAQ BBND 2010-02-08 2.92 2.98 2.86 2.96 483800
2.96
NASDAQ BTFG 2009-12-21 2.92 2.92 2.75 2.79 15100
2.79
NASDAQ BJCT 2004-04-21 2.92 2.98 2.9 2.98 3200
2.98
NASDAQ BJCT 2004-04-20 2.92 3.0 2.92 2.95 27900
2.95
…

Time taken: 12.785 seconds

29
DEFINITION: ACID

Atomicity

Atomicity requires that each transaction be "all or nothing": if one part of the transaction
fails, the entire transaction fails, and the database state is left unchanged. An atomic
system must guarantee atomicity in each and every situation, including power failures,
errors, and crashes. To the outside world, a committed transaction appears (by its effects on

Sunnie Chung CIS 612 Lecture Notes

the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

Consistency

The consistency property ensures that any transaction will bring the database from one
valid state to another. Any data written to the database must be valid according to all
defined rules, including constraints, cascades, triggers, and any combination thereof. This
does not guarantee correctness of the transaction in all ways the application programmer
might have wanted (that is the responsibility of application-level code) but merely that any
programming errors cannot result in the violation of any defined rules.

Isolation

The isolation property ensures that the concurrent execution of transactions result in a
system state that would be obtained if transactions were executed serially, i.e. one after the
other. Providing isolation is the main goal of concurrency control. Depending on
concurrency control method, the effects of an incomplete transaction might not even be
visible to another transaction.[citation needed]

Durability

Durability means that once a transaction has been committed, it will remain so, even in the
event of power loss, crashes, or errors. In a relational database, for instance, once a group
of SQL statements execute, the results need to be stored permanently (even if the database
crashes immediately thereafter). To defend against power loss, transactions (or their
effects) must be recorded in a non-volatile memory. 30
ACID IN HIVE

ACID for Hive is added manually with the use
cases:

Sunnie Chung CIS 612 Lecture Notes

A set of Inserts and Updates is processed once an
hour.

A set of Deletes is processed once a day.

A log of transactions is exported from a RDBMS

to reflect new data once an hour.

The delay is not an important issue here due to
the purpose of Hive, also the number of
transactions committed each time is huge (100 to
500 thousands rows.)
31
HIVE ACHIEVEMENTS & FUTURE PLANS

First step to provide warehousing layer for
Hadoop(Web-based Map-Reduce data processing

Sunnie Chung CIS 612 Lecture Notes

system)

Accepts only sub-set of SQL: Working to subsume
SQL syntax

Working on Rule-based optimizer : Plans to build
Cost-based optimizer

Enhancing JDBC and ODBC drivers for making
the interactions with commercial BI tools.

Working on making it perform better

32
PROJECTS & TOOLS ON HADOOP

HBase

Hive

Sunnie Chung CIS 612 Lecture Notes

Pig

Jaql

ZooKeeper

AVRO

UIMA

Sqoop

33
HIVE TUTORIAL
https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-HiveTutorial

Sunnie Chung CIS 612 Lecture Notes

34
REFERENCES
[1] "Apache Hadoop", http://hadoop.apache.org/Hadoop/

[2] “Apache Hive”, http://hive.apache.org/hive

Sunnie Chung CIS 612 Lecture Notes

[3] “Apache HBase”, https://hbase.apache.org/hbase

[4] “Apache ZooKeeper”, http://zookeeper.apache.org/zookeeper

[5] Jason Venner, "Pro Hadoop", Apress Books, 2009

[6] "Hadoop Wiki", http://wiki.apache.org/hadoop/

[7] Jiong Xie, Shu Yin, Xiaojun Ruan, Zhiyang Ding, Yun Tian, James Majors,
Adam Manzanares, Xiao Qin, " Improving MapReduce Performance
through Data Placement in Heterogeneous Hadoop Clusters", 19th
International Heterogeneity in Computing Workshop, Atlanta, Georgia,
April 2010
35
REFERENCES
[8]Dhruba Borthakur, The Hadoop Distributed File System:
Architecture and Design, The Apache Software Foundation 2007.
[9] "Apache Hadoop",
http://en.wikipedia.org/wiki/Apache_Hadoop

Sunnie Chung CIS 612 Lecture Notes

[10] "Hadoop Overview",
http://www.revelytix.com/?q=content/hadoop-overview
[11] Konstantin Shvachko, Hairong Kuang, Sanjay Radia,
Robert Chansler, The Hadoop Distributed File System,
Yahoo!, Sunnyvale, California USA, Published in: Mass
Storage Systems and Technologies (MSST), 2010 IEEE
26th Symposium.

36