Snowflake SQL脚本引用变量创建表报错，如何排查解决？

问题描述 #

编写的Snowflake存储过程尝试批量创建表，执行时触发语法错误unexpected 'table'，即使使用EXECUTE IMMEDIATE包裹存储过程仍无法解决，原代码如下：

execute immediate $$ 
declare
  tnames cursor for select value as tname from table( flatten ( ['OPERATIONS.TABLE1','OPERATIONS.TABLE2','OPERATIONS.TABLE3' ] ) );
  src_db_name text default 'SRC_DB';
  tgt_db_name text default 'TARGET_DB';
  dev_schema_name text default 'MYSCHEMA';
begin
    for r in tnames do
        let src_name := src_db_name ||'.'|| r.tname;
        let tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
        create table :tgt_name as select * from :src_name ;
        commit;
    end for;
end;
$$

错误原因 #

Snowflake存储过程中，绑定变量（:变量名）仅支持作为SQL语句的参数值使用，不能直接用于对象标识符（如表名、库名、模式名）。原代码中直接用:tgt_name和:src_name作为表名，SQL解析器会将其识别为参数值而非合法的对象名称，从而触发语法错误。

解决方案 #

需要通过动态拼接完整的SQL语句字符串，再调用EXECUTE IMMEDIATE执行这条动态生成的语句。推荐两种实现方式：

方式1：直接拼接字符串 #

execute immediate $$ 
declare
  tnames cursor for select value as tname from table( flatten ( ['OPERATIONS.TABLE1','OPERATIONS.TABLE2','OPERATIONS.TABLE3' ] ) );
  src_db_name text default 'SRC_DB';
  tgt_db_name text default 'TARGET_DB';
  dev_schema_name text default 'MYSCHEMA';
begin
    for r in tnames do
        let src_name := src_db_name ||'.'|| r.tname;
        let tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
        -- 动态拼接CREATE TABLE语句并执行
        execute immediate 'create table ' || tgt_name || ' as select * from ' || src_name;
        commit;
    end for;
end;
$$

方式2：使用IDENTIFIER()配合绑定变量（更安全，防SQL注入） #

如果对象名称包含特殊字符、大小写敏感或来自不可信输入，建议用IDENTIFIER()函数处理对象名，同时通过USING传递变量：

execute immediate $$ 
declare
  tnames cursor for select value as tname from table( flatten ( ['OPERATIONS.TABLE1','OPERATIONS.TABLE2','OPERATIONS.TABLE3' ] ) );
  src_db_name text default 'SRC_DB';
  tgt_db_name text default 'TARGET_DB';
  dev_schema_name text default 'MYSCHEMA';
begin
    for r in tnames do
        let src_name := src_db_name ||'.'|| r.tname;
        let tgt_name := tgt_db_name ||'.'|| dev_schema_name || '_' || r.tname;
        -- 用IDENTIFIER处理对象名，通过USING传递变量
        execute immediate 'create table identifier(?) as select * from identifier(?)' using (tgt_name, src_name);
        commit;
    end for;
end;
$$

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