当源不匹配时

x6yk4ghg 于 2021-05-19 发布在 Spark

关注(0)|答案(1)|浏览(374)

我正在databricks中编写python和sparksql代码，我正在使用spark2.4.5。
我有两张table。

Create table IF NOT EXISTS db_xsi_ed_faits_shahgholi_ardalan.Destination
(
  id Int,
  Name string,
  Deleted int
) USING Delta;

Create table IF NOT EXISTS db_xsi_ed_faits_shahgholi_ardalan.Source
(
  id Int,
  Name string,
  Deleted int
) USING Delta;

我需要在源和目标之间运行一个合并命令。我写了下面的命令

%sql
MERGE INTO db_xsi_ed_faits_shahgholi_ardalan.Destination AS D
USING db_xsi_ed_faits_shahgholi_ardalan.Source AS S
ON (S.id = D.id)
-- UPDATE
WHEN MATCHED AND S.Name <> D.Name THEN 
  UPDATE SET 
    D.Name = S.Name
-- INSERT    
WHEN NOT MATCHED THEN 
  INSERT (id, Name, Deleted)
  VALUES (S.id, S.Name, S.Deleted)
 -- DELETE
WHEN NOT MATCHED BY SOURCE THEN 
  UPDATE SET 
     D.Deleted = 1

运行此命令时出现以下错误：

看来我们没有 NOT MATCHED BY SOURCE 在Spark里！我需要一个解决办法。

apache-spark apache-spark-sql azure-databricks

来源：https://stackoverflow.com/questions/64485260/merge-in-spark-sql-when-not-matched-by-source-then

1条答案

按热度按时间

k3fezbri1#

我写了这个代码，但我仍然在寻找更好的方法

%sql
MERGE INTO db_xsi_ed_faits_shahgholi_ardalan.Destination AS D
USING db_xsi_ed_faits_shahgholi_ardalan.Source AS S
ON (S.id = D.id)
-- UPDATE
WHEN MATCHED AND S.Name <> D.Name THEN 
  UPDATE SET 
    D.Name = S.Name
-- INSERT    
WHEN NOT MATCHED THEN 
  INSERT (id, Name, Deleted)
  VALUES (S.id, S.Name, S.Deleted)
;

%sql
-- Logical delete
UPDATE db_xsi_ed_faits_shahgholi_ardalan.Destination
  SET Deleted = 1
WHERE db_xsi_ed_faits_shahgholi_ardalan.Destination.id in
(
  SELECT
    D.id
  FROM db_xsi_ed_faits_shahgholi_ardalan.Destination AS D
  LEFT JOIN db_xsi_ed_faits_shahgholi_ardalan.Source AS S ON (S.id = D.id)
  WHERE S.id is null
)

赞(0）回复(0）举报 2021-05-19

我来回答

当源不匹配时

1条答案

相关问题

热门标签

最新问答