Snowpark Migration Accelerator : Fusionner¶
Description¶
L’instruction MERGE combine les données d’une ou de plusieurs tables sources avec une table cible, ce qui vous permet d’effectuer des mises à jour et des insertions en une seule opération. En fonction des conditions que vous définissez, détermine s’il faut mettre à jour les lignes existantes ou en insérer de nouvelles dans la table cible. Cette méthode est plus efficace que l’utilisation d’instructions INSERT, UPDATE et DELETE distinctes. L’instruction MERGE génère toujours des résultats cohérents lorsqu’elle est exécutée plusieurs fois avec les mêmes données.
Dans Spark, vous trouverez la syntaxe MERGE dans la documentation Spark.
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 } } [, ...] }
Dans Snowflake, l’instruction MERGE suit la syntaxe suivante (pour plus de détails, reportez-vous à la documentation Snowflake) :
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> [ , ... ] )
La distinction clé est que Snowflake n’a pas d’équivalent direct à la clause WHEN NOT MATCHED BY SOURCE. Une solution de contournement est nécessaire pour obtenir une fonctionnalité similaire dans Snowflake.
Modèles d’échantillons de sources¶
Exemple de données auxiliaires¶
Note
Les exemples de code suivants ont été exécutés pour vous aider à mieux comprendre leur fonctionnement :
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');
Instruction MERGE - Opérations d’insertion et de mise à jour¶
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 |
Les opérations INSERT et UPDATE fonctionnent de la même manière dans Snowflake. Dans les deux variantes SQL, vous pouvez utiliser DEFAULT comme expression pour définir une colonne sur sa valeur par défaut.
Spark autorise les opérations d’insertion et de mise à jour sans répertorier explicitement les colonnes. Lorsque les colonnes ne sont pas spécifiées, l’opération concerne toutes les colonnes de la table. Pour que cela fonctionne correctement, les tables source et de destination doivent avoir des structures de colonnes identiques. Si les structures des colonnes ne correspondent pas, vous recevrez une erreur d’analyse.
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 |
L’action DELETE dans Snowflake fonctionne de la même manière que dans les autres bases de données. Vous pouvez également ajouter des conditions supplémentaires aux clauses MATCHED et NOT MATCHED.
WHEN NOT MATCHED BY TARGET et WHEN NOT MATCHED sont des clauses équivalentes qui peuvent être utilisées de manière interchangeable dans les instructions de fusion SQL.
MERGE Instruction - WHENNOTMATCHEDBYSOURCE¶
Les clauses WHEN NOT MATCHED BY SOURCE sont déclenchées lorsqu’une ligne de la table cible n’a pas de ligne correspondante dans la table source. Cela se produit lorsque la condition merge_condition et la condition facultative not_match_by_source_condition sont évaluées sur true. Pour plus de détails, consultez la documentation Spark.
Snowflake ne prend pas en charge directement cette clause. Pour gérer cette limite, vous pouvez utiliser la solution de contournement suivante pour les actions DELETE et UPDATE.
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 |
L’action DELETE dans Snowflake fonctionne de la même manière que dans les autres bases de données. Vous pouvez également ajouter des conditions supplémentaires aux clauses MATCHED et NOT MATCHED.
Problèmes connus¶
1. MERGE is very similar in both languages¶
Bien qu’Apache Spark offre des fonctions supplémentaires, vous pouvez obtenir des fonctionnalités similaires dans Snowflake en utilisant des approches alternatives, comme le démontrent les exemples précédents.