本文介绍了雪花没有实现完整的SQL MERGE语句吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试创建执行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语句吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!