为Snowflake存储过程SEND_FAILURE_ALERT添加时间单位与数值参数

修改要点 #

• 新增两个输入参数：TIME_UNIT（取值限定为'hh'/'mm'）和TIME_VALUE（正整数），用于动态指定扫描的时间范围
• 添加参数合法性校验，避免非法输入导致SQL执行错误
• 动态构建DATEADD函数的参数，替换原固定的24小时逻辑
• 修复原代码中列索引错位的问题（原代码中部分字段的索引取值错误，导致数据对应列错位）

修改后的完整存储过程代码 #

CREATE OR REPLACE PROCEDURE SEND_FAILURE_ALERT(TIME_UNIT VARCHAR, TIME_VALUE INT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
-- 参数合法性校验
if (TIME_UNIT !== 'hh' && TIME_UNIT !== 'mm') {
    return "Error: TIME_UNIT must be either 'hh' (hours) or 'mm' (months)";
}
if (TIME_VALUE <= 0 || !Number.isInteger(TIME_VALUE)) {
    return "Error: TIME_VALUE must be a positive integer";
}

-- 动态构建查询SQL，替换固定时间范围
var sql_query = `
SELECT 
    file_name, stage_location, last_load_time, row_count, row_parsed, 
    file_size, first_error_message, first_error_line_number, 
    first_error_character_pos, first_error_column_name, error_count, 
    error_limit, status, table_name, table_schema_name, table_catalog_name 
FROM SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY 
WHERE last_load_time BETWEEN DATEADD('${TIME_UNIT}', -${TIME_VALUE}, GETDATE()) AND GETDATE() 
    AND status = 'Load failed'
`;

var sqlstmt = snowflake.createStatement({ sqlText: sql_query });
var rs = sqlstmt.execute();

var msg = `<html><body><table border="1"><tr>
<th>File_name</th><th>Stage_location</th><th>Last_load_time</th>
<th>Row_count</th><th>Row_parsed</th><th>File_size</th>
<th>First_error_message</th><th>First_error_line_number</th>
<th>First_error_character_pos</th><th>First_error_column_name</th>
<th>Error_count</th><th>Error_limit</th><th>Status</th>
<th>Table_name</th><th>Table_schema_name</th><th>Table_catalog_name</th>
</tr>`;

while (rs.next()) {
  var File_name = rs.getColumnValue(1);
  var Stage_location = rs.getColumnValue(2);
  var Last_load_time = rs.getColumnValue(3);
  var Row_count = rs.getColumnValue(4);
  var Row_parsed = rs.getColumnValue(5); -- 修复原代码索引错误
  var File_size = rs.getColumnValue(6); -- 修复原代码索引错误
  var First_error_message = rs.getColumnValue(7); -- 修复原代码索引错误
  var First_error_line_number = rs.getColumnValue(8); -- 修复原代码索引错误
  var First_error_character_pos = rs.getColumnValue(9); -- 修复原代码索引错误
  var First_error_column_name = rs.getColumnValue(10); -- 修复原代码索引错误
  var Error_count = rs.getColumnValue(11); -- 修复原代码索引错误
  var Error_limit = rs.getColumnValue(12); -- 修复原代码索引错误
  var Status = rs.getColumnValue(13); -- 修复原代码索引错误
  var Table_name = rs.getColumnValue(14); -- 修复原代码索引错误
  var Table_schema_name = rs.getColumnValue(15); -- 修复原代码索引错误
  var Table_catalog_name = rs.getColumnValue(16); -- 修复原代码索引错误

  msg += `<tr>
<td>${File_name}</td><td>${Stage_location}</td><td>${Last_load_time}</td>
<td>${Row_count}</td><td>${Row_parsed}</td><td>${File_size}</td>
<td>${First_error_message}</td><td>${First_error_line_number}</td>
<td>${First_error_character_pos}</td><td>${First_error_column_name}</td>
<td>${Error_count}</td><td>${Error_limit}</td><td>${Status}</td>
<td>${Table_name}</td><td>${Table_schema_name}</td><td>${Table_catalog_name}</td>
</tr>`;
}

msg += `</table></body></html>`;

var proc = `CALL SYSTEM$SEND_EMAIL('ERRORS_ALERTS', 'my_email@gmail.com', 'Task Failure Alert: Snowflake Jobs', '${msg}', 'text/html')`;

var stmt = snowflake.createStatement({ sqlText: proc });
stmt.execute();

return "Succeeded! Email sent";
$$;

调用示例 #

• 扫描最近24小时的加载错误：

  CALL SEND_FAILURE_ALERT('hh', 24);
  

• 扫描最近1个月的加载错误：

  CALL SEND_FAILURE_ALERT('mm', 1);
  

补充说明 #

1. 参数校验部分会在输入非法时直接返回错误信息，避免后续SQL执行失败
2. 原代码中存在列索引与SELECT字段顺序不匹配的问题，已全部修正，确保邮件中展示的数据与字段对应
3. 动态SQL构建时使用了模板字符串，确保时间单位和数值正确注入到DATEADD函数中

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