snowflake-cloud-data-platform - 从雪花发送电子邮件警报

Question

如果 Snowflake 中的存储过程失败，我们有什么方法可以发送电子邮件警报？
当我检查雪花文档时，雪花中没有提到电子邮件实用程序

Answer 1

您可以直接从 Snowflake 发送电子邮件，也可以选择将表/ View 中的数据作为附件发送。这是使用 Snowflake External 函数完成的，该函数又通过 AWS Gateway 调用 AWS Lambda 函数。
第一步是设置 AWS 网关。您可以按照以下说明进行操作:
Creating a Customizable External Function on AWS
如果您从 Snowflake 获得了示例函数，则您已经成功地为添加电子邮件功能奠定了基础。接下来是设置一个 S3 存储桶来创建需要作为电子邮件附件发送的数据文件。

创建一个名为“snowapi”的 AWS S3 存储桶。我们不需要将此存储桶公开到互联网，因此将“阻止所有公共(public)访问”设置为“开”。

现在您需要提供对该存储桶的 Snowflake 访问权限。创建 IAM 用户“雪花”。添加权限 -> 附加退出策略:AmazonS3FullAccess。转到“安全凭据”选项卡和“创建访问 key ”。使用以下命令中的访问 key ID 和 secret 访问 key 将数据卸载到 S3 存储桶中。
创建或替换 UTIL.AWS_S3_STAGE URL='s3://snowapi/'
凭据=(AWS_KEY_ID='ABCD123456789123456789'
AWS_SECRET_KEY='ABCD12345678901234567890123456789');
复制到@UTIL.AWS_S3_STAGE/outbound/SampleData.csv
从
文件格式 =
覆盖 = 真
单 = 真;

下一步是使用下面的 Nodejs 代码创建一个新的 Lambda 函数。请注意，这使用 SENDGRID API 。 Sendgrid 有一个永久免费层，每天 100 封电子邮件。我在本地安装了这个库并将 uploaded the 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');

祝你好运!!