Unit-4

Apache HIVE
What is Hive?

Apache Hive is a data warehouse software that facilitates reading, writing, and
managing large datasets residing in distributed storage using SQL. Hive is built on
top of Apache Hadoop and allows users to query and analyze large datasets
stored in Hadoop Distributed File System (HDFS) using a SQL-like language called
HiveQL (Hive Query Language). Hive provides a familiar SQL interface for users
who are not familiar with Hadoop, making it easier to analyze large datasets.

Key Features of Hive

SQL-like language (HiveQL): Hive uses a SQL-like language called HiveQL, which is
easy to learn and use for users who are familiar with SQL.

Scalability: Hive is designed to scale to handle large datasets, making it suitable

for big data analytics.

Performance: Hive can query large datasets efficiently by leveraging Hadoop's

distributed processing capabilities.

Flexibility: Hive supports various storage formats, including plain text, ORC, and
Parquet, and can integrate with other Hadoop ecosystem tools.

Ease of Use: Hive's SQL-like interface makes it easy for users who are familiar with
SQL to query and analyze large datasets in Hadoop.

Data Management: Hive provides data management capabilities, such as table

creation, data partitioning, and data summarization, making it easier to organize
and analyze large datasets.

Integration with Hadoop Ecosystem: Hive integrates seamlessly with other

Hadoop ecosystem tools, such as HDFS, MapReduce, and Spark, enabling a unified
big data analytics platform.

Use Cases of Hive

Data Warehousing: Hive can be used to create and manage data warehouses for
storing and analyzing large datasets.

ETL (Extract, Transform, Load): Hive can be used to extract data from various
sources, transform it into a suitable format, and load it into Hadoop for further
analysis.

Ad-hoc Analysis: Hive allows users to perform ad-hoc analysis on large datasets
using SQL-like queries.

Reporting: Hive can be used to generate reports and visualizations based on large
datasets.

Overall, Hive is a valuable tool for big data analytics, providing a user-friendly SQL
interface for querying and analyzing large datasets stored in Hadoop.

Hive Shell
What is Hive Shell?

Hive Shell, also known as the Hive CLI (command-line interface), is a primary way
to interact with Apache Hive. It provides a text-based interface for executing
HiveQL statements, managing Hive metadata, and interacting with HiveServer2.
Hive Shell is a legacy tool that was introduced in the early versions of Hive and is
still supported in newer versions. However, it is recommended to use Beeline, a
more modern and feature-rich CLI for Hive, for most use cases.

Features of Hive Shell

Interactive mode: Hive Shell allows users to interactively execute HiveQL
statements and view the results immediately.

Batch mode: Hive Shell can also be used to execute HiveQL statements from
scripts or files.

Metadata management: Hive Shell provides commands for managing Hive

metadata, such as creating, dropping, and altering tables and databases.

HiveServer2 interaction: Hive Shell can be used to connect to and interact with
HiveServer2, a remote Hive service that allows clients to execute HiveQL
statements and manage metadata.

Benefits of Using Hive Shell

Simplicity: Hive Shell is a simple and easy-to-use tool for executing HiveQL
statements and managing Hive metadata.

Flexibility: Hive Shell can be used in both interactive and batch modes, making it
suitable for a variety of use cases.

Maturity: Hive Shell is a mature tool that has been well-tested and is supported
by the Apache Hive community.

Hive Services
Apache Hive offers a comprehensive suite of services to facilitate data
warehousing and analytics in the Hadoop ecosystem. These services work
together to provide a seamless and efficient way to query, manage, and analyze
large datasets stored in distributed storage systems.

Core Hive Services

Hive Metastore: The Hive Metastore is a central repository that stores metadata
about Hive objects, such as tables, partitions, and UDFs (user-defined functions).
This metadata is essential for Hive to function properly and is used by various
Hive components, including the Hive Shell, HiveServer2, and Hive Web UI.

Hive Driver: The Hive Driver is the component responsible for receiving HiveQL
statements from clients and translating them into MapReduce jobs. It parses the
HiveQL statement, checks syntax and access permissions, and generates the
appropriate MapReduce job configuration.

Hive Compiler: The Hive Compiler transforms the HiveQL statement into an
execution plan, which is a series of MapReduce tasks that represent the steps
required to execute the query. The compiler optimizes the execution plan to
improve performance and takes into account factors such as data partitioning and
table schema.

Hive Execution Engine: The Hive Execution Engine executes the MapReduce jobs
generated by the Hive Compiler. It interacts with Hadoop to submit the jobs,
monitor their execution, and collect the results. The execution engine handles
various aspects of job execution, such as handling failures and managing task
dependencies.

Hive Server: Hive Server provides a remote service for clients to execute HiveQL
statements and manage Hive metadata. It acts as an intermediary between Hive
clients and the Hive Metastore, Hive Driver, and Hive Compiler. Clients can
connect to Hive Server using various protocols, such as JDBC/ODBC or Thrift.

Hive Web UI: The Hive Web UI provides a web-based interface for interacting
with Hive. It allows users to submit HiveQL statements, view query results,
browse Hive metadata, and manage Hive objects. The Hive Web UI is a
convenient tool for users who prefer a graphical interface.

Hive Meta Store

Hive Metastore is a crucial component of the Apache Hive data warehouse
system. It serves as a central repository for storing metadata about Hive objects,
such as tables, partitions, and user-defined functions (UDFs). This metadata is
essential for Hive to function properly and is used by various Hive components,
including the Hive Shell, HiveServer2, and Hive Web UI.

Core Functions of Hive Metastore

Metadata Storage: The Hive Metastore stores metadata about Hive objects,
including table names, column names, data types, partitions, and UDFs.

Metadata Access: The Hive Metastore provides an interface for Hive components
and clients to access and retrieve metadata about Hive objects. This allows clients
to query and analyze data without having to know the details of the underlying
storage format or schema.

Metadata Management: The Hive Metastore provides operations for managing

Hive metadata, such as creating, dropping, altering, and querying tables,
partitions, and UDFs. This allows users to maintain the integrity and consistency
of Hive metadata.

Transaction Management: The Hive Metastore supports ACID (Atomicity,

Consistency, Isolation, Durability) transactions to ensure the integrity of metadata
updates. This is crucial for maintaining the consistency of Hive metadata across
multiple Hive clients and operations.

Security: The Hive Metastore supports authentication and authorization

mechanisms to control access to metadata. This ensures that only authorized
users can create, modify, or delete Hive objects.

Traditional databases V/S Hive

HiveQL
HiveQL (Hive Query Language) is a dialect of SQL used to query and manage data
stored in Apache Hive, a data warehouse system built on top of Apache Hadoop.
HiveQL is similar to standard SQL, but it also includes extensions to handle the
unique characteristics of Hive, such as its support for distributed storage and its
ability to process large datasets.

Key Features of HiveQL

 SQL-like syntax: HiveQL uses a syntax that is very similar to standard SQL,
making it easy for users familiar with SQL to learn and use.
 Data definition statements (DDL): HiveQL supports DDL statements for
creating, dropping, and altering Hive objects, such as tables, partitions, and
UDFs (user-defined functions).
 Data manipulation statements (DML): HiveQL supports DML statements
for querying, inserting, updating, and deleting data in Hive tables.
  Data control language (DCL): HiveQL supports DCL statements for granting
and revoking access privileges to Hive objects.
 Support for distributed data: HiveQL can handle data stored in a
distributed manner across multiple nodes, making it suitable for querying
and analyzing large datasets.
 Support for custom data types and UDFs: HiveQL allows users to define
their own custom data types and UDFs, providing flexibility for data
analysis.

Common HiveQL Statements

 SELECT: Used to retrieve data from Hive tables.

 WHERE: Filters the rows to be retrieved based on certain conditions.
 GROUP BY: Groups the retrieved rows based on specified columns
 HAVING: Filters the groups of rows based on certain conditions
 ORDER BY: Sorts the retrieved rows based on specified columns.
 INSERT: Inserts data into a Hive table.
 UPDATE: Updates existing data in a Hive table.
 DELETE: Removes rows from a Hive table.
 CREATE TABLE: Creates a new Hive table.
 ALTER TABLE: Modifies an existing Hive table.
 DROP TABLE: Drops an existing Hive table.

Hive Tables
Hive tables are the fundamental data storage units in Apache Hive, a data
warehouse system built on top of Apache Hadoop. Hive tables are used to
organize and store large datasets in a structured manner, making them suitable
for querying and analyzing data using HiveQL, a dialect of SQL.

Types of Hive Tables

 Managed Tables: Managed tables are the default type of Hive tables. Hive
manages the metadata and data files associated with these tables.
 External Tables: External tables reference data files that are stored outside
of Hive's control. Hive only manages the metadata associated with these
tables.
 Index Tables: Index tables provide faster access to data by maintaining
indexes for specific columns.
 Materialized Views: Materialized views are pre-computed summaries of
data from other tables, enabling faster querying and analysis.
 Virtual Views: Virtual views are dynamically generated views that do not
store their own data but instead refer to the data from other tables.

Benefits of Using Hive Tables

 Structured Data Organization:
 ScalabilityQuery
 Performance Optimization
 Integration with HiveQL:
 Integration with Hadoop Ecosystem:

Querying Data and User Defined Functions

Querying data in Hive is primarily done using HiveQL (Hive Query Language), a
dialect of SQL that is specifically designed for interacting with Hive data stored in
distributed storage systems like HDFS (Hadoop Distributed File System). HiveQL
provides a familiar and powerful way to retrieve, manipulate, and analyze data
stored in Hive tables.
A basic HiveQL query typically consists of the following clauses:

 SELECT: Specifies the columns to be retrieved from the table(s).

 FROM: Identifies the table(s) from which data should be retrieved.
 WHERE: Filters the rows to be retrieved based on specific conditions.
 ORDER BY: Sorts the retrieved rows based on specified columns.
 LIMIT: Limits the number of rows to be retrieved.
Example

User-Defined Functions (UDFs) in Hive

Hive provides a mechanism for users to define their own custom functions, known
as UDFs (user-defined functions). UDFs extend the capabilities of HiveQL by
allowing users to implement complex logic or specialized operations that are not
built-in to HiveQL.

Creating and Using UDFs

UDFs can be written in various programming languages, such as Java, Python, or

Scala. The UDF code is compiled and packaged into a JAR file that is then
registered with Hive. Once registered, the UDF can be used in HiveQL queries like
any other built-in function.

Example
Benefits of Using UDFs

 Extensibility: UDFs allow users to extend HiveQL's capabilities to

implement complex logic or specialized operations.

 Reusability: UDFs can be written once and reused in multiple queries and
applications.

 Encapsulation: UDFs encapsulate complex logic, making queries more

concise and easier to understand.