apache-spark - How to REPLACE INTO using MERGE INTO for upsert in Delta Lake?

Question

The recommended way of doing an upsert in a delta table is the following.

MERGE INTO users
USING updates
ON users.userId = updates.userId
WHEN MATCHED THEN
      UPDATE SET address = updates.addresses
WHEN NOT MATCHED THEN
      INSERT (userId, address) VALUES (updates.userId, updates.address)

Here updates is a table. My question is how can we do an upsert directly, that is, without using a source table. I would like to give the values myself directly.

In SQLite, we could simply do the following.

REPLACE INTO table(column_list)
VALUES(value_list);

Is there a simple way to do that for Delta tables?

标签： apache-sparkapache-spark-sqlupsertdelta-lake

Answer 1

源表可以是子查询，因此以下内容应为您提供所需的内容。

MERGE INTO events
USING (VALUES(...)) // round brackets are required to denote a subquery
ON false            // an artificial merge condition
WHEN NOT MATCHED THEN INSERT *