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.

Transcript

1. April 10-12 | Chicago, IL The Future of Apache Hive and Hadoop 2 Bigger, Faster, Stronger Carter Shanklin, Director Product Management: Hortonworks, @cshanklin
2. April 10-12 | Chicago, IL Please silence cell phones
3. 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
4. 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
5. 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
6. 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
7. 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
8. 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
9. The top BI vendors support Hive today Page 9
10. Hive Use Cases Page 10
11. 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.
12. 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.
13. 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
14. 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
15. The “Stinger” Initiative Making Hive 100x Faster Page 15
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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.
22. ORCFile Provides High Compression Page 22
23. 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
24. 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
25. 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
26. 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>
27. 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
28. 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
29. 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
30. Stinger Progress Report Hive 0.11 Page 30
31. Hive 0.11 Star Schema Join Performance Page 31 In-Memory Hash Join From 1400s to < 40s (35x speedup). Improvement through better plan generation.
32. 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
33. 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
34. DEMO! Agile Analytics with Hive Page 34
35. 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.
36. 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.
37. Try Hive Today Page 37
38. Questions? Page 38
39. 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.
40. April 10-12 | Chicago, IL Thank you! Follow us: @hortonworks Diamond Sponsor Platinum Sponsor