Broadcast Join in Spark SQL

0
votes

I am new to Spark and have a question around Broadcast Joins. We use Spark 2.4.0 and use Spark Temporary Views for data transformations -

create temporary view product as
select /*+ BROADCAST(b) */
a.custid, b.prodid
from cust a
join prod b
on a.prodid = b.prodid

I know there is a parameter for broadcast joins spark.sql.autoBroadcastJoinThreshold which has a value of 10 i.e. 10MB. But, I also read somewhere that the maximum size of a broadcast table could be 8GB. What is the significance of these two values ?

For broadcasting a table/view, does the size always have to be in MB or, is it also possible to broadcast (with a hint) a table/view of 5GB (for example) ? In that case, do I have to manipulate the value of the parameter spark.sql.autoBroadcastJoinThreshold by setting it to a higher value (=5120 i.e. 5GB) ? Or, will it allow me to broadcast the table/view since it is below the max limit (i.e. 8GB) ?

If I want to broadcast a table (in GBs) is it recommended from a query performance perspective ?

Any help is appreciated.

Thanks

1
apache-spark-sql

1 Answers

1
votes

  • If you do not explicitly state a broadcast via hint in the SQL of via DF syntax, then for tables of which stats are known up to size 10MB, Catalyst may elect to utilize a broadcast join approach.

  • If you do explicitly state a broadcast join, then if the table size exceeds 8GB, Catalyst will ignore and use another join strategy over the broadcast join.

  • More formally:

If a broadcast hint is specified, the join side with the hint will be broadcasted irrespective of autoBroadcastJoinThreshold. If both sides of the join have the broadcast hints, the side with a smaller estimated physical size will be broadcasted. If there is no hint and if the estimated physical size of the table < autoBroadcastJoinThreshold then that table is broadcasted to all the executor nodes.

Should I broadcast" yes, if it means the query is faster and there are enough resources (to share).