Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 47 ยท SQL and NoSQL on Hadoop

Hive Performance: Execution Engines and Tuning

Make Hive fast: why Tez replaced MapReduce, vectorized execution, the cost-based optimizer, map-join versus shuffle-join, and ACID tables, with cost-model examples.

Early Hive earned a reputation for being slow. A query was compiled into a chain of MapReduce jobs, and each job wrote its result to HDFS before the next one started. Modern Hive is much faster, and the gains come from a handful of ideas: a better execution engine, processing rows in batches, an optimizer that knows your data's size, and smarter joins. This lesson explains each and shows how to reason about them.

Real Hive settings and queries here are read-only, because no cluster can run on this site. The runnable examples are Python cost models: they use simple formulas for how many bytes a join moves or how many rows a plan produces. Real engines are more complicated, but the models show why the choices matter.

Execution engines: MapReduce, Tez and LLAP

Three ways to run the same SQL

MapReduce Every stage writes to HDFS. Deprecated for Hive.
Tez One DAG per query, data flows between stages directly.
LLAP Long-lived daemons with cached data for short queries.

Set hive.execution.engine=tez, the default in modern Hive.

1 / 2

A query with a join, a group-by and a sort needed about three MapReduce jobs in classic Hive. Between jobs, results were written to HDFS with three replicas and read back. Tez represents the whole query as one directed acyclic graph of tasks. Output goes straight from one vertex to the next, often without touching HDFS, and tasks reuse containers instead of starting a fresh JVM every time. The same query typically runs several times faster. LLAP (Live Long and Process) goes further for interactive use: daemons stay alive on the nodes, cache columnar data in memory and skip the startup cost entirely.

SQL
SET hive.execution.engine=tez;

Spark can also be used as an engine in some deployments, and many sites query the same tables with Spark SQL or Trino instead of Hive, since the metastore is shared.

Vectorization

A classical engine processes one row at a time: for each row, call a function for the filter, a function for the expression, another for the aggregate. The per-row overhead dominates. Vectorized execution processes a batch of 1,024 rows at once. Each operator works on whole column arrays in a tight loop, which uses CPU caches well and avoids a method call per value. It works on ORC (and Parquet in recent versions) and is on by default in modern Hive:

SQL
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;

You can see it in EXPLAIN: operators show Execution mode: vectorized. Some functions and data types are not vectorized and make the stage fall back to row mode, which is a common reason for an unexpectedly slow query. Vectorization is one more reason to store tables as ORC or Parquet (Parquet and ORC Internals).

The cost-based optimizer

A rule-based optimizer applies fixed rewrites (push filters down, prune columns). Hive also has a cost-based optimizer (CBO, built on Apache Calcite) that chooses between plans using statistics: row counts, distinct values, min/max, column sizes. Chief among its decisions are the join order and the join algorithm.

The CBO is only as good as its statistics, so collect them:

SQL
ANALYZE TABLE sales COMPUTE STATISTICS;
ANALYZE TABLE sales COMPUTE STATISTICS FOR COLUMNS;
SET hive.cbo.enable=true;
SET hive.stats.fetch.column.stats=true;

Join order matters because each join's output feeds the next. Joining the two largest tables first can create a huge intermediate result, while joining a tiny, filtered table first shrinks the data early. This is a stand-in estimate with made-up numbers.

Python Join order: small intermediate results win

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 optimizer's job is to find the cheap orders at the top of that list without you rewriting the query. With no statistics it cannot, and may pick an order that looks fine syntactically but is terrible in practice.

Join strategies

Joins are the costliest operation in Hive. Three strategies matter:

  1. Shuffle (reduce-side) join. Both tables are partitioned by the join key and shuffled over the network, and each reducer joins one slice. Works for any sizes, and is the most expensive: all rows of both tables cross the network. This is the join from MapReduce Design Patterns.
  2. Map join (broadcast join). If one table is small, copy it to every task that scans the big table and join in memory in the map phase. The big table is never shuffled.
  3. Bucket map join / sort-merge-bucket join. If both tables are bucketed (and sorted) on the join key, matching buckets join locally (Partitioning, Bucketing and File Layout).

Hive converts to a map join automatically when a table fits under a size threshold:

SQL
SET hive.auto.convert.join=true;
SET hive.auto.convert.join.noconditionaltask.size=268435456;  -- bytes; tune to your memory

Check the exact defaults of your Hive version; the threshold is a balance, because a broadcast table must fit in each task's memory.

The cost of each strategy, in network bytes, is easy to model: a shuffle join moves big + small, while a map join sends the small table to each of T task nodes, which costs small x T. Broadcasting wins while the small table is much smaller than the big one divided by the number of tasks.

Python Map join versus shuffle join

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.

In the model the break-even is about 2,600 MB, but real clusters hit memory limits far earlier, which is why the real threshold is only tens or hundreds of megabytes. Note also that a map join needs no reduce stage and no sort, which saves more than the network bytes alone suggest.

Other join tips: filter early, join on columns of the same type (a STRING joined to an INT forces conversions), watch for skewed keys (Hive has hive.optimize.skewjoin), and avoid ORDER BY on huge results (one reducer).

ACID tables

Files in HDFS are write-once, so how can Hive UPDATE and DELETE? With ACID (transactional) tables, stored as ORC. Instead of rewriting files, Hive writes delta directories:

TEXT
/warehouse/orders/
  base_0000100/            <- compacted data up to transaction 100
  delta_0000101_0000101/   <- rows inserted by transaction 101
  delete_delta_0000102_0000102/  <- rows deleted by transaction 102

A reader merges the base with the deltas, hides deleted rows and sees a consistent snapshot. Over time many deltas accumulate, so background compaction runs: minor compaction merges deltas together, major compaction folds them into a new base. This should remind you of how log-structured stores work, which you will build in HBase: Wide-Column Storage.

SQL
CREATE TABLE orders (id INT, status STRING)
STORED AS ORC
TBLPROPERTIES ('transactional' = 'true');

UPDATE orders SET status = 'shipped' WHERE id = 42;
MERGE INTO orders o USING updates u ON o.id = u.id
WHEN MATCHED THEN UPDATE SET status = u.status
WHEN NOT MATCHED THEN INSERT VALUES (u.id, u.status);

ACID tables suit slowly changing data and corrections, not high-rate row-by-row updates. Many teams prefer table formats such as Iceberg for this (see Data Lakes, Warehouses and Lakehouses).

A tuning checklist

  • Use Tez (or LLAP for interactive queries) and ORC or Parquet with vectorization.
  • Run ANALYZE TABLE so the CBO has statistics; re-run after big loads.
  • Partition and prune, and make sure the filter is on the partition column itself, not a function of it.
  • Let map joins happen: keep dimension tables small and the threshold sensible.
  • Avoid the small-files problem and keep files near the block size.
  • Read EXPLAIN before blaming the cluster.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is Hive on Tez usually faster than Hive on MapReduce?

    MapReduce chains jobs, each writing to HDFS. Tez keeps data flowing between stages inside one DAG and reuses containers.
  2. 2. What does vectorized execution change?

    Batches of column values are processed in tight loops, which reduces per-row overhead and uses CPU caches better.
  3. 3. The cost-based optimizer chooses a poor join order. What is the most likely cause?

    The CBO relies on statistics from ANALYZE TABLE. Without them it estimates sizes badly.
  4. 4. When does a map join beat a shuffle join?

    Broadcasting the small table avoids shuffling the big one entirely. It stops being possible when the small table no longer fits in task memory.
  5. 5. How do Hive ACID tables support UPDATE on write-once files?

    Changes go into delta and delete-delta directories. Readers merge them, and compaction folds them into a new base.

Summary

  • Tez runs a query as one DAG and replaced MapReduce; LLAP caches data in long-lived daemons for short queries.
  • Vectorization processes batches of rows and works best with ORC or Parquet.
  • The cost-based optimizer needs statistics (ANALYZE TABLE) to pick join order and algorithms.
  • A shuffle join moves both tables; a map join broadcasts the small one and avoids the shuffle.
  • ACID tables use base and delta files with compaction to allow UPDATE, DELETE and MERGE.

Next: Pig Latin Dataflows.