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

--
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