Snowpark Migration Accelerator: Zusammenführung¶
Beschreibung¶
Die MERGE-Anweisung kombiniert Daten aus einer oder mehreren Quelltabellen mit einer Zieltabelle, so dass Sie Aktualisierungen und Einfügungen in einem einzigen Vorgang durchführen können. Anhand der von Ihnen definierten Bedingungen bestimmt es, ob bestehende Zeilen aktualisiert oder neue Zeilen in die Zieltabelle eingefügt werden sollen. Dies ist effizienter als die Verwendung separater INSERT, UPDATE und DELETE- Anweisungen. Die MERGE-Anweisung liefert immer konsistente Ergebnisse, wenn sie mehrmals mit denselben Daten ausgeführt wird.
In Spark finden Sie die MERGE-Syntax in der Spark-Dokumentation.
MERGE INTO target_table_name [target_alias]
USING source_table_reference [source_alias]
ON merge_condition
{ WHEN MATCHED [ AND matched_condition ] THEN matched_action |
WHEN NOT MATCHED [BY TARGET] [ AND not_matched_condition ] THEN not_matched_action |
WHEN NOT MATCHED BY SOURCE [ AND not_matched_by_source_condition ] THEN not_matched_by_source_action } [...]
matched_action
{ DELETE |
UPDATE SET * |
UPDATE SET { column = { expr | DEFAULT } } [, ...] }
not_matched_action
{ INSERT * |
INSERT (column1 [, ...] ) VALUES ( expr | DEFAULT ] [, ...] )
not_matched_by_source_action
{ DELETE |
UPDATE SET { column = { expr | DEFAULT } } [, ...] }
In Snowflake folgt die MERGE-Anweisung dieser Syntax (Weitere Einzelheiten finden Sie in der Snowflake-Dokumentation):
MERGE INTO <target_table> USING <source> ON <join_expr> { matchedClause | notMatchedClause } [ ... ]
matchedClause ::=
WHEN MATCHED [ AND <case_predicate> ] THEN { UPDATE SET <col_name> = <expr> [ , <col_name2> = <expr2> ... ] | DELETE } [ ... ]
notMatchedClause ::=
WHEN NOT MATCHED [ AND <case_predicate> ] THEN INSERT [ ( <col_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )
Der entscheidende Unterschied besteht darin, dass Snowflake keine direkte Entsprechung zur WHEN NOT MATCHED BY SOURCE-Klausel hat. Um eine ähnliche Funktionalität in Snowflake zu erreichen, ist eine Problemumgehung erforderlich.
Beispielhafte Quellcode-Muster¶
Beispielhafte Hilfsdaten¶
Bemerkung
Die folgenden Codebeispiele wurden ausgeführt, damit Sie besser verstehen, wie sie funktionieren:
CREATE OR REPLACE people_source (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
title STRING NOT NULL,
);
CREATE OR REPLACE TABLE people_target (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name STRING NOT NULL,
last_name STRING NOT NULL,
title STRING NOT NULL DEFAULT 'NONE'
);
INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');
CREATE OR REPLACE TABLE people_source (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
title VARCHAR(10) NOT NULL
);
CREATE OR REPLACE TABLE people_target (
person_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
title VARCHAR(10) NOT NULL DEFAULT 'NONE'
);
INSERT INTO people_target VALUES (1, 'John', 'Smith', 'Mr');
INSERT INTO people_target VALUES (2, 'alice', 'jones', 'Mrs');
INSERT INTO people_source VALUES (2, 'Alice', 'Jones', 'Mrs.');
INSERT INTO people_source VALUES (3, 'Jane', 'Doe', 'Miss');
INSERT INTO people_source VALUES (4, 'Dave', 'Brown', 'Mr');
MERGE-Anweisung - Fall einfügen und aktualisieren¶
Spark¶
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = DEFAULT
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
1|John |Smith |Mr |
2|Alice |Jones |NONE |
3|Jane |Doe |Miss |
4|Dave |Brown |Mr |
Snowflake¶
MERGE INTO people_target2 pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED THEN UPDATE
SET pt.first_name = ps.first_name,
pt.last_name = ps.last_name,
pt.title = DEFAULT
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM PUBLIC.people_target ORDER BY person_id;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
1|John |Smith |Mr |
2|Alice |Jones |NONE |
3|Jane |Doe |Miss |
4|Dave |Brown |Mr |
Die INSERT und UPDATE-Operationen funktionieren in Snowflake auf die gleiche Weise. In beiden SQL-Dialekten können Sie DEFAULT als Ausdruck verwenden, um eine Spalte auf ihren Standardwert zu setzen.
Spark erlaubt Einfüge- und Aktualisierungsoperationen, ohne die Spalten explizit aufzulisten. Wenn keine Spalten angegeben sind, betrifft die Operation alle Spalten der Tabelle. Damit dies korrekt funktioniert, müssen die Quell- und die Zieltabelle identische Spaltenstrukturen haben. Wenn die Spaltenstrukturen nicht übereinstimmen, erhalten Sie einen Parsing-Fehler.
UPDATE SET *
-- This is equivalent to UPDATE SET col1 = source.col1 [, col2 = source.col2 ...]
INSERT *
-- This command copies all columns from the source table to the target table, matching columns by name. It is the same as explicitly listing all columns in both the INSERT and VALUES clauses.
Since Snowflake doesn't support these options, the migration process will instead list all columns from the target table.
### MERGE Statement - Delete Case
```{code} sql
:force:
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED AND pt.person_id < 3 THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT *;
SELECT * FROM people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
1|John |Smith |Mr |
3|Jane |Doe |Miss |
4|Dave |Brown |Mr |
Snowflake¶
MERGE INTO people_target pt
USING people_source ps
ON (pt.person_id = ps.person_id)
WHEN MATCHED AND pt.person_id < 3 THEN DELETE
WHEN NOT MATCHED THEN INSERT
(pt.person_id, pt.first_name, pt.last_name, pt.title)
VALUES (ps.person_id, ps.first_name, ps.last_name, ps.title);
SELECT * FROM people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
1|John |Smith |Mr |
3|Jane |Doe |Miss |
4|Dave |Brown |Mr |
Die DELETE-Aktion funktioniert in Snowflake auf dieselbe Weise wie in anderen Datenbanken. Sie können auch zusätzliche Bedingungen zu den Klauseln MATCHED und NOT MATCHED hinzufügen.
WHEN NOT MATCHED BY TARGET und WHEN NOT MATCHED sind gleichwertige Klauseln, die austauschbar in SQL-Zusammenführungsanweisungen verwendet werden können.
MERGE-Anweisung - WHENNOTMATCHEDBYSOURCE¶
WHEN NOT MATCHED BY SOURCE-Klauseln werden ausgelöst, wenn eine Zeile in der Zieltabelle keine passenden Zeilen in der Quelltabelle hat. Dies geschieht, wenn sowohl die merge_condition als auch die optionale not_match_by_source_condition auf „true“ ausgewertet werden. Weitere Einzelheiten finden Sie in der Spark-Dokumentation.
Snowflake unterstützt diese Klausel nicht direkt. Um diese Beschränkung zu umgehen, können Sie die folgende Abhilfe sowohl für DELETE als auch für UPDATE-Aktionen verwenden.
MERGE INTO people_target pt
USING people_source ps
ON pt.person_id = ps.person_id
WHEN NOT MATCHED BY SOURCE THEN DELETE;
SELECT * FROM people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
2|Alice |Jones |NONE |
Snowflake¶
MERGE INTO people_target pt
USING (
SELECT
pt.person_id
FROM
people_target pt LEFT
JOIN people_source ps ON pt.person_id = ps.person_id
WHERE
ps.person_id is null
) s_src
ON s_src.person_id = pt.person_id
WHEN MATCHED THEN DELETE;
SELECT * FROM people_target;
PERSON_ID|FIRST_NAME|LAST_NAME|TITLE|
---------+----------+---------+-----+
2|Alice |Jones |NONE |
Die DELETE-Aktion funktioniert in Snowflake auf dieselbe Weise wie in anderen Datenbanken. Sie können auch zusätzliche Bedingungen zu den Klauseln MATCHED und NOT MATCHED hinzufügen.
Bekannte Probleme¶
1. MERGE is very similar in both languages¶
Apache Spark bietet zwar zusätzliche Features, aber Sie können ähnliche Funktionen in Snowflake mit alternativen Ansätzen erreichen, wie in den vorherigen Beispielen gezeigt.