snowflake-cloud-data-platform - 从雪花发送电子邮件警报
问题描述
如果 Snowflake 中的存储过程失败,我们有什么方法可以发送电子邮件警报?当我检查雪花文档时,雪花中没有提到电子邮件实用程序
解决方案
您可以直接从 Snowflake 发送电子邮件,也可以选择从表格/视图作为附件发送数据。这是使用 Snowflake External 函数完成的,该函数又通过 AWS Gateway 调用 AWS Lambda 函数。第一步是设置 AWS 网关。您可以按照以下说明进行操作: 在 AWS 上创建可自定义的外部函数
如果您从 Snowflake 中获得了示例函数,那么您已经成功地为添加电子邮件功能奠定了基础。接下来是设置一个 S3 存储桶来创建需要作为电子邮件附件发送的数据文件。
创建一个名为“snowapi”的 AWS S3 存储桶。我们不需要将此存储桶公开到 Internet,因此请将“阻止所有公共访问”设置为 ON。
现在您需要提供对这个 Bucket 的 Snowflake 访问权限。创建一个 IAM 用户“雪花”。添加权限 -> 附加退出策略:AmazonS3FullAccess。转到“安全凭据”选项卡和“创建访问密钥”。使用以下命令中的访问密钥 ID 和秘密访问密钥将数据卸载到 S3 存储桶中。
创建或替换 UTIL.AWS_S3_STAGE URL='s3://snowapi/' CREDENTIALS=(AWS_KEY_ID='ABCD123456789123456789' AWS_SECRET_KEY='ABCD12345678901234567890123456789');
从 FILE_FORMAT 复制到 @UTIL.AWS_S3_STAGE/outbound/SampleData.csv = OVERWRITE = TRUE SINGLE = TRUE;
下一步是使用下面的 Nodejs 代码创建一个新的 Lambda 函数。请注意,这使用SENDGRID API。Sendgrid 有一个永远免费的层级,每天 100 封电子邮件。我在本地安装了这个库并将zip文件上传到 AWS 以创建 Lambda 函数。
//Lambda Function name: email
const sgMail = require('@sendgrid/mail');
var AWS = require('aws-sdk');
var s3 = new AWS.S3();
exports.handler = async (event, context, callback) => {
sgMail.setApiKey(process.env.SENDGRID_KEY);
const paramArray = JSON.parse(event.body).data[0];
//paramArray[0] has the row number from Snowflake
var message = {
to: paramArray[1].replace(/\s/g, '').split(','),
from: paramArray[2].replace(/\s/g, ''),
subject: paramArray[3],
html: paramArray[4]
};
// Attach file
if (paramArray.length > 5) {
var fileName = paramArray[5].substring(paramArray[5].lastIndexOf("/")+1);
var filePath = paramArray[5].substring(0, paramArray[5].lastIndexOf("/"));
try {
const params = {Bucket: process.env.BUCKET_NAME + filePath, Key: fileName};
const data = await s3.getObject(params).promise();
var fileContent = data.Body.toString('base64');
} catch (e) {
throw new Error(`Could not retrieve file from S3: ${e.message}`);
}
message.attachments = [{content: fileContent,
filename: fileName,
type: "application/text",
disposition: "attachment"
}];
}
try{
await sgMail.send(message);
return {
'statusCode': 200,
'headers': { 'Content-Type': 'application/json' },
'body' : "{'data': [[0, 'Email Sent to "+ paramArray[1] + "']]}"
};
} catch(e){
return {
'statusCode': 202,
'headers': { 'Content-Type': 'application/json' },
'body' : "{'data': [[0, 'Error - " + e.message + "']]}"
};
}
};
为 Lambda 函数设置以下两个环境变量:
SENDGRID_KEY: <sendgrid_api_key> BUCKET_NAME: snowapi
创建雪花外部函数:
create or replace external function util.aws_email (mailTo varchar,mailFrom varchar,subject varchar,htmlBody varchar,fileName varchar) returns variant api_integration = aws_api_integration as 'https://xxxxxxxxxx.execute-api.us-east-1.amazonaws.com/PROD/email';
为上述外部函数创建一个包装程序:
create or replace procedure util.sendemail (MAILTO varchar,MAILFROM varchar,SUBJECT varchar,HTMLBODY varchar,FILENAME varchar) returns string language javascript EXECUTE AS OWNER as $$ // Call the AWSLambda function. var qry = "select util.aws_email(:1,:2,:3,:4,:5)"; // null should be in lowercase. var stmt = snowflake.createStatement({sqlText: qry, binds: [MAILTO, MAILFROM||'no-reply@yourdomain.com', SUBJECT ||'Email sent from Snowflake', HTMLBODY||'<p>Hi there,</p> <p>Good luck!</p>', FILENAME||null] }); var rs; try{ rs = stmt.execute(); rs.next(); return rs.getColumnValue(1); } catch(err) { throw "ERROR: " + err.message.replace(/\n/g, " "); } $$;
可以了,好了!最终结果是一个干净的呼叫,发送如下电子邮件。
Call SENDEMAIL('to_email@dummy.com, to_another_email@dummy.com', 'from@yourdomain.com', 'Test Subject', 'Sample Body');
祝你好运!!
推荐阅读
- kotlin - 如何将规则应用于 AndroidJUnitRunner 中的所有测试用例?
- php - 查询模块的 WP ACF Schema
- python-unittest.mock - python mock patch 原始函数仍然被调用
- flutter - Flutter Unhandled Exception: FormatException: Invalid date format
- python - 假设在 smart_open (python) 中的 AWS 角色不起作用
- mysql - 重启后无法在 mysql db 上创建用户或授予权限
- python - crypt.crypt 引发 OSError: Invalid Argument
- elasticsearch - fluentd可以使用elasticsearch插件轮换日志吗?
- ruby-on-rails - 来自具有许多子关联的父记录的 Rails csv 行
- r - 具有重叠日期的 10 天间隔