雪花没有实现完整的SQL MERGE语句吗？ [英] Snowflake does not implement the full SQL MERGE statement?

问题描述

我正在尝试创建执行MERGE语句的Snowflake任务。

但是，Snowflake似乎无法识别"WHEN NOT MATCH BY TARGET"或"WHEN NOT MATCH BY SOURCE"语句。

create or replace task MERGE_TEAM_TOUCHPOINT
 warehouse = COMPUTE_WH
 schedule = '1 minute'
 when system$stream_has_data('TEAMTOUCHPOINT_CDC')
as
merge into dv.Team_Touchpoint as f using TeamTouchpoint_CDC as s
    on s.uniqueid = f.uniqueid
    when matched then 
        update set TEAMUNIQUEID = s.TEAMUNIQUEID,
                    TOUCHPOINTUNIQUEID = s.TOUCHPOINTUNIQUEID
    when not matched by target then 
                   insert (
                     ID,
                     UniqueID,
                     TEAMUNIQUEID,
                     TOUCHPOINTUNIQUEID                   
                   )
                 values (
                    s.ID,
                    s.UniqueID,
                    s.TEAMUNIQUEID,
                    s.TOUCHPOINTUNIQUEID
                 )
    when not matched by source then delete;

我如何才能做到这一点？除了在javascript中创建存储过程首先截断表，然后插入临时表中的所有内容之外，真的没有其他方法吗？

推荐答案

"by target"或"by source"都不是Snowflake的Merge命令中的有效关键字，匹配应仅为"by target"(https://docs.snowflake.com/en/sql-reference/sql/merge.html)。要实现您的目标，您需要与合并分开运行删除-在合并中，您将能够运行更新(匹配时)和插入(不匹配时按目标)，因为事实上，只有当按目标匹配时，合并才能处理删除。

您可以在存储过程中的单个显式事务中处理两个步骤(1.DELETE；2.MERGE-UPDATE&；INSERT)，也可以通过两个单独的任务处理两个不同的事务，其中一个任务是After Task。

或者，您也可以使用可选参数overwrite运行INSERT，这将在单个事务中运行目标表的截断和后续从源表加载： https://docs.snowflake.com/en/sql-reference/sql/insert.html#optional-parameters

以下是DELETE+MERGE(UPDATE&；INSERT)方法的可重复示例：

USE DEV;

CREATE OR REPLACE TEMPORARY TABLE Public.My_Merge_Target (
    Id INTEGER, Name VARCHAR    
)
AS
SELECT column1, column2 
FROM (VALUES (1, 'Stay as is'), (2, 'This name has to change'), (3, 'This needs to go'));

CREATE OR REPLACE TEMPORARY TABLE Public.My_Merge_Source (
    Id INTEGER, Name VARCHAR  
)
AS
SELECT column1, column2 
FROM (VALUES (1, 'Stay as is'), (2, 'This is the new name for id=2'), (4, 'A new row'));



SELECT * FROM Public.My_Merge_Target ORDER BY Id;
/*  
------------------------------------
    Id  | Name
------------------------------------
    1   | Stay as is
    2   | This name has to change
    3   | This needs to go    
*/


SELECT * FROM Public.My_Merge_Source ORDER BY Id;
/*
------------------------------------
    Id  | Name
------------------------------------
    1   | Stay as is
    2   | This is the new name for id=2
    4   | A new row
*/


DELETE FROM Public.My_Merge_Target AS trg
USING (
  SELECT t.Id FROM Public.My_Merge_Source AS s
  RIGHT JOIN Public.My_Merge_Target AS t 
    ON s.Id = t.Id
  WHERE s.Id IS NULL
) AS src
WHERE trg.Id = src.Id;

/*
-----------------------
number of rows deleted 
-----------------------
                     1 
-----------------------                      
*/

SELECT * FROM Public.My_Merge_Target ORDER BY Id;
/*  
------------------------------------
    Id  | Name
------------------------------------
    1   | Stay as is
    2   | This is the new name
*/

MERGE 
    INTO Public.My_Merge_Target AS trg
USING (
    SELECT Id, Name 
    FROM Public.My_Merge_Source
) AS src
ON 
    trg.Id = src.Id
WHEN
    MATCHED 
    AND (src.Name != trg.Name) THEN UPDATE 
        SET Name = src.Name
WHEN 
    NOT MATCHED THEN INSERT (Id, Name) 
        VALUES (src.Id, src.Name)
;    
/*
-------------------------------------------------
number of rows inserted | number of rows updated
-------------------------------------------------
                      1 |                      1
-------------------------------------------------                      
*/

SELECT * FROM Public.My_Merge_Target ORDER BY Id;
/*  
------------------------------------
    Id  | Name
------------------------------------
    1   | Stay as is
    2   | This is the new name for id=2
    4   | A new row
*/

这篇关于雪花没有实现完整的SQL MERGE语句吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！