Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Hard
A data engineer needs to join two very large Spark Delta tables, `customer_demographics` (10 billion rows) and `customer_preferences` (500 million rows), both partitioned by `customer_id`. The join condition is `demographics.customer_id = preferences.customer_id`. To optimize performance, they want to ensure Spark performs a hash join. Which Spark SQL hint should be applied to encourage this behavior?
- A/*+ BROADCAST(customer_demographics) */
- B/*+ SHUFFLE_HASH(preferences) */
- C/*+ REPARTITION(customer_preferences) */
- D/*+ MERGE(preferences) */
Show answer & explanationAnswer & explanation
Correct answer: B. /*+ SHUFFLE_HASH(preferences) */
The `SHUFFLE_HASH` hint explicitly tells Spark to use a shuffle hash join strategy. This is generally efficient for joining two large tables where one is significantly smaller than the other (or both are large but can fit partitions in memory after shuffling). In this scenario, while both are large, the hint directly requests the desired join strategy for potentially better performance over other shuffle joins.
Why the other options are wrong
- A. The `BROADCAST` hint (or `BROADCASTJOIN`) is used when one table is small enough to fit entirely into the memory of all executor nodes. 10 billion rows is far too large for broadcasting.
- C. The `REPARTITION` hint forces a repartitioning of the specified table but does not directly dictate the join strategy itself; it's more about data distribution.
- D. The `MERGE` hint suggests a sort-merge join, which requires sorting both sides and is often less efficient than a hash join for large, unsorted tables.
Spark SQL SHUFFLE_HASH Join Hint
A Spark SQL hint that explicitly tells the Catalyst optimizer to use a shuffle hash join strategy for the specified table(s) in a join operation.
- Forces Spark to use shuffle hash join.
- Beneficial for joining large tables.
- Requires shuffling data for both tables based on join keys.
- One table's partitions should ideally fit in memory after shuffling.
Memory trick: Hints Guide Spark's Joins: Hash for Shuffles, Broadcast for Small.