Snowflake脚本存储过程日志写入事件表失败及异常捕获排查

1. 核心问题定位 #

你的脚本存在几个关键问题导致日志无法写入事件表：

• SYSTEM$LOG的变量引用语法错误：使用了&{:accumulator}这种无效格式，会引发语法错误且日志无法正常记录
• 缺少异常处理逻辑：未捕获执行过程中的异常，无法主动记录错误日志
• 未验证事件表的权限与配置有效性：执行角色可能缺少事件表的写入权限，或账户级配置未生效

2. 分步修复方案 #

2.1 修正SYSTEM$LOG变量引用语法 #

Snowflake Scripting中需用||拼接变量与字符串，正确写法如下：

SYSTEM$LOG('INFO', 'DDL FOR TABLE ' || :accumulator || ' SUCCESSFUL!');

2.2 添加异常处理块 #

在存储过程中加入EXCEPTION逻辑，捕获所有异常并记录错误日志：

EXCEPTION
  WHEN OTHERS THEN
    SYSTEM$LOG('ERROR', '存储过程执行失败: ' || SQLERRM || ' 错误代码: ' || SQLCODE);
    RETURN 'FAILED: ' || SQLERRM;

2.3 验证事件表配置与权限 #

• 给执行存储过程的角色授予事件表写入权限：

  GRANT INSERT ON MYDB.MYSCHEMA.LOGGING_TABLE TO ROLE YOUR_CALLER_ROLE;
  

• 确认账户级事件表配置生效：

  SHOW PARAMETERS LIKE 'EVENT_TABLE' IN ACCOUNT;
  

  需确保返回的VALUE字段值为MYDB.MYSCHEMA.LOGGING_TABLE

2.4 修正后的完整存储过程 #

CREATE OR REPLACE PROCEDURE MYDB.MYSCHEMA.TEST()
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  accumulator VARCHAR;
  res RESULTSET DEFAULT ( select CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME) as FQ_TABLE_NAME
                            from SNOWFLAKE.ACCOUNT_USAGE.TABLES 
                            where TABLE_TYPE = 'EXTERNAL TABLE' and DELETED is null ) ;
  c1 CURSOR FOR res;
BEGIN
  SYSTEM$LOG('INFO', '存储过程开始执行');
  -- 注意：确保table1存在且当前角色拥有INSERT权限
  INSERT INTO table1 (fq_name, scripts) VALUES (null, 'USE ROLE ACCOUNTADMIN;');
  FOR row_variable IN c1 DO
    accumulator := row_variable.FQ_TABLE_NAME;
    INSERT INTO table1 (fq_name, scripts) SELECT :accumulator, GET_DDL('table', :accumulator);
    SYSTEM$LOG('INFO', 'DDL FOR TABLE ' || :accumulator || ' SUCCESSFUL!');
  END FOR;
  SYSTEM$LOG('INFO', '存储过程执行完成');
  RETURN 'SUCCESS';
EXCEPTION
  WHEN OTHERS THEN
    SYSTEM$LOG('ERROR', '存储过程执行失败: ' || SQLERRM || ' 错误代码: ' || SQLCODE);
    RETURN 'FAILED: ' || SQLERRM;
END;
$$;

2.5 重新执行与验证 #

1. 确认存储过程日志级别配置：

   ALTER PROCEDURE MYDB.MYSCHEMA.TEST() SET LOG_LEVEL = INFO;
   

2. 调用存储过程：

   CALL MYDB.MYSCHEMA.TEST();
   

3. 查询事件表（日志可能延迟1-2分钟写入）：

   SELECT EVENT_TYPE, MESSAGE, TIMESTAMP, OBJECT_NAME 
   FROM MYDB.MYSCHEMA.LOGGING_TABLE
   WHERE OBJECT_NAME = 'TEST'
   ORDER BY TIMESTAMP DESC;
   

3. 额外注意事项 #

• SYSTEM$LOG日志存在写入延迟，执行后需等待1-2分钟再查询事件表
• 确保EXECUTE AS CALLER对应的角色拥有SYSTEM$LOG执行权限及相关对象的访问权限
• 检查账户级EVENT_RETENTION_TIME参数，避免日志被自动清理：

  SHOW PARAMETERS LIKE 'EVENT_RETENTION_TIME' IN ACCOUNT;
  

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