You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@iceberg.apache.org by "mrbrahman (via GitHub)" <gi...@apache.org> on 2024/04/29 22:24:30 UTC

[I] Getting storage partitioned join to work [iceberg]

mrbrahman opened a new issue, #10250:
URL: https://github.com/apache/iceberg/issues/10250

   ### Query engine
   
   Spark on AWS EMR 6.15 
   
   ### Question
   
   Trying to get Storage Partitioned join to work in a simple test case, but not successful. I followed most of the settings mentioned in #7832, but still not able to get it to work.
   
   Here's what I did:
   
   ~~~scala
   val df = spark.range(0,1000000)
   
   val a = df.repartition(10).withColumn("part", spark_partition_id)
   a.write.partitionBy("part").format("iceberg").saveAsTable("ice1")
   a.write.partitionBy("part").format("iceberg").saveAsTable("ice2")
   
   ~~~
   
   ~~~sql
   %sql
   set spark.sql.autoBroadcastJoinThreshold = -1;
   
   set spark.sql.adaptive.enabled = false;
   set spark.sql.sources.bucketing.enabled = true;
   set spark.sql.sources.v2.bucketing.enabled=true;
   -- set "spark.sql.iceberg.planning.preserve-data-grouping=true";  -- this didn't work for me
   set spark.sql.sources.v2.bucketing.pushPartValues.enabled=true;
   set spark.sql.requireAllClusterKeysForCoPartition=false;
   set spark.sql.sources.v2.bucketing.partiallyClusteredDistribution.enabled=true;
   
   
   create table ice_joined_9 using iceberg
   select a.id id1, b.id id2
   from ice1 a
     inner join ice2 b on a.id=b.id and a.part=b.part
   ~~~
   The DAG is still showing exchange + sort
   
   ![spj not working](https://github.com/apache/iceberg/assets/16898939/1cbad73d-7c5e-40e5-93df-6489eadd569c)
   


-- 
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@iceberg.apache.org.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org


Re: [I] Getting storage partitioned join to work [iceberg]

Posted by "mrbrahman (via GitHub)" <gi...@apache.org>.
mrbrahman commented on issue #10250:
URL: https://github.com/apache/iceberg/issues/10250#issuecomment-2084281224

   The problem was the commented out parameter. Apparently I had to set it thus:
   
   ~~~sql
   set `spark.sql.iceberg.planning.preserve-data-grouping` = true;
   ~~~
   
   Once that was set, Spark nicely avoided the shuffle!
   


-- 
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@iceberg.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org


Re: [I] Getting storage partitioned join to work [iceberg]

Posted by "soumilshah1995 (via GitHub)" <gi...@apache.org>.
soumilshah1995 commented on issue #10250:
URL: https://github.com/apache/iceberg/issues/10250#issuecomment-3218114973

   Hi there I tried above example its still doing sort merge join 
   ```
   from pyspark.sql import SparkSession
   from pyspark.sql.functions import spark_partition_id
   import os,sys
   
   # ----------------------------
   # Environment
   # ----------------------------
   os.environ["JAVA_HOME"] = "/opt/homebrew/opt/openjdk@11"
   
   ICEBERG_VERSION = "1.5.0"  # SPJ support
   SPARK_VERSION = "3.4"
   
   SUBMIT_ARGS = f"--packages org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:{ICEBERG_VERSION} pyspark-shell"
   os.environ["PYSPARK_SUBMIT_ARGS"] = SUBMIT_ARGS
   os.environ['PYSPARK_PYTHON'] = sys.executable
   
   warehouse_path = "/Users/soumilshah/Desktop/warehouse"
   
   # ----------------------------
   # Spark Session
   # ----------------------------
   spark = SparkSession.builder \
       .appName("IcebergSPJExample") \
       .config("spark.sql.extensions", "org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions") \
       .config("spark.sql.catalog.dev", "org.apache.iceberg.spark.SparkCatalog") \
       .config("spark.sql.catalog.dev.type", "hadoop") \
       .config("spark.sql.catalog.dev.warehouse", warehouse_path) \
       .config("spark.sql.join.preferSortMergeJoin", "false") \
       .config("spark.sql.sources.v2.bucketing.enabled", "true") \
       .config("spark.sql.sources.v2.bucketing.pushPartValues.enabled", "true") \
       .config("spark.sql.iceberg.planning.preserve-data-grouping", "true") \
       .config("spark.sql.requireAllClusterKeysForCoPartition", "false") \
       .config("spark.sql.autoBroadcastJoinThreshold", "-1") \
       .config("spark.sql.sources.v2.bucketing.partiallyClusteredDistribution.enabled", "true") \
       .getOrCreate()
   
   
   # ----------------------------
   # Create two tables with same bucket partitioning
   # ----------------------------
   df = spark.range(0, 100_000)  # simple numeric dataset
   
   # Add partition column based on Spark partition id
   df_a = df.repartition(10).withColumn("part", spark_partition_id())
   df_b = df.repartition(10).withColumn("part", spark_partition_id())
   
   # Write table A
   df_a.write.partitionBy("part").format("iceberg").mode("overwrite").saveAsTable("dev.ice1")
   
   # Write table B
   df_b.write.partitionBy("part").format("iceberg").mode("overwrite").saveAsTable("dev.ice2")
   
   spark.sql("SET spark.sql.autoBroadcastJoinThreshold=-1")
   spark.sql("SET spark.sql.join.preferSortMergeJoin=false")
   spark.sql("SET `spark.sql.sources.v2.bucketing.enabled`=true")
   spark.sql("SET `spark.sql.sources.v2.bucketing.pushPartValues.enabled`=true")
   spark.sql("SET `spark.sql.iceberg.planning.preserve-data-grouping`=true")
   spark.sql("SET `spark.sql.requireAllClusterKeysForCoPartition`=false")
   spark.sql("SET `spark.sql.sources.v2.bucketing.partiallyClusteredDistribution.enabled`=true")
   
   
   spark.sql("""
   SELECT a.id AS id1, b.id AS id2
   FROM dev.ice1 a
   JOIN dev.ice2 b
   ON a.id = b.id AND a.part = b.part
   LIMIT 20
   """).show()
   ```
   
   
   <img width="551" height="620" alt="Image" src="https://github.com/user-attachments/assets/d1560a63-0cd6-4a41-9034-f6424f6c96a2" />


-- 
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@iceberg.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org


Re: [I] Getting storage partitioned join to work [iceberg]

Posted by "mrbrahman (via GitHub)" <gi...@apache.org>.
mrbrahman closed issue #10250: Getting storage partitioned join to work
URL: https://github.com/apache/iceberg/issues/10250


-- 
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@iceberg.apache.org

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


---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@iceberg.apache.org
For additional commands, e-mail: issues-help@iceberg.apache.org