CODEWITHSUNDEEP
snowflake-cloud-data-platform
Naveed
Naveed

Reputation: 411

Sending Email alerts from Snowflake

Is there any way we can send email alerts if stored procedure fails in Snowflake? When I checked snowflake documentation, there is no mention of email utility in Snowflake

Upvotes: 4

Views: 18085

Answers (4)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

Sending Email Notifications:

This feature uses the notification integration object, which is a Snowflake object that provides an interface between Snowflake and third-party services (e.g. cloud message queues, email, etc.). A single account can define a maximum of ten email integrations and enable one or more simultaneously.

To create an email notification integration, use the CREATE NOTIFICATION INTEGRATION command with TYPE=EMAIL:

CREATE [ OR REPLACE ] NOTIFICATION INTEGRATION [IF NOT EXISTS]
    <integration_name>
    TYPE=EMAIL
    ENABLED={TRUE|FALSE}
    ALLOWED_RECIPIENTS=('<email_address_1>' [, ... '<email_address_N>'])
    [ COMMENT = '<string_literal>' ]
;

After creating the email notification integration, you can call SYSTEM$SEND_EMAIL() to send an email notification, as follows:

 CALL SYSTEM$SEND_EMAIL(
    '<integration_name>',
    '<email_address_1> [, ... <email_address_N>]',
    '<email_subject>',
    '<email_content>'
);

...

For example:

CALL SYSTEM$SEND_EMAIL(
  'my_email_int',
  '[email protected], [email protected]',
  'Email Alert: Task A has finished.',
  'Task A has successfully finished.\nStart Time: 10:10:32\nEnd Time: 12:15:45\nTotal Records Processed: 115678'
);

Upvotes: 3

Vijay Nair
Vijay Nair

Reputation: 69

You can send email directly from Snowflake optionally sending data from a table/view as an attachment. This is done using Snowflake External function which in turn calls an AWS Lambda function via AWS Gateway. The first step is to setup the AWS Gateway. You may follow instructions below: Creating a Customizable External Function on AWS

If you got the sample function working from Snowflake, you have successfully setup the foundation for adding email functionality. Next is to setup an S3 bucket to create datafiles that need to be sent as email-attachment.

  1. Create an AWS S3 bucket with the name 'snowapi'. We need not expose this bucket to the internet, so keep 'Block all public access' set to ON.

  2. Now you need to provide Snowflake access to this Bucket. Create an IAM user 'snowflake'. Add Permissions -> Attach exiting Policy: AmazonS3FullAccess. Go to 'Security Credetials' tab and 'Create access key'. Use the Access Key ID and Secret Access Key in the below command to unload data into S3 bucket.

    CREATE OR REPLACE UTIL.AWS_S3_STAGE URL='s3://snowapi/' CREDENTIALS=(AWS_KEY_ID='ABCD123456789123456789' AWS_SECRET_KEY='ABCD12345678901234567890123456789');

    COPY INTO @UTIL.AWS_S3_STAGE/outbound/SampleData.csv FROM FILE_FORMAT = OVERWRITE = TRUE SINGLE = TRUE;

  3. Next step is to create a new Lambda function using the Nodejs code below. Note that this uses SENDGRID API. Sendgrid has a forever-free tier with 100 emails per day. I installed this library locally and uploaded the zip file to AWS to create the Lambda function.

//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. Set the below two environment variables for the Lambda function:

     SENDGRID_KEY: <sendgrid_api_key>
 BUCKET_NAME: snowapi

  2. Create a Snowflake External Function:

     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';

  3. Create a wrapper Procedure for the above external function:

     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||'[email protected]',
                                                 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, " ");
   }
 $$;

  4. All Set! The end result is a clean call that sends email like below.

     Call SENDEMAIL('[email protected], [email protected]',
                 '[email protected]', 
                 'Test Subject',
                 'Sample Body');

Good Luck!!

Upvotes: 6

Ben K
Ben K

Reputation: 41

We use the snowsql command from bash scripts, and use the "-o exit_on_error=true" option on the command line, checking the return code at the end of the step. If the Snowflake commands have failed, then the exit on error setting will mean that Snowflake will stop at the point of the error and return control to the calling program.

If the return code is zero, then we move onto the next step.

If it is non-zero, then we call an error handler which sends an email and then quits the job.

We're on Amazon Linux for our orchestration, and we use mutt as an email application.

Upvotes: 1

Sriga
Sriga

Reputation: 1321

I believe there is no email utility in Snowflake, But you can run your snowflake stored procedure using python and check the stored procedure status, based on the status you can trigger mail from python.

Upvotes: 4

Related Questions