Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท HiveQL Basics

GROUP BY and Aggregation in Hive

Summarize big data in Hive with GROUP BY, HAVING, COUNT DISTINCT, GROUPING SETS, ROLLUP, CUBE and window functions, and learn how map-side aggregation and skew affect speed.

Aggregation is what Hive is for: turn billions of rows into a few hundred numbers. The syntax is standard SQL, so this lesson concentrates on what is special in Hive: extra grouping forms, how aggregation runs in two phases, the cost of COUNT(DISTINCT), data skew and the window functions that give you rankings and running totals.

Standard aggregation is runnable here on the bookstore database in SQLite as a stand-in. Hive-only features (GROUPING SETS forms, WITH ROLLUP, settings such as hive.groupby.skewindata) are read-only. SQLite 3.25+ supports window functions.

Basic aggregates

COUNT, SUM, AVG, MIN, MAX collapse many rows into one per group. Any column in SELECT that is not inside an aggregate must appear in GROUP BY.

SQL Stand-in: GROUP BY with several aggregates (SQLite, not Hive) ยท database: bookstore

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

COUNT(*) counts rows, COUNT(col) skips NULLs, COUNT(DISTINCT col) counts unique values. Aggregates ignore NULLs (except COUNT(*)), so AVG is the average of known values.

HAVING filters groups

WHERE removes rows before grouping; HAVING removes groups after aggregation.

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

Hive allows an alias in HAVING only in newer versions; repeating the aggregate is portable.

How aggregation runs

flowchart LR
    S1["Map task 1<br/>partial SUM per key"] --> SH["Shuffle by key"]
    S2["Map task 2<br/>partial SUM per key"] --> SH
    S3["Map task 3<br/>partial SUM per key"] --> SH
    SH --> R1["Reducer: final SUM<br/>keys A..M"]
    SH --> R2["Reducer: final SUM<br/>keys N..Z"]

Map-side aggregation (the "combiner") shrinks the data before the shuffle.

With hive.map.aggr=true (the default), each map task pre-aggregates its own rows so that far less data crosses the network. This works for SUM, COUNT, MIN, MAX and AVG (kept as sum and count), but not for aggregates that need all raw values, such as exact medians.

Skewed groups

If one key holds most of the rows (one customer with 90% of the events, or NULL keys), a single reducer receives most of the data and the whole query waits on it. Hive can add an extra stage that spreads the load:

SQL
SET hive.groupby.skewindata=true;

The first job distributes rows randomly and aggregates partially; a second job combines by key. It costs one extra stage, so enable it only when you see a skewed reducer (one task far slower than the others in the Tez UI).

COUNT(DISTINCT) is expensive

An exact distinct count needs all values of a key in one place. On a big table COUNT(DISTINCT user_id) can be slow or run on a single reducer. Alternatives:

  • Rewrite as SELECT COUNT(*) FROM (SELECT user_id FROM t GROUP BY user_id) x, which spreads the work across reducers.
  • Use an approximate function where an estimate is enough (for example approx_distinct-style functions in newer Hive, or HyperLogLog sketches from the DataSketches library).

The portable rewrite can be tested here:

SQL Stand-in: two ways to count distinct values (SQLite, not Hive) ยท database: bookstore

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

GROUPING SETS, ROLLUP, CUBE

These produce several levels of subtotals in a single pass over the data:

SQL
SELECT country, city, SUM(sales) AS sales
FROM store_sales
GROUP BY country, city WITH ROLLUP;
-- rows for (country, city), (country, NULL) subtotals and (NULL, NULL) grand total

SELECT country, channel, SUM(sales)
FROM store_sales
GROUP BY country, channel WITH CUBE;
-- every combination, including channel-only totals

SELECT country, channel, SUM(sales)
FROM store_sales
GROUP BY country, channel
GROUPING SETS ((country, channel), (country), ());

The function GROUPING__ID (and grouping(col) in newer releases) tells you whether a NULL in a result column is a real null or a subtotal marker. Without it, a NULL country row is ambiguous.

Window functions

A window function computes a value for each row without collapsing rows. Syntax: function() OVER (PARTITION BY ... ORDER BY ... [frame]).

SQL
SELECT customer_id, order_date, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS nth_order,
       SUM(total)   OVER (PARTITION BY customer_id ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       LAG(total)   OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_total
FROM orders;

Useful functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE(n), LAG, LEAD, FIRST_VALUE, LAST_VALUE, and any aggregate with OVER. A very common pattern is "top n per group", in which you rank rows and keep rank 1 to n:

SQL Stand-in: top 2 most expensive books per genre (SQLite, not Hive) ยท database: bookstore

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

Window queries need all rows of a partition together, so they trigger a shuffle by the PARTITION BY columns. A window with no PARTITION BY sends everything to one reducer and is slow at scale.

Percentiles and statistics

percentile(col, 0.5) is the exact percentile (integers only), percentile_approx(col, 0.95) is an estimate that scales, variance, stddev_pop and corr cover basics. For medians on big data prefer the approximate form.

Common mistakes

  • Selecting a non-grouped column (the query fails in Hive; MySQL's leniency does not exist).
  • Putting a row-level filter in HAVING, which forces grouping of rows that could have been removed earlier.
  • Running COUNT(DISTINCT) of several columns in one query (each needs its own pass).
  • Using a window function without PARTITION BY on a huge table.
  • Reading NULL subtotal rows from ROLLUP as real data.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does map-side aggregation do?

    Partial aggregation before the shuffle reduces network traffic.
  2. 2. What is the difference between WHERE and HAVING?

    WHERE is applied to input rows, HAVING to grouped results.
  3. 3. Which setting helps when one GROUP BY key holds most of the rows?

    It adds a stage that spreads skewed keys over several reducers before the final combine.
  4. 4. How do window functions differ from GROUP BY?

    OVER (...) keeps every input row and adds a computed column.

Summary

  • Aggregates collapse rows per group; HAVING filters groups, WHERE filters rows.
  • Hive pre-aggregates in map tasks; skewed keys and COUNT(DISTINCT) are the usual bottlenecks.
  • ROLLUP, CUBE and GROUPING SETS give subtotals in one pass.
  • Window functions (ROW_NUMBER, LAG, running totals) add per-row calculations; always partition them.

Next: Managed and External Tables