Allow user to define a subset of columns for update detection in UPSERT
Nobody has claimed this yet.
Assessment
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Newbie friendliness
- 55/100
- Issue type
- Feature
- Clarity
- Mostly clear
- Activity status
- Quiet
- Tech stack
- python
- Domain
- data-engineering, databases
Research direction
Start by inspecting get_rows_to_update and the upsert methods, then compare the proposed behavior with commit 59d18337a61b106566c2e6a432b7d4899ca7f334. Add optional difference_cols handling so selected non-primary-key columns drive update detection, and confirm the chosen behavior when no requested columns intersect the available columns.
Written by the indexing model from the issue text.
Description
Feature Request / Improvement
Currently, when detecting which rows should be updated in the upsert, all non-primary key columns are iterated over, converted to a Python type, and compared. This can be time-consuming and memory-consuming (e.g., with complex columns containing JSON data in struct, list, ...).
If the user knows that a change has occurred to specific columns, it would be a huge performance improvement to just iterate over these columns. Or if the user has a column that implies that any change has occurred (e.g., a hash of the data)
For example:
Hash column: if the user has the possibility to create a column containing a hash for each row, then upsert has to look only at this column when detecting changes. This way, pyiceberg doesn't have to convert all columns to Python type and compare them.
Proposition:
Update the function get_rows_to_update to also accept an optional parameter difference_cols. Update the upsert methods with this parameter and pass it to the get_rows_to_update.
In case there is no intersection between non-primary key columns and difference_cols, pyiceberg can either raise and error or it can fall-back to the default behaviour (iterating over all non-PK columns).
Usage:
from pyiceberg.schema import Schema
from pyiceberg.types import IntegerType, NestedField, StringType
import pyarrow as pa
schema = Schema(
NestedField(1, "city", StringType(), required=True),
NestedField(2, "inhabitants", IntegerType(), required=True),
# Mark City as the identifier field, also known as the primary-key
identifier_field_ids=[1]
)
tbl = catalog.create_table("default.cities", schema=schema)
arrow_schema = pa.schema(
[
pa.field("city", pa.string(), nullable=False),
pa.field("inhabitants", pa.int32(), nullable=False),
]
)
# Write some data
df = pa.Table.from_pylist(
[
{"city": "Amsterdam", "inhabitants": 921402},
{"city": "San Francisco", "inhabitants": 808988},
{"city": "Drachten", "inhabitants": 45019},
{"city": "Paris", "inhabitants": 2103000},
],
schema=arrow_schema
)
tbl.append(df)
df = pa.Table.from_pylist(
[
# Will be updated, the inhabitants has been updated
{"city": "Drachten", "inhabitants": 45505},
# New row, will be inserted
{"city": "Berlin", "inhabitants": 3432000},
# Ignored, already exists in the table
{"city": "Paris", "inhabitants": 2103000},
],
schema=arrow_schema
)
upd = tbl.upsert(df, difference_cols=["inhabitants"])
I have already prepared how it can look in my fork 59d18337a61b106566c2e6a432b7d4899ca7f334.
If the proposition is accepted, I can prepare the PR.
- Dominant language
- Python
- Stars
- 1.1k
- Forks
- 589
- Avg merge
- 2d 4h
- Merged PRs (30d)
- 72
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from apache/iceberg-python
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
apache/iceberg-python#3996 ·
-
bug
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
apache/iceberg-python#3979 ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
apache/iceberg-python#3885 ·
-
[Bug] PyArrowFileIO fails to propagate s3.ssl.ca-cert to pyarrow.fs.S3FileSystem tls_ca_file_path Open
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
apache/iceberg-python#3866 · 1 comment ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
apache/iceberg-python#3836 · 1 comment ·
All issues in apache/iceberg-python
Similar issues
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
syfoud/Simulated_Scepter#172 ·
-
A cancelled tests run makes the coverage comment workflow fail and reports it as a red check on main Openarea: ci bug perceived difficulty: 3
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
Nitjsefnie-Harness-Commons/daedalus#921 · 1 comment ·
-
Difficulty 2/5 1-3 hours Newbie friendliness 86/100
EleutherAI/lm-evaluation-harness#4207 ·
-
Difficulty 1/5 Under an hour Newbie friendliness 92/100
-
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
ClickHouse/clickhouse-connect#1057 ·