Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 36 ยท Joins and Optimization

Skew and Sort-Merge Bucket Joins

Fix slow Hive joins caused by skewed keys, and use bucket map joins and sort-merge bucket joins to join huge tables without a full shuffle.

Shuffle joins have two weak spots. First, if one key has far more rows than the rest, the reducer that receives it works alone for a long time while the others sit idle. That is data skew. Second, two huge tables always pay for a full shuffle even if you join them every day on the same key.

Hive has answers to both: skew-aware joins for the first problem, and bucketed table layouts for the second.

Recognizing skew

Skew shows up as a query where 199 of 200 reducers finish in a minute and the last one runs for an hour. The cause is usually a "popular" key: a default user id such as 0, an empty string, a NULL replacement like -1, or a genuinely huge customer.

Find hot keys by counting rows per key on the big side:

SQL Find the hottest keys ยท database: shop

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

If the top key holds many times more rows than the average, you have skew. In production you would run the same query against the real fact table, ideally on a sample.

Skew join settings

Hive can handle skewed keys separately at run time:

SQL
SET hive.optimize.skewjoin = true;
SET hive.skewjoin.key = 100000;   -- a key with more rows than this is "skewed"

With these settings, the first join job writes rows of skewed keys to a side location instead of sending them to one reducer. A follow-up map join then joins those rows against the matching rows of the other table, spread over many tasks. Rows of normal keys are joined as usual.

You can also declare skew in the table definition so Hive knows in advance which values are hot:

SQL
CREATE TABLE clicks (user_id BIGINT, url STRING)
SKEWED BY (user_id) ON (0, -1)
STORED AS ORC;

SKEWED BY records the skewed values in the metastore. It also lets Hive store them separately with STORED AS DIRECTORIES. The declaration only helps if the listed values really are the hot ones.

Manual fixes

Often the best fix is in the query:

  • Filter junk keys. If the hot key is NULL, 0 or '' and carries no meaning, remove it before the join: WHERE user_id IS NOT NULL AND user_id <> 0.
  • Handle the hot key separately. Join the hot key with a map join, everything else normally, and UNION ALL the two results.
  • Salt the key. Add a random number to the key on the big side and replicate the small side once per salt value, so one hot key spreads over several reducers.

The salting idea is easier to see than to read. This stand-in runs in SQLite, not Hive, and shows how rows of the hot key spread over four buckets while the small side is repeated four times.

SQL Salting a hot key (SQLite stand-in)

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

Key 0 now forms four groups of two rows instead of one group of eight, so four reducers share the work.

Bucket map join

A table can be bucketed: rows are divided into a fixed number of files by hash(column) mod buckets.

SQL
CREATE TABLE orders_b (order_id BIGINT, customer_id BIGINT, total DECIMAL(10,2))
CLUSTERED BY (customer_id) INTO 32 BUCKETS
STORED AS ORC;

If both tables are bucketed on the join key and the bucket count of one is a multiple of the other, bucket n of the left table can only match bucket n (or its fold) of the right table. A bucket map join then loads only the matching bucket of the small table per task, which allows joins where the whole small table would not fit in memory.

SQL
SET hive.optimize.bucketmapjoin = true;

Sort-merge bucket (SMB) join

If the buckets are also sorted on the join key, Hive does not need a hash table at all. It reads matching bucket pairs side by side and merges them like the merge step of merge sort: two sorted streams advance together, so memory use is tiny and neither table is shuffled.

SQL
CREATE TABLE customers_b (customer_id BIGINT, name STRING)
CLUSTERED BY (customer_id) SORTED BY (customer_id ASC) INTO 32 BUCKETS
STORED AS ORC;

SET hive.optimize.bucketmapjoin = true;
SET hive.optimize.bucketmapjoin.sortedmerge = true;
SET hive.auto.convert.sortmerge.join = true;

Requirements for an SMB join:

  1. Both tables are bucketed and sorted on the join key, in the same sort order.
  2. Their bucket counts are equal, or one is a multiple of the other.
  3. The data was loaded in a way that respects bucketing. Since Hive 2, inserts honor bucketing automatically; in older versions you needed SET hive.enforce.bucketing = true and hive.enforce.sorting = true.

The payoff is real when you join the same two big tables every day: you pay for bucketing and sorting once, at write time, and every later join skips the shuffle.

See the merge in action

This JavaScript stand-in merges two sorted lists the way an SMB join does, with two pointers and no hash table.

JS Merging two sorted buckets

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

No output.

Choosing a layout

flowchart TD
    Q["Join slow?"] --> A{"One table small?"}
    A -- Yes --> M["Map join"]
    A -- No --> B{"Joined often on the same key?"}
    B -- Yes --> S["Bucket and sort both tables: SMB join"]
    B -- No --> C{"One key dominates?"}
    C -- Yes --> K["Skew join settings or manual split"]
    C -- No --> F["Plain shuffle join, tune reducers"]

Match the fix to the shape of the data.

Bucketing has a cost: you must pick a key and a bucket count up front, and a bad choice cannot be changed without rewriting the table. Use it for large, stable tables with a clear, evenly distributed join key such as customer_id, not for small tables.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is data skew in a join?

    Skew means unevenly distributed keys: the reducer with the hot key becomes the bottleneck.
  2. 2. What does hive.optimize.skewjoin = true do?

    Rows of keys above hive.skewjoin.key are held back and joined in a later map-join step spread over many tasks.
  3. 3. Which condition is required for a sort-merge bucket join?

    SMB joins read matching sorted buckets side by side, which only works when the layouts line up.
  4. 4. What is salting?

    Salting splits one hot key into several sub-keys so more reducers share the load.

Summary

  • Skew means a few keys dominate; find them with a GROUP BY key ORDER BY COUNT(*) DESC.
  • hive.optimize.skewjoin and SKEWED BY let Hive treat hot keys separately; filtering junk keys or salting are query-level fixes.
  • CLUSTERED BY ... INTO n BUCKETS enables bucket map joins; adding SORTED BY enables sort-merge bucket joins without a hash table.
  • Bucketing is a design decision made at table creation, so choose the key and count carefully.

Next: EXPLAIN and the Cost-Based Optimizer