"Could not execute broadcast in 300 secs" Error: How to resolve

Error Message

query job failed due to: An error occurred while calling o78034.count. : 
org.apache.spark.SparkException: Could not execute broadcast in 300 secs. 
You can increase the timeout for broadcasts via spark.sql.broadcastTimeout or 
disable broadcast join by setting spark.sql.autoBroadcastJoinThreshold to -1 at 
org.apache.spark.sql.execution.exchange.BroadcastExchangeExec.doExecuteBroadcast
(BroadcastExchangeExec.scala:205) at .

Cause

“This is due to a limitation with Spark’s size estimator.

If the estimated size of one of the DataFrames is less than the autoBroadcastJoinThreshold, Spark may use BroadcastHashJoin to perform the join. If the available nodes do not have enough resources to accommodate the broadcast DataFrame, your job fails due to an out of memory error.” [1]

Solution

• “Use ANALYZE TABLE to collect details and compute statistics about the DataFrames before attempting a join.” [1]

• “Cache the table you are broadcasting.

  • Run explain on your join command to return the physical plan.

  • Review the physical plan.

  • If the broadcast join returns BuildLeft, cache the left side table. If the broadcast join returns BuildRight, cache the right side table.” [1]

• “In Databricks Runtime 7.0 and above, set the join type to SortMergeJoin with join hints enabled.”

• Try using a SHUFFLE_HASH hint to Spark if you see this error

[1] Broadcast join exceeds threshold, returns out of memory error