如何在SQL语言中实现Snowflake JS存储过程中的动态MERGE操作？

你在JavaScript存储过程里实现的动态MERGE逻辑，完全可以用Snowflake原生SQL来实现，核心是通过EXECUTE IMMEDIATE执行动态生成的SQL语句，结合Snowflake的数组函数替代JS里的map和join操作。

场景1：手动指定匹配列和更新列 #

如果已经明确知道需要匹配的列（对应原JS里的rm数组）和要更新/插入的列（对应原JS里的col数组），可以用以下方式实现：

-- 定义变量：替换成你的实际对象名和列名
SET TARGET_TABLE = 'your_target_table';
SET SOURCE_OBJECT = 'your_source_table_or_view';
SET MATCH_COLUMNS = ARRAY_CONSTRUCT('col1', 'col2'); -- 用于匹配的列
SET UPDATE_COLUMNS = ARRAY_CONSTRUCT('col1', 'col2', 'col3'); -- 要更新/插入的列

-- 生成MERGE的ON子句条件
SET ON_CLAUSE = ARRAY_TO_STRING(
    ARRAY_TRANSFORM($MATCH_COLUMNS, 
        x => 'COALESCE(T."' || x || '", ''-1'') = COALESCE(S."' || x || '", ''-1'')'
    ),
    ' AND '
);

-- 生成UPDATE SET子句
SET UPDATE_CLAUSE = ARRAY_TO_STRING(
    ARRAY_TRANSFORM($UPDATE_COLUMNS, 
        x => 'T."' || x || '" = S."' || x || '"'
    ),
    ', '
);

-- 生成INSERT的列列表和VALUES列表
SET INSERT_COLUMNS = ARRAY_TO_STRING(
    ARRAY_TRANSFORM($UPDATE_COLUMNS, x => '"' || x || '"'),
    ', '
);
SET INSERT_VALUES = ARRAY_TO_STRING(
    ARRAY_TRANSFORM($UPDATE_COLUMNS, x => 'S."' || x || '"'),
    ', '
);

-- 拼接完整的MERGE语句
SET MERGE_SQL = 'MERGE INTO ' || $TARGET_TABLE || ' T 
USING (SELECT * FROM ' || $SOURCE_OBJECT || ') S 
ON ' || $ON_CLAUSE || '
WHEN MATCHED THEN UPDATE SET 
    ' || $UPDATE_CLAUSE || '
WHEN NOT MATCHED THEN INSERT (
    ' || $INSERT_COLUMNS || '
) VALUES (
    ' || $INSERT_VALUES || '
);';

-- 执行动态MERGE
EXECUTE IMMEDIATE $MERGE_SQL;

场景2：封装成SQL存储过程复用 #

如果需要重复使用这个逻辑，可以把它封装成SQL语言的存储过程：

CREATE OR REPLACE PROCEDURE dynamic_merge(
    TARGET_TABLE VARCHAR,
    SOURCE_OBJECT VARCHAR,
    MATCH_COLUMNS ARRAY,
    UPDATE_COLUMNS ARRAY
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    ON_CLAUSE VARCHAR;
    UPDATE_CLAUSE VARCHAR;
    INSERT_COLUMNS VARCHAR;
    INSERT_VALUES VARCHAR;
    MERGE_SQL VARCHAR;
BEGIN
    -- 构建ON子句
    ON_CLAUSE := ARRAY_TO_STRING(
        ARRAY_TRANSFORM(MATCH_COLUMNS, 
            x => 'COALESCE(T."' || x || '", ''-1'') = COALESCE(S."' || x || '", ''-1'')'
        ),
        ' AND '
    );

    -- 构建UPDATE SET子句
    UPDATE_CLAUSE := ARRAY_TO_STRING(
        ARRAY_TRANSFORM(UPDATE_COLUMNS, 
            x => 'T."' || x || '" = S."' || x || '"'
        ),
        ', '
    );

    -- 构建INSERT列和VALUES
    INSERT_COLUMNS := ARRAY_TO_STRING(
        ARRAY_TRANSFORM(UPDATE_COLUMNS, x => '"' || x || '"'),
        ', '
    );
    INSERT_VALUES := ARRAY_TO_STRING(
        ARRAY_TRANSFORM(UPDATE_COLUMNS, x => 'S."' || x || '"'),
        ', '
    );

    -- 生成完整MERGE语句
    MERGE_SQL := 'MERGE INTO ' || TARGET_TABLE || ' T 
USING (SELECT * FROM ' || SOURCE_OBJECT || ') S 
ON ' || ON_CLAUSE || '
WHEN MATCHED THEN UPDATE SET 
    ' || UPDATE_CLAUSE || '
WHEN NOT MATCHED THEN INSERT (
    ' || INSERT_COLUMNS || '
) VALUES (
    ' || INSERT_VALUES || '
);';

    -- 执行语句
    EXECUTE IMMEDIATE MERGE_SQL;
    RETURN '动态MERGE执行成功';
END;
$$;

-- 调用存储过程，替换成你的实际参数
CALL dynamic_merge('your_target_table', 'your_source_object', ARRAY_CONSTRUCT('col1','col2'), ARRAY_CONSTRUCT('col1','col2','col3'));

场景3：自动获取目标表列 #

如果不想手动指定更新列，可以通过Snowflake的系统视图INFORMATION_SCHEMA.COLUMNS自动获取目标表的所有列：

SET TARGET_TABLE = 'your_target_table';
SET SOURCE_OBJECT = 'your_source_object';
SET MATCH_COLUMNS = ARRAY_CONSTRUCT('col1', 'col2'); -- 仍需手动指定匹配列

-- 自动获取目标表的所有列
SET UPDATE_COLUMNS = (
    SELECT ARRAY_AGG(COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = $TARGET_TABLE
      AND TABLE_SCHEMA = CURRENT_SCHEMA()
      AND TABLE_CATALOG = CURRENT_DATABASE()
);

-- 后续步骤和场景1一致，生成并执行MERGE语句

核心逻辑说明 #

SQL版本用ARRAY_TRANSFORM替代JS的map方法，用ARRAY_TO_STRING替代join方法，最终生成的MERGE语句和你原JS存储过程生成的完全一致，实现相同的匹配、更新、插入逻辑。

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