You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@iceberg.apache.org by "koenvo (via GitHub)" <gi...@apache.org> on 2025/03/19 12:55:18 UTC
[PR] Move actual implementation of upsert from Table to Transaction [iceberg-python]
koenvo opened a new pull request, #1817:
URL: https://github.com/apache/iceberg-python/pull/1817
<!-- Closes #1776 -->
# Rationale for this change
Previously, the **upsert** functionality was implemented at the **table** level, which meant it always initiated a new **Transaction**. This change moves the **upsert** implementation to the **Transaction** level while keeping `table.upsert(...)` as an entry point.
With this refactor, end users now have the flexibility to call **upsert** in two ways:
- `table.upsert(...)` – which still starts a new transaction.
- `transaction.upsert(...)` – allowing upserts within an existing transaction.
# Are these changes tested?
Not yet.
# Are there any user-facing changes?
Yes. This change enables users to perform **upserts within an existing transaction** using `transaction.upsert(...)`, in addition to the existing `table.upsert(...)` method.
<!-- In the case of user-facing changes, please add the changelog label. -->
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "koenvo (via GitHub)" <gi...@apache.org>.
koenvo commented on PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#issuecomment-2745302629
Agree! I will work on the test.
With "update" you mean "delete", right?
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "Fokko (via GitHub)" <gi...@apache.org>.
Fokko commented on code in PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#discussion_r2086983291
##########
pyiceberg/table/__init__.py:
##########
@@ -695,6 +695,122 @@ def delete(
if not delete_snapshot.files_affected and not delete_snapshot.rewrites_needed:
warnings.warn("Delete operation did not match any records")
+ def upsert(
+ self,
+ df: pa.Table,
+ join_cols: Optional[List[str]] = None,
+ when_matched_update_all: bool = True,
+ when_not_matched_insert_all: bool = True,
+ case_sensitive: bool = True,
+ ) -> UpsertResult:
+ """Shorthand API for performing an upsert to an iceberg table.
+
+ Args:
+
+ df: The input dataframe to upsert with the table's data.
+ join_cols: Columns to join on, if not provided, it will use the identifier-field-ids.
+ when_matched_update_all: Bool indicating to update rows that are matched but require an update due to a value in a non-key column changing
+ when_not_matched_insert_all: Bool indicating new rows to be inserted that do not match any existing rows in the table
+ case_sensitive: Bool indicating if the match should be case-sensitive
+
+ To learn more about the identifier-field-ids: https://iceberg.apache.org/spec/#identifier-field-ids
+
+ Example Use Cases:
+ Case 1: Both Parameters = True (Full Upsert)
+ Existing row found → Update it
+ New row found → Insert it
+
+ Case 2: when_matched_update_all = False, when_not_matched_insert_all = True
+ Existing row found → Do nothing (no updates)
+ New row found → Insert it
+
+ Case 3: when_matched_update_all = True, when_not_matched_insert_all = False
+ Existing row found → Update it
+ New row found → Do nothing (no inserts)
+
+ Case 4: Both Parameters = False (No Merge Effect)
+ Existing row found → Do nothing
+ New row found → Do nothing
+ (Function effectively does nothing)
+
+
+ Returns:
+ An UpsertResult class (contains details of rows updated and inserted)
+ """
+ try:
+ import pyarrow as pa # noqa: F401
+ except ModuleNotFoundError as e:
+ raise ModuleNotFoundError("For writes PyArrow needs to be installed") from e
+
+ from pyiceberg.io.pyarrow import expression_to_pyarrow
+ from pyiceberg.table import upsert_util
+
+ if join_cols is None:
+ join_cols = []
+ for field_id in self.table_metadata.schema().identifier_field_ids:
+ col = self.table_metadata.schema().find_column_name(field_id)
+ if col is not None:
+ join_cols.append(col)
+ else:
+ raise ValueError(f"Field-ID could not be found: {join_cols}")
+
+ if len(join_cols) == 0:
+ raise ValueError("Join columns could not be found, please set identifier-field-ids or pass in explicitly.")
+
+ if not when_matched_update_all and not when_not_matched_insert_all:
+ raise ValueError("no upsert options selected...exiting")
+
+ if upsert_util.has_duplicate_rows(df, join_cols):
+ raise ValueError("Duplicate rows found in source dataset based on the key columns. No upsert executed")
+
+ from pyiceberg.io.pyarrow import _check_pyarrow_schema_compatible
+
+ downcast_ns_timestamp_to_us = Config().get_bool(DOWNCAST_NS_TIMESTAMP_TO_US_ON_WRITE) or False
+ _check_pyarrow_schema_compatible(
+ self.table_metadata.schema(), provided_schema=df.schema, downcast_ns_timestamp_to_us=downcast_ns_timestamp_to_us
+ )
+
+ # get list of rows that exist so we don't have to load the entire target table
+ matched_predicate = upsert_util.create_match_filter(df, join_cols)
+
+ # We must use Transaction.table_metadata for the scan. This includes all uncommitted - but relevant - changes.
+ matched_iceberg_table = DataScan(
Review Comment:
Nice, thanks for pointing out, and also covering this in the tests 👍
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "koenvo (via GitHub)" <gi...@apache.org>.
koenvo commented on PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#issuecomment-2759386009
There is a nice edgecase here..
```python
tbl = catalog.create_table(identifier, schema=schema)
# Define exact schema: required int32 and required string
arrow_schema = pa.schema([
pa.field("id", pa.int32(), nullable=False),
pa.field("name", pa.string(), nullable=False),
])
tbl.append(pa.Table.from_pylist([{"id": 1, "name": "Alice"}], schema=arrow_schema))
df = pa.Table.from_pylist([{"id": 2, "name": "Bob"}, {"id": 1, "name": "Alicia"}], schema=arrow_schema)
with tbl.transaction() as txn:
txn.upsert(df, join_cols=["id"])
# This will re-insert Bob, instead of reading the uncommitted changes and ignore Bob
txn.upsert(df, join_cols=["id"])
```
@Fokko should it be possible to read uncommitted changes?
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "koenvo (via GitHub)" <gi...@apache.org>.
koenvo commented on code in PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#discussion_r2086299262
##########
tests/table/test_upsert.py:
##########
@@ -709,3 +709,65 @@ def test_upsert_with_nulls(catalog: Catalog) -> None:
],
schema=schema,
)
+
+
+def test_transaction(catalog: Catalog) -> None:
+ """Test the upsert within a Transaction. Make sure that if something fails the entire Transaction is
+ rolled back."""
+ identifier = "default.test_merge_source_dups"
+ _drop_table(catalog, identifier)
+
+ ctx = SessionContext()
+
+ table = gen_target_iceberg_table(1, 10, False, ctx, catalog, identifier)
+ df_before_transaction = table.scan().to_arrow()
+
+ source_df = gen_source_dataset(5, 15, False, True, ctx)
+
+ with pytest.raises(Exception, match="Duplicate rows found in source dataset based on the key columns. No upsert executed"):
+ with table.transaction() as tx:
+ tx.delete(delete_filter=AlwaysTrue())
+ tx.upsert(df=source_df, join_cols=["order_id"])
+
+ df = table.scan().to_arrow()
+
+ assert df_before_transaction == df
+
+
+# @pytest.mark.skip("This test is just for reference. Multiple upserts or delete+upsert doesn't work in a transaction")
+def test_transaction_multiple_upserts(catalog: Catalog) -> None:
+ identifier = "default.test_multi_upsert"
+ _drop_table(catalog, identifier)
+
+ schema = Schema(
+ NestedField(1, "id", IntegerType(), required=True),
+ NestedField(2, "name", StringType(), required=True),
+ identifier_field_ids=[1],
+ )
+
+ tbl = catalog.create_table(identifier, schema=schema)
+
+ # Define exact schema: required int32 and required string
+ arrow_schema = pa.schema(
+ [
+ pa.field("id", pa.int32(), nullable=False),
+ pa.field("name", pa.string(), nullable=False),
+ ]
+ )
+
+ tbl.append(pa.Table.from_pylist([{"id": 1, "name": "Alice"}], schema=arrow_schema))
+
+ df = pa.Table.from_pylist([{"id": 2, "name": "Bob"}, {"id": 1, "name": "Alicia"}], schema=arrow_schema)
+
+ with tbl.transaction() as txn:
+ txn.delete(delete_filter="id = 1")
+ txn.append(df)
+
+ # This should read the uncommitted changes
Review Comment:
Test uncommitted changes are read
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "Fokko (via GitHub)" <gi...@apache.org>.
Fokko commented on PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#issuecomment-2813868016
> @Fokko should it be possible to read uncommitted changes?
Yes, it should. If you do a subsequent upsert with the same data, it should be a no-op. This should be the case today, otherwise, https://github.com/apache/iceberg-python/pull/1903 will fix 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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "kevinjqliu (via GitHub)" <gi...@apache.org>.
kevinjqliu commented on PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#issuecomment-2816813052
now that #1903 is merged, could you rebase this PR?
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "koenvo (via GitHub)" <gi...@apache.org>.
koenvo commented on code in PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#discussion_r2086298291
##########
pyiceberg/table/__init__.py:
##########
@@ -695,6 +695,122 @@ def delete(
if not delete_snapshot.files_affected and not delete_snapshot.rewrites_needed:
warnings.warn("Delete operation did not match any records")
+ def upsert(
+ self,
+ df: pa.Table,
+ join_cols: Optional[List[str]] = None,
+ when_matched_update_all: bool = True,
+ when_not_matched_insert_all: bool = True,
+ case_sensitive: bool = True,
+ ) -> UpsertResult:
+ """Shorthand API for performing an upsert to an iceberg table.
+
+ Args:
+
+ df: The input dataframe to upsert with the table's data.
+ join_cols: Columns to join on, if not provided, it will use the identifier-field-ids.
+ when_matched_update_all: Bool indicating to update rows that are matched but require an update due to a value in a non-key column changing
+ when_not_matched_insert_all: Bool indicating new rows to be inserted that do not match any existing rows in the table
+ case_sensitive: Bool indicating if the match should be case-sensitive
+
+ To learn more about the identifier-field-ids: https://iceberg.apache.org/spec/#identifier-field-ids
+
+ Example Use Cases:
+ Case 1: Both Parameters = True (Full Upsert)
+ Existing row found → Update it
+ New row found → Insert it
+
+ Case 2: when_matched_update_all = False, when_not_matched_insert_all = True
+ Existing row found → Do nothing (no updates)
+ New row found → Insert it
+
+ Case 3: when_matched_update_all = True, when_not_matched_insert_all = False
+ Existing row found → Update it
+ New row found → Do nothing (no inserts)
+
+ Case 4: Both Parameters = False (No Merge Effect)
+ Existing row found → Do nothing
+ New row found → Do nothing
+ (Function effectively does nothing)
+
+
+ Returns:
+ An UpsertResult class (contains details of rows updated and inserted)
+ """
+ try:
+ import pyarrow as pa # noqa: F401
+ except ModuleNotFoundError as e:
+ raise ModuleNotFoundError("For writes PyArrow needs to be installed") from e
+
+ from pyiceberg.io.pyarrow import expression_to_pyarrow
+ from pyiceberg.table import upsert_util
+
+ if join_cols is None:
+ join_cols = []
+ for field_id in self.table_metadata.schema().identifier_field_ids:
+ col = self.table_metadata.schema().find_column_name(field_id)
+ if col is not None:
+ join_cols.append(col)
+ else:
+ raise ValueError(f"Field-ID could not be found: {join_cols}")
+
+ if len(join_cols) == 0:
+ raise ValueError("Join columns could not be found, please set identifier-field-ids or pass in explicitly.")
+
+ if not when_matched_update_all and not when_not_matched_insert_all:
+ raise ValueError("no upsert options selected...exiting")
+
+ if upsert_util.has_duplicate_rows(df, join_cols):
+ raise ValueError("Duplicate rows found in source dataset based on the key columns. No upsert executed")
+
+ from pyiceberg.io.pyarrow import _check_pyarrow_schema_compatible
+
+ downcast_ns_timestamp_to_us = Config().get_bool(DOWNCAST_NS_TIMESTAMP_TO_US_ON_WRITE) or False
+ _check_pyarrow_schema_compatible(
+ self.table_metadata.schema(), provided_schema=df.schema, downcast_ns_timestamp_to_us=downcast_ns_timestamp_to_us
+ )
+
+ # get list of rows that exist so we don't have to load the entire target table
+ matched_predicate = upsert_util.create_match_filter(df, join_cols)
+
+ # We must use Transaction.table_metadata for the scan. This includes all uncommitted - but relevant - changes.
+ matched_iceberg_table = DataScan(
Review Comment:
Most important change. Required https://github.com/apache/iceberg-python/pull/1903
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "Fokko (via GitHub)" <gi...@apache.org>.
Fokko merged PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "mattmartin14 (via GitHub)" <gi...@apache.org>.
mattmartin14 commented on PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#issuecomment-2745289992
I think since the transaction wrapper has been moved out, there should be a unit test added to do partial upsert and then throw an error and ensure the rollback occurs and we are not left in a state where a partial upsert succeeded.
Example:
- start an upsert
- let the update succeed
- force an error on the insert component
- rollback the transaction and make sure the update did not persist
Just my thoughts 😃. Thanks,
Matt
--
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: [PR] Move implementation of upsert from Table to Transaction [iceberg-python]
Posted by "mattmartin14 (via GitHub)" <gi...@apache.org>.
mattmartin14 commented on PR #1817:
URL: https://github.com/apache/iceberg-python/pull/1817#issuecomment-2755026682
> Agree! I will work on the test.
>
> With "update" you mean "delete", right?
Hey sorry; just saw this; when i mean update, i mean it invokes an "overwrite" operation, which i believe is what delete's also trigger under the covers. 😀
--
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