Apache Hive Tutorial
  • hive - Tutorial
  • - Css
  • - W3css
  • hive - Useful Resources
  • Hive - Ebook Download

  • Apache Hive

    Apache Hive is a data warehouse software project built on top of Apache Hadoop for providing data query and analysis. Hive gives a SQL-like interface to query data stored in various databases and file systems that integrate with Hadoop. Traditional SQL queries must be implemented in the MapReduce Java API to execute SQL applications and queries over distributed data. Hive provides the necessary SQL abstraction to integrate SQL-like queries HiveQL into the underlying Java without the need to implement queries in the low-level Java API. Since most data warehousing applications work with SQL-based querying languages, Hive aids portability of SQL-based applications to Hadoop. While initially developed by Facebook, Apache Hive is used and developed by other companies such as Netflix and the Financial Industry Regulatory Authority FINRA. Amazon maintains a software fork of Apache Hive included in Amazon Elastic MapReduce on Amazon Web Services.


    Apache Hive supports analysis of large datasets stored in Hadoop's HDFS and compatible file systems such as Amazon S3 filesystem and Alluxio. It provides a SQL-like query language called HiveQL with schema on read and transparently converts queries to MapReduce, Apache Tez and Spark jobs. All three execution engines can run in Hadoop's resource negotiator, YARN Yet Another Resource Negotiator. To accelerate queries, it provides indexes, including bitmap indexes. Other features of Hive include:

    By default, Hive stores metadata in an embedded Apache Derby database, and other client/server databases like MySQL can optionally be used.

    The first four file formats supported in Hive were plain text, sequence file, optimized row columnar ORC format and RCFile. Apache Parquet can be read via plugin in versions later than 0.10 and natively starting at 0.13. Additional Hive plugins support querying of the Bitcoin Blockchain.


    Major components of the Hive architecture are:


    While based on SQL, HiveQL does not strictly follow the full SQL-92 standard. HiveQL offers extensions not in SQL, including multitable inserts and create table as select, but only offers basic support for indexes. HiveQL lacked support for transactions and materialized views, and only limited subquery support. Support for insert, update, and delete with full ACID functionality was made available with release 0.14.

    Internally, a compiler translates HiveQL statements into a directed acyclic graph of MapReduce, Tez, or Spark jobs, which are submitted to Hadoop for execution.

    1 input_lines = LOAD '/tmp/word.txt' AS line:chararray;
    2 words = FOREACH input_lines GENERATE FLATTENTOKENIZEline AS word;
    3 filtered_words = FILTER words BY word MATCHES '\\w+';
    4 word_groups = GROUP filtered_words BY word;
    5 word_count = FOREACH word_groups GENERATE COUNTfiltered_words AS count, group AS word;
    6 ordered_word_count = ORDER word_count BY count DESC;
    7 STORE ordered_word_count INTO '/tmp/results.txt';

    The word count program counts the number of times each word occurs in the input. The word count can be written in HiveQL as:

    2 CREATE TABLE docs line STRING;
    4 CREATE TABLE word_counts AS
    5 SELECT word, count1 AS count FROM
    6  SELECT explodesplitline, '\s' AS word FROM docs temp
    7 GROUP BY word
    8 ORDER BY word;

    A brief explanation of each of the statements is as follows:

    2 CREATE TABLE docs line STRING;

    Checks if table docs exists and drops it if it does. Creates a new table called docs with a single column of type STRING called line.


    Loads the specified file or directory In this case “input_file” into the table. OVERWRITE specifies that the target table to which the data is being loaded into is to be re-written; Otherwise the data would be appended.

    4 CREATE TABLE word_counts AS
    5 SELECT word, count1 AS count FROM
    6 SELECT explodesplitline, '\s' AS word FROM docs temp
    7 GROUP BY word
    8 ORDER BY word;

    The query CREATE TABLE word_counts AS SELECT word, count1 AS count creates a table called word_counts with two columns: word and count. This query draws its input from the inner query SELECT explodesplitline, '\s' AS word FROM docs temp". This query serves to split the input words into different rows of a temporary table aliased as temp. The GROUP BY WORD groups the results based on their keys. This results in the count column holding the number of occurrences for each word of the word column. The ORDER BY WORDS sorts the words alphabetically.

    Comparison with traditional databases

    The storage and querying operations of Hive closely resemble those of traditional databases. While Hive is a SQL dialect, there are a lot of differences in structure and working of Hive in comparison to relational databases. The differences are mainly because Hive is built on top of the Hadoop ecosystem, and has to comply with the restrictions of Hadoop and MapReduce.

    A schema is applied to a table in traditional databases. In such traditional databases, the table typically enforces the schema when the data is loaded into the table. This enables the database to make sure that the data entered follows the representation of the table as specified by the table definition. This design is called schema on write. In comparison, Hive does not verify the data against the table schema on write. Instead, it subsequently does run time checks when the data is read. This model is called schema on read. The two approaches have their own advantages and drawbacks. Checking data against table schema during the load time adds extra overhead, which is why traditional databases take a longer time to load data. Quality checks are performed against the data at the load time to ensure that the data is not corrupt. Early detection of corrupt data ensures early exception handling. Since the tables are forced to match the schema after/during the data load, it has better query time performance. Hive, on the other hand, can load data dynamically without any schema check, ensuring a fast initial load, but with the drawback of comparatively slower performance at query time. Hive does have an advantage when the schema is not available at the load time, but is instead generated later dynamically.

    Transactions are key operations in traditional databases. As any typical RDBMS, Hive supports all four properties of transactions ACID: Atomicity, Consistency, Isolation, and Durability. Transactions in Hive were introduced in Hive 0.13 but were only limited to the partition level. Recent version of Hive 0.14 were these functions fully added to support complete ACID properties. Hive 0.14 and later provides different row level transactions such as INSERT, DELETE and UPDATE. Enabling INSERT, UPDATE, DELETE transactions require setting appropriate values for configuration properties such as hive.support.concurrency, hive.enforce.bucketing, and hive.exec.dynamic.partition.mode.


    Hive v0.7.0 added integration with Hadoop security. Hadoop began using Kerberos authorization support to provide security. Kerberos allows for mutual authentication between client and server. In this system, the client's request for a ticket is passed along with the request. The previous versions of Hadoop had several issues such as users being able to spoof their username by setting the hadoop.job.ugi property and also MapReduce operations being run under the same user: hadoop or mapred. With Hive v0.7.0's integration with Hadoop security, these issues have largely been fixed. TaskTracker jobs are run by the user who launched it and the username can no longer be spoofed by setting the hadoop.job.ugi property. Permissions for newly created files in Hive are dictated by the HDFS. The Hadoop distributed file system authorization model uses three entities: user, group and others with three permissions: read, write and execute. The default permissions for newly created files can be set by changing the umask value for the Hive configuration variable hive.files.umask.value.