Transcript Hive on steroid Project stinger © Hortonworks Inc. 2013 Who Am I? • Olivier Renault • Hortonworks Solution engineer for EMEA – Join Hortonworks EMEA.

Hive on steroid
Project stinger
© Hortonworks Inc. 2013
2011
Who Am I?
• Olivier Renault
• Hortonworks Solution engineer for EMEA
– Join Hortonworks EMEA in Jan 2013
• Eucalyptus – Open source Cloud solution
• Red Hat – Solution engineer
© Hortonworks Inc. 2013
What’s Hive ?
• Use HiveQL
• Hive translate SQL query into
MapReduce job using
• De facto SQL interface in Hadoop
• Entry point for most BI tools
– ODBC
• HCatalog merge with Hive
– Metadata server
• Hive is able to query Pb of data
© Hortonworks Inc. 2013
Hive: Strength Through Community
Dozens of Vendors
integrate with Hive
Loyal Open Source Community and
Real Corporate Interest/Contributions
Teradata
Microsoft
Microstrategy
Tableau
Karmasphere
Datameer
Information Builders
SAP
Oracle
Actuate
QlikView
SAS
arcplan
Pentaho
Jaspersoft
Tibco
Talend
Informatica
…
© Hortonworks Inc. 2013
Vendors
Open
Source
Facebook
Teradata
SAP
Intel
Microsoft
Huawei
Yahoo
…
End Users
Countless Enterprises Use Hive
as the defacto SQL interface
to Hadoop data
Page 4
Problem : Hive was slow …
• Hive is able to interact with visualization tools but you needed to be
patient …
• February 2013, Hortonworks launch Stinger initiative. The aim is to
improve Hive performance by 100x
• Bringing Hive in the interactive query world
© Hortonworks Inc. 2013
Stinger Initiative
• Community initiative around Hive
• Enables Hive to support interactive workloads
• Enhances Hive’s standard SQL interface for Hadoop
• Improves existing tools & preserves investments
Execution
Engine
Query
Planner
+
+
Tez
© Hortonworks Inc. 2013
File
Format
Hive
= 100X
ORC file
Batch AND Interactive SQL-IN-Hadoop
Stinger Initiative
A broad, community-based effort to
drive the next generation of HIVE
Stinger Project
(announced February 2013)
Phase One
• Base Optimizations
• SQL Analytic Functions
• ORCFile, Modern File Format
Goals:
Speed
Improve Hive query performance by 100X to
allow for interactive query times (seconds)
Scale
The only SQL interface to Hadoop designed
for queries that scale from TB to PB
Phase Two
•
•
•
•
VARCHAR, DATE Types
ORCFile predicate pushdown
Advanced Optimizations
Performance Boosts via YARN
Phase Three
SQL
Support broadest range of SQL semantics for
analytic applications running against Hadoop
…all IN Hadoop
© Hortonworks Inc. 2013
•
•
•
•
•
Hive on Apache Tez
Query Service
Buffer Cache
Cost Based Optimizer (Optiq)
Vectorized Processing
Hive : Base optimization
New dags, analytics tools, ..
© Hortonworks Inc. 2013
Hive Advanced Analytics
• Add OVER clause to support windowing queries
– With standard arguments
– Ranking functions
– rank, ntile, row_number, dense_rank
– With analytics functions:
– cume_dist, first_value, lag, last_value, lead, percentile_cont, percentile_disc,
percent_rank
• Add CUBE and ROLLUP
– Easily create summaries of your data
• Extend aggregation functions
– STDDEV, VAR
© Hortonworks Inc. 2013
Page 9
Hive Data Type Conformance
• Extend Hive to support additional types from SQL
– Improves applications and interoperability between tools
• Specific additions
– Add fixed point NUMERIC and DECIMAL type (in progress)
– Add VARCHAR and CHAR types with limited field size
– Add DATETIME
– Add size ranges from 1 to 53 for FLOAT
– Add synonyms for compatibility
– BLOB for BINARY
– TEXT for STRING
– REAL for FLOAT
© Hortonworks Inc. 2013
Page 10
SQL: Enhancing SQL Semantics
Hive SQL Datatypes
Hive SQL Semantics
SQL Compliance
INT
SELECT, INSERT
TINYINT/SMALLINT/BIGINT
GROUP BY, ORDER BY, SORT BY
BOOLEAN
JOIN on explicit join key
FLOAT
Inner, outer, cross and semi joins
DOUBLE
Sub-queries in FROM clause
Hive 12 provides a wide
array of SQL datatypes
and semantics so your
existing tools integrate
more seamlessly with
Hadoop
STRING
ROLLUP and CUBE
TIMESTAMP
UNION
BINARY
Windowing Functions (OVER, RANK, etc)
DECIMAL
Custom Java UDFs
ARRAY, MAP, STRUCT, UNION
Standard Aggregation (SUM, AVG, etc.)
DATE
Advanced UDFs (ngram, Xpath, URL)
VARCHAR
Sub-queries in WHERE, HAVING
CHAR
Expanded JOIN Syntax
SQL Compliant Security (GRANT, etc.)
INSERT/UPDATE/DELETE (ACID)
© Hortonworks Inc. 2013
Available
Hive 0.12
Roadmap
Example Benchmark Spec
• The TPC-DS benchmark data+query set
• Query 27
– big table(store_sales) joins lots of small tables
– A.K.A Star Schema Join
• What does Query 27 do?
For all items sold in stores located in specified states during a given year, find the
average quantity, average list price, average list sales price, average coupon amount
for a given gender, marital status, education and customer demographic..
© Hortonworks Inc. 2013
Query 27 - Star Schema Join
• Derived from TPC-DS Query 27
SELECT col5, avg(col6)
41 GB
FROM store_sales_fact ssf
58 MB
join item_dim on (ssf.col1 = item_dim .col1)
11MB
join date_dim on (ssf.col2 = date_dim.col2
80MB
join custdmgrphcs_dim on (ssf.col3 =custdmgrphcs_dim.col3)
join store_dim106
on (ssf.col4 = store_dim.col4)
KB
GROUP BY col5
ORDER BY col5
LIMIT 100;
© Hortonworks Inc. 2013
Page 13
New Query Planner
© Hortonworks Inc. 2013
Query27 Execution Before Hive 11-Text Format
The intermediate output of
each job is written to HDFS
Query spawned 5 MR Jobs
© Hortonworks Inc. 2013
179 total mappers got executed
Query Response Time
Query27 Execution With Hive 11-Text Format
Job 1 of 1 – Each Mapper loads into memory the 4
small dimension tables and streams parts of the
large fact table. Joins then occur in Mapper hence
the name MapJoin
Query spawned of 1 job
with Hive 11 compared to 5
MR Jobs with Hive 10
© Hortonworks Inc. 2013
Increase in performance with Hive
11 as query time went down from
21 minutes to about 4 minutes
Query27 Execution With Hive 11- RC Format
Conversion from Text to
RC file format decreased
size of dimension data set
from 38 GB to 8.21 GB
© Hortonworks Inc. 2013
Smaller file equates to less
IO causing the query time
to decrease from 246
seconds to 136 seconds
Query27 Execution With Hive 11- ORC Format
ORC File type consolidates
data more tighly than
RCFile as the size of
dataset decreased from
8.21 GB to 2.83 GB
© Hortonworks Inc. 2013
Smaller file equates to less
IO causing the query time
to decrease from 136
seconds to 104 seconds
Summary of Results
File Type
Number of
MR Jobs
Input Size
Mappers
Time
Text/Hive 10
5
43.1 GB
179
1260 Seconds
Text/Hive 11
1
38 GB
151
246 seconds
RC/Hive 11
1
8.21 GB
76
136 seconds
ORC/Hive 11
1
2.83 GB
38
104 seconds
RC/Hive
1
11/Partitioned/
Bucketed
ORC/Hive
1
11/Partitioned
/Bucketed
1.73 GB
19
104 seconds
687 MB
27
79.62
© Hortonworks Inc. 2013
ORC file format
Optimized RC File
© Hortonworks Inc. 2013
ORCFile - Optimized Column Storage
• Make a better columnar storage file
– Evolve based on Google Dremel format
• Decompose complex row types into primitive fields
– Better compression and projection
• Only read bytes from HDFS for the required columns.
• Store column level aggregates in the files
– Only need to read the file meta information for common queries
– Stored both for file and each section of a file
– Aggregates: min, max, sum, average, count
– Allows fast access by sorted columns
• Ability to add bloom filters for columns
– Enables quick checks for whether a value is present
© Hortonworks Inc. 2013
Page 24
ORCFile - File Layout
© Hortonworks Inc. 2013
Page 25
Interactive Query at Scale
Sustained Query Times
Smaller Footprint
Apache Hive 0.12 provides
sustained acceptable query
times even at petabyte scale
Better encoding with ORC in
Apache Hive 0.12 reduces resource
requirements for your cluster
File Size Comparison Across Encoding Methods
Dataset: TPC-DS Scale 500 Dataset
585 GB
(Original Size)
505 GB
Impala
(14% Smaller)
221 GB
(62% Smaller)
Hive 12
131 GB
(78% Smaller)
Encoded with
Encoded with
Encoded with
Encoded with
Text
RCFile
Parquet
ORCFile
© Hortonworks Inc. 2013
• Larger Block Sizes
• Columnar format
arranges columns
adjacent within the
file for compression
& fast access
Apache Tez
A New Hadoop Data Processing Framework
© Hortonworks Inc. 2013
2011
Page 27
Moving Hadoop Beyond MapReduce
• Low level data-processing execution engine
• Built on YARN
• Enables pipelining of jobs
• Removes task and job launch times
• Does not write intermediate output to HDFS
– Much lighter disk and network usage
• New base of MapReduce, Hive, Pig, Cascading etc.
• Hive and Pig jobs no longer need to move to the end of the queue
between steps in the pipeline
© Hortonworks Inc. 2013
FastQuery: Beyond Batch with YARN
Tez Generalizes Map-Reduce
Always-On Tez Service
Simplified execution plans process
data more efficiently
Low latency processing for
all Hadoop data processing
© Hortonworks Inc. 2013
Page 29
Apache Tez as the new Primitive
MapReduce as Base
Apache Tez as Base
HADOOP 1.0
HADOOP 2.0
Pig
Hive
(data flow)
(sql)
Others
(cascading)
Batch
Data Flow
SQL
Others
MapReduce
Pig
Hive
(cascading)
Tez
Storm
(execution engine)
MapReduce
YARN
(cluster resource management
& data processing)
(cluster resource management)
HDFS
HDFS2
(redundant, reliable storage)
(redundant, reliable storage)
© Hortonworks Inc. 2013
Online
Real Time
Data
Stream
Processing
HBase,
Processing
Accumulo
Hive-on-MR vs. Hive-on-Tez
SELECT a.x, AVERAGE(b.y) AS avg
FROM a JOIN b ON (a.id = b.id) GROUP BY a
UNION SELECT x, AVERAGE(y) AS AVG
FROM c GROUP BY x
ORDER BY AVG;
Hive – MR
M
M
Hive – Tez
M
SELECT a.state
SELECT b.id
R
R
Tez avoids
unneeded writes to
HDFS
M
SELECT a.state,
c.itemId
M
M
R
M
SELECT b.id
R
M
M
HDFS
JOIN (a, c)
SELECT c.price
M
R
M
R
R
HDFS
JOIN (a, c)
R
HDFS
JOIN(a, b)
GROUP BY a.state
COUNT(*)
AVERAGE(c.price)
© Hortonworks Inc. 2013
M
M
M
R
JOIN(a, b)
GROUP BY a.state
COUNT(*)
AVERAGE(c.price)
R
Test Cluster:
• 200 GB Data (ORCFile)
• 20 Nodes, 24GB RAM
each, 6x disk each
Speed: Interactive Query In Hadoop
Query 27: Pricing Analytics using Star Schema Join
Query 82: Inventory Analytics Joining 2 Large Fact Tables
1400s
190x
Improvement
3200s
200x
Improvement
65s
39s
14.9s
7.2s
TPC-DS Query 27
Hive 10
Hive 0.11 (Phase 1)
TPC-DS Query 82
Trunk (Phase 3)
All Results at Scale Factor 200 (Approximately 200GB Data)
© Hortonworks Inc. 2013
Page 32
There is NO second place
Hortonworks
…the Bull Elephant of Hadoop Innovation
© Hortonworks Inc. 2013
Page 33
Thank You
hortonworks.com
hortonworks.com/sandbox
© Hortonworks Inc. 2013