从Snowflake发送电子邮件警报

Question

如果Snowflake中的存储过程失败，我们有没有办法发送电子邮件警报？当我查看snowflake文档时，没有提到Snowflake中的电子邮件实用程序

Answer 1

您可以直接从Snowflake发送电子邮件，也可以选择将表/视图中的数据作为附件发送。这是使用Snowflake外部函数完成的，该函数通过AWS网关调用AWS Lambda函数。第一步是设置AWS网关。您可以按照以下说明进行操作：Creating a Customizable External Function on AWS

如果您从Snowflake获得了示例函数，那么您已经成功地建立了添加电子邮件功能的基础。下一步是设置一个S3存储桶来创建需要作为电子邮件附件发送的数据文件。

1. 创建一个名为'snowapi‘的S3存储桶。我们不需要将此存储桶暴露给互联网，因此请将“阻止所有公共访问”设置为ON。

1. 现在您需要提供对此Bucket的雪花访问权限。创建一个IAM用户'snowflake‘。添加权限->附加退出策略: AmazonS3FullAccess。转到“安全凭证”选项卡和“创建访问密钥”。使用以下命令中的Access Key ID和Secret Access Key将数据卸载到S3存储桶。

创建或替换雪花URL=‘s3:// UTIL.AWS_S3_STAGE /’CREDENTIALS=(AWS_KEY_ID='ABCD123456789123456789‘AWS_SECRET_KEY='ABCD12345678901234567890123456789')；

从FILE_FORMAT = OVERWRITE = TRUE SINGLE =TRUE复制到@UTIL.AWS_S3_STAGE/outbound/SampleData.csv；

1. 下一步是使用下面的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 + "']]}" 
      };
    } 
 
};

​

1. 为Lambda函数设置以下两个环境变量：

SENDGRID_KEY： BUCKET_NAME: snowapi

1. 创建雪花外部函数：

作为'https://xxxxxxxxxx.execute-api.us-east-1.amazonaws.com/PROD/email';，创建或替换外部函数util.aws_email (mailTo varchar，mailFrom varchar，subject varchar，htmlBody varchar，fileName varchar)返回变量api_integration = aws_api_integration

1. 为上面的外部函数创建一个包装过程：

创建或替换过程util.sendemail (MAILTO varchar，MAILFROM varchar，SUBJECT varchar，HTMLBODY varchar，FILENAME varchar)返回字符串语言javascript EXECUTE AS OWNER as $$ //调用AWSLambda函数。var qry = "select util.aws_email(:1，:2，:3，:4，:5)"；// null应为小写。var stmt = snowflake.createStatement({sqlText: qry，binds: MAILTO，MAILFROM||'no-reply@yourdomain.com'，主题||‘来自雪花的电子邮件’，HTMLBODY||‘你好，祝你好运！’，FILENAME||null })；变量rs；尝试{ rs = stmt.execute()；rs.next()；return rs.getColumnValue(1)；} catch(err) {抛出"ERROR：“+ err.message.replace(/\n/g，”")；} $$;

1. 都设置好了！最终结果是一个发送电子邮件的干净调用，如下所示。

Call SENDEMAIL('to_email@dummy.com，to_another_email@dummy.com'，'from@yourdomain.com'，‘测试对象’，‘样本正文’)；

祝你好运！

Answer 2

我相信Snowflake中没有电子邮件工具，但您可以使用python运行snowflake存储过程，并根据您可以从python触发邮件的状态来检查存储过程的状态。

Answer 3

我们在bash脚本中使用snowsql命令，并在命令行中使用"-o exit_on_error=true“选项，检查步骤结束时的返回代码。如果Snowflake命令失败，那么exit on error设置将意味着Snowflake将在错误点处停止并将控制权返回给调用程序。

如果返回码是零，那么我们进入下一步。

如果它不是零，那么我们调用一个错误处理程序，该处理程序发送一封电子邮件，然后退出作业。

我们使用Amazon Linux进行编排，并使用mutt作为电子邮件应用程序。