Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 36 ยท Joins and Optimization

Map Joins and Shuffle Joins

See how Hive executes a join as a shuffle (reduce-side) join or a broadcast map join, which settings trigger each one, and how to use hints and automatic conversion.

When you write JOIN, you describe what you want. Hive's planner decides how to do it. On a cluster there are two main strategies, and the difference between them is often the difference between a query that takes two minutes and one that takes two seconds.

Understanding them lets you read an EXPLAIN plan, choose table sizes and settings sensibly, and know why Hive sometimes says a join "converted to a map join".

Shuffle join (reduce-side join)

This is the general-purpose strategy and the only one that works when both tables are large.

  1. Map phase. Each worker reads a slice of either table and emits rows keyed by the join column.
  2. Shuffle. The framework sends all rows with the same key to the same reducer, normally by hashing the key: reducer = hash(key) mod numReducers.
  3. Reduce phase. A reducer sees all left and right rows for a key and produces the matches.
flowchart LR
    A["Orders slices"] --> S["Shuffle by customer_id"]
    B["Customers slices"] --> S
    S --> R1["Reducer 1: keys 1,4,7"]
    S --> R2["Reducer 2: keys 2,5,8"]
    S --> R3["Reducer 3: keys 3,6,9"]

Every row of both tables crosses the network once. That movement is the cost.

On Tez the same idea is called a shuffle (merge) join between two vertices. The cost grows with the total size of both inputs, so a join of a 2 TB fact table with a 10 MB lookup table still shuffles 2 TB when it does not need to.

Map join (broadcast join)

If one table is small, Hive can copy it to every worker and join it right where the big table is read. No shuffle, no reducers, and the big table is scanned in place.

  1. The small table is read and turned into an in-memory hash table.
  2. The hash table is shipped (broadcast) to every task that reads a slice of the big table.
  3. Each task probes the hash table for every row it reads and emits matches.
flowchart LR
    D["Small dimension table"] --> H["Hash table in memory"]
    H --> T1["Task 1: big slice A"]
    H --> T2["Task 2: big slice B"]
    H --> T3["Task 3: big slice C"]

The small table travels once to each task. The big table never moves.

Map joins work for inner joins and for outer joins where the small side is the non-preserved side (for example a left join where the right table is small). A left join with a small left table cannot be a map join, because unmatched big-table rows would need to be reported from every task.

Turning it on

Automatic conversion is controlled by a few settings:

SQL
SET hive.auto.convert.join = true;                      -- default true
SET hive.auto.convert.join.noconditionaltask = true;    -- default true
SET hive.auto.convert.join.noconditionaltask.size = 268435456;  -- 256 MB

hive.auto.convert.join.noconditionaltask.size is the total size of the small tables that may be loaded into memory. The built-in default is only about 10 MB, and many distributions set it higher. Hive judges size from table statistics, so run ANALYZE TABLE ... COMPUTE STATISTICS (see explain and the cost-based optimizer). Without statistics Hive guesses from file sizes.

You can also force the decision with a hint, which is useful when statistics are missing:

SQL
SELECT /*+ MAPJOIN(c) */ o.id, c.first_name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

Hints are a last resort. If the hinted table is too big for memory, tasks fail with out-of-memory errors, so prefer good statistics and the automatic setting.

Dimension tables (customers, products, countries) are the classic broadcast candidates. Fact tables (events, orders, logs) stay on the big side. Star-schema queries in Hive often run as one big scan plus several broadcast joins.

A bucketed variation

When both tables are bucketed on the join key, a bucket map join can broadcast only the matching bucket instead of the entire small table, and a sort-merge bucket join needs no hash table at all. Those are covered in skew and sort-merge bucket joins.

Feel the difference: a simulation

The editor below is a stand-in written in JavaScript, not Hive. It counts how many rows each strategy moves across a fake network for a big orders table and a small customers table, using the same rules described above.

JS Rows moved: shuffle vs broadcast

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

No output.

The same idea in SQL

SQLite is a single-node engine, but it makes a similar choice. For a join to a small table it builds a lookup structure instead of scanning repeatedly. EXPLAIN QUERY PLAN shows the choice:

SQL Reading a plan (SQLite stand-in) ยท database: shop

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

Look for SEARCH c USING INTEGER PRIMARY KEY (c is the customers alias): for each order SQLite looks the customer up directly, the single-node cousin of probing a broadcast hash table. Hive's EXPLAIN prints something different (vertices, Map Join Operator, Reduce Output Operator), but you read it with the same question: which input is scanned, and which one is looked up?

Choosing between them

Situation Best strategy
One side fits in memory (tens to hundreds of MB) Map join
Both sides huge, no layout help Shuffle join
Both sides huge and bucketed and sorted on the key Sort-merge bucket join
One key holds most rows Skew-aware join

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a shuffle join send across the network?

    Rows from both tables are keyed by the join column and routed to reducers so that equal keys meet.
  2. 2. Which table is broadcast in a map join?

    The small table is hashed in memory on every task, while the big table is read in place.
  3. 3. Which setting lets Hive convert eligible joins to map joins on its own?

    hive.auto.convert.join (true by default) allows the planner to pick a map join when a table is small enough.
  4. 4. Why can a map join fail with an out-of-memory error?

    The small side is held entirely in memory on every task, so a wrong size estimate or a forced hint can exhaust it.

Summary

  • A shuffle join moves both tables by key; it works for any size but costs network and disk.
  • A map join broadcasts a small table into memory and avoids the shuffle.
  • hive.auto.convert.join and the noconditionaltask.size threshold decide automatically, based on statistics.
  • /*+ MAPJOIN(t) */ forces a map join; use it only when you know the table is small.
  • Only the non-preserved side of an outer join can be the broadcast table.

Next: Skew and Sort-Merge Bucket Joins