Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data engineer is optimizing a Spark SQL query that frequently joins a large `FactSales` table with a smaller `DimProduct` table. Both tables are stored in a Delta Lake. To improve join performance, the engineer wants to ensure the smaller table is broadcasted during the join operation. Which Spark SQL hint should be used for this purpose?
- A/*+ SHUFFLE_HASH(table_name) */
- B/*+ BROADCAST(table_name) */
- C/*+ MERGE(table_name) */
- D/*+ REPARTITION(n) */
Show answer & explanationAnswer & explanation
Correct answer: B. /*+ BROADCAST(table_name) */
The `BROADCAST` hint in Spark SQL is used to encourage Spark to broadcast the specified table to all worker nodes. This is particularly effective for joining a small table with a large table, as it avoids a costly shuffle of the large table.
Why the other options are wrong
- A. SHUFFLE_HASH hint suggests using a shuffle hash join strategy, which is different from a broadcast join and still involves shuffling.
- C. MERGE hint is not a standard Spark SQL hint for join strategy; it might relate to merge operations in Delta Lake but not join optimization.
- D. REPARTITION hint controls the number of partitions for the result of a query, not directly for broadcast joins.
Spark SQL BROADCAST Hint
The `BROADCAST` hint in Spark SQL is used to explicitly instruct Spark to broadcast the specified table to all executor nodes when performing a join, optimizing performance for small table joins.
- Optimizes joins between small and large tables.
- Reduces data shuffling by sending the small table to all executors.
- Syntax: `/*+ BROADCAST(table_name) */`.
Memory trick: Broadcast small tables to speed up joins.