Apache Hive Overview — SQL on Hadoop for Batch Analytics

Why it matters

Hive democratized big data. Before it, working with HDFS meant writing MapReduce in Java. After Hive, any analyst who knew SQL could query terabytes. That shift is what turned Hadoop from a research project into an enterprise platform.

Even today, when Spark SQL is faster and Presto is more interactive, Hive stays in production for batch ETL, historical data, and the huge ecosystem of tables and views built over years.

The architecture

Hive has four main components. The metastore holds table schemas, partition information, and location pointers. The driver parses SQL and coordinates execution. The compiler turns SQL into a logical plan and then a physical plan targeting an execution engine. The execution engine (MR, Tez, or Spark) actually runs the work.

Data lives in HDFS as files (Parquet, ORC, text, sequence). Hive tables are metadata that describes how to interpret those files as rows and columns.

How it works end to end

A query starts at the driver, which parses SQL and asks the metastore for schema and partition info. The compiler builds a logical plan, applies the cost-based optimizer, and produces a physical plan. The execution engine runs the plan as jobs — MapReduce tasks classically, Tez DAG in modern deployments, or Spark stages when Hive-on-Spark is enabled.

Results are either returned to the client (small queries) or written to HDFS (large queries). Sessions can maintain state via HiveServer2, which is the thin server that accepts JDBC/ODBC connections.

Schema on read - what a Hive table actually is

A Hive table is not a storage engine. It is a row in the metastore saying: these files live at this path, read them with this SerDe and InputFormat, and call the resulting columns these names and types. Nothing validates data when it lands - validation happens when a query reads a row. That is schema-on-read, and almost everything else about Hive follows from it.

The upside is that loading is nearly free: LOAD DATA just moves files, and ALTER TABLE ... ADD COLUMNS rewrites nothing, because the reader returns NULL for a column older files lack.

The downside is that every data-quality failure is deferred to query time and is usually silent. A malformed integer raises no error - the SerDe returns NULL, so a bad load surfaces weeks later as a suspicious count of NULLs. Self-describing formats are the fix: ORC, Parquet and Avro carry their schema in the file.

The metastore: the piece you cannot lose

The Hive Metastore is a Thrift service in front of an ordinary relational database - MySQL, PostgreSQL or Oracle in production, Derby only on a laptop. Its schema is worth knowing: DBS, TBLS, SDS for storage descriptors (location, formats, SerDe), PARTITIONS, COLUMNS_V2, plus statistics and transaction state.

It is shared: Spark SQL, Impala, Trino and Iceberg catalogs read the same metastore, so it is often the only place four engines agree on what a table is. It is also stateful in a real RDBMS, so it needs the backup and HA care of any OLTP database: every other component can be restarted, a lost metastore means lost table definitions.

Partition count is what kills metastores. Each partition is several rows, so a table partitioned on a date plus a high-cardinality attribute turns pruning into a slow query against the backing database. When compile time is seconds and execution is fast, suspect metastore round-trips, not the optimizer. Internals in the metastore architecture article.

From HiveQL to an execution plan

Compilation is a fixed pipeline. The parser turns HiveQL into an AST; the semantic analyzer resolves names against the metastore, expands views, and emits a tree of logical operators - TableScan, Filter, Select, GroupBy, Join, ReduceSink, FileSink. The logical optimizer rewrites that tree: column pruning, predicate pushdown toward the scan, partition pruning so whole directories are never listed, map-side partial aggregation so a GROUP BY shrinks data before the shuffle.

With CBO enabled, Apache Calcite takes over join ordering and join-algorithm choice using column statistics. Without fresh statistics it guesses, which is the commonest reason a report that was fast last month is slow today - see Hive CBO and predicate pushdown.

ANALYZE TABLE events PARTITION (dt='2026-08-01')
  COMPUTE STATISTICS FOR COLUMNS;

SET hive.cbo.enable=true;
SET hive.stats.autogather=true;
SET hive.vectorized.execution.enabled=true;

The physical plan is cut at every ReduceSink, and each cut becomes a stage in the engine below. EXPLAIN prints it; read three things - how many partitions the TableScan lists, whether a join is a map join or a shuffle join, and whether vectorization is enabled.

Execution engines: MapReduce, then Tez, then Spark

Hive originally compiled to MapReduce, and the engine changed for structural reasons rather than tuning. Every stage is a separate job: intermediate results go to HDFS, are replicated, and are read back by the next job, and every task gets a fresh JVM. A five-stage query means five submissions, four disk-and-network round-trips, and tens of seconds of scheduling before useful work starts - invisible in a two-hour ETL job, ruinous at interactive latency.

Tez removes the stage boundary. The query is submitted once as a DAG; vertices exchange data directly rather than through HDFS; containers are reused so JVMs stay warm; parallelism is decided at runtime from observed data volume. Dynamic partition pruning lets the dimension side of a join tell the fact scan which partitions to touch. See Hive on Tez.

Hive on Spark reuses Spark as the DAG runtime, mostly for shops already standardised on Spark. The engine is chosen with SET hive.execution.engine=tez;. MapReduce was deprecated as a Hive engine well before Hive 4 and should be treated as legacy. For low-latency serving, LLAP adds persistent daemons and a shared columnar cache on top of Tez.

HiveServer2 and how clients connect

HiveServer2 is the Thrift front door, and in a managed cluster the only supported one. The old hive CLI ran the driver in the client process and talked to the metastore database directly - no central authorization, no session isolation. Beeline, a thin JDBC client against HS2, replaced it.

HS2 owns sessions and operation handles, so clients submit asynchronously, poll, and fetch results in batches. Transport is binary Thrift or HTTP. The doAs impersonation setting decides whether HDFS sees the end user or the hive service user - which decides whether HDFS permissions or Ranger policies are your real authorization layer. Multiple instances register in ZooKeeper so a JDBC URL with service discovery load-balances.

Expect heap to be the failure mode: HS2 holds plan and session state per connection plus result staging, so a few hundred abandoned sessions is a classic out-of-memory. Track compile and execute time separately in monitoring - the split says whether to blame the catalog or the cluster. More in the HiveServer2 article.

Managed and external tables

The distinction is ownership, best expressed as a question: what does DROP TABLE delete? For a managed table Hive owns the directory under the warehouse path and DROP removes metadata and data; for an external table Hive owns only metadata and DROP leaves every file in place. Rule of thumb - if another system writes or reads those files, make the table external.

Hive 3 sharpened the split: a plain CREATE TABLE produces a managed, transactional ORC table in a separate managed warehouse directory, while external tables are non-transactional. Setting external.table.purge=true makes DROP delete external data too - occasionally useful, more often a foot-gun.

External tables also drift: a Spark job or Kafka sink writes directories the metastore never hears about, and those partitions stay invisible until MSCK REPAIR TABLE reconciles filesystem with catalog - an expensive listing on object storage, so mature pipelines add partitions explicitly.

Partitioning and bucketing

Partitioning is Hive's primary data-skipping mechanism and it is purely physical: a partition column is a directory name, not a value stored in the files. A predicate on it lets the compiler discard whole directories before any I/O, which is why a partition key should be low-cardinality and something queries actually filter on - almost always a date.

CREATE EXTERNAL TABLE events (
  event_id    BIGINT,
  user_id     BIGINT,
  event_type  STRING,
  event_ts    TIMESTAMP
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 128 BUCKETS
STORED AS ORC
LOCATION 's3a://lake/events/';

Over-partitioning is the classic self-inflicted wound: partition by date, country and device and you get tens of thousands of directories a day holding a few megabytes each, so metastore rows explode and per-file open cost dwarfs the reading. Writing many partitions from one statement needs dynamic partitioning, fenced by hive.exec.max.dynamic.partitions - see dynamic partitioning and the small-file problem.

Bucketing is the orthogonal knob: hash(col) mod N puts each row in one of N files, co-locating keys across tables bucketed alike, which enables bucket map joins, sort-merge-bucket joins and deterministic sampling. The bucket count is effectively permanent - a design decision, not a tuning flag. Neither defeats skew - one key holding most of the rows - which needs skew join handling. Alignment rules are in the bucketing article.

File formats and why columnar matters

Hive reads text and CSV, SequenceFile, Avro, ORC and Parquet. Text is a landing format and nothing more; Avro is row-oriented with strong schema evolution, so it suits ingest. Analytical tables should be ORC or Parquet, for three reasons.

Column projection: a query touching three of eighty columns reads roughly three-eightieths of the bytes, because columns are stored contiguously rather than interleaved per row. Compression: a column holds homogeneous values, so dictionary, run-length and delta encodings beat general-purpose compression over mixed rows. Embedded statistics: ORC stores min, max, count and sum per stripe and index group, Parquet the equivalent per row group and page, so a predicate skips a whole stripe without decompressing it.

Columnar layout is also what makes vectorized execution possible: the reader materialises a thousand values of one column into a primitive array and the filter runs as a tight loop instead of a virtual call per row. ORC is the more deeply integrated choice inside Hive - ACID requires it - while Parquet is safer when many engines read the same files. See the ORC format article.

ACID and transactional tables

Hive supports UPDATE, DELETE and MERGE over files that are effectively immutable, and does so by never modifying a file. A write produces a delta directory tagged with a write ID; a reader merges the base with the deltas and filters rows using a valid-write-ID list captured when the query started. That is snapshot isolation: readers never block writers and never see half of someone else's insert. The transaction manager in the metastore issues IDs and holds locks.

Deltas accumulate and read amplification with them, so the design depends on compaction - minor merges deltas into a larger delta, major rewrites everything into a fresh base. A compactor falling behind is the top operational issue: file counts climb and queries degrade steadily. See Hive ACID and compaction.

Right uses: slowly changing dimensions, regulatory deletes, late corrections, streaming ingest - not OLTP, since the floor on latency is a query, not a millisecond. For new tables, Iceberg increasingly answers the same need.

Where Hive fits next to Impala, Spark SQL and Trino

They usually read the same metastore and the same files, so the question is which to point at a given workload, not which to install.

The real axis is fault tolerance against latency. Hive checkpoints between stages, so a four-hour job survives a lost node and reruns only the affected tasks, while an MPP engine holding everything in flight restarts the query. So the durable pattern is not either/or: transform in Hive or Spark, register the output once, and serve the same tables to analysts through Impala or Trino.