JingsongLi commented on PR #11174:
URL: https://github.com/apache/paimon/pull/11174#issuecomment-7004053118
Could we use a single native `MERGE INTO` as the baseline for this proposal?
For a fixed batch with the same business-key types as the target, the following
statement replaces all rows for each incoming key in one snapshot. I ran it on
Spark 3.5.7 with Paimon 2.2-SNAPSHOT against a Data Evolution table with
deletion vectors enabled.
```sql
MERGE INTO lake.db.target AS t
USING (
SELECT DISTINCT tenant, id, CAST(NULL AS STRING) AS payload, 'D' AS op
FROM lake.db.batch
UNION ALL
SELECT tenant, id, payload, 'I' AS op
FROM lake.db.batch
) AS s
ON s.op = 'D'
AND t.tenant = s.tenant
AND t.id = s.id
AND t.tenant = 7 -- optional target_filter, applies only to deletion
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED AND s.op = 'I' THEN
INSERT (tenant, id, payload) VALUES (s.tenant, s.id, s.payload);
```
Each distinct key produces one delete marker that can match every old row
for that key. Every insert row reaches the `NOT MATCHED` branch. In my test,
both old rows for one key disappeared, both identical incoming rows received
fresh row IDs, and the target advanced by exactly one snapshot. Old rows with a
NULL key or outside tenant 7 retained their row IDs, while their incoming
counterparts were appended.
The procedure does offer additional guarantees. It pins the Paimon source
snapshots across both preparation jobs and follows Spark's `IN (subquery)`
coercion for deletion. For example, with ANSI disabled and LEGACY assignment, I
verified that an INT key of `1` matches a STRING key of `'01'` in the equality
join above, while the positive `IN` predicate leaves that target row untouched.
Is there a representative workload that depends on those guarantees, or one
where the dedicated path materially reduces I/O or runtime? A comparison
against this statement on a wide table with a small replacement batch,
reporting target bytes read, shuffle bytes, and end-to-end time, would help us
decide. My preference is to document the SQL pattern first and review the
deletion-vector conflict fix independently, then add a public procedure if the
workload or measurements justify it.
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]