How to merge correctly?

[rangp]

I am missing documentation on how merging works with DeltaLake.DotNet.

I looked at the delta-rs documentation and there it seems that the sdk uses some sort of merge query builder, e.g.

DeltaOps(table)
    .merge(source_data, "target.x = source.x")
    .with_source_alias("source")
    .with_target_alias("target")
    .when_matched_update(|update| 
        update
        .update("x", "source.x")
        .update("y", "source.y"))?
    .await?;

Since this library says it is a rust port, I would expect a similar behavior, but the only method I could find in the ITable interface

Task MergeAsync(string query, IReadOnlyCollection<RecordBatch> records, Schema schema, CancellationToken cancellationToken);

only returns a task. I suspect there is some magic hidden in the query, but I have no idea how to write it correctly. Especially how aliases are defined. Are source and target some magic keywords that I can use without defining them?

I would appreciate some guidance/documentation on how to performe a simple upsert via merge. Thank you!

[mightyshazam]

The best example of how to merge is in MergeTests.cs. Basically, it works by passing a collection of record batches corresponding to the data you want to upsert, the schema for that collection, and a sql statement representing the merge statement.

[rangp]

Thanks for the insights, that helps.

Two follow up questions about the test cases in MergeTests.cs:

1. I assume newdata is the alias defined for the record batch that is merged. Can it be set to whatever name I prefer with the USING statement?
2. I also assume that mytable must correspond to the name of the table I created earlier. Correct?

MERGE INTO mytable USING newdata
        ON newdata.test = mytable.test
        WHEN MATCHED THEN
          UPDATE SET
            second = newdata.second,
            third = newdata.third
        WHEN NOT MATCHED BY SOURCE THEN DELETE
        WHEN NOT MATCHED BY TARGET
          THEN INSERT (
            test,
            second,
            third
          )
          VALUES (
            newdata.test,
            'inserted data',
            99
          )

I will give it a try and see whether it works

[rangp]

Okay, I think I figured it out. Will just leave this here for posterity.

For an upsert logic the following statement seems to work:

MERGE INTO tableName as tableAlias USING recordBatchAlias
ON tableAlias.test = recordBatchAlias.test (AND ...)
WHEN MATCHED THEN
UPDATE SET
second = recordBatchAlias.second,
.. list all column names to be updated here
WHEN NOT MATCHED BY TARGET
THEN INSERT (
.. list all column names here
)
VALUES (
recordBatchAlias.test
.. list all column names here prefixed with recordBatchAlias
)

tableAlias / recordBatchAlias can be freely defined

tableName needs to correspond to the name of the table and needs to be escaped using backticks ` when it contains special characters

You need to list all column names explicitly. UPDATE SET * or INSERT SET * does not work, despite it being mentioned in the delta lake documentation: https://docs.databricks.com/gcp/en/delta/merge

VALUES also needs to contain all columns names again in the right order or constant values / functions if you want to insert something that is not in the recordBatch.

It would be nice to have some sort of query builder fluent api for this stuff, as writing the query is pretty wordy and not super maintainable in code, as you need to use a lot of magic strings here. I just wrote a hacky query builder for my own purposes and can imagine it would be a great addition to this SDK.