Snowflake跨数据库克隆表与视图：存储过程调用报错排查

在Snowflake中创建了跨数据库克隆表和视图的存储过程，存储过程创建时无报错，但调用时触发语法错误。

原代码 #

CREATE OR REPLACE PROCEDURE DATABASE2.USERNAME.CLONE_TABLES_TO_SANDBOX(USERNAME VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  TAB_NAME VARCHAR;
  SCHEMA_NAME VARCHAR;
  OBJ_TYPE VARCHAR;
  c1 CURSOR FOR (
    SELECT TABLE_NAME AS TAB_NAME, TABLE_SCHEMA AS SCHEMA_NAME, TABLE_TYPE AS OBJ_TYPE
    FROM DATABASE1.INFORMATION_SCHEMA.TABLES
  );
BEGIN
  FOR REC IN c1 DO
    TAB_NAME := REC.TAB_NAME;
    SCHEMA_NAME := REC.SCHEMA_NAME;
    OBJ_TYPE := REC.OBJ_TYPE;
    EXECUTE IMMEDIATE 'CREATE SCHEMA IF NOT EXISTS DATABASE2."' || USERNAME || '_' || SCHEMA_NAME || '"';
    IF (OBJ_TYPE = 'VIEW') THEN
        EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW DATABASE2."' 
                        || USERNAME || '_' || SCHEMA_NAME || '".' 
                        || TAB_NAME || ' CLONE DATABASE1."' || SCHEMA_NAME || '"."' || TAB_NAME || '"';
    ELSE 
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE DATABASE2."' 
                        || USERNAME || '_' || SCHEMA_NAME || '".' 
                        || TAB_NAME || ' CLONE DATABASE1."' || SCHEMA_NAME || '"."' || TAB_NAME || '"';  
    END IF;
  END FOR;
  RETURN ('Tables copied successfully');
END;

CALL DATABASE2.USERNAME.CLONE_TABLES_TO_SANDBOX('USERNAME');

报错信息 #

SQL Error [1003] [42000]: Uncaught exception of type 'STATEMENT_ERROR'
on line 16 at position 5 : SQL compilation error: syntax error line 1
at position 77 unexpected 'ANALYTICS'. syntax error line 1 at position
116 unexpected ''.

代码中错误使用了HTML转义字符"来表示双引号，Snowflake SQL存储过程的字符串语境下，该转义字符会被直接解析为普通文本，导致生成的SQL语句出现语法混乱，触发编译错误。

CREATE OR REPLACE PROCEDURE DATABASE2.USERNAME.CLONE_TABLES_TO_SANDBOX(USERNAME VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  TAB_NAME VARCHAR;
  SCHEMA_NAME VARCHAR;
  OBJ_TYPE VARCHAR;
  c1 CURSOR FOR (
    SELECT TABLE_NAME AS TAB_NAME, TABLE_SCHEMA AS SCHEMA_NAME, TABLE_TYPE AS OBJ_TYPE
    FROM DATABASE1.INFORMATION_SCHEMA.TABLES
  );
BEGIN
  FOR REC IN c1 DO
    TAB_NAME := REC.TAB_NAME;
    SCHEMA_NAME := REC.SCHEMA_NAME;
    OBJ_TYPE := REC.OBJ_TYPE;
    -- 直接使用双引号包裹标识符，外层单引号已完成字符串界定
    EXECUTE IMMEDIATE 'CREATE SCHEMA IF NOT EXISTS DATABASE2."' || USERNAME || '_' || SCHEMA_NAME || '"';
    IF (OBJ_TYPE = 'VIEW') THEN
        EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW DATABASE2."' 
                        || USERNAME || '_' || SCHEMA_NAME || '".' 
                        || TAB_NAME || ' CLONE DATABASE1."' || SCHEMA_NAME || '".' || TAB_NAME || '"';
    ELSE 
        EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE DATABASE2."' 
                        || USERNAME || '_' || SCHEMA_NAME || '".' 
                        || TAB_NAME || ' CLONE DATABASE1."' || SCHEMA_NAME || '".' || TAB_NAME || '"';  
    END IF;
  END FOR;
  RETURN 'Tables copied successfully';
END;

CALL DATABASE2.USERNAME.CLONE_TABLES_TO_SANDBOX('USERNAME');

1. 将原代码中的"替换为直接的双引号"，Snowflake中用单引号包裹的SQL字符串里，双引号无需额外转义，可直接用于界定大小写敏感或含特殊字符的标识符。
2. 若你的标识符（表名、Schema名）均为常规格式（无特殊字符、无需区分大小写），也可去掉双引号，直接使用标识符名称。

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