如何在Snowflake存储过程中动态选列实现两表关联查询

核心实现思路 #

针对动态选列、动态关联的需求，核心通过3个逻辑点解决：

• 列名动态获取：通过信息模式视图查询传入表的所有字段，自动拼接带表别名的查询字段，同时给同名字段加前缀避免结果表重名报错
• 多关联键支持：关联键通过英文逗号分隔的字符串传入，存储过程内自动拆分拼接为ON子句的等值关联条件
• 条件动态拼接：自动拼接传入的过滤列差异判断条件，最终生成可直接执行的建表查询语句

注意：原硬编码SQL存在同名列未加别名导致建表失败、t1.product字段后缺失逗号的语法问题，以下实现会自动规避这两类错误

可直接运行的存储过程代码 #

CREATE OR REPLACE PROCEDURE mydb.compare_table_diff(
    source_db VARCHAR,
    source_table VARCHAR,
    target_db VARCHAR,
    target_table VARCHAR,
    key_join VARCHAR, -- 多关联键用英文逗号分隔，例：'id,name'
    filter_col VARCHAR
)
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
// 1. 获取源表所有字段，拼接为t1.col as t1_col格式，避免重名
var t1_col_sql = `
SELECT LISTAGG('t1.' || COLUMN_NAME || ' AS t1_' || COLUMN_NAME, ', ') 
       WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS col_str
FROM IDENTIFIER(:1).INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = :2
`;
var t1_cols_stmt = snowflake.createStatement({
    sqlText: t1_col_sql,
    binds: [SOURCE_DB, SOURCE_TABLE]
});
var t1_col_list = t1_cols_stmt.execute().next() ? t1_cols_stmt.getColumnValue('COL_STR') : '';

// 2. 获取目标表所有字段，拼接为t2.col as t2_col格式
var t2_col_sql = `
SELECT LISTAGG('t2.' || COLUMN_NAME || ' AS t2_' || COLUMN_NAME, ', ') 
       WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS col_str
FROM IDENTIFIER(:1).INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = :2
`;
var t2_cols_stmt = snowflake.createStatement({
    sqlText: t2_col_sql,
    binds: [TARGET_DB, TARGET_TABLE]
});
var t2_col_list = t2_cols_stmt.execute().next() ? t2_cols_stmt.getColumnValue('COL_STR') : '';

// 3. 拼接关联ON条件
var join_keys = KEY_JOIN.split(',');
var on_clause = join_keys.map(key => `t1.${key.trim()} = t2.${key.trim()}`).join(' AND ');

// 4. 拼接最终建表SQL
var final_sql = `
DROP TABLE IF EXISTS mydb.result_table;
CREATE TABLE mydb.result_table AS
SELECT 
${t1_col_list},
${t2_col_list}
FROM ${SOURCE_DB}.${SOURCE_TABLE} t1
FULL OUTER JOIN ${TARGET_DB}.${TARGET_TABLE} t2
ON ${on_clause}
WHERE t1.${FILTER_COL} <> t2.${FILTER_COL}
-- 补充NULL值判断，避免一边为空的差异被漏掉
OR (t1.${FILTER_COL} IS NULL AND t2.${FILTER_COL} IS NOT NULL)
OR (t1.${FILTER_COL} IS NOT NULL AND t2.${FILTER_COL} IS NULL)
`;

// 5. 执行SQL
var run_stmt = snowflake.createStatement({sqlText: final_sql});
run_stmt.execute();

return '执行成功，差异结果已写入mydb.result_table';
$$;

调用示例 #

对应原硬编码的场景，调用方式如下：

CALL mydb.compare_table_diff(
    'source_db',
    'source_table',
    'target_db',
    'target_table',
    'id,name', -- 关联键，多个用逗号分隔
    'place' -- 过滤差异的列
);

使用注意事项 #

• 执行存储过程使用的角色，需要拥有源表、目标表的SELECT权限，以及mydb库的建表权限
• 结果表所有字段会自动加t1_/t2_前缀，比如源表的id字段在结果表中名为t1_id，目标表的id字段名为t2_id，完全规避同名列冲突
• 如果需要支持schema级别的表定位，只需要新增source_schema、target_schema两个入参，调整取列的SQL和FROM子句的表名拼接逻辑即可
• 代码中已经补充了过滤列的NULL值判断，避免某一侧字段为空时，<>运算符无法识别差异的问题

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