[GitHub] [spark] ulysses-you commented on a diff in pull request #41609: [SPARK-44065][SQL] Optimize BroadcastHashJoin skew in OptimizeSkewedJoin

["ulysses-you (via GitHub)" <gi...@apache.org>]

ulysses-you commented on code in PR #41609:
URL: https://github.com/apache/spark/pull/41609#discussion_r1253820018


##########
sql/core/src/main/scala/org/apache/spark/sql/execution/adaptive/OptimizeSkewedJoin.scala:
##########
@@ -215,6 +252,18 @@ case class OptimizeSkewedJoin(ensureRequirements: EnsureRequirements)
         case (newLeft, newRight) =>
           shj.copy(left = newLeft, right = newRight, isSkewJoin = true)
       }.getOrElse(shj)
+
+    case bhj @ BroadcastHashJoinExec(_, _, _, BuildRight, _,
+        ShuffleStage(left: ShuffleQueryStageExec), _, _, _) =>
+      tryOptimizeBroadcastHashJoinStreamedPlan(left).map {
+        case newLeft => bhj.copy(left = newLeft, isSkewJoin = true)
+      }.getOrElse(bhj)
+
+    case bhj @ BroadcastHashJoinExec(_, _, _, BuildLeft, _, _,
+        ShuffleStage(right: ShuffleQueryStageExec), _, _) =>

Review Comment:
   ```suggestion
           ShuffleStage(right: ShuffleQueryStageExec), _, false) =>
   ```



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/adaptive/AdaptiveQueryExecSuite.scala:
##########
@@ -2851,6 +2851,68 @@ class AdaptiveQueryExecSuite
     val unionDF = aggDf1.union(aggDf2)
     checkAnswer(unionDF.select("id").distinct, Seq(Row(null)))
   }
+
+  test("SPARK-44065: Optimize BroadcastHashJoin skew when localShuffleReader is disabled") {
+    withSQLConf(
+      SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "true",
+      SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+      SQLConf.ADAPTIVE_AUTO_BROADCASTJOIN_THRESHOLD.key -> "1000",
+      SQLConf.LOCAL_SHUFFLE_READER_ENABLED.key -> "false",
+      SQLConf.SHUFFLE_PARTITIONS.key -> "10",
+      SQLConf.SKEW_JOIN_SKEWED_PARTITION_THRESHOLD.key -> "600",
+      SQLConf.ADVISORY_PARTITION_SIZE_IN_BYTES.key -> "600") {
+      withTempView("skewData", "smallData") {
+        spark
+          .range(0, 1000, 1, 10)
+          .selectExpr("if(id >= 5, 5, id) as key1", "id as value1")
+          .createOrReplaceTempView("skewData1")
+        spark
+          .range(0, 5, 1, 10)
+          .selectExpr("id key2", "id as value2")
+          .createOrReplaceTempView("smallData")
+
+        Seq(true, false).foreach { localShuffleReader =>
+          withSQLConf(SQLConf.LOCAL_SHUFFLE_READER_ENABLED.key -> localShuffleReader.toString) {
+            val sqlText =
+              s"""
+                 |select * from skewData1 a join smallData b on a.key1 = b.key2
+                 |""".stripMargin
+            val (_, plan) = runAdaptiveAndVerifyResult(sqlText)
+            val bhjs = findTopLevelBroadcastHashJoin(plan)
+            assert(bhjs.nonEmpty)
+
+            if (localShuffleReader) {
+              val localShuffleReaders = collect(plan) {
+                case c: AQEShuffleReadExec if c.isLocalRead => c

Review Comment:
   I think there is a issue with local shuffle read. Before, the rule `OptimizeShuffleWithLocalRead#getPartitionSpecs` assume all the shuffle block are the same data size, https://github.com/apache/spark/blob/29e09473a816f1cb0ddea3e49d6dcb492ad473d9/sql/core/src/main/scala/org/apache/spark/sql/execution/adaptive/OptimizeShuffleWithLocalRead.scala#L67-L69
   but now the assumption is broken. We should update the code to consider skewed partitions, otherwise it can be still skewed when enabled local shuffle read.



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/adaptive/OptimizeSkewedJoin.scala:
##########
@@ -215,6 +252,18 @@ case class OptimizeSkewedJoin(ensureRequirements: EnsureRequirements)
         case (newLeft, newRight) =>
           shj.copy(left = newLeft, right = newRight, isSkewJoin = true)
       }.getOrElse(shj)
+
+    case bhj @ BroadcastHashJoinExec(_, _, _, BuildRight, _,
+        ShuffleStage(left: ShuffleQueryStageExec), _, _, _) =>

Review Comment:
   ```suggestion
           ShuffleStage(left: ShuffleQueryStageExec), _, _, false) =>
   ```



##########
sql/core/src/test/scala/org/apache/spark/sql/execution/adaptive/AdaptiveQueryExecSuite.scala:
##########
@@ -2851,6 +2851,68 @@ class AdaptiveQueryExecSuite
     val unionDF = aggDf1.union(aggDf2)
     checkAnswer(unionDF.select("id").distinct, Seq(Row(null)))
   }
+
+  test("SPARK-44065: Optimize BroadcastHashJoin skew when localShuffleReader is disabled") {

Review Comment:
   test name should be changed



##########
sql/core/src/main/scala/org/apache/spark/sql/execution/adaptive/OptimizeSkewedJoin.scala:
##########
@@ -198,6 +199,42 @@ case class OptimizeSkewedJoin(ensureRequirements: EnsureRequirements)
     }
   }
 
+  /**
+   * Split the skewed partition of the BroadcastHashJoin streamPlan.
+   */
+  private def tryOptimizeBroadcastHashJoinStreamedPlan(
+      shuffle: ShuffleQueryStageExec): Option[SparkPlan] = {
+    val mapStats = shuffle.mapStats.get
+    val bytesByPartitionId = mapStats.bytesByPartitionId
+    val medSize = Utils.median(bytesByPartitionId, false)
+    val skewThreshold = getSkewThreshold(medSize)
+    val streamedPlanTargetSize = targetSize(bytesByPartitionId, skewThreshold)
+
+    val newPartitionsSpec = bytesByPartitionId.indices.flatMap { reduceIndex =>

Review Comment:
   Is there existed code can be reused ? We can put it to `ShufflePartitionsUtil`



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org