如何通过Snowflake获取30天未更新表的邮件告警？

Snowflake本身没有内置邮件发送功能，但可以通过存储过程+外部邮件服务+定时任务的组合实现需求，具体步骤如下：

1. 核心逻辑 #

用存储过程封装你的查询逻辑，借助Snowflake的外部访问集成调用第三方SMTP服务（比如Gmail、AWS SES）发送查询结果，再通过Snowflake任务定期触发这个存储过程。

2. 存储过程示例 #

以下是JavaScript编写的存储过程，包含查询未更新表、拼接邮件内容、调用SMTP发送邮件的完整逻辑：

CREATE OR REPLACE PROCEDURE SEND_STALE_TABLE_ALERTS()
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
EXTERNAL_ACCESS_INTEGRATIONS = (YOUR_EXTERNAL_ACCESS_INTEGRATION_NAME)
PACKAGES = ('snowflake-sdk', 'nodemailer')
AS
$$
const snowflake = require('snowflake-sdk');
const nodemailer = require('nodemailer');

// 执行30天未更新基表的查询
const stmt = snowflake.createStatement({
  sqlText: `
    SELECT table_schema,
           table_name,
           last_altered AS modify_time
    FROM information_schema.tables
    WHERE last_altered < DATEADD(DAY, -30, CURRENT_TIMESTAMP)
          AND table_type = 'BASE TABLE'
    ORDER BY last_altered DESC;
  `
});
const result = stmt.execute();

// 拼接邮件文本内容
let emailContent = "Snowflake中30天未更新的基表列表：\n\n";
emailContent += "Schema\t表名\t最后修改时间\n";
emailContent += "----------------------------------------\n";
while (result.next()) {
  emailContent += `${result.getColumnValue(1)}\t${result.getColumnValue(2)}\t${result.getColumnValue(3)}\n`;
}

// 配置SMTP发送器（以Gmail为例，需提前开启应用密码）
const transporter = nodemailer.createTransport({
  service: 'gmail',
  auth: {
    user: 'your-alert-account@gmail.com',
    pass: 'your-app-specific-password'
  }
});

// 邮件参数配置
const mailOptions = {
  from: 'your-alert-account@gmail.com',
  to: 'alert-recipient@yourcompany.com',
  subject: '【告警】Snowflake存在30天未更新基表',
  text: emailContent
};

// 发送邮件并返回结果
try {
  const info = await transporter.sendMail(mailOptions);
  return `告警邮件已发送: ${info.response}`;
} catch (error) {
  return `邮件发送失败: ${error.toString()}`;
}
$$;

3. 前置配置 #

• 创建外部访问集成：需先配置网络规则和集成，允许存储过程访问SMTP服务的端口（比如Gmail的587端口）。
• 邮箱准备：使用的邮箱需支持SMTP，比如Gmail要开启两步验证后生成应用密码，不能直接用登录密码。
• 包依赖：nodemailer是Snowflake支持的npm包，无需额外上传。

4. 配置定时任务 #

创建Snowflake任务，定期执行存储过程，比如每天UTC时间9点运行：

CREATE OR REPLACE TASK STALE_TABLE_ALERT_TASK
WAREHOUSE = YOUR_WAREHOUSE_NAME
SCHEDULE = 'USING CRON 0 9 * * * UTC'
AS
CALL SEND_STALE_TABLE_ALERTS();

-- 启用任务
ALTER TASK STALE_TABLE_ALERT_TASK RESUME;

注意事项 #

• 可将邮件内容改成HTML格式提升可读性，只需修改mailOptions中的html字段。
• 外部访问集成的配置需对应你的云环境（AWS/Azure/GCP），具体规则参考Snowflake官方文档的外部访问部分。
• 测试时可手动调用CALL SEND_STALE_TABLE_ALERTS();验证邮件发送状态。

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