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