Broadcast Join and Skew Join in PySpark

Spark tutorial · PySpark.in

Quick reference: PySpark join guide

A broadcast join sends a small DataFrame to every executor, so the large one is joined without a shuffle. Skewed joins are fixed with AQE or salting. This tutorial shows both in PySpark, with tested code, real output and explain() plans.

New to joins? Start with PySpark joins: inner, left, right, full, semi and anti or the PySpark Joins tutorial; this page is about making joins fast.

1. Broadcast join

A normal join between two DataFrames is a shuffle join: Spark repartitions both sides by the join key over the network so matching keys meet in the same partition, then usually sorts them (a sort-merge join). With large tables the shuffle is the expensive part.

In a broadcast join, Spark collects the small DataFrame on the driver and sends a full copy to every executor. Each partition of the large DataFrame is then joined with that local copy, so the large side is never shuffled. That pays off when one side is small, typically a dimension or lookup table joined to a large fact table.

Example: broadcast() and explain()

Mark the small side with broadcast() from pyspark.sql.functions. Broadcasting changes how Spark runs the join, never its result:

```

python

from pyspark.sql import SparkSession

from pyspark.sql.functions import broadcast

spark = SparkSession.builder.appName("BroadcastJoin").getOrCreate()

employees = spark.createDataFrame(

[(1, "Alice", 101), (2, "Bob", 102), (3, "Charlie", None), (4, "David", 104), (5, "Eve", 101)],

["emp_id", "name", "dept_id"])

departments = spark.createDataFrame(

[(101, "HR"), (102, "IT"), (103, "Marketing")],

["dept_id", "dept_name"])

joined = employees.join(broadcast(departments), on="dept_id", how="inner")

joined.orderBy("emp_id").show()

joined.explain()

```

Output:

```

hide

+-------+------+-----+---------+

|dept_id|emp_id| name|dept_name|

+-------+------+-----+---------+

| 101| 1|Alice| HR|

| 102| 2| Bob| IT|

| 101| 5| Eve| HR|

+-------+------+-----+---------+

== Physical Plan ==

AdaptiveSparkPlan isFinalPlan=false

+- Project [dept_id#2L, emp_id#0L, name#1, dept_name#7]

+- BroadcastHashJoin [dept_id#2L], [dept_id#6L], Inner, BuildRight, false

:- Filter isnotnull(dept_id#2L)

: +- Scan ExistingRDD[emp_id#0L,name#1,dept_id#2L]

+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]),false), [plan_id=87]

+- Filter isnotnull(dept_id#6L)

+- Scan ExistingRDD[dept_id#6L,dept_name#7]

```

Charlie (no department) and David (department 104, which does not exist) are dropped because this is an inner join. The plan proves the broadcast: BroadcastExchange ships the departments side and BroadcastHashJoin ... BuildRight builds the hash table from it. The numbers after # and plan_id change from run to run.

Automatic broadcasting: spark.sql.autoBroadcastJoinThreshold

Without a hint, Spark broadcasts a side on its own when its estimated size is below spark.sql.autoBroadcastJoinThreshold, 10 MB by default. To turn automatic broadcasting off, set it to -1. Do not raise it to a huge value to stop broadcasts: that has the opposite effect and makes Spark broadcast larger tables. An explicit broadcast() hint is honoured either way:

```

python

from pyspark.sql import SparkSession

from pyspark.sql.functions import broadcast

spark = SparkSession.builder.appName("BroadcastThreshold").getOrCreate()

employees = spark.createDataFrame([(1, "Alice", 101), (2, "Bob", 102)], ["emp_id", "name", "dept_id"])

departments = spark.createDataFrame([(101, "HR"), (102, "IT")], ["dept_id", "dept_name"])

print(spark.conf.get("spark.sql.autoBroadcastJoinThreshold")) # 10 MB by default

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1) # turn automatic broadcasting off

employees.join(departments, "dept_id").explain() # now a SortMergeJoin

employees.join(broadcast(departments), "dept_id").explain() # an explicit hint still broadcasts

```

Output:

```

hide

10485760b

== Physical Plan ==

AdaptiveSparkPlan isFinalPlan=false

+- Project [dept_id#2L, emp_id#0L, name#1, dept_name#7]

+- SortMergeJoin [dept_id#2L], [dept_id#6L], Inner

:- Sort [dept_id#2L ASC NULLS FIRST], false, 0

: +- Exchange hashpartitioning(dept_id#2L, 200), ENSURE_REQUIREMENTS, [plan_id=25]

: +- Filter isnotnull(dept_id#2L)

: +- Scan ExistingRDD[emp_id#0L,name#1,dept_id#2L]

+- Sort [dept_id#6L ASC NULLS FIRST], false, 0

+- Exchange hashpartitioning(dept_id#6L, 200), ENSURE_REQUIREMENTS, [plan_id=26]

+- Filter isnotnull(dept_id#6L)

+- Scan ExistingRDD[dept_id#6L,dept_name#7]

== Physical Plan ==

AdaptiveSparkPlan isFinalPlan=false

+- Project [dept_id#2L, emp_id#0L, name#1, dept_name#7]

+- BroadcastHashJoin [dept_id#2L], [dept_id#6L], Inner, BuildRight, false

:- Filter isnotnull(dept_id#2L)

: +- Scan ExistingRDD[emp_id#0L,name#1,dept_id#2L]

+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]),false), [plan_id=55]

+- Filter isnotnull(dept_id#6L)

+- Scan ExistingRDD[dept_id#6L,dept_name#7]

```

With adaptive query execution (AQE) on, Spark can also switch a join to a broadcast at run time once it knows the real size of a side; that limit is spark.sql.adaptive.autoBroadcastJoinThreshold, which defaults to the same value. Other join hints: df.hint("broadcast") is the same as broadcast(df), and hint("merge") or hint("shuffle_hash") ask for a sort-merge or shuffle hash join instead.

When to broadcast, and when not to

2. Skewed joins

A join is skewed when a few key values hold most of the rows. In a shuffle join every row with the same key goes to the same partition, so one task processes far more data than the rest: the stage waits for one straggler, and that task can run out of memory. Typical causes are a default or unknown value, one very large customer or country, or nulls.

Find the skew

Count rows per join key. The example data below has 100,000 orders, 90% of them for customer 1:

```

python

from pyspark.sql import SparkSession

from pyspark.sql import functions as F

spark = SparkSession.builder.appName("SkewedJoin").getOrCreate()

# 100,000 orders: 9 in 10 belong to customer 1, the rest are spread over customers 0-99

orders = spark.range(100000).select(

F.col("id").alias("order_id"),

F.when(F.col("id") % 10 == 9, F.col("id") % 100).otherwise(F.lit(1)).alias("customer_id"),

(F.col("id") % 50).alias("amount"))

customers = spark.range(100).select(

F.col("id").alias("customer_id"),

F.concat(F.lit("customer_"), F.col("id")).alias("customer_name"))

orders.groupBy("customer_id").count().orderBy(F.desc("count"), "customer_id").show(5)

```

Output:

```

hide

+-----------+-----+

|customer_id|count|

+-----------+-----+

| 1|90000|

| 9| 1000|

| 19| 1000|

| 29| 1000|

| 39| 1000|

+-----------+-----+

only showing top 5 rows

```

One key with 90,000 rows against 1,000 for the others is severe skew. In the Spark UI the same problem shows as one task in the join stage with far more input and duration than the median.

Fix 1: let AQE split the skewed partition

Adaptive query execution (Spark 3.x, on by default since Spark 3.2) can detect a skewed partition of a sort-merge join at run time and split it into smaller tasks, reading the matching partition of the other side once per split. These settings control it:

```

python

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("AqeSettings").getOrCreate()

for key in ["spark.sql.adaptive.enabled",

"spark.sql.adaptive.skewJoin.enabled",

"spark.sql.adaptive.skewJoin.skewedPartitionFactor",

"spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes",

"spark.sql.adaptive.advisoryPartitionSizeInBytes"]:

print(key, "=", spark.conf.get(key))

```

Output:

```

hide

spark.sql.adaptive.enabled = true

spark.sql.adaptive.skewJoin.enabled = true

spark.sql.adaptive.skewJoin.skewedPartitionFactor = 5.0

spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes = 268435456b

spark.sql.adaptive.advisoryPartitionSizeInBytes = 67108864b

```

A partition counts as skewed when it is larger than skewedPartitionFactor times the median partition and larger than skewedPartitionThresholdInBytes (5 and 256 MB by default). Small example data never reaches 256 MB, so the next example lowers the thresholds to show AQE at work:

```

python

from pyspark.sql import SparkSession

from pyspark.sql import functions as F

spark = SparkSession.builder.appName("SkewedJoin").getOrCreate()

# 100,000 orders: 9 in 10 belong to customer 1, the rest are spread over customers 0-99

orders = spark.range(100000).select(

F.col("id").alias("order_id"),

F.when(F.col("id") % 10 == 9, F.col("id") % 100).otherwise(F.lit(1)).alias("customer_id"),

(F.col("id") % 50).alias("amount"))

customers = spark.range(100).select(

F.col("id").alias("customer_id"),

F.concat(F.lit("customer_"), F.col("id")).alias("customer_name"))

spark.conf.set("spark.sql.autoBroadcastJoinThreshold", -1) # force a shuffle join

spark.conf.set("spark.sql.shuffle.partitions", 8)

# Demo-sized thresholds so 100,000 rows count as skewed (the defaults are 256 MB and 64 MB)

spark.conf.set("spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes", "64KB")

spark.conf.set("spark.sql.adaptive.advisoryPartitionSizeInBytes", "64KB")

result = orders.join(customers, "customer_id")

rows = result.collect() # run the join

print(len(rows))

result.explain() # the plan Spark actually ran

```

Output:

```

hide

100000

== Physical Plan ==

AdaptiveSparkPlan isFinalPlan=true

+- == Final Plan ==

*(5) Project [customer_id#3L, order_id#2L, amount#4L, customer_name#11]

+- *(5) SortMergeJoin(skew=true) [customer_id#3L], [customer_id#10L], Inner

:- *(3) Sort [customer_id#3L ASC NULLS FIRST], false, 0

: +- AQEShuffleRead coalesced and skewed

: +- ShuffleQueryStage 0

: +- Exchange hashpartitioning(customer_id#3L, 8), ENSURE_REQUIREMENTS, [plan_id=43]

: +- *(1) Project [id#0L AS order_id#2L, CASE WHEN ((id#0L % 10) = 9) THEN (id#0L % 100) ELSE 1 END AS customer_id#3L, (id#0L % 50) AS amount#4L]

: +- *(1) Filter CASE WHEN ((id#0L % 10) = 9) THEN isnotnull((id#0L % 100)) ELSE true END

: +- *(1) Range (0, 100000, step=1, splits=2)

+- *(4) Sort [customer_id#10L ASC NULLS FIRST], false, 0

+- AQEShuffleRead coalesced

+- ShuffleQueryStage 1

+- Exchange hashpartitioning(customer_id#10L, 8), ENSURE_REQUIREMENTS, [plan_id=55]

+- *(2) Project [id#8L AS customer_id#10L, concat(customer_, cast(id#8L as string)) AS customer_name#11]

+- *(2) Range (0, 100, step=1, splits=2)

+- == Initial Plan ==

Project [customer_id#3L, order_id#2L, amount#4L, customer_name#11]

+- SortMergeJoin [customer_id#3L], [customer_id#10L], Inner

:- Sort [customer_id#3L ASC NULLS FIRST], false, 0

: +- Exchange hashpartitioning(customer_id#3L, 8), ENSURE_REQUIREMENTS, [plan_id=29]

: +- Project [id#0L AS order_id#2L, CASE WHEN ((id#0L % 10) = 9) THEN (id#0L % 100) ELSE 1 END AS customer_id#3L, (id#0L % 50) AS amount#4L]

: +- Filter CASE WHEN ((id#0L % 10) = 9) THEN isnotnull((id#0L % 100)) ELSE true END

: +- Range (0, 100000, step=1, splits=2)

+- Sort [customer_id#10L ASC NULLS FIRST], false, 0

+- Exchange hashpartitioning(customer_id#10L, 8), ENSURE_REQUIREMENTS, [plan_id=30]

+- Project [id#8L AS customer_id#10L, concat(customer_, cast(id#8L as string)) AS customer_name#11]

+- Range (0, 100, step=1, splits=2)

```

isFinalPlan=true is the plan Spark actually ran. SortMergeJoin(skew=true) and AQEShuffleRead coalesced and skewed show that AQE split the oversized partition of customer 1. The lowered thresholds are for the demo only; keep the defaults in production unless the Spark UI shows skewed tasks that AQE leaves alone.

Fix 2: salt the hot key

When AQE is off or cannot help, for example in a full outer join, which its skew handling does not cover, salting spreads the hot key over several join keys:

```

python

from pyspark.sql import SparkSession

from pyspark.sql import functions as F

spark = SparkSession.builder.appName("SkewedJoin").getOrCreate()

# 100,000 orders: 9 in 10 belong to customer 1, the rest are spread over customers 0-99

orders = spark.range(100000).select(

F.col("id").alias("order_id"),

F.when(F.col("id") % 10 == 9, F.col("id") % 100).otherwise(F.lit(1)).alias("customer_id"),

(F.col("id") % 50).alias("amount"))

customers = spark.range(100).select(

F.col("id").alias("customer_id"),

F.concat(F.lit("customer_"), F.col("id")).alias("customer_name"))

N = 8 # salt buckets: the hot key's rows are spread over N join keys

salted_orders = orders.withColumn("salt", F.abs(F.hash("order_id")) % N)

salted_customers = customers.crossJoin(spark.range(N).withColumnRenamed("id", "salt"))

salted = salted_orders.join(salted_customers, ["customer_id", "salt"]).drop("salt")

salted_orders.filter("customer_id = 1").groupBy("salt").count().orderBy("salt").show()

print(salted.count(), orders.join(customers, "customer_id").count()) # same number of rows

```

Output:

```

hide

+----+-----+

|salt|count|

+----+-----+

| 0|11227|

| 1|11256|

| 2|11237|

| 3|11247|

| 4|11173|

| 5|11269|

| 6|11178|

| 7|11413|

+----+-----+

100000 100000

```

Customer 1's 90,000 rows are now spread evenly over 8 salt values, and the salted join returns the same 100,000 rows as the plain join.

Salting has costs: the small side grows N times, the code needs extra columns, and N has to be chosen. Salting only the known hot keys (and joining the rest normally) keeps the replication small.

Other ways to reduce skew

Which technique to use

Practice

More Spark tutorials

PySpark tutorial: all lessons · All tutorials · Try the free PySpark compiler · Practice challenges