在Snowflake存储过程中使用split_to_table结合WHERE子句报错

错误原因分析 #

你的代码存在两个核心问题导致语法错误：

1. split_to_table未指定别名：在sql_split_col_name语句中，LATERAL split_to_table(cols, ',')没有给横向连接的表函数指定别名，而Snowflake要求LATERAL关联的对象必须有别名，否则SQL解析器会误判WHERE子句的位置，抛出"unexpected )"错误。对比你正确的sql_split_pk_cols语句，里面给split_to_table指定了别名x，这是符合语法规范的写法。
2. 字符串拼接参数的风险：直接将存储过程参数S_TBL拼进SQL字符串，当参数包含点号（如PROJECT.LAKE.LAKE_TABLE1）时，虽暂时能运行，但存在SQL注入风险，后续若参数含特殊字符会直接触发语法错误。

修正后的存储过程代码 #

create or replace procedure merge_dynamic(S_TBL VARCHAR, T_TBL VARCHAR)
returns varchar
language javascript
EXECUTE AS CALLER
as
$$
var return_value= "";
var target_value= "TGT.";
var return_pk= "";
var target_pk= "TGT.";
var source_table= "";
var target_table= "";

// 改用绑定变量替代字符串拼接，规避语法错误与注入风险
var tables= "select source_table, target_table from project.reference.merge_info where source_table = ?";
var sql_split_col_name= "select y.value as split_value from project.reference.merge_info, LATERAL split_to_table(cols, ',') y where source_table = ?";
var sql_split_pk_cols= "select x.value as split_value from project.reference.merge_info, LATERAL split_to_table(primary_cols, ',') x where source_table = ?";

// 执行查询时传入绑定参数
sql_res_tables= snowflake.createStatement({
    sqlText: tables,
    binds: [S_TBL]
});
rs_tables= sql_res_tables.execute();

while(rs_tables.next())
{
    source_table= rs_tables.getColumnValue(1);
    target_table= rs_tables.getColumnValue(2);
}

sql_res= snowflake.createStatement({
    sqlText: sql_split_col_name,
    binds: [S_TBL]
});
rs_cols= sql_res.execute();
if(rs_cols.next())
{
    return_value+= rs_cols.getColumnValue(1);
    target_value+= rs_cols.getColumnValue(1);
}
while(rs_cols.next())
{
    return_value+= ', '
    return_value+= rs_cols.getColumnValue(1);
    target_value+= ', ';
    target_value+= "TGT."+rs_cols.getColumnValue(1);
}

sql_res_pk= snowflake.createStatement({
    sqlText: sql_split_pk_cols,
    binds: [S_TBL]
});
rs_pk= sql_res_pk.execute();
if(rs_pk.next())
{
    return_pk+= rs_pk.getColumnValue(1);
    target_pk+= rs_pk.getColumnValue(1);
}

while(rs_pk.next())
{
    return_pk+= ', ';
    return_pk+= rs_pk.getColumnValue(1);
    target_pk+= ', ';
    target_pk+= "TGT."+rs_pk.getColumnValue(1);
}

tbl_query= "merge into "+T_TBL+" TGT using " +S_TBL+ " src on hash(src. "+return_pk+")= hash("+target_pk+") when not matched then insert (" +target_value+ ") values ("+ return_value+ ")";
tbl_res= snowflake.createStatement({sqlText:tbl_query});
result= tbl_res.execute();
result.next();
col1= result.getColumnValue(1);

return col1;
$$;

call merge_dynamic('PROJECT.LAKE.LAKE_TABLE1', 'PROJECT.HUB.HUB_TABLE1');

关键修改点 #

• 给sql_split_col_name中的split_to_table添加别名y，符合Snowflake对LATERAL表函数的语法要求。
• 将所有含参数的SQL语句改为使用绑定变量（?占位符），通过binds数组传递参数，彻底解决字符串拼接带来的语法问题与安全风险。
• 给sql_split_pk_cols补充WHERE子句与绑定参数，确保只查询当前传入表对应的主键列，避免返回无关数据。

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