# Apache Hive

Apache Hive brought SQL to Hadoop and its metastore became the standard catalogue for big data tables. Learn HiveQL, partitions and Hive's lasting role.

## Overview

Apache Hive is open-source data warehouse software that lets you query files in distributed storage with SQL. You define tables over folders of files in HDFS or object storage, then write queries in HiveQL, a dialect close to standard SQL. Hive turns each query into distributed jobs that run across a cluster.

Hive has two lasting contributions. The first is SQL on the data lake: analysts could use a language they already knew instead of writing Java MapReduce programs. The second is the Hive Metastore, a service that records table names, columns, types, partitions and file locations. Many other engines, including [Apache Spark](/tools/apache-spark), [Trino](/tools/trino) and Apache Flink, can read table definitions from it, so it became a shared catalogue for the whole ecosystem.

An everyday picture helps. A data lake without Hive is a warehouse full of unlabelled boxes. Hive adds a library card index: each card names a table, lists what is inside and says which shelves hold the boxes. You ask for "sales, March 2026" and the system walks only to the right shelves.

## What it is

| Part | Role |
|---|---|
| HiveQL | SQL dialect for creating tables and querying them |
| Hive Metastore (HMS) | Service that stores table schemas, partitions and storage locations, backed by a relational database |
| HiveServer2 | Server that accepts client connections over JDBC and ODBC and runs queries |
| Beeline | Command-line client that connects to HiveServer2 |
| Compiler and optimiser | Parses queries and produces an execution plan, with cost-based optimisation through Apache Calcite |
| Execution engine | Runs the plan; Apache Tez is the main engine in current releases |
| SerDe | Serialiser and deserialiser that tells Hive how to read and write a file format |
| Storage formats | ORC, Parquet, Avro, text and others |

Hive is not a database that owns its storage in the usual sense. The data stays as files. Hive owns the definitions and the query processing. That split is why other engines can share the same tables.

## Where it came from

Hive was built at Facebook, which needed a way for many employees to analyse fast-growing data in Hadoop without writing MapReduce code. Facebook contributed it to Apache as a Hadoop subproject in 2008, and it became a top-level Apache project in 2010. HCatalog, a layer that exposed Hive tables to other tools, was merged into Hive in March 2013.

Over the following decade Hive gained the ORC columnar format, the Tez execution engine, cost-based optimisation, LLAP for low-latency caching and full ACID transactions on managed tables. Hive 4.0.0 was released in March 2024. It deprecated Hive on MapReduce, removed Hive on Spark, deprecated the old Hive CLI and added deep [Apache Iceberg](/tools/apache-iceberg) integration with inserts, updates, deletes, merges, snapshots, branches and tags. Hive 4.1.0 followed in July 2025 with JDK 17 support and the Metastore shipped as a standalone component. Hive 4.2.0 in November 2025 requires JDK 21, and the 4.2.1 bug-fix release came in August 2026. The 1.x, 2.x and 3.x lines have been declared end of life.

## How it works

### Defining tables

You create a table with a schema and a location. A managed table is owned by Hive: dropping it deletes the data. An external table only points at files: dropping it removes the definition and leaves the files alone. Tables can be partitioned by one or more columns. Each partition maps to a sub-folder, such as `.../sales/dt=2026-03-01/`. The Metastore records every partition and its location.

### Running a query

1. A client sends HiveQL to HiveServer2 over JDBC or ODBC.
2. The compiler parses the query and asks the Metastore for the table's schema, partitions and file locations.
3. The optimiser rewrites the plan. It prunes partitions that the `WHERE` clause excludes, pushes filters down to the file readers and picks join strategies using statistics.
4. The plan becomes a directed acyclic graph of tasks that runs on Tez over YARN.
5. Readers use the SerDe for the file format. Columnar formats such as ORC and [Apache Parquet](/tools/apache-parquet) let them read only the needed columns and skip blocks using stored statistics.
6. Results return to the client.

### Transactions

Hive supports ACID tables, usually stored as ORC. Updates and deletes are written as delta files next to the base files. Background compaction later merges deltas into new base files so reads stay fast. With Hive 4, Iceberg tables offer another route to transactions, with snapshots managed by the Iceberg format.

```sql
CREATE EXTERNAL TABLE page_views (user_id BIGINT, url STRING, ms INT)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION 's3a://lake/page_views/';
```

## Key concepts and terms

| Term | Meaning |
|---|---|
| HiveQL | Hive's SQL dialect |
| Metastore | Catalogue service holding schemas, partitions and locations |
| Managed table | Table whose data Hive owns and deletes on drop |
| External table | Table that only points at existing files |
| Partition | Sub-folder of a table defined by column values |
| Partition pruning | Skipping partitions that a filter rules out |
| Bucketing | Hashing rows into a fixed number of files by a column |
| SerDe | Component that reads and writes a file format |
| ORC | Optimised Row Columnar, a columnar format closely tied to Hive |
| Tez | DAG-based execution engine used by Hive |
| LLAP | Long-lived daemons that cache data for faster queries |
| Compaction | Merging delta files into base files for ACID tables |

## A worked example

This example is illustrative. An online shop writes clickstream events to object storage, about 30 GB of Parquet per day, in folders named by date. It has kept two years, roughly 730 partitions and 22 TB.

1. The data team creates an external table partitioned by `dt` and registers existing folders with `MSCK REPAIR TABLE`, which adds missing partitions to the Metastore.
2. An analyst asks for last week's sessions by country. The filter on `dt` lets Hive prune 723 of 730 partitions, so it scans about 210 GB rather than 22 TB.
3. Because Parquet is columnar and the query needs only three of 40 columns, the readers fetch a small part of those 210 GB.
4. The same table definition is visible to Spark and Trino through the shared Metastore. A data scientist reads it in Spark with no extra setup.
5. Each night an ingestion job adds the new partition with `ALTER TABLE ... ADD PARTITION`, so queries see new data immediately.

## Strengths and limitations

**Strengths:**

- Familiar SQL for very large data sets.
- The Metastore is a widely supported catalogue shared by many engines.
- Partition pruning and columnar formats cut the data scanned.
- Works with files in open formats, so data is not locked in.
- Mature features: ACID tables, cost-based optimisation and Iceberg support.

**Limitations:**

- Query start-up and latency are higher than in interactive engines.
- Running Hive well needs YARN, Tez, a Metastore database and tuning.
- Folder-based partitions do not scale well to very many partitions, and listing files in object storage is slow.
- Classic Hive tables lack the snapshot isolation, time travel and safe schema evolution of modern table formats.
- ACID tables need compaction to stay healthy.

## How it compares with alternatives

| Tool | Approach | Typical use |
|---|---|---|
| Apache Hive | SQL compiled into Tez jobs; owns the Metastore | Large batch ETL and a shared catalogue |
| [Apache Spark](/tools/apache-spark) SQL | General engine with SQL and DataFrames, can use the Metastore | ETL, ML and mixed workloads |
| [Trino](/tools/trino) | Distributed in-memory SQL engine over many sources | Interactive queries on the lake |
| [Amazon Athena](/tools/amazon-athena) | Serverless SQL on S3 using a Hive-compatible catalogue | Ad hoc queries without clusters |
| [AWS Glue Data Catalog](/tools/aws-glue-data-catalog) | Managed catalogue compatible with the Hive Metastore API | Replacing a self-run Metastore on AWS |

## Practical guidance and best practices

1. Partition by the columns most queries filter on, usually a date, and keep partition counts moderate.
2. Store data in ORC or Parquet rather than text or CSV.
3. Collect table and column statistics so the cost-based optimiser can choose good plans.
4. Use external tables for data that other systems also write, so a drop does not delete shared files.
5. Run the Metastore on a reliable, backed-up relational database; it is a critical shared service.
6. For new tables that need updates, schema evolution or time travel, consider Iceberg tables, which Hive 4 supports.
7. Use Tez rather than MapReduce, which is deprecated in Hive 4.
8. Watch for small files and merge them during ingestion.

## Common mistakes

- Partitioning on a high-cardinality column such as user ID, creating millions of tiny partitions.
- Forgetting to register new partitions, so fresh data seems missing.
- Dropping a managed table and deleting data that other teams relied on.
- Querying without a partition filter and scanning the whole table.
- Expecting interactive dashboard latency from batch Hive queries.
- Neglecting compaction on ACID tables until reads slow down.

## Frequently asked questions

### Is Hive a database?

Not in the traditional sense. Hive stores table definitions and runs queries, but the data stays as files in HDFS or object storage. It behaves like a data warehouse layer over a data lake.

### What is the Hive Metastore used for today?

It acts as a catalogue that maps table names to schemas, partitions and file locations. Spark, Trino, Flink and other engines can use it, and managed services such as the AWS Glue Data Catalog offer a compatible interface. Iceberg can also use it as a catalogue.

### Does Hive still use MapReduce?

Hive on MapReduce is deprecated in Hive 4, and Hive on Spark was removed. Tez is the main execution engine in current releases.

### What is the difference between Hive and Spark SQL?

Hive is a SQL warehouse system with its own server, compiler and catalogue. Spark SQL is part of a general processing engine that also offers DataFrames, streaming and machine learning. Spark can read and write Hive tables through the Metastore, so the two often coexist.

### Does Hive support updates and deletes?

Yes, on ACID managed tables, usually in ORC, and on Iceberg tables in Hive 4. Plain external tables over files do not support row-level updates.

### Should I start a new project on Hive?

It depends. If you already run Hadoop, Hive remains a reasonable batch SQL layer. For new cloud projects, teams more often choose a lakehouse with open table formats and engines such as Spark or Trino, while still using a Hive-compatible catalogue.

## Glossary

- Beeline: command-line client for HiveServer2.
- HiveServer2: the service that receives client queries over JDBC or ODBC.
- HCatalog: table-access layer merged into Hive in 2013.
- Delta file: file holding inserted, updated or deleted rows for an ACID table.
- Predicate pushdown: passing filters down to file readers so they skip data.
- Cost-based optimiser: a planner that chooses plans using statistics.
- Schema on read: applying a table's schema when files are read rather than when they are written.
- DAG: directed acyclic graph of processing steps.

## Summary

Apache Hive made the data lake usable by people who know SQL. It maps tables onto files, plans queries that read only the needed partitions and columns, and runs them as distributed jobs, now mainly on Tez. Its Metastore became the common catalogue that lets many engines share table definitions, and that role outlived Hive's place as the main query engine. Hive 4 modernised the project with Iceberg support and dropped old execution paths. For new designs, Hive's ideas usually appear inside a [lakehouse](/tools/lakehouse) built on [Apache Iceberg](/tools/apache-iceberg) or similar formats, queried by engines such as [Trino](/tools/trino) and [Apache Spark](/tools/apache-spark).