Definition
- Syntax –
SELECT /*+ BROADCAST(table_alias) */ ... FROM ... JOIN ...placed immediately after SELECT. - Synonyms –
BROADCASTJOINandMAPJOINare equivalent keywords (MAPJOIN from Hive heritage). - Multiple tables –
/*+ BROADCAST(a, b) */names both; Spark broadcasts the smaller eligible one. - Advisory – the hint overrides the threshold but Spark may ignore it if the table is too large or the join type forbids it.
- Verification – always check
explainfor BroadcastExchange + BroadcastHashJoin.
At the National Bank of India, the SQL hint is a sticky note on the work order that says "no matter what the size estimate says, photocopy the customers list for every clerk." You put the note right at the top of the order, next to SELECT, and name exactly which list to copy.
Understanding the Concepts
What is the broadcast hint syntax?
The hint is a special comment block written immediately after SELECT. It tells Catalyst to broadcast the named table regardless of the automatic size threshold.
-- Basic broadcast hint in Spark SQL
SELECT /*+ BROADCAST(b) */
t.accountId,
t.amount,
b.branchName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("NBI-SQLHint").getOrCreate()
result = spark.sql("""
SELECT /*+ BROADCAST(b) */
t.accountId,
t.amount,
b.branchName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
""")
result.explain("formatted")
== Physical Plan ==
*(1) BroadcastHashJoin [branchId], [branchId], Inner, BuildRight
:- FileScan parquet nbi.transactions
+- BroadcastExchange HashedRelationBroadcastMode
+- FileScan parquet nbi.branches
What are the equivalent keyword synonyms?
Spark accepts three keywords that all mean the same thing: BROADCAST, BROADCASTJOIN, and MAPJOIN.
-- All three are identical:
SELECT /*+ BROADCAST(b) */ ...
SELECT /*+ BROADCASTJOIN(b) */ ...
SELECT /*+ MAPJOIN(b) */ ... -- Hive heritage
How do you hint multiple tables?
You can name more than one table in a single hint. Spark considers broadcasting each and picks the smaller eligible one.
-- Name both dimension tables
SELECT /*+ BROADCAST(b, p) */
t.accountId,
t.amount,
b.branchName,
p.productName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
JOIN nbi.products p ON t.productId = p.productId
result = spark.sql("""
SELECT /*+ BROADCAST(b, p) */
t.accountId, t.amount, b.branchName, p.productName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
JOIN nbi.products p ON t.productId = p.productId
""")
result.explain("formatted")
# Both branches and products are broadcast
What other join hints are available?
Spark has hints for every join strategy, so you can steer the optimizer toward any algorithm.
| Hint | Strategy |
|---|---|
| BROADCAST(t) | Broadcast Hash Join |
| MERGE(t) | Sort Merge Join |
| SHUFFLE_HASH(t) | Shuffle Hash Join |
| SHUFFLE_REPLICATE_NL(t) | Broadcast Nested Loop Join |
-- Force sort-merge join even if one side is small
SELECT /*+ MERGE(t, a) */
t.accountId, t.amount, a.customerName
FROM nbi.transactions t
JOIN nbi.accounts a ON t.accountId = a.accountId
Where must the hint be placed?
The hint must appear right after the SELECT of the query block whose join you want to influence. A hint in a different query block or subquery may not apply.
-- CORRECT: hint in the main query block
SELECT /*+ BROADCAST(b) */
t.accountId, b.branchName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
-- WRONG: hint in a subquery does not affect the outer join
SELECT *
FROM (
SELECT /*+ BROADCAST(b) */ t.*, b.branchName -- applies to inner join
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
) sub
JOIN nbi.accounts a ON sub.accountId = a.accountId
-- The outer join to accounts is NOT affected by the inner hint
When is the hint ignored?
The hint is advisory with safety limits. Spark may ignore it in several situations.
-- CASE 1: Table is far too large to broadcast
SELECT /*+ BROADCAST(t) */ -- transactions is 500 GB!
t.accountId, a.customerName
FROM nbi.transactions t -- Spark may ignore this hint
JOIN nbi.accounts a ON t.accountId = a.accountId
-- CASE 2: Wrong side for outer join
SELECT /*+ BROADCAST(t) */
t.accountId, a.customerName
FROM nbi.transactions t
LEFT JOIN nbi.accounts a ON t.accountId = a.accountId
-- Cannot broadcast the LEFT side of a LEFT JOIN; hint ignored
How is the SQL hint different from the DataFrame broadcast?
They achieve the same result through different syntax. Use SQL hints in Spark SQL queries and the broadcast() function in DataFrame code.
# SQL hint
result_sql = spark.sql("""
SELECT /*+ BROADCAST(b) */ t.*, b.branchName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
""")
# DataFrame equivalent
from pyspark.sql.functions import broadcast
txns = spark.table("nbi.transactions")
branches = spark.table("nbi.branches")
result_df = txns.join(broadcast(branches), "branchId")
# Both produce the same physical plan: BroadcastHashJoin
How do you verify the hint worked?
Always check the physical plan after adding a hint. A silently ignored hint leaves a SortMergeJoin in place.
result = spark.sql("""
SELECT /*+ BROADCAST(b) */ t.*, b.branchName
FROM nbi.transactions t
JOIN nbi.branches b ON t.branchId = b.branchId
""")
result.explain("formatted")
# GOOD: BroadcastExchange + BroadcastHashJoin
# BAD: Exchange hashpartitioning + SortMergeJoin (hint ignored)
What should a senior engineer know?
A senior engineer places hints in the correct query block, verifies they took effect, and uses them for the same reasons as the DataFrame broadcast() function: stale statistics, post-filter small tables, or sizes just above the threshold.
# Senior practice for SQL analysts at the bank:
# 1. Add BROADCAST hint for known-small dimensions
# 2. Verify with explain() immediately
# 3. After execution, check Spark UI SQL tab for final plan
# 4. If hint was ignored, check table size and join type
Visual Overview
SQL hint placement:
SELECT /*+ BROADCAST(b) */ ← hint goes here, right after SELECT
t.accountId,
t.amount,
b.branchName
FROM nbi.transactions t ← large table
JOIN nbi.branches b ← small table (named in hint)
ON t.branchId = b.branchId
Result: BroadcastHashJoin
- branches broadcast to all executors
- transactions stay local, no shuffle
Hint synonyms: /*+ BROADCAST(b) */ ← standard /*+ BROADCASTJOIN(b) */ ← same thing /*+ MAPJOIN(b) */ ← Hive heritage, same thing Other join hints: /*+ MERGE(t, a) */ → SortMergeJoin /*+ SHUFFLE_HASH(t) */ → ShuffleHashJoin /*+ SHUFFLE_REPLICATE_NL(t) */ → BroadcastNestedLoopJoin
Key Takeaway
A broadcast join hint in Spark SQL is a comment-style directive written immediately after SELECT that forces the optimizer to broadcast a named table. The syntax is /*+ BROADCAST(alias) */, and Spark also accepts BROADCASTJOIN and MAPJOIN as synonyms. This is the SQL equivalent of wrapping a DataFrame in the broadcast() function.
You can name multiple tables in the hint, and Spark broadcasts the smaller eligible one. The hint belongs to a family of join hints that includes MERGE, SHUFFLE_HASH, and SHUFFLE_REPLICATE_NL, so you can steer the optimizer toward any strategy.
At the National Bank of India, our analysts used this constantly in Spark SQL reports. A join of the huge transactions table to a regionally filtered customers table was planned as sort-merge because the estimate was above the threshold. Adding a BROADCAST hint on the customers alias flipped it to a fast broadcast join.
The important caution is that the hint must sit right after SELECT in the correct query block, it is advisory so Spark may ignore it for very large tables or incompatible join types, and you should always verify with explain that BroadcastExchange and BroadcastHashJoin appear.
Remember one sentence: write /*+ BROADCAST(alias) */ after SELECT to force Spark to broadcast that table, and always verify the hint took effect in the physical plan.
---