Snowpark Migration Accelerator: Merge¶
Descrição¶
A instrução MERGE combina dados de uma ou mais tabelas de origem com uma tabela de destino, permitindo fazer atualizações e inserções em uma única operação. Com base nas condições definidas por você, ele determina se deve atualizar as linhas existentes ou inserir novas linhas na tabela de destino. Isso o torna mais eficiente do que usar instruções separadas INSERT, UPDATE e DELETE. A instrução MERGE sempre produz resultados consistentes quando executada várias vezes com os mesmos dados.
No Spark, você pode encontrar a sintaxe de MERGE na documentação do 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 } } [, ...] }
No Snowflake, a instrução MERGE segue esta sintaxe (para obter mais detalhes, consulte a documentação do 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> [ , ... ] )
A principal distinção é que o Snowflake não tem um equivalente direto à cláusula WHEN NOT MATCHED BY SOURCE. É necessária uma solução alternativa para obter uma funcionalidade semelhante no Snowflake.
Amostra de padrões da origem¶
Amostra de dados auxiliares¶
Nota
Os exemplos de código a seguir foram executados para ajudar a entender melhor como eles funcionam:
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');
Instrução MERGE - Caso de inserção e atualização¶
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 |
As operações INSERT e UPDATE funcionam da mesma forma no Snowflake. Em ambos os dialetos SQL, você pode usar DEFAULT como uma expressão para definir uma coluna como seu valor padrão.
O Spark permite operações de inserção e atualização sem listar explicitamente as colunas. Quando as colunas não são especificadas, a operação afeta todas as colunas da tabela. Para que isso funcione corretamente, as tabelas de origem e destino devem ter estruturas de colunas idênticas. Se as estruturas das colunas não corresponderem, você receberá um erro de análise.
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 |
A ação DELETE no Snowflake funciona da mesma forma que em outros bancos de dados. Você também pode acrescentar condições adicionais às cláusulas MATCHED e NOT MATCHED.
WHEN NOT MATCHED BY TARGET e WHEN NOT MATCHED são cláusulas equivalentes que podem ser usadas de forma intercambiável nas instruções de mesclagem SQL.
Instrução MERGE - WHENNOTMATCHEDBYSOURCE¶
As cláusulas WHEN NOT MATCHED BY SOURCE são acionadas quando uma linha na tabela de destino não tem linhas correspondentes na tabela de origem. Isso ocorre quando tanto a condição merge_condition quanto a condição opcional not_match_by_source_condition são avaliadas como verdadeiras. Para obter mais detalhes, consulte a documentação do Spark.
O Snowflake não oferece suporte direto a essa cláusula. Para lidar com essa limitação, você pode usar a seguinte solução alternativa para as ações DELETE e 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 |
A ação DELETE no Snowflake funciona da mesma forma que em outros bancos de dados. Você também pode acrescentar condições adicionais às cláusulas MATCHED e NOT MATCHED.
Problemas conhecidos¶
1. MERGE is very similar in both languages¶
Embora o Apache Spark ofereça recursos adicionais, você pode obter funcionalidade semelhante no Snowflake usando abordagens alternativas, conforme demonstrado nos exemplos anteriores.