Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 47 ยท SQL and NoSQL on Hadoop

Hive: SQL over Files

Learn how Hive puts SQL on top of files in HDFS: the metastore, managed and external tables, HiveQL, SerDes and how a query is compiled into distributed stages.

Writing a MapReduce job in Java for every question is slow: a simple GROUP BY takes dozens of lines. Most analysts already know SQL. Apache Hive closes that gap. It lets you describe files in HDFS (or an object store) as tables and query them with a SQL dialect called HiveQL. Hive turns each query into a plan of distributed stages, runs it on the cluster and writes the result.

The key idea to remember: Hive is not a database that stores data. It is a metadata catalog plus a query compiler on top of files. The files stay where they are, in the formats you chose in Parquet and ORC Internals.

Hive needs a cluster, so every HiveQL statement and command in this lesson is read-only. The runnable examples use Python and an in-memory SQLite database as a stand-in: SQLite is not Hive, but it lets you run a similar SQL query and see how a query decomposes into scan, filter, group and sort stages, as Hive would plan them.

The parts of Hive

Hive in four pieces

Metastore A database of table names, columns and file locations.
Compiler Parses SQL and builds a plan.
Engine Runs the plan on the cluster (Tez, or MapReduce).
HiveServer2 Accepts JDBC and ODBC connections.

Data stays in HDFS or object storage, never inside Hive.

1 / 2

The metastore is a small relational database (MySQL or PostgreSQL in production) that remembers, for each table: its columns and types, the directory that holds its files, the file format, the partitions and statistics. Because many tools use it, the Hive Metastore became the standard catalog of the Hadoop world: Spark, Trino, Impala and Presto read the same table definitions.

Databases: organizing tables

A Hive database (also called a schema; the two words mean the same thing in Hive) is a namespace that groups tables, like a folder. Without one, every table lands in the built-in default database. Separate databases per team or per project avoid name clashes and let you set access rules and locations per group.

SQL
CREATE DATABASE IF NOT EXISTS sales
  COMMENT 'Orders and customers for the sales team'
  LOCATION '/warehouse/sales.db'
  WITH DBPROPERTIES ('owner' = 'sales-team', 'created' = '2025-01-15');

SHOW DATABASES;
SHOW DATABASES LIKE 'sal*';
DESCRIBE DATABASE EXTENDED sales;       -- comment, location, owner, properties
USE sales;                               -- later table names are looked up here
SELECT current_database();
SHOW TABLES;
SHOW TABLES IN sales LIKE 'ord*';
  • LOCATION is the HDFS (or S3) directory for the database's managed tables. If you leave it out, Hive uses <warehouse dir>/<name>.db, where the warehouse directory is hive.metastore.warehouse.dir (default /user/hive/warehouse). Each managed table then gets a subdirectory.
  • COMMENT and DBPROPERTIES are free-text and key-value metadata kept in the metastore; they are documentation, not behavior.
  • ALTER DATABASE sales SET DBPROPERTIES ('owner' = 'bi-team') changes the properties; ALTER DATABASE sales SET OWNER USER maria changes the owner. You cannot rename a database in a simple way, so choose names carefully.
  • You can always name a table fully, sales.orders, without USE, which is clearer in scripts.

Dropping needs care:

SQL
DROP DATABASE sales;                    -- fails if the database still contains tables (RESTRICT is the default)
DROP DATABASE sales CASCADE;            -- drops the tables too

CASCADE removes the tables first. For managed tables that deletes their data files from HDFS as well, while external tables lose only their definition (see the next section). Because the metastore and the file system disagree about ownership here, treat DROP DATABASE ... CASCADE as dangerous and make sure what is inside before running it.

Python How databases map to warehouse directories

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Creating tables over files

Suppose a directory /data/books/ contains tab-separated text files. You declare a table over it:

SQL
CREATE EXTERNAL TABLE books (
  id      INT,
  title   STRING,
  author  STRING,
  year    INT,
  price   DECIMAL(6,2)
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/data/books/';

Nothing is copied or converted. Hive records the definition in the metastore. When you query the table, Hive reads whatever files sit in that directory and interprets each line using the declared columns. This is schema on read: the schema is applied when you read, and a malformed line yields NULL in the affected column instead of failing the load.

Managed versus external tables

Managed (internal) table External table
Data location Hive's warehouse directory (/warehouse/tablespace/managed/...) Any path you give with LOCATION
Who owns the files Hive You, or another system
DROP TABLE Deletes metadata and files Deletes metadata only; files stay
ACID transactions Yes (ORC) No
Typical use Tables Hive creates and controls Raw data landed by ingestion tools, shared tables

The safe habit: use external tables for raw and shared data, so a mistaken DROP TABLE cannot delete the only copy. Use managed tables for tables Hive fully owns, such as ACID tables.

SerDes and formats

A SerDe (serializer/deserializer) tells Hive how to turn bytes into columns and back. The STORED AS clause picks a ready-made combination:

SQL
STORED AS PARQUET        -- or ORC, AVRO, TEXTFILE, SEQUENCEFILE

For text you can pick a SerDe explicitly, for example one for CSV or JSON:

SQL
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "\"")

Plain text tables are fine for landing raw data. For anything you query often, convert to ORC or Parquet with a one-line statement:

SQL
CREATE TABLE books_orc STORED AS ORC AS SELECT * FROM books;

HiveQL in practice

HiveQL looks like SQL, so a lot is familiar. These are all read-only examples:

SQL
SELECT author, COUNT(*) AS titles, ROUND(AVG(price), 2) AS avg_price
FROM books
WHERE year >= 2000
GROUP BY author
HAVING COUNT(*) >= 2
ORDER BY titles DESC
LIMIT 10;

Differences worth knowing:

  • Partitions: PARTITIONED BY (dt STRING) plus INSERT OVERWRITE TABLE t PARTITION (dt='2026-03-01') SELECT ... (Partitioning, Bucketing and File Layout).
  • Complex types: ARRAY, MAP, STRUCT columns, and LATERAL VIEW explode(col) to unnest them.
  • Load commands: LOAD DATA INPATH '/staging/books.tsv' INTO TABLE books; moves a file into the table's directory.
  • Latency: even a trivial query takes seconds, because a distributed job must start. Hive is built for large scans, not for single-row lookups.
  • Updates: only transactional (ACID) tables support UPDATE, DELETE and MERGE (see Hive Performance).
  • Older Hive versions had restricted syntax for subqueries and joins; modern ones (3 and 4) cover most of standard SQL, including window functions and CTEs.

From SQL to distributed stages

When you submit a query, Hive does not run SQL directly. It:

  1. Parses the text into a syntax tree and checks table and column names against the metastore.
  2. Builds a logical plan (scan, filter, aggregate, sort) and optimizes it: pushing filters down, pruning partitions and columns, reordering joins.
  3. Compiles it into a physical plan: a DAG of stages that run as tasks on the cluster.

You can see the plan with EXPLAIN:

SQL
EXPLAIN
SELECT author, COUNT(*) FROM books WHERE year >= 2000 GROUP BY author;

The output (shortened) describes a map stage that scans books, filters year >= 2000 and starts a partial aggregation, then a reduce stage that merges the partial counts per author. That is exactly the map -> shuffle -> reduce shape from the MapReduce model: GROUP BY key is the shuffle key, and the "partial aggregation" is a combiner. A JOIN becomes a shuffle on the join key and an ORDER BY becomes a single final reducer (so prefer LIMIT with it, or SORT BY for per-reducer sorting).

Here is the same decomposition done by hand, with Python for the scan-and-map half and plain SQL as a check.

Python A GROUP BY as map, shuffle and reduce

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The hand-written stages and the SQL give identical answers, which is the point: Hive's job is to produce the stages from the SQL text and to spread them over many machines.

Seeing the plan

SQLite can also describe how it executes a query. This is a different engine with different plans, but the habit of reading a plan is the same one you will use with Hive's EXPLAIN.

Python Reading a query plan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first part is real SQLite output; the last list is our own simplified description of Hive's likely stages, not output from Hive.

When to use Hive

Hive is a good fit for batch SQL over very large datasets, ETL (turning raw files into ORC or Parquet tables), and as the table catalog shared by other engines. It is a poor fit for low-latency dashboards (use an engine like Impala, Trino or a warehouse), row-level updates at high rates and online serving. The next lesson, Hive Performance, shows how Hive got faster by replacing MapReduce with Tez and tuning joins.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the Hive metastore store?

    The metastore is a catalog. The data stays in HDFS or object storage as ordinary files.
  2. 2. What happens to the data files when you DROP an external table?

    For an external table Hive does not own the files, so dropping removes only the definition. Dropping a managed table deletes the data too.
  3. 3. What does 'schema on read' mean in Hive?

    Hive applies the declared columns when reading, so bad lines may show up as NULL rather than being rejected at load time.
  4. 4. Which part of a Hive GROUP BY query corresponds to the MapReduce shuffle key?

    Rows with equal GROUP BY values must meet at the same reducer, so the grouping column becomes the shuffle key.

Summary

  • Hive is a SQL layer: a metastore catalog plus a compiler over files that live in HDFS or object storage.
  • External tables leave data alone on DROP; managed tables own and delete it.
  • A SerDe and STORED AS define how bytes become columns; use ORC or Parquet for tables you query often.
  • HiveQL is mostly standard SQL with partitions, complex types and batch-style latency.
  • A query is parsed, optimized and compiled into a DAG of map and reduce style stages; EXPLAIN shows it.

Next: Hive Performance: Execution Engines and Tuning.