You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@sedona.apache.org by GitBox <gi...@apache.org> on 2022/11/11 14:40:28 UTC

[GitHub] [incubator-sedona] tanelk opened a new pull request, #711: [WIP][SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

tanelk opened a new pull request, #711:
URL: https://github.com/apache/incubator-sedona/pull/711

   
   ## Did you read the Contributor Guide?
   
   - Yes, I have read [Contributor Rules](https://sedona.apache.org/community/rule/) and [Contributor Development Guide](https://sedona.apache.org/community/develop/)
   
   ## Is this PR related to a JIRA ticket?
   
   - Yes, the URL of the assoicated JIRA ticket is https://issues.apache.org/jira/browse/SEDONA-191. The PR name follows the format `[SEDONA-XXX] my subject`.
   
   ## What changes were proposed in this PR?
   Extended the `BroadcastIndexJoinExec` to support left semi, left anti, left outer and right outer joins if there is a broadcast hint on correct side. Currently those joins are run with Sparks `BroadcastNestedLoopJoinExec`, that can be much slower.
   
   A lot of the code is based on `org.apache.spark.sql.execution.joins.HashJoin`.
   
   ## How was this patch tested?
   New unittests
   
   ## Did this PR include necessary documentation updates?
   
   - No, this PR does not affect any public API so no need to change the docs.
   
   
   
   


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] jiayuasu commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
jiayuasu commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1320358302

   @Kimahriman Adam, would you please share some insights about this?


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on a diff in pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on code in PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#discussion_r1027312994


##########
sql/src/main/scala/org/apache/spark/sql/sedona_sql/strategy/join/JoinQueryDetector.scala:
##########
@@ -206,14 +223,32 @@ class JoinQueryDetector(sparkSession: SparkSession) extends Strategy {
     }
   }
 
-  private def planBroadcastJoin(left: LogicalPlan,
-                                right: LogicalPlan,
-                                children: Seq[Expression],
-                                spatialPredicate: SpatialPredicate,
-                                indexType: IndexType,
-                                broadcastLeft: Boolean,
-                                extraCondition: Option[Expression],
-                                distance: Option[Expression]): Seq[SparkPlan] = {
+  private def planBroadcastJoin(
+    left: LogicalPlan,
+    right: LogicalPlan,
+    children: Seq[Expression],
+    joinType: JoinType,
+    spatialPredicate: SpatialPredicate,
+    indexType: IndexType,
+    broadcastLeft: Boolean,
+    broadcastRight: Boolean,
+    extraCondition: Option[Expression],
+    distance: Option[Expression]): Seq[SparkPlan] = {
+
+    val broadcastSide = joinType match {
+      case Inner if broadcastLeft => Some(LeftSide)
+      case Inner if broadcastRight => Some(RightSide)
+      case LeftSemi if broadcastRight => Some(RightSide)
+      case LeftAnti if broadcastRight => Some(RightSide)
+      case LeftOuter if broadcastRight => Some(RightSide)
+      case RightOuter if broadcastLeft => Some(LeftSide)

Review Comment:
   I could not figure out how to easily implement other combinations



-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] jiayuasu merged pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
jiayuasu merged PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] Kimahriman commented on a diff in pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
Kimahriman commented on code in PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#discussion_r1027163220


##########
sql/src/main/scala/org/apache/spark/sql/sedona_sql/strategy/join/JoinQueryDetector.scala:
##########
@@ -206,14 +223,32 @@ class JoinQueryDetector(sparkSession: SparkSession) extends Strategy {
     }
   }
 
-  private def planBroadcastJoin(left: LogicalPlan,
-                                right: LogicalPlan,
-                                children: Seq[Expression],
-                                spatialPredicate: SpatialPredicate,
-                                indexType: IndexType,
-                                broadcastLeft: Boolean,
-                                extraCondition: Option[Expression],
-                                distance: Option[Expression]): Seq[SparkPlan] = {
+  private def planBroadcastJoin(
+    left: LogicalPlan,
+    right: LogicalPlan,
+    children: Seq[Expression],
+    joinType: JoinType,
+    spatialPredicate: SpatialPredicate,
+    indexType: IndexType,
+    broadcastLeft: Boolean,
+    broadcastRight: Boolean,
+    extraCondition: Option[Expression],
+    distance: Option[Expression]): Seq[SparkPlan] = {
+
+    val broadcastSide = joinType match {
+      case Inner if broadcastLeft => Some(LeftSide)
+      case Inner if broadcastRight => Some(RightSide)
+      case LeftSemi if broadcastRight => Some(RightSide)
+      case LeftAnti if broadcastRight => Some(RightSide)
+      case LeftOuter if broadcastRight => Some(RightSide)
+      case RightOuter if broadcastLeft => Some(LeftSide)

Review Comment:
   What's the limitation of doing the other jointype/broadcast side combinations? I understand maybe less utility in them, but does enabling them add complexity?



-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1325372291

   Added additional tests in https://github.com/apache/incubator-sedona/pull/711/commits/2624db496d34621c5e55ad287752adec7564f12e


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] jiayuasu commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
jiayuasu commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1327999492

   Thanks for your contribution!


-- 
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: dev-unsubscribe@sedona.apache.org

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


Re: [PR] [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec [sedona]

Posted by "jiayuasu (via GitHub)" <gi...@apache.org>.
jiayuasu commented on PR #711:
URL: https://github.com/apache/sedona/pull/711#issuecomment-3589998065

   Sorry just replied


-- 
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: issues-unsubscribe@sedona.apache.org

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


Re: [PR] [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec [sedona]

Posted by "michelelaporta (via GitHub)" <gi...@apache.org>.
michelelaporta commented on PR #711:
URL: https://github.com/apache/sedona/pull/711#issuecomment-3589993419

   thanks [petern48](https://github.com/petern48) any example usage?  I post a discussion with a sample here https://github.com/apache/sedona/discussions/2520 by nobody has answer yet.


-- 
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: issues-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1321202514

   > Looks good to me. Only other comment would be if the tests could be parameterized at all so to dedupe some of it, and maybe verify the schema and at least one row of data to make sure the output is correct and we don't run into a similar problem as #706
   
    I'll improve the test coverage in few days


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1320173155

   @Kimahriman you introduced broadcast joins here, perhaps you are the best person to take a look at this improvement.
   Also pinging @jiayuasu - I think this is ready for review now


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] Kimahriman commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
Kimahriman commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1321191422

   Looks good to me. Only other comment would be if the tests could be parameterized at all so to dedupe some of it, and maybe verify the schema and at least one row of data to make sure the output is correct and we don't run into a similar problem as https://github.com/apache/incubator-sedona/pull/706


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] Kimahriman commented on a diff in pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
Kimahriman commented on code in PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#discussion_r1027323371


##########
sql/src/main/scala/org/apache/spark/sql/sedona_sql/strategy/join/JoinQueryDetector.scala:
##########
@@ -206,14 +223,32 @@ class JoinQueryDetector(sparkSession: SparkSession) extends Strategy {
     }
   }
 
-  private def planBroadcastJoin(left: LogicalPlan,
-                                right: LogicalPlan,
-                                children: Seq[Expression],
-                                spatialPredicate: SpatialPredicate,
-                                indexType: IndexType,
-                                broadcastLeft: Boolean,
-                                extraCondition: Option[Expression],
-                                distance: Option[Expression]): Seq[SparkPlan] = {
+  private def planBroadcastJoin(
+    left: LogicalPlan,
+    right: LogicalPlan,
+    children: Seq[Expression],
+    joinType: JoinType,
+    spatialPredicate: SpatialPredicate,
+    indexType: IndexType,
+    broadcastLeft: Boolean,
+    broadcastRight: Boolean,
+    extraCondition: Option[Expression],
+    distance: Option[Expression]): Seq[SparkPlan] = {
+
+    val broadcastSide = joinType match {
+      case Inner if broadcastLeft => Some(LeftSide)
+      case Inner if broadcastRight => Some(RightSide)
+      case LeftSemi if broadcastRight => Some(RightSide)
+      case LeftAnti if broadcastRight => Some(RightSide)
+      case LeftOuter if broadcastRight => Some(RightSide)
+      case RightOuter if broadcastLeft => Some(LeftSide)

Review Comment:
   Ah yeah I see now how the outer logic only works in one direction



-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on a diff in pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on code in PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#discussion_r1026562679


##########
sql/src/main/scala/org/apache/spark/sql/sedona_sql/strategy/join/JoinQueryDetector.scala:
##########
@@ -206,14 +223,32 @@ class JoinQueryDetector(sparkSession: SparkSession) extends Strategy {
     }
   }
 
-  private def planBroadcastJoin(left: LogicalPlan,
-                                right: LogicalPlan,
-                                children: Seq[Expression],
-                                spatialPredicate: SpatialPredicate,
-                                indexType: IndexType,
-                                broadcastLeft: Boolean,
-                                extraCondition: Option[Expression],
-                                distance: Option[Expression]): Seq[SparkPlan] = {
+  private def planBroadcastJoin(
+    left: LogicalPlan,
+    right: LogicalPlan,
+    children: Seq[Expression],
+    joinType: JoinType,
+    spatialPredicate: SpatialPredicate,
+    indexType: IndexType,
+    broadcastLeft: Boolean,
+    broadcastRight: Boolean,
+    extraCondition: Option[Expression],
+    distance: Option[Expression]): Seq[SparkPlan] = {
+
+    val broadcastSide = joinType match {

Review Comment:
   I'll cover `LeftExistence` in a followup - it's more tedious to implement and test.



-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on pull request #711: [WIP][SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1311775314

   This requires more testing before it can be merged. Just wanted to get the draft up.


-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] tanelk commented on a diff in pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
tanelk commented on code in PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#discussion_r1026563488


##########
sql/src/test/scala/org/apache/sedona/sql/BroadcastIndexJoinSuite.scala:
##########
@@ -19,37 +19,42 @@
 
 package org.apache.sedona.sql
 
+import org.apache.spark.sql.execution.joins.BroadcastNestedLoopJoinExec
 import org.apache.spark.sql.sedona_sql.strategy.join.BroadcastIndexJoinExec
 import org.apache.spark.sql.functions._
 
 class BroadcastIndexJoinSuite extends TestBaseScala {
 
-  describe("Sedona-SQL Broadcast Index Join Test") {
+  describe("Sedona-SQL Broadcast Index Join Test for inner joins") {
 
     // Using UDFs rather than lit prevents optimizations that would circumvent the checks we want to test
-    val one = udf(() => 1)
-    val two = udf(() => 2)
+    val one = udf(() => 1).asNondeterministic()

Review Comment:
   Added nondeterminist so the optimizer could modify the plans even less



-- 
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: dev-unsubscribe@sedona.apache.org

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


[GitHub] [incubator-sedona] Kimahriman commented on pull request #711: [SEDONA-191] Support join types other than inner in BroadcastIndexJoinExec

Posted by GitBox <gi...@apache.org>.
Kimahriman commented on PR #711:
URL: https://github.com/apache/incubator-sedona/pull/711#issuecomment-1320893645

   Yeah I peaked at this when it first was put up, I'll look through again with the latest updates 


-- 
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: dev-unsubscribe@sedona.apache.org

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