Snowflake中使用带JOIN的MERGE语句填充PRODUCTID外键问题

核心问题确认 #

• Snowflake完全支持在MERGE语句的USING子句中使用任意JOIN操作，你之前报错是语法使用错误导致
• 支持基于PRODUCTNAME自动关联DIM_PRODUCT表填充PRODUCTID外键

过往操作错误点 #

1. 首次执行的MERGE语句没有关联DIM_PRODUCT表，USING子查询中没有PRODUCTID字段，插入时自然为NULL
2. 后续补充PRODUCTID时使用了INSERT语句而非UPDATE语句，INSERT会新增行而非修改已有行，导致出现大量其他字段为NULL的记录
3. 尝试在MERGE中加JOIN时的两处语法错误：
   • USING子查询内部不能引用MERGE目标表的别名a，该别名仅在ON关联条件和后续的INSERT/UPDATE子句中可访问
   • LATERAL FLATTEN的执行顺序在LEFT JOIN之前，且存在表名拼写错误、关联字段归属错误的问题

正确实现代码 #

merge into PRODUCTSDETAIL as a 
using (
    select 
        b.CUSTOMERID,
        c.PRODUCTID, -- 从关联的产品维表获取主键ID
        f.value as Productunit,
        b.Productname[f.index]::Varchar as Productname,
        b.Productcode[f.index]::Varchar as Productcode,
        b.Productquantity[f.index]::Varchar as Productquantity,
        b.PURCHASEDate[f.index]::Timestamp as PURCHASEDate
    from staging_json b
    -- 先执行LATERAL FLATTEN拆分数组字段
    , LATERAL FLATTEN(input => b.Productunit, RECURSIVE=>true) f
    -- 拆分后关联产品维表获取外键值
    LEFT JOIN test."manufacturer".dim_product c 
        ON c.PRODUCTNAME = b.Productname[f.index]::Varchar
) as b 
on a.PURCHASEDate = b.PURCHASEDate
and a.Productcode = b.Productcode 
and a.CUSTOMERID = b.CUSTOMERID
and a.Productquantity = b.Productquantity
when not matched then insert (
    CUSTOMERID, PRODUCTID, Productunit, Productname, 
    Productcode,Productquantity, PURCHASEDate
) 
values (
    b.CUSTOMERID, b.PRODUCTID, b.Productunit, b.Productname, 
    b.Productcode, b.Productquantity, b.PURCHASEDate
);

补充说明 #

如果存在staging中的PRODUCTNAME在DIM_PRODUCT中不存在的场景，需要先对DIM_PRODUCT维表执行增量插入，再执行上面的MERGE语句即可保证PRODUCTID全部非空。
如果需要清理之前错误插入的无效NULL记录，可以执行以下语句：

DELETE FROM PRODUCTSDETAIL WHERE ProductsdetailID IN (
    SELECT ProductsdetailID FROM PRODUCTSDETAIL WHERE CUSTOMERID IS NULL AND PURCHASEdate IS NULL
);

内容的提问来源于stack exchange，提问作者SMR