HOME

TheInfoList



OR:

Apache Hive is a
data warehouse In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for Business intelligence, reporting and data analysis and is a core component of business intelligence. Data warehouses are central Re ...
software project. It is built on top of
Apache Hadoop Apache Hadoop () is a collection of open-source software utilities for reliable, scalable, distributed computing. It provides a software framework for distributed storage and processing of big data using the MapReduce programming model. Hadoop wa ...
for providing data query and analysis. Hive gives an 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 MapReduce is a programming model and an associated implementation for processing and generating big data sets with a parallel and distributed algorithm on a cluster. A MapReduce program is composed of a ''map'' procedure, which performs filte ...
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. Hive facilitates the integration of SQL-based querying languages with Hadoop, which is commonly used in data warehousing applications. While initially developed by
Facebook Facebook is a social media and social networking service owned by the American technology conglomerate Meta Platforms, Meta. Created in 2004 by Mark Zuckerberg with four other Harvard College students and roommates, Eduardo Saverin, Andre ...
, Apache Hive is used and developed by other companies such as
Netflix Netflix is an American subscription video on-demand over-the-top streaming service. The service primarily distributes original and acquired films and television shows from various genres, and it is available internationally in multiple lang ...
and the
Financial Industry Regulatory Authority The Financial Industry Regulatory Authority (FINRA) is a private American corporation that acts as a self-regulatory organization (SRO) that regulates member brokerage firms and exchange markets. FINRA is the successor to the National Associati ...
(FINRA). Amazon maintains a software fork of Apache Hive included in Amazon Elastic MapReduce on
Amazon Web Services Amazon Web Services, Inc. (AWS) is a subsidiary of Amazon.com, Amazon that provides Software as a service, on-demand cloud computing computing platform, platforms and Application programming interface, APIs to individuals, companies, and gover ...
.


Features

Apache Hive supports the analysis of large datasets stored in Hadoop's HDFS and compatible file systems such as
Amazon S3 Amazon Simple Storage Service (S3) is a service offered by Amazon Web Services (AWS) that provides object storage through a web service interface. Amazon S3 uses the same scalable storage infrastructure that Amazon.com uses to run its e-commerc ...
filesystem and Alluxio. It provides a SQL-like query language called HiveQL with schema on read and transparently converts queries to
MapReduce MapReduce is a programming model and an associated implementation for processing and generating big data sets with a parallel and distributed algorithm on a cluster. A MapReduce program is composed of a ''map'' procedure, which performs filte ...
, Apache Tez and Spark jobs. All three execution engines can run in
Hadoop Apache Hadoop () is a collection of Open-source software, open-source software utilities for reliable, scalable, distributed computing. It provides a software framework for Clustered file system, distributed storage and processing of big data usin ...
's resource negotiator, YARN (Yet Another Resource Negotiator). To accelerate queries, it provided indexes, but this feature was removed in version 3.0 Other features of Hive include: * Different storage types such as plain text,
RCFile Within database management systems, the record columnar file or RCFile is a data placement structure that determines how to store Table (database), relational tables on computer clusters. It is designed for systems using the MapReduce framework. Th ...
, HBase, ORC, and others. * Metadata storage in a
relational database management system A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
, significantly reduces the time to perform semantic checks during query execution. * Operating on compressed data stored in the Hadoop ecosystem using algorithms including DEFLATE, BWT, Snappy, etc. * Built-in
user-defined function A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its c ...
s (UDFs) to manipulate dates, strings, and other data-mining tools. Hive supports extending the UDF set to handle use cases not supported by built-in functions. * SQL-like queries (HiveQL), which are implicitly converted into MapReduce or Tez, or Spark jobs. By default, Hive stores metadata in an embedded
Apache Derby Apache Derby (previously distributed as IBM Cloudscape) is a relational database management system (RDBMS) developed by the Apache Software Foundation that can be embedded in Java programs and used for online transaction processing. It has a 3.5 ...
database, and other client/server databases like
MySQL MySQL () is an Open-source software, open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A rel ...
can optionally be used. The first four file formats supported in Hive were plain text, sequence file, optimized row columnar (ORC) format and
RCFile Within database management systems, the record columnar file or RCFile is a data placement structure that determines how to store Table (database), relational tables on computer clusters. It is designed for systems using the MapReduce framework. Th ...
.
Apache Parquet Apache Parquet is a free and open-source column-oriented data storage format in the Apache Hadoop ecosystem. It is similar to RCFile and ORC, the other columnar-storage file formats in Hadoop, and is compatible with most of the data processing f ...
can be read via plugin in versions later than 0.10 and natively starting at 0.13.


Architecture

Major components of the Hive architecture are: * Metastore: Stores metadata for each of the tables such as their schema and location. It also includes the partition metadata which helps the driver to track the progress of various data sets distributed over the cluster. The data is stored in a traditional
RDBMS A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
format. The metadata helps the driver to keep track of the data and it is crucial. Hence, a backup server regularly replicates the data which can be retrieved in case of data loss. * Driver: Acts like a controller which receives the HiveQL statements. It starts the execution of the statement by creating sessions and monitors the life cycle and progress of the execution. It stores the necessary metadata generated during the execution of a HiveQL statement. The driver also acts as a collection point of data or query results obtained after the Reduce operation. * Compiler: Performs compilation of the HiveQL query, which converts the query to an execution plan. This plan contains the tasks and steps needed to be performed by the
Hadoop Apache Hadoop () is a collection of Open-source software, open-source software utilities for reliable, scalable, distributed computing. It provides a software framework for Clustered file system, distributed storage and processing of big data usin ...
MapReduce MapReduce is a programming model and an associated implementation for processing and generating big data sets with a parallel and distributed algorithm on a cluster. A MapReduce program is composed of a ''map'' procedure, which performs filte ...
to get the output as translated by the query. The compiler converts the query to an
abstract syntax tree An abstract syntax tree (AST) is a data structure used in computer science to represent the structure of a program or code snippet. It is a tree representation of the abstract syntactic structure of text (often source code) written in a formal ...
(AST). After checking for compatibility and compile time errors, it converts the AST to a
directed acyclic graph In mathematics, particularly graph theory, and computer science, a directed acyclic graph (DAG) is a directed graph with no directed cycles. That is, it consists of vertices and edges (also called ''arcs''), with each edge directed from one ...
(DAG). The DAG divides operators to MapReduce stages and tasks based on the input query and data. * Optimizer: Performs various transformations on the execution plan to get an optimized DAG. Transformations can be aggregated together, such as converting a pipeline of joins to a single join, for better performance. It can also split the tasks, such as applying a transformation on data before a reduced operation, to provide better performance and scalability. However, the logic of transformation used for optimization can be modified or pipelined using another optimizer. An optimizer called YSmart is a part of Apache Hive. This correlated optimizer merges correlated MapReduce jobs into a single MapReduce job, significantly reducing the execution time. * Executor: After compilation and optimization, the executor executes the tasks. It interacts with the job tracker of Hadoop to schedule tasks to be run. It takes care of pipelining the tasks by making sure that a task with dependency gets executed only if all other prerequisites are run. * CLI, UI, and Thrift Server: A
command-line interface A command-line interface (CLI) is a means of interacting with software via command (computing), commands each formatted as a line of text. Command-line interfaces emerged in the mid-1960s, on computer terminals, as an interactive and more user ...
(CLI) provides a
user interface In the industrial design field of human–computer interaction, a user interface (UI) is the space where interactions between humans and machines occur. The goal of this interaction is to allow effective operation and control of the machine fro ...
for an external user to interact with Hive by submitting queries, and instructions and monitoring the process status. Thrift server allows external clients to interact with Hive over a network, similar to the
JDBC Java Database Connectivity (JDBC) is an application programming interface (API) for the Java (programming language), Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java ...
or
ODBC In computing, Open Database Connectivity (ODBC) is a standard application programming interface (API) for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems. An ...
protocols.


HiveQL

While based on SQL, HiveQL does not strictly follow the full
SQL-92 SQL-92 (also called SQL 2) was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. Aside from a few minor incompatibilities, the SQL-89 standard is forward-compatible with SQL-92. The ...
standard. HiveQL offers extensions not in SQL, including ''multi-table inserts,'' and ''creates tables as select''. HiveQL lacked support for transactions and
materialized view In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summa ...
s, and only limited subquery support. Support for insert, update, and delete with full
ACID An acid is a molecule or ion capable of either donating a proton (i.e. Hydron, hydrogen cation, H+), known as a Brønsted–Lowry acid–base theory, Brønsted–Lowry acid, or forming a covalent bond with an electron pair, known as a Lewis ...
functionality was made available with release 0.14. Internally, a
compiler In computing, a compiler is a computer program that Translator (computing), translates computer code written in one programming language (the ''source'' language) into another language (the ''target'' language). The name "compiler" is primaril ...
translates HiveQL statements into a
directed acyclic graph In mathematics, particularly graph theory, and computer science, a directed acyclic graph (DAG) is a directed graph with no directed cycles. That is, it consists of vertices and edges (also called ''arcs''), with each edge directed from one ...
of
MapReduce MapReduce is a programming model and an associated implementation for processing and generating big data sets with a parallel and distributed algorithm on a cluster. A MapReduce program is composed of a ''map'' procedure, which performs filte ...
, Tez, or Spark jobs, which are submitted to Hadoop for execution.


Example

The word count program counts the number of times each word occurs in the input. The word count can be written in HiveQL as: DROP TABLE IF EXISTS docs; CREATE TABLE docs (line STRING); LOAD DATA INPATH 'input_file' OVERWRITE INTO TABLE docs; CREATE TABLE word_counts AS SELECT word, count(1) AS count FROM (SELECT explode(split(line, '\s')) AS word FROM docs) temp GROUP BY word ORDER BY word; A brief explanation of each of the statements is as follows: DROP TABLE IF EXISTS docs; 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. LOAD DATA INPATH 'input_file' OVERWRITE INTO TABLE docs; 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. CREATE TABLE word_counts AS SELECT word, count(1) AS count FROM (SELECT explode(split(line, '\s')) AS word FROM docs) temp GROUP BY word ORDER BY word; The query creates a table called word_counts with two columns: word and count. This query draws its input from the inner query . This query serves to split the input words into different rows of a temporary table aliased as temp. The 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 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 Apache Hadoop () is a collection of Open-source software, open-source software utilities for reliable, scalable, distributed computing. It provides a software framework for Clustered file system, distributed storage and processing of big data usin ...
ecosystem, and has to comply with the restrictions of Hadoop and
MapReduce MapReduce is a programming model and an associated implementation for processing and generating big data sets with a parallel and distributed algorithm on a cluster. A MapReduce program is composed of a ''map'' procedure, which performs filte ...
. 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 A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
, Hive supports all four properties of transactions (
ACID An acid is a molecule or ion capable of either donating a proton (i.e. Hydron, hydrogen cation, H+), known as a Brønsted–Lowry acid–base theory, Brønsted–Lowry acid, or forming a covalent bond with an electron pair, known as a Lewis ...
): Atomicity,
Consistency In deductive logic, a consistent theory is one that does not lead to a logical contradiction. A theory T is consistent if there is no formula \varphi such that both \varphi and its negation \lnot\varphi are elements of the set of consequences ...
, Isolation, and
Durability Durability is the ability of a physical product to remain functional, without requiring excessive maintenance or repair, when faced with the challenges of normal operation over its design lifetime. There are several measures of durability in us ...
. Transactions in Hive were introduced in Hive 0.13 but were only limited to the partition level. The recent version of Hive 0.14 had these functions fully added to support complete
ACID An acid is a molecule or ion capable of either donating a proton (i.e. Hydron, hydrogen cation, H+), known as a Brønsted–Lowry acid–base theory, Brønsted–Lowry acid, or forming a covalent bond with an electron pair, known as a Lewis ...
properties. Hive 0.14 and later provides different row level transactions such as '', and ''. Enabling '', , and '' transactions require setting appropriate values for configuration properties such as hive.support.concurrency, hive.enforce.bucketing, and hive.exec.dynamic.partition.mode.


Security

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 unmask value for the Hive configuration variable hive.files.umask.value.


See also

*
Apache Pig Apache Pig is a high-level platform for creating programs that run on Hadoop, Apache Hadoop. The language for this platform is called Pig Latin. Pig can execute its Hadoop jobs in MapReduce, Apache Tez, or Apache Spark. Pig Latin abstracts the ...
*
Sqoop Sqoop is a command-line interface application for transferring data between relational databases and Hadoop. The Apache Sqoop project was retired in June 2021 and moved to the Apache Attic. Description Sqoop supports incremental loads of a single ...
*
Apache Impala Apache Impala is an open source massively parallel processing (MPP) SQL query engine for data stored in a computer cluster running Apache Hadoop. Impala has been described as the open-source equivalent of Google F1, which inspired its developmen ...
* Apache Drill *
Apache Flume This list of Apache Software Foundation projects contains the software development projects of The Apache Software Foundation (ASF). Besides the projects, there are a few other distinct areas of Apache: *Incubator: for aspiring ASF projects *Att ...
*
Apache HBase HBase is an open-source non-relational distributed database modeled after Google's Bigtable and written in Java. It is developed as part of Apache Software Foundation's Apache Hadoop project and runs on top of HDFS (Hadoop Distributed Fil ...
* Trino (SQL query engine)


References


External links

* {{DEFAULTSORT:Hive 2015 software Hive Cloud computing Facebook software Free software programmed in Java (programming language) Free system software Hadoop Software using the Apache license