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]

Reply via email to