Hive architecture and HiveQL

Hadoop Tutorial for Beginnerseducaterz · Hive, HQL vs SQL, MetastoreArchitecture of Hive, Components of Hive: Hive shell, HiveQLTech Guide VB · Hive architecture and componentsWhat is Hive and HiveQL?COSO IT · What Hive and HiveQL are

Why Hive existsSearch on YouTubeBookmarkSaved

Hadoop and HDFS could store huge datasets. The catch: the way to ask a question of that data was a hand-written MapReduce job in Java. A task as small as "count rows by category" needed a full Mapper class, a Reducer class, a Driver, and job config code.

That put analysis out of reach for analysts who knew SQL but not distributed Java. Companies like Facebook needed a bridge. Hive is that bridge: SQL-literate people query petabyte-scale data in HDFS without writing MapReduce first.

The same question, two ways ("total sales by region"):

SELECT region, SUM(amount) AS total
FROM sales.orders
GROUP BY region;

Three lines of HiveQL replace 100+ lines of Java. Anyone who knows SQL can read and change it at once.

Where Hive came fromSearch on YouTubeBookmarkSaved

Facebook built Hive out of need. Its data grew fast. It rose from tens of gigabytes a day in 2006 to about 1 terabyte a day in 2007. Within a few more years it hit roughly 15 terabytes a day. The data sat in Hadoop. The analysts knew SQL, not Java.

Hive architectureSearch on YouTubeBookmarkSaved

A query passes through fixed layers. Each layer has one job.

Hive Client (top layer). This is how a user or program connects:

• Thrift Server. Hive is built on Apache Thrift, a cross-language RPC framework. Clients in Python, PHP, or C++ connect, not only Java clients.
• JDBC Driver. Java applications connect through the standard java.sql interface, like any relational database.
• ODBC Driver. BI and reporting tools (Tableau, Power BI) connect through the ODBC standard.

Hive Services. These entry points receive the query:

• Hive Web UI. A browser interface for queries without a terminal.
• CLI. The command-line shell. It is the Hive equivalent of the Pig Grunt shell.
• HiveServer2. Listens for remote JDBC, ODBC, and Thrift connections. It serves many users at once. This makes Hive a shared warehouse, not a single-user tool.

Hive Driver. Every query ends up here, whatever client sent it. The Driver manages the full query lifecycle. It receives the HiveQL statement. It parses, compiles, and optimizes it into an execution plan. It talks to the Metastore first, because it must know table schemas, column types, and partition locations before it compiles.

Metastore. The catalog, not the data. It stores table names, column names and types, partition info, and the HDFS paths of each table's files.

Execution engine and HDFS. The compiled plan runs here. In the classic design this box is MapReduce; in a modern deployment it is often Tez or Spark. The role stays the same: the engine that crunches the data. HDFS holds the real files. The engine reads from HDFS and writes back to it.

The slide lists the run as numbered steps:

1. Send the query from the CLI, Web UI, or a remote client to the driver.
2. Get the plan: the driver opens a session handle and passes the query to the compiler.
3. Get metadata: the compiler asks the Metastore for table and column details.
4. Send metadata: the Metastore answers the compiler.
5. Send plan: the compiler returns the execution plan to the driver.
6. Execute plan: the driver sends the plan to the execution engine, which runs the job and touches HDFS files.
7. Fetch results: the driver fetches results from the execution engine.
8. Send results: the driver sends results to the user interface.

MetastoreSearch on YouTubeBookmarkSaved

The Metastore is a central repository. It stores structure info for every table and Hive partition in the warehouse. That means column names and types used to read and write data, plus the HDFS files where the data lives. It never stores the data itself.

One shared catalog serves many tools. Other engines can read the same table definitions.

Key characteristicsSearch on YouTubeBookmarkSaved

Schema-on-read has one sharp edge: LOAD DATA never validates column types. Bad rows surface at query time.

Primitive data typesSearch on YouTubeBookmarkSaved

Complex data typesSearch on YouTubeBookmarkSaved

One column can hold nested data. This fits JSON and log sources that are not flat.

Access syntax mirrors Pig: tags[0] for an ARRAY, attributes['color'] for a MAP, dimensions.length for a STRUCT with dot notation.

CREATE TABLE sales.product_catalog (
  product_id INT, sku SMALLINT, total_sold BIGINT,
  unit_price FLOAT, revenue DOUBLE, margin_pct DECIMAL(5,2),
  launch_date DATE, last_updated TIMESTAMP,
  product_name STRING, category_code VARCHAR(10),
  warehouse_zone CHAR(3), is_active BOOLEAN,
  tags ARRAY<STRING>,
  attributes MAP<STRING,STRING>,
  dimensions STRUCT<length:FLOAT,width:FLOAT,height:FLOAT>)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;

INSERT INTO sales.product_catalog VALUES (
  501, 12, 3400, 799.99, 2719966.00, 18.50,
  '2025-01-15', '2026-08-20 10:15:00',
  'Wireless Mouse', 'ELEC', 'A12', true,
  array('electronics','accessory'),
  map('color','black','brand','TechCo'),
  named_struct('length',10.5,'width',6.2,'height',3.1));

SELECT product_name,
  tags[0] AS first_tag,
  attributes['color'] AS color,
  dimensions.length AS length_cm
FROM sales.product_catalog;

Wireless Mouse  electronics  black  10.5

File formatsSearch on YouTubeBookmarkSaved

STORED AS in CREATE TABLE controls the physical layout. The choice changes speed, size, and features such as ACID.

CREATE TABLE orders (order_id INT, customer_id INT, amount DOUBLE)
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

Databases in HiveSearch on YouTubeBookmarkSaved

DATABASE and SCHEMA are interchangeable. CREATE SCHEMA and CREATE DATABASE do the same thing.

CREATE DATABASE IF NOT EXISTS sales
  COMMENT 'Sales warehouse'
  WITH DBPROPERTIES ('owner'='analytics-team');
SHOW DATABASES;
USE sales;
DESCRIBE DATABASE EXTENDED sales;
ALTER DATABASE sales SET DBPROPERTIES
  ('reviewed'='2026-08');
DROP DATABASE IF EXISTS sales CASCADE;

Three rules from the slides:

• DROP DATABASE fails on a non-empty database unless CASCADE is added. CASCADE drops every table inside too.
• A database LOCATION change affects new tables created after the change. It never moves existing data.
• Every table belongs to exactly one database. default is a database named default.

CREATE DATABASE IF NOT EXISTS sales
  COMMENT 'Sales analytics warehouse'
  WITH DBPROPERTIES ('owner'='cse412-batch2026');

SHOW DATABASES;

default
sales

USE sales;
DESCRIBE DATABASE EXTENDED sales;

sales  hdfs://localhost:9000/user/hive/warehouse/sales.db
cse412-student  USER  {owner=cse412-batch2026}

ALTER DATABASE sales SET DBPROPERTIES ('reviewed'='2026-08-24');

HiveQL categoriesSearch on YouTubeBookmarkSaved

HiveQL looks like SQL. Hive is a warehouse over files, not a transactional database. That shapes each category:

Creating and changing tablesSearch on YouTubeBookmarkSaved

CREATE TABLE IF NOT EXISTS sales.orders (
    order_id INT, customer_id INT, amount DOUBLE, order_date DATE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC;
ALTER TABLE sales.orders ADD COLUMNS (region STRING);
ALTER TABLE sales.orders RENAME TO sales.customer_orders;
TRUNCATE TABLE sales.customer_orders;

Managed (also called internal) table. Hive owns the data lifecycle. DROP TABLE deletes both the schema and the files.

External table. Created with EXTERNAL and a LOCATION. DROP TABLE removes the metadata. The data stays on disk.

TRUNCATE TABLE. Removes all rows, keeps the definition. Works on managed tables.

1,Rajiv,2021
2,Omer,2022
3,Rajesh,2020
4,Divya,2023

101,1,450.00,2026-08-10
102,2,0.00,2026-08-10
103,1,220.50,2026-08-11
104,3,180.00,2026-08-11
105,4,999.99,2026-08-12

CREATE TABLE IF NOT EXISTS sales.orders (
  order_id INT, customer_id INT, amount DOUBLE, order_date STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
LOAD DATA LOCAL INPATH '/home/student/orders.csv'
  INTO TABLE sales.orders;

Loading data to table sales.orders
Table sales.orders stats: [numFiles=1, numRows=0, totalSize=157]

SELECT * FROM sales.orders;

101  1  450.0   2026-08-10
102  2  0.0     2026-08-10
103  1  220.5   2026-08-11
104  3  180.0   2026-08-11
105  4  999.99  2026-08-12

ALTER TABLE sales.orders ADD COLUMNS (region STRING);
SELECT order_id, region FROM sales.orders LIMIT 2;

101  NULL
102  NULL

CREATE EXTERNAL TABLE sales.orders_ext (
  order_id INT, customer_id INT, amount DOUBLE, order_date STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/user/student/external/orders';
DROP TABLE sales.orders_ext;

Partitioning and bucketingSearch on YouTubeBookmarkSaved

PARTITIONED BY splits a table into separate directories by column value. Queries that filter on the Hive partition column skip whole directories. Use columns with low-to-moderate cardinality that queries filter on often (date, region, country). Too many tiny partitions strain the Metastore.

CLUSTERED BY hashes rows into a fixed number of files by column value, within a Hive partition or across the table. Use it for high-cardinality join or sampling keys. It enables bucket map joins and TABLESAMPLE.

This lecture gives the short form. Partitioning, bucketing and joins gives the full treatment with the bucket-number rule and the join types.

CREATE TABLE sales.orders_partitioned (
  order_id INT, customer_id INT, amount DOUBLE)
PARTITIONED BY (order_date STRING)
STORED AS ORC;
INSERT OVERWRITE TABLE sales.orders_partitioned
  PARTITION (order_date='2026-08-10')
  SELECT order_id, customer_id, amount FROM sales.orders
  WHERE order_date='2026-08-10';

SHOW PARTITIONS sales.orders_partitioned;

order_date=2026-08-10
order_date=2026-08-11
order_date=2026-08-12

SELECT * FROM sales.orders_partitioned
WHERE order_date = '2026-08-11';

103  1  220.5  2026-08-11
104  3  180.0  2026-08-11

CREATE TABLE sales.orders_bucketed (
  order_id INT, customer_id INT, amount DOUBLE, order_date STRING)
CLUSTERED BY (customer_id) INTO 4 BUCKETS
STORED AS ORC;
INSERT OVERWRITE TABLE sales.orders_bucketed
  SELECT order_id, customer_id, amount, order_date FROM sales.orders;

Loading and inserting dataSearch on YouTubeBookmarkSaved

LOAD DATA moves or copies files as-is. No MapReduce or Tez job runs. INSERT ... SELECT always runs a query, so it can filter, join, or transform on the way in.

LOAD DATA LOCAL INPATH '/home/user/orders.csv'
  INTO TABLE sales.orders;
INSERT INTO TABLE sales.orders
  SELECT * FROM sales.orders_staging WHERE amount > 0;
INSERT OVERWRITE TABLE sales.orders PARTITION (order_date='2026-08-18')
  SELECT order_id, customer_id, amount FROM sales.orders_staging;
EXPORT TABLE sales.orders TO 'hdfs:///backup/orders';
IMPORT TABLE sales.orders FROM 'hdfs:///backup/orders';

INSERT INTO appends rows. INSERT OVERWRITE replaces a table (or Hive partition) fully. EXPORT / IMPORT move a table, schema plus data, between clusters.

ACID tables with UPDATE and DELETESearch on YouTubeBookmarkSaved

UPDATE and DELETE work on ACID (transactional) tables. An ordinary table does not support them; the statement fails. The ACID recipe: STORED AS ORC plus TBLPROPERTIES ('transactional'='true'), with CLUSTERED BY into buckets on older Hive versions.

CREATE TABLE sales.accounts (id INT, balance DOUBLE)
CLUSTERED BY (id) INTO 8 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');
UPDATE sales.accounts SET balance = balance - 100 WHERE id = 42;
DELETE FROM sales.accounts WHERE balance <= 0;

INSERT INTO TABLE sales.orders (order_id, customer_id, amount, order_date)
  VALUES (106, 2, 375.25, '2026-08-13');
SELECT * FROM sales.orders WHERE order_id = 106;

106  2  375.25  2026-08-13  NULL

CREATE TABLE sales.accounts (
  customer_id INT, balance DOUBLE)
CLUSTERED BY (customer_id) INTO 4 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');
INSERT INTO sales.accounts VALUES
  (1,1000.00), (2,500.00), (3,750.00), (4,220.00);
UPDATE sales.accounts SET balance = balance - 100
  WHERE customer_id = 1;
SELECT * FROM sales.accounts WHERE customer_id = 1;

1  900.0

UPDATE sales.orders SET amount = 0 WHERE order_id = 106;

FAILED: SemanticException [Error 10294]: Attempt to do update or delete
using transaction manager that does not support these operations.

QueriesSearch on YouTubeBookmarkSaved

Standard shape: WHERE filters rows, GROUP BY aggregates, HAVING filters the aggregates, ORDER BY sorts, LIMIT caps output.

SELECT customer_id, SUM(amount) AS total
FROM sales.orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY total DESC
LIMIT 10;

Four ways to order rows:

Join types: INNER, LEFT / RIGHT / FULL OUTER, LEFT SEMI (an EXISTS-style join), and CROSS.

SELECT customer_id, SUM(amount) AS total_spent
FROM sales.orders
WHERE amount > 0
GROUP BY customer_id
ORDER BY total_spent DESC;

4  999.99
1  670.5
2  375.25
3  180.0

SELECT o.order_id, c.name, o.amount
FROM sales.orders o
JOIN sales.customers c ON o.customer_id = c.customer_id
WHERE o.amount > 0
ORDER BY o.order_id;

101  Rajiv   450.0
103  Rajiv   220.5
104  Rajesh  180.0
105  Divya   999.99
106  Omer    375.25

SELECT customer_id, SUM(amount) AS total
FROM sales.orders
GROUP BY customer_id
HAVING SUM(amount) > 500
ORDER BY total DESC
LIMIT 2;

4  999.99
1  670.5

Views and materialized viewsSearch on YouTubeBookmarkSaved

A plain view is a saved query, not a copy of data. Each query re-runs the SELECT on current data. Plain views are read-only. They simplify complex queries and hide columns from some users.

CREATE VIEW sales.big_orders AS
  SELECT order_id, customer_id, amount
  FROM sales.orders
  WHERE amount > 1000;
DROP VIEW IF EXISTS sales.big_orders;

A materialized view stores its result physically (Hive 3.0 and later). The optimizer can rewrite a matching base-table query to use it: same answer, far less work. Stale data is the cost: inserts into the base table stay invisible until REBUILD.

CREATE MATERIALIZED VIEW sales.daily_totals AS
  SELECT order_date, SUM(amount) AS total
  FROM sales.orders GROUP BY order_date;
ALTER MATERIALIZED VIEW sales.daily_totals
  REBUILD;

CREATE VIEW sales.big_orders AS
  SELECT order_id, customer_id, amount
  FROM sales.orders
  WHERE amount > 400;
SELECT * FROM sales.big_orders;

101  1  450.0
105  4  999.99

CREATE MATERIALIZED VIEW sales.daily_totals AS
  SELECT order_date, SUM(amount) AS total
  FROM sales.orders
  GROUP BY order_date;
SELECT * FROM sales.daily_totals;

2026-08-10  450.0
2026-08-11  400.5
2026-08-12  999.99
2026-08-13  375.25

INSERT INTO TABLE sales.orders (order_id, customer_id, amount, order_date)
  VALUES (107, 3, 60.00, '2026-08-13');
SELECT * FROM sales.daily_totals WHERE order_date = '2026-08-13';

2026-08-13  375.25

ALTER MATERIALIZED VIEW sales.daily_totals REBUILD;
SELECT * FROM sales.daily_totals WHERE order_date = '2026-08-13';

2026-08-13  435.25

What replaced indexesSearch on YouTubeBookmarkSaved

Hive had CREATE INDEX before 3.0. It was removed in Hive 3.0 (tracked as HIVE-18448) for limited benefit versus maintenance cost. Current Hive has no CREATE INDEX at all. Four tools do the job now:

ANALYZE TABLE orders COMPUTE STATISTICS;

Schema design principlesSearch on YouTubeBookmarkSaved

1. Schema-on-read, not schema-on-write. Hive applies structure at query time. LOAD DATA never validates column types.
2. Pick Hive partition columns with care. Low-to-moderate cardinality, filtered often. PARTITIONED BY (order_date STRING), not customer_id.
3. Use bucketing for high-cardinality keys. Enables bucket map joins that partitioning cannot. CLUSTERED BY (customer_id) INTO 16 BUCKETS.
4. Default to ORC or Parquet. Keep TEXTFILE for raw landing data. Convert with STORED AS ORC; before serious querying.
5. Avoid the small-files problem. Many tiny files slow the NameNode and planning. INSERT OVERWRITE compacts after streaming ingestion.
6. Keep statistics current. Feeds the optimizer real numbers, not guesses. ANALYZE TABLE orders COMPUTE STATISTICS FOR COLUMNS;

Quick self-testSearch on YouTubeBookmarkSaved