Snowflake JavaScript存储过程编译错误排查求助

错误信息 #

JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in USP_TASKFAILURE_ALERT at 'var task_info = 'select name,state,scheduled_time,completed_time,error_code,error_message,database_name,schema_name from snowflake.account_usage.task_history where scheduled_time >= dateadd('hour',-3,current_timestamp())' position 191

问题背景 #

我是JavaScript及Snowflake JavaScript存储过程的新手，尝试创建一个存储过程，用于发送近3小时内失败的Snowflake任务列表的邮件告警通知，但执行时出现上述编译错误。原代码如下：

CREATE PROCEDURE IF NOT EXISTS "SALESANALYSIS_DW"."SALES".USP_TASKFAILURE_ALERT()
RETURNS string
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var task_info = 'select name,state,scheduled_time,completed_time,error_code,error_message,database_name,schema_name from snowflake.account_usage.task_history where scheduled_time >= dateadd('hour',-3,current_timestamp())
and query_id is not null and state != 'SUCCEEDED' and cast(scheduled_time as date) = current_date() order by scheduled_time desc';

rs = snowflake.execute({sqlText: task_info});

while (rs.next())
{
tsk_name = rs.getcolumnvalue(1);
tsk_state = rs.getcolumnvalue(2);
tsk_scheduletime = rs.getcolumnvalue(3);
tsk_completedtime = rs.getcolumnvalue(4);
tsk_error_code = rs.getcolumnvalue(5);
tsk_error_msg = rs.getcolumnvalue(6);
tsk_database_name = rs.getcolumnvalue(7);
tsk_schema_name = rs.getcolumnvalue(8);
var tsk_err_msg_parse = tsk_error_msg.replace(/'/g,'"');

var proc = 'call system$send_email('NOTIFICATION_INT','abhixyz@outlook.com','TASK FAILURE NOTIFICATION','Task Name '+ tsk_name +' is failed due to the error code '+ tsk_error_code +' and error message is '+ tsk_err_msg_parse + '' );'
var stmt = snowflake.createstatement({sqlText: proc});
var result = stmt.execute();
}

result = "Succeeded! Email Alert has been sent for the list of Failed Task";
return result;
$$;

错误原因 #

核心问题是JavaScript字符串中的单引号嵌套未转义：

• 定义task_info时用单引号包裹SQL语句，但SQL里的dateadd('hour', ...)和state != 'SUCCEEDED'中的单引号会直接终止外层字符串，导致JS解析器无法识别后续内容，抛出语法错误。
• 同理，proc变量里调用system$send_email时的单引号也存在相同的嵌套问题。

修正后的代码 #

CREATE PROCEDURE IF NOT EXISTS "SALESANALYSIS_DW"."SALES".USP_TASKFAILURE_ALERT()
RETURNS string
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
// 使用反引号定义模板字符串，避免单引号嵌套问题，同时支持多行格式
var task_info = `
select name,state,scheduled_time,completed_time,error_code,error_message,database_name,schema_name 
from snowflake.account_usage.task_history 
where scheduled_time >= dateadd('hour',-3,current_timestamp())
and query_id is not null 
and state != 'SUCCEEDED' 
and cast(scheduled_time as date) = current_date() 
order by scheduled_time desc
`;

rs = snowflake.execute({sqlText: task_info});

while (rs.next()) {
    // 给变量添加var声明，避免全局变量污染
    var tsk_name = rs.getcolumnvalue(1);
    var tsk_state = rs.getcolumnvalue(2);
    var tsk_scheduletime = rs.getcolumnvalue(3);
    var tsk_completedtime = rs.getcolumnvalue(4);
    var tsk_error_code = rs.getcolumnvalue(5);
    var tsk_error_msg = rs.getcolumnvalue(6);
    var tsk_database_name = rs.getcolumnvalue(7);
    var tsk_schema_name = rs.getcolumnvalue(8);
    
    var tsk_err_msg_parse = tsk_error_msg.replace(/'/g,'"');

    // 用模板字符串拼接send_email调用，简化动态内容拼接
    var proc = `
        call system$send_email(
            'NOTIFICATION_INT',
            'abhixyz@outlook.com',
            'TASK FAILURE NOTIFICATION',
            'Task Name ${tsk_name} is failed due to the error code ${tsk_error_code} and error message is ${tsk_err_msg_parse}'
        )
    `;
    var stmt = snowflake.createStatement({sqlText: proc});
    stmt.execute();
}

return "Succeeded! Email Alert has been sent for the list of Failed Task";
$$;

关键修改说明 #

• 字符串定义优化：将包裹SQL的单引号替换为反引号（`），JavaScript模板字符串允许直接嵌套单引号，且支持多行格式，大幅提升代码可读性和避免语法错误。
• 变量声明规范：给循环内的变量添加var声明，避免隐式全局变量引发的潜在问题。
• 代码格式化：将长SQL拆分为多行，逻辑更清晰。

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