Share
Embed
Future of Apache Hive -- SQL PASS BA 2013
This presentation gives an introduction to Apache Hive, the data warehousing system for Hadoop that provides a SQL interface and tabular model to data residing within Hadoop. Hive is extremely scalable and used in production in many companies for batch oriented workloads.
This talk also covers how Hadoop 2 changes the game to allow Hive to serve interactive queries and gives a deep look at Project Stinger, an initiative that aims to make Hive 100x faster than it is today. Details are given on exactly how this will happen, including a modern column store, a vectorized query engine, a more intelligent query planner, and Apache Tez, the framework that brings true interactivity to Hadoop.
Carter Shanklin
April 11, 2013
Transcript
40 slides
-
April 10-12 | Chicago, IL The Future of Apache Hive
and Hadoop 2 Bigger, Faster, Stronger Carter Shanklin, Director Product Management: Hortonworks, @cshanklin -
In This Session We Will • Discuss Hive, a petabyte
scale data warehouse. • Compare Hive to other RDBMS you’ve seen. • Look at key ways you can benefit from Hive today. • Talk about how Hive will become a solution for interactive query. • See a demo on agile analytics with Hive. Page 3 -
Hive: The SQL Interface to Hadoop Page 4 HiveServer2 Hive
Job Tracker Name Node Task Tracker Data Node Web UI JDBC / ODBC CLI Hive Hadoop Compiler Optimizer Executor Hive SQL Map / Reduce 1) User issues SQL query 2) Hive parses and plans query 3) Query converted to Map/Reduce 4) Map/Reduce run by Hadoop -
How Hive is like other SQL Databases • Supports most
SQL query semantics. • Data type model is “Java flavored” • Support being added for important SQL datatypes. • Supports CUBE and ROLLUP. • ODBC and JDBC Connectivity • Authentication via Kerberos and LDAP • Authorizations • Table-level Authorizations • Filesystem-level Permissions Page 5 -
How Hive Is Expanding SQL Coverage Page 6 SQL Feature
Version DECIMAL 0.10 CUBE and ROLLUP 0.10 OVER with PARTITION BY and ORDER BY 0.11 RANK, DENSE_RANK, CUME_DIST, etc. 0.11 Subquery in IN, NOT IN, HAVING 0.12 CHAR, VARCHAR, DATETIME 0.12 -
How Hive Differs From Other RDBMS • Execution all through
Map/Reduce • Runs on hundreds or thousands of nodes • Resilient to node failure. • Processing is checkpointed and restarted in event of failure. • Designed for high concurrency. • Easily handles hundreds or thousands of concurrent queries. • Support for semi-structured data like JSON. • Non-traditional datatypes like maps, structs and unions. • Not suitable for online apps. • No transactions. • No UPDATE support. Page 7 -
Hive: Reliable Distributed SQL Processing Page 8 Hive Task Tracker
Time 1: Job = 50% Complete Progress Stored in HDFS Node 1 Node 2 Node 3 Node 4 H D F S Time 2: Node 3 Fails, Job = 85% Complete Node 1 Node 2 Node 3 Node 4 H D F S Time 3: Job moves to Node 4 Job = 100% Complete Node 1 Node 2 Node 3 Node 4 H D F S SQL Map / Reduce -
Main Ways People Use Hive Today Page 11 ETL /
ELT – Convert data stored in HDFS and export it to MPP. Data Retention / MPP Offload – Query 3 years of data rather than 3 months of data. More Scalable SQL Processing – Original motivation for building Hive. Agile Analytics – Query structured or unstructured data in the same platform. – Uses the “Big Fat Table” pattern. -
Hive Use Cases Page 12 " The original developers of
Hive. " More data than existing RDBMS could handle. " 100+ PB of data. " 15+ TB of data loaded daily. " 60,000+ Hive queries per day. " More than 1,000 users per day. -
Hive Use Cases Page 13 Nationwide Retail Chain " ELT
/ Data Preparation " Ingest clickstream data " Use custom Hive functions to apply structure " Data then exported to MPP store " Data Retention and Analytics Offload " Wanted to retain more data than they could in MPP " Move data to Hive and retain ability to query -
Hive Use Cases Page 14 Major Healthcare Company "
Agile Analytics " Traditionally ETL necessary to make query feasible " ETL implementation led to long cycles and rigid results " Use Hive to make “big fat tables”, no pre-processing " Data explored to identify meaningful patterns " Patterns systematized into formal ETL processes -
Differing Needs For Scale / Interaction Page 16 Interactive Batch
• Parameterized Reports • Drilldown • Visualization • Exploration • Operational batch processing • Enterprise Reports • Data Mining Data Size 5s – 1m 1m – 1h 1h+ Non- Interactive • Data preparation • Incremental batch processing • Dashboards / Scorecards Interactivity is key Scalability and Reliability are key -
Stinger: Make Hive Best for All Needs Page 17 Interactive
Batch • Parameterized Reports • Drilldown • Visualization • Exploration • Operational batch processing • Enterprise Reports • Data Mining Data Size 5s – 1m 1m – 1h 1h+ Non- Interactive • Data preparation • Incremental batch processing • Dashboards / Scorecards Improve Latency & Throughput • Query engine improvements • ORCFile column store • Next-gen runtime (elim’s M/R latency) Extend Deep Analytical Ability • Analytics functions • Improved SQL coverage • Continued focus on core Hive use cases -
What Causes Latency in Hive? Some query plans generated sub-optimally.
Unnecessary persistence of intermediate data to HDFS. Stored data not optimized for read. Non-optimized operations for aggregations, projections, etc. High job startup time. Page 18 -
Stinger Initiative: Making Hive 100x Faster Page 19 Hadoop
Hive ORCFile Column Store High Compression Predicate / Filter Pushdowns Buffer Caching Cache accessed data Op:mized for vector engine Tez Express data processing tasks more simply Eliminate disk writes Tez Service Pre-‐warmed Containers Low-‐latency dispatch Vector Query Engine Op:mized for modern processor architectures Base Op@miza@ons Generate simplified DAGs Join Improvements Query Planner Intelligent Cost-‐Based Op:mizer Current 3 – 9 Months 9 – 18 Months -
Base Optimizations Performance Improvements in Hive 0.11: New Join Types
added or improved in Hive 0.11: – In-memory Hash Join: Fast for fact-to-dimension joins. – Sort-Merge-Bucket Join: Scalable for large-table to large-table joins. More Efficient Query Plan Generation – Joins done in-memory when possible, saving map-reduce steps. – Combine map/reduce jobs when GROUP BY and ORDER BY use the same key. More Than 30x Performance Improvement for Star Schema Join – More on this later. Page 20 -
ORCFile – Efficient Columnar Layout Page 21 Large block size
well suited for HDFS. Columnar format arranges columns adjacent within the file for compression and fast access. -
Hive Performance Roadmap - Vectorization Designed for Modern Processor Architectures
– Make the most use of L1 and L2 cache. – Avoid branching whenever possible. How It Works – Process records in batches of 1,000 to maximize cache use. – Generate code on-the-fly to minimize branching. What It Gives – 30x+ improvement in number of rows processed per second. Page 23 -
Hadoop 2 and YARN YARN = Next Gen Resource Management
for Hadoop – Provides a generic compute and resource management service – Cluster-wide and deeply integrated with Hadoop The Foundation for Data Processing on Hadoop – Data processing frameworks can extend YARN – Graph Processing – Streaming – Etc. Page 24 -
Tez – The Foundation For Fast Data Low-Level Data Processing
Engine – Generalizes Map/Reduce – Enables pipelining of jobs – Allows results to be streamed rather than batched Enables Low Latency and Faster Processing – Intermediate results don’t need to be written to HDFS – Tez Service eliminates job startup times Built on YARN – Suitable for mixed workload environments Page 25 -
Tez - Core Idea Task with pluggable Input, Processor &
Output Page 26 YARN ApplicationMaster to run DAG of Tez Tasks Input Processor Task Output Tez Task - <Input, Processor, Output> -
Tez – Blocks for building tasks Page 27 MapReduce ‘Map’
MapReduce ‘Reduce’ HDFS Input Map Processor MapReduce ‘Map’ Task Sorted Output Shuffle Input Reduce Processor HDFS Output Intermediate ‘Reduce’ for Map-Reduce-Reduce Shuffle Input Reduce Processor Intermediate ‘Reduce’ for Map- Reduce-Reduce Sorted Output MapReduce ‘Reduce’ Task -
Tez Service Map/Reduce Query Startup Expensive Solution – Tez Service – Hot
containers ready for immediate use – Removes task and job launch overhead (~5s – 30s) – Hive – Submits query plan directly to Tez Service – Native Hadoop service, not ad-hoc Page 28 -
Hive – MR Hive – Tez Hive/MR versus Hive/Tez Page
29 SELECT a.state, COUNT(*), AVERAGE(c.price) FROM a JOIN b ON (a.id = b.id) JOIN c ON (a.itemId = c.itemId) GROUP BY a.state SELECT a.state JOIN (a, c) SELECT c.price SELECT b.id JOIN(a, b) GROUP BY a.state COUNT(*) AVERAGE(c.price) M M M R R M M R M M R M M R HDFS HDFS HDFS M M M R R R M M R R SELECT a.state, c.itemId JOIN (a, c) JOIN(a, b) GROUP BY a.state COUNT(*) AVERAGE(c.price) SELECT b.id Tez avoids unnecessary writes to HDFS -
Hive 0.11 Star Schema Join Performance Page 31 In-Memory Hash
Join From 1400s to < 40s (35x speedup). Improvement through better plan generation. -
Hive 0.11 Large-to-Large Join Page 32 In-Memory Hash Join From
3200s to < 40s (80x speedup). Improvement through more efficient join implementation -
Hive: Summary Proven and scalable for batch processing – Reliable processing
for any data size. – Hundreds or thousands of concurrent queries. Hive is evolving to be good for interactive query – Query big data from tools like Excel, PowerPivot, Tableau, etc. Page 33 -
Hive Use Cases: Agile + Analytics Page 35 {"country":"USA", "languages":["English"],
"attrs":{"date":"2012-‐04-‐06"}} {"country":"England", "languages":["English"], "attrs":{"battery":"1.553114","color":"whi.. {"country":"China", "languages":["Chinese"], "attrs":{"y":"11.514241","battery":"59.64114.. Data: JSON records country, languages spoken, and attributes about source device. (Data set is randomly generated) Challenge: • Software version revs over time. • Different versions of the software record different attributes. • In this demo v1 records date, v2 adds phone properties, v3 adds GPS coordinates • Leads to “ragged data” that is challenging to process in pure relational systems Need: Perform various analytics on this semi-structured data. Note: JSON = Javascript Object Notation, commonly used for semistructured data. -
Hive Use Cases: Agile + Analytics Page 36 ADD JAR
/home/sandbox/hivedemo/json-‐serde-‐1.1.4.jar; CREATE TABLE IF NOT EXISTS json (country string, languages array<string>, attrs map<string, string>) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE; LOAD DATA LOCAL INPATH 'records.json' OVERWRITE INTO TABLE json; # Average # of languages by country. SELECT AVG(languages.length) FROM json GROUP BY country; # Compute the average battery level for android devices. select avg(attrs["battery"]) from json where attrs["type"] == "android”; Solution: Use a JSON SerDe SerDe = Serializer/Deserializer. Allows Hive to read arbitrarily formatted data. Map challenging JSON structures into nested Hive datatypes such as array or map. -
Win a Microsoft Surface Pro! Complete an online SESSION EVALUATION
to be entered into the draw. Draw closes April 12, 11:59pm CT Winners will be announced on the PASS BA Conference website and on Twitter. Go to passbaconference.com/evals or follow the QR code link displayed on session signage throughout the conference venue. Your feedback is important and valuable. All feedback will be used to improve and select sessions for future events. -
April 10-12 | Chicago, IL Thank you! Follow us: @hortonworks
Diamond Sponsor Platinum Sponsor