Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 36 ยท Architecture and Metastore

The Life of a Hive Query

Follow one HiveQL query from text to results: parsing, semantic analysis, logical and physical planning, optimization, execution and fetching, and how to read EXPLAIN output.

When you run SELECT in Hive nothing is executed immediately. The text is analysed, planned, optimized and turned into distributed tasks. Knowing these stages explains why a query takes 10 seconds to start, why a typo fails quickly, and how to read an EXPLAIN to find out why a query is slow.

The stages below describe real Hive. We cannot run Hive here, so Hive statements are read-only; the runnable editors are SQLite stand-ins that practise the SQL itself. We do not print Hive plan output, because its exact text varies by version.

Stage by stage

flowchart LR
    A["HiveQL text"] --> B["1. Parse<br/>AST"]
    B --> C["2. Semantic analysis<br/>(metastore lookups)"]
    C --> D["3. Logical plan<br/>operator tree"]
    D --> E["4. Optimize<br/>(Calcite + rules)"]
    E --> F["5. Physical plan<br/>stages / DAG"]
    F --> G["6. Execute<br/>on the engine"]
    G --> H["7. Fetch results"]

Errors found in stages 1 to 3 appear instantly, before any cluster work starts.

1. Parse

The parser turns text into a syntax tree. A typo such as SELEC or a missing parenthesis fails here with a ParseException, and the metastore is never contacted.

2. Semantic analysis

Now names are checked against the metastore: does table orders exist, does it have column total, are the types of a + b compatible? Views are expanded, SELECT * becomes a column list, and for partitioned tables the partition list is read. Errors like "Table not found" or "Invalid column reference" come from this stage.

3. Logical plan

The query becomes a tree of operators. For this SQL:

SQL
SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY customer_id
HAVING SUM(total) > 1000;

the logical tree is roughly: table scan of orders, then filter on order_date, then group-by with SUM, then filter on the sum, then select. SQL's written order (SELECT first) is not the execution order; the logical order is FROM, WHERE, GROUP BY, HAVING, SELECT.

4. Optimization

The optimizer rewrites the tree without changing the answer. Common rewrites:

  • Predicate push-down: move filters as close to the scan as possible, so fewer rows travel through later stages. With ORC or Parquet the filter can even be pushed into the file reader, skipping blocks.
  • Partition pruning: if the table is partitioned by order_date, folders that cannot match are never listed.
  • Column pruning: only the columns a query uses are read from columnar files.
  • Join reordering and join type: the cost-based optimizer uses table statistics to pick the order and, for a small table, a map join (broadcast).
  • Constant folding and simplification: WHERE 1 = 1 disappears.

Statistics matter. Without them, the optimizer guesses. That is why you run ANALYZE TABLE ... COMPUTE STATISTICS (see the tuning chapter).

5. Physical plan

The optimized tree is divided into stages (vertices in Tez). Operations that can run on data where it sits, such as scan, filter and partial aggregation, form one stage. Operations that need data from many places, such as a global group-by, a join or a sort, require a shuffle: data is redistributed across the network by key. Shuffles are the expensive parts of any plan.

6. Execution

The engine requests containers from YARN, starts tasks near the data, runs the stages, and writes final output to a temporary location. You see progress lines in Beeline:

TEXT
VERTICES      MODE        STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED
Map 1 ........ container     SUCCEEDED     12         12        0        0       0
Reducer 2 .... container     SUCCEEDED      4          4        0        0       0

7. Fetch

HiveServer2 reads the result files and streams rows to the client. For simple queries Hive skips stages 5 and 6 entirely and uses a fetch task, reading files directly. Control it with hive.fetch.task.conversion (more allows filters and limits on a single table).

Reading EXPLAIN

Put EXPLAIN in front of any statement to see the plan without running it:

SQL
EXPLAIN
SELECT customer_id, SUM(total)
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY customer_id;

The output lists stages with their operators. Read it from the bottom up (data flows from scans upward). Look for:

  • TableScan: the table and, if partitioned, which partitions are read.
  • Filter Operator: whether your WHERE appears right after the scan (good) or late.
  • Reduce Output Operator: a shuffle. Count them; each is a cost.
  • Map Join Operator versus Merge Join Operator: broadcast versus shuffle join.

Variants include EXPLAIN EXTENDED (more detail), EXPLAIN FORMATTED (JSON), EXPLAIN DEPENDENCY (which tables and partitions are touched) and EXPLAIN ANALYZE (runs the query and adds actual row counts).

The same ideas in SQLite

The logical order of clauses is the same in every SQL engine, so we can see it with the stand-in. The HAVING filter runs after grouping; WHERE runs before:

SQL Stand-in: WHERE runs before GROUP BY, HAVING after (SQLite, not Hive) ยท database: bookstore

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

Moving a condition from HAVING to WHERE when it concerns a plain column lets the engine discard rows earlier. Hive's optimizer often does this for you, but writing it right is free.

Why queries seem slow at first

A Hive query pays fixed costs: the compile step, a request to YARN, container start-up and the first task wave. For a tiny table those costs dominate, which is why SELECT COUNT(*) on a small table can take several seconds. Tez container reuse and LLAP reduce them; they never disappear. Judge Hive by its throughput on large data, not by its latency on tiny tables.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A query fails immediately with 'Table not found'. In which stage?

    Name resolution happens in semantic analysis, before any cluster work begins.
  2. 2. What is predicate push-down?

    Filtering early reduces the amount of data processed in later stages.
  3. 3. Which operator in an EXPLAIN plan indicates a shuffle?

    Reduce Output Operator redistributes data by key across the network.
  4. 4. Why can SELECT COUNT(*) on a tiny table still take seconds in Hive?

    Query start-up costs do not shrink with the data size.

Summary

  • A query passes through parse, semantic analysis, logical plan, optimization, physical plan, execution and fetch.
  • Syntax and name errors surface early; heavy work starts only after planning.
  • The optimizer pushes filters down, prunes partitions and columns and chooses join types using statistics.
  • Shuffles are the expensive part of a plan; EXPLAIN shows them.

Next: The Hive Metastore