Processing Relational Data with Hive Lecture BigData Analytics Julian M. Kunkel julian.kunkel@googlemail.com University of Hamburg / German Climate Computing Center (DKRZ) 2016-12-02 Disclaimer: Big Data software is constantly updated, code samples may be outdated.
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Outline 1 Hive: SQL in the Hadoop Environment 2 Query Execution 3 File Formats 4 HiveQL 5 Summary Julian M. Kunkel Lecture BigData Analytics, 2016 2 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Hive Overview Hive: Data warehouse functionality on top of Hadoop/HDFS Compute engines: Map/Reduce, Tez, Spark Storage formats: Text, ORC, HBASE, RCFile, Avro Manages metadata (schemes) in RDBMS (or HBase) Access via: SQL-like query language HiveQL Similar to SQL-92 but several features are missing Limited transactions, subquery and views Query latency: 10s of seconds to minutes (new versions: sub-seconds) Features Basic data indexing (compaction and bitmaps) User-defined functions to manipulate data and support data-mining Interactive shell: hive Hive Web interface (simple GUI data access and query execution) WebHCat API (RESTful interface) Julian M. Kunkel Lecture BigData Analytics, 2016 3 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Data Model [22] Data types Primitive types (int, float, strings, dates, boolean) Bags (arrays), dictionaries Derived data types (structs) can be defined by users Data organization Table: Like in relational databases with a schema The Hive data definition language (DDL) manages tables Data is stored in files on HDFS Partitions: table key determining the mapping to directories Reduces the amount of data to be accessed in filters Example key: /ds=<date> for table T Predicate T.ds=’2008-09-01’ searches for files in /ds=2008-09-01/ directory Buckets/Clusters: Data of partitions are mapped into files Hash value of a column determines partition Julian M. Kunkel Lecture BigData Analytics, 2016 4 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Managing Schemas with HCatalog Schemas are stored in Hive’s Metastore HCatalog is a layer providing a relational data abstraction [29] Tables incl. metadata can be accessed regardless of file format Exposes schemas and allows data access by other apps Can be used by Pig, Spark, MapReduce jobs Streaming API for Flume and Storm ⇒ Create a table in HCatalog and use the schema elsewhere Can send a Java Message upon integration of new data (for workflows) Provides the REST API WebHCat Command line interface: hcat Source [29] Julian M. Kunkel Lecture BigData Analytics, 2016 5 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Accessing Schemas and Data [45] hive Interactive SQL shell Allows execution from the command line 1 hive -S -e <SQL COMMAND> 2 Example: hive -S -e ’SELECT * from stud’ 3 4 hive -f <SQL SCRIPT> hcat Executes Hives Data Definition Language (DDL) 1 hcat -e <COMMAND> 2 Example: hcat -e ’CREATE TABLE tbl(a INT);’ 3 4 hcat -f <script.hcatalog> -- runs the script of DDL commands Lightweight, but executes only operations that do not require MapReduce Julian M. Kunkel Lecture BigData Analytics, 2016 6 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary 1 Hive: SQL in the Hadoop Environment 2 Query Execution 3 File Formats 4 HiveQL 5 Summary Julian M. Kunkel Lecture BigData Analytics, 2016 7 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Execution of Hive Queries with Hadoop 1 A user submits a query to a user interface 2 The driver receives the SQL query 3 The compiler parses the SQL query Case 1: It changes the data definition or is very simple Case 2: It requires to actually process/ingest data 4 Information about the data types (incl. de/serializer) and structure of the tables is queried from the metastore (HCatalog) 5 The query plan generator translates the query into a execution plan Creates DAG Optimizes the execution, e.g., via cost-based evaluation May use intermediate files on HDFS to process the query Goal: reduce intermediate files 6 The execution engine runs the DAG and handles dependencies 7 The result is stored on HDFS and read by the UI Julian M. Kunkel Lecture BigData Analytics, 2016 8 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Hive Architecture and Query Execution in Hadoop Architecture. Source: Design – Apache Hive [22] Julian M. Kunkel Lecture BigData Analytics, 2016 9 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Example Query Execution and Optimization [23] Count the number of keys which values are above the average 1 SELECT tmp1.key, count(*) 2 FROM (SELECT key, avg(value) AS avg /* Read select 1 = RS1 */ 3 FROM t1 4 GROUP BY /*AGG1*/ key) tmp1 5 JOIN /*JOIN1*/ t1 ON (tmp1.key = t2.key) 6 WHERE t1.value > tmp1.avg 7 GROUP BY /*AGG2*/ tmp1.key; Source: The original operator tree [23] Source: The optimized operator tree [23] Julian M. Kunkel Lecture BigData Analytics, 2016 10 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Example Query Execution and Optimization [23] Count orders, their profit and costs for a given company and state 1 SELECT count(distinct ws1.ws_order_number) as order_count, 2 sum(ws1.ws_ext_ship_cost) as total_shipping_cost, 3 sum(ws1.ws_net_profit) as total_net_profit 4 FROM web_sales ws1 5 JOIN /*MJ1*/ customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) 6 JOIN /*MJ2*/ web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) 7 JOIN /*MJ3*/ date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) 8 LEFT SEMI JOIN /*JOIN4*/ (SELECT ws2.ws_order_number as ws_order_number 9 FROM web_sales ws2 JOIN /*JOIN1*/ web_sales ws3 10 ON (ws2.ws_order_number = ws3.ws_order_number) 11 WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk) ws_wh1 12 ON (ws1.ws_order_number = ws_wh1.ws_order_number) 13 LEFT SEMI JOIN /*JOIN4*/ (SELECT wr_order_number 14 FROM web_returns wr 15 JOIN /*JOIN3*/ (SELECT ws4.ws_order_number as ws_order_number 16 FROM web_sales ws4 JOIN /*JOIN2*/ web_sales ws5 17 ON (ws4.ws_order_number = ws5.ws_order_number) 18 WHERE ws4.ws_warehouse_sk <> ws5.ws_warehouse_sk) ws_wh2 19 ON (wr.wr_order_number = ws_wh2.ws_order_number)) tmp1 20 ON (ws1.ws_order_number = tmp1.wr_order_number) 21 WHERE d.d_date >= ’2001-05-01’ and d.d_date <= ’2001-06-30’ and 22 ca.ca_state = ’NC’ and s.web_company_name = ’pri’; Julian M. Kunkel Lecture BigData Analytics, 2016 11 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Example Query: Original Execution Plan Source: The original operator tree [23] Julian M. Kunkel Lecture BigData Analytics, 2016 12 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Example Query: Optimized Execution Plan Source: The optimized operator tree [23] Julian M. Kunkel Lecture BigData Analytics, 2016 13 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary Hive DAG Execution on TEZ Source: H. Shah [20] Julian M. Kunkel Lecture BigData Analytics, 2016 14 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary LLAP: Live Long and Process [38, 39] Features identified to be necessary to improve performance [38] Asynchronous spindle-aware IO Pre-fetching, caching of column chunks Multi-threaded JIT-friendly operator pipelines Strategy Optional (LLAP) long-living daemon can be run on worker nodes LLAP daemon receives tasks (instead of HDFS) and query fragments A processing engine that forwards I/O requests to HDFS It survives single queries, processes data and caches Multiple queries can be processed at the same time Compress cached data LLAP is integrated into YARN and can use Slider for fast deployment Supports container delegation, i.e., workload depending resources But, locality becomes the responsibility of the caller (YARN) Julian M. Kunkel Lecture BigData Analytics, 2016 15 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary LLAP Benefits and Architecture [38,39] Significantly reduced startup time (sub second for some queries) Scalable architecture Caching of compressed data reduces memory footprint Other frameworks such as Pig can use the daemon Together with Apache Ranger, it enables column-level access control Hive 2 with LLAP: Architecture Overview. Source [39] Julian M. Kunkel Lecture BigData Analytics, 2016 16 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary 1 Hive: SQL in the Hadoop Environment 2 Query Execution 3 File Formats 4 HiveQL 5 Summary Julian M. Kunkel Lecture BigData Analytics, 2016 17 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary File Formats: Overview Support typical HDFS file types SequenceFile, CSV, JSON, AVRO Can access data stored in HBASE via HBaseStorageHandler [30] Formats are not necessarily well suited for queries A query may require a full scan of all content ORC is an optimized file format To see table properties/file format: describe extended < table > Julian M. Kunkel Lecture BigData Analytics, 2016 18 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary ORC: Optimized Row Colmnar [25] Features Light-weight, index stored within the file Compression based on data type Concurrent reads of the same file Split files without scanning for markers Support for adding/removal of fields Update of files is partially supported Potential ACID support Julian M. Kunkel Lecture BigData Analytics, 2016 19 / 47
Hive: SQL in the Hadoop Environment Query Execution File Formats HiveQL Summary ORC Files [25] Stripe: group of row data Postcript: contains file metadata Compression parameters Size of the file footer Index data (per stripe & row group) Min and max values Bloom filter (to pre-filter matches) Row position Compression of blocks: RLE, ZLIB, SNAPPY, LZO Tool to output ORC files: hive -orcfiledump Source: [25] Row groups are by default 10k rows of one column Julian M. Kunkel Lecture BigData Analytics, 2016 20 / 47
Hiển thị 20 trang đầu. Tải tài liệu để xem đầy đủ.
Chưa có bình luận nào. Hãy là người đầu tiên!
Bình luận (0)
Chưa có bình luận nào. Hãy là người đầu tiên!