Apache HIVE (HQL)
Apache HIVE
• The Apache Hive is a distributed, fault-tolerant data warehouse
system that enables analytics at a massive scale and facilitates
reading, writing, and managing petabytes of data residing in
distributed storage using SQL.
• Official Docker Image: [Link]
• A popular open-source data warehousing framework that allows users
to query large datasets stored in distributed storage systems like
Hadoop HDFS, Apache Ozone, Amazon S3, or Microsoft Azure Data
Lake Storage (ADLS).
• It provides a SQL-like (called HiveQL) interface to query, analyze, and
manage structured and semi-structured data.
• With Hive, users can define tables and schemas, write queries in a
familiar SQL-like language and perform various data analysis tasks
such as filtering, aggregating, and joining data.
• Hive supports many popular data formats including CSV, JSON, Avro,
ORC, and Parquet.
• An open-source data warehousing tool for performing distributed
processing and data analysis.
• It was developed by Facebook to reduce the work of writing the Java
MapReduce program.
• Hive translates the hive queries into MapReduce programs.
• It supports developers to perform processing and analyses on
structured and semi-structured data by replacing complex java
MapReduce programs with hive queries.
Working of Hive
Step 1: executeQuery: The user interface calls the execute interface to
the driver.
Step 2: getPlan: The driver accepts the query, creates a session handle
for the query, and passes the query to the compiler for generating the
execution plan.
Step 3: getMetaData: The compiler sends the metadata request to the
metastore.
Step 4: sendMetaData: The metastore sends the metadata to the
compiler.
Step 5: sendPlan: The compiler then sends the generated
execution plan to the driver.
Step 6: executePlan: After receiving the execution plan from
compiler, driver sends the execution plan to the execution
engine for executing the plan.
Step 7: submit job to MapReduce: The execution engine then
sends these stages of DAG to appropriate components.
Step 8,9,10: sendResult: Now for queries, the execution
engine reads the contents of the temporary files directly from
HDFS as part of a fetch call from the driver. The driver then
sends results to the Hive interface.
• The major components of Apache Hive are the Hive clients, Hive
services, Processing framework and Resource Management, and the
Distributed Storage.
• The user interacts with the Hive through the user interface by
submitting Hive queries.
• The driver passes the Hive query to the compiler. The compiler
generates the execution plan. The Execution engine executes the
plan.
Primitives
Data Type
Data Type
Description
TINYINT 1-byte signed integer
SMALLINT 2-byte signed integer
INT 4-byte signed integer
BIGINT 8-byte signed integer
FLOAT Single precision floating point
DOUBLE Double precision floating point
DECIMAL Fixed-point numbers (e.g., DECIMAL(10,2))
STRING Text string
VARCHAR Variable-length string with limit
CHAR Fixed-length string
BOOLEAN TRUE or FALSE
DATE Date value (yyyy-mm-dd)
TIMESTAMP Date and time
BINARY Binary data
Complex Data Types
Type Example Description
ARRAY ARRAY<STRING> Ordered collection
MAP MAP<STRING, INT> Key-value pairs
STRUCT<name:STRING,
STRUCT age:INT> Group of related elements
One value out of multiple
UNIONTYPE UNIONTYPE<INT, STRING> types
HQL – Hive Query Language
Commands:
1. Database Operations
2. Table Operations
3. Data Loading
4. Querying Data
5. Join Query
6. Group and Aggregate
7. Alter Table
1. Database Operations
CREATE DATABASE mydb;
USE mydb;
SHOW DATABASES;
DROP DATABASE mydb;
2. Table Operations
-- Create Table
CREATE TABLE students (
id INT,
name STRING,
marks FLOAT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',‘ STORED AS TEXTFILE;
-- View Tables
SHOW TABLES;
-- Drop Table
DROP TABLE students;
3. Data Loading
LOAD DATA LOCAL INPATH '/path/to/[Link]' INTO TABLE
students;
4. Querying Data
SELECT * FROM students;
SELECT name, marks FROM students WHERE marks > 70;
5. Join Examples
SELECT [Link], [Link], [Link]
FROM students a
JOIN courses b
ON ([Link] = b.stu_id);
6. Group and Aggregate
SELECT course, COUNT(*) FROM students GROUP BY course;
SELECT MAX(marks), MIN(marks) FROM students;
7. Alter Table
ALTER TABLE old_table_name RENAME TO new_table_name;
ALTER TABLE students ADD COLUMNS (age INT);
ALTER TABLE table_name REPLACE COLUMNS (col1 STRING,
col2 INT, col3 DATE);