Snowflake存储过程函数传参绑定方法及LOG_LEVEL未定义报错解决咨询

根因分析 #

Snowflake JavaScript存储过程遵循JavaScript大小写敏感规则，报错的直接原因是log函数中参数绑定使用的全大写变量LOG_LEVEL、SRC_NAME等，和函数定义的小写形参log_level、src_name名称不匹配，属于未定义变量调用错误。
同时代码存在2个隐藏问题：

• 如果copy_into_stmt还未初始化就触发异常，catch块中调用copy_into_stmt.getQueryId()会触发二次报错
• 异常场景下日志级别仍使用INFO不符合日志分类规范

修复后完整代码 #

create or replace procedure function_test()
returns varchar not null
language JAVASCRIPT
as
$$
const ProceName=Object.keys(this)[0];
// 提前声明变量，避免catch块访问未定义变量
var copy_into_stmt;

try
{
 log("INFO",ProceName,0,"RUNNING","","")
 var copy_into_cmd = `copy into my_table from @my_stage
                      file_format=(format_name= 'my_file_format')
                      on_error = skip_file;`;
copy_into_stmt = snowflake.createStatement({sqlText: copy_into_cmd});
log("INFO",ProceName,0,"EXECUTING",copy_into_stmt.getQueryId(),copy_into_stmt.getSqlText())
var result_set= copy_into_stmt.execute();
return "SUCCESS"
}

catch(err)
{
var query_id = typeof copy_into_stmt !== 'undefined' ? copy_into_stmt.getQueryId() : '';
var sqltext= typeof copy_into_stmt !== 'undefined' ? copy_into_stmt.getSqlText() : '';
// 异常场景使用ERROR日志级别
log("ERROR",ProceName,err.code || -1,err.message,query_id,sqltext)
return err.message
}

function log(log_level,src_name,err,message,query_id,stmt_txt)
{
  try
  { 
    var load_cmd="insert into log_table(timestamp,log_level,usr_name,src_name,err,message,query_id,sql_text) values(current_timestamp(),?,current_user(),?,NULLIFZERO(?),?,?,?);"
    // 绑定变量和形参大小写保持完全一致
    var load_stmt=snowflake.createStatement({sqlText: load_cmd, binds:[log_level,src_name,err,message,query_id,stmt_txt]}).execute();
  }
  catch(err)
  {
    throw "ERROR: "+err.message
  }
 }
    
$$;

Snowflake JS存储过程变量绑定规范 #

• 变量、参数名称大小写必须完全匹配，JavaScript不存在自动大小写转换逻辑
• 绑定数组的元素顺序必须和SQL语句中?占位符的顺序一一对应
• 函数内直接使用定义的形参名即可，不需要额外添加命名空间或格式转换
• 数字、字符串、布尔值等基础类型可以直接绑定，Snowflake会自动适配SQL字段类型

额外优化建议 #

• 可以预先定义日志级别常量，避免拼写错误：const LOG_INFO = "INFO"; const LOG_ERROR = "ERROR";
• 日志写入异常可以调整为静默失败，避免日志功能异常打断主业务逻辑运行
• 可以新增日志表的权限校验逻辑，避免存储过程运行账号没有日志表写入权限导致的报错

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