You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@iceberg.apache.org by "vamshipasunuru (via GitHub)" <gi...@apache.org> on 2025/09/27 17:22:55 UTC
[I] Using SPJ when merge contains both match and not matched [iceberg]
vamshipasunuru opened a new issue, #14198:
URL: https://github.com/apache/iceberg/issues/14198
### Query engine
Spark
### Question
We're encountering a performance bottleneck with Iceberg UPSERT operations, specifically within the "WHEN NOT MATCHED" clause when using Spark Partially Clustered Joins (SPJ) and highly skewed data.
Our current Spark SQL configuration for SPJ is as follows:
```
"spark.sql.sources.v2.bucketing.enabled": "true"
"spark.sql.iceberg.planning.preserve-data-grouping": "true"
"spark.sql.sources.v2.bucketing.pushPartValues.enabled": "true"
"spark.sql.sources.v2.bucketing.partiallyClusteredDistribution.enabled": "true"
```
The UPSERT query performs a MERGE INTO operation between a source and a destination table. Both tables are partitioned on (bucket(256, experiment_key), bucket(32, randomization_unit_id)). The destination table is large and has a significant skew in the experiment_key column.
MERGE INTO local.default.$destTable t
USING local.default.$sourceTable s
ON t.${q("experiment_key")} = s.${q("exp_key")} AND t.${q("unit_id")} = s.${q("randomization_unit_id")}
WHEN NOT MATCHED THEN INSERT ($insertCols) VALUES ($insertVals)
The attached explain plan shows SPJ effectively optimizing the "WHEN MATCHED" part, utilizing the partially clustered feature. However, the "WHEN NOT MATCHED" clause is problematic. As discussed in https://github.com/apache/iceberg/issues/8387, this scenario requires a full shuffle because SPJ optimization cannot be applied without a mechanism for reducing keys after checks.
This limitation can lead to Out-Of-Memory (OOM) errors in cases of extreme skew. Without a shuffle or partial clustering, a single task ends up processing all data for specific bucket combinations, causing data spills and eventually OOM.
Our current workaround involves splitting the UPSERT operation into two separate queries and explicitly disabling SPJ for the "NOT MATCHED" part to prevent OOM errors. This works but I'm trying to see if there is a better solution.
<img width="3794" height="8080" alt="Image" src="https://github.com/user-attachments/assets/ec327ca0-9d25-42e7-ae4f-770d29e19993" />
--
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] Using SPJ when merge contains both match and not matched [iceberg]
Posted by "vamshipasunuru (via GitHub)" <gi...@apache.org>.
vamshipasunuru commented on issue #14198:
URL: https://github.com/apache/iceberg/issues/14198#issuecomment-3341932992
@sunchao @RussellSpitzer @aokolnychyi appreciate any ideas on 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: 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] Using SPJ when merge contains both match and not matched [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #14198:
URL: https://github.com/apache/iceberg/issues/14198#issuecomment-4139199769
This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.
--
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] Using SPJ when merge contains both match and not matched [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #14198: Using SPJ when merge contains both match and not matched
URL: https://github.com/apache/iceberg/issues/14198
--
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] Using SPJ when merge contains both match and not matched [iceberg]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #14198:
URL: https://github.com/apache/iceberg/issues/14198#issuecomment-4218844887
This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'
--
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