Sending Email Notifications

Note

The Notification System Stored Procedure feature is only hosted on Amazon Web Services (AWS). If you choose to use this feature, the hosting provider for this feature will be AWS and the available location(s) of the hosting will be as set forth in the table below. The content of a message sent using AWS SES may be retained by Snowflake for up to thirty days to manage the delivery of the message and is thereafter deleted.

Supported AWS Regions

us-west-2

us-east-1

eu-west-1

This topic explains how to use the built-in SYSTEM$SEND_EMAIL() stored procedure to send email notifications.

In this Topic:

Introduction

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 send an email notification:

  1. Create a notification integration.

  2. Make sure that the intended recipients of the email notification have verified their email addresses.

  3. Grant the privilege to use the notification integration.

  4. Call SYSTEM$SEND_EMAIL() to send an email notification.

Creating a Notification Integration

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

Where:

  • integration_name is the name of the new integration.

  • 'email_address_1' [, ... 'email_address_N'] is a comma-separated list of email addresses (each surrounded by single quotes) that can receive notification emails from this integration.

    These email addresses must be associated with users in the current account. The email addresses must be verified.

    The maximum number of email addresses that you can specify is 50.

  • 'string_literal' specifies an optional comment for the integration.

For example:

CREATE NOTIFICATION INTEGRATION my_email_int
    TYPE=EMAIL
    ENABLED=TRUE
    ALLOWED_RECIPIENTS=('first.last@example.com','first2.last2@example.com')
;

Note

You can define a maximum of ten email notification integrations for a given account.

For details about the syntax of this command, see CREATE NOTIFICATION INTEGRATION.

Verifying Email Addresses of Notification Recipients

Email notifications can only be sent to Snowflake users within the same account, and those users must verify their email addresses through one of the following interfaces:

For each email address specified in the ALLOWED_RECIPIENTS parameter in the email notification integration, make sure that the corresponding Snowflake user has verified that email address.

Granting the Privilege to Use the Notification Integration

When calling SYSTEM$SEND_EMAIL(), you pass in an email notification integration that is used to send the email. You must use a role that is granted the USAGE privilege on this email notification integration.

To grant this privilege to the role for the owner or caller, execute the GRANT <privileges> … TO ROLE command.

For example, to grant the USAGE privilege on the notification integration my_email_int to the role my_sp_owner_role, execute the following command:

GRANT USAGE ON INTEGRATION my_email_int TO ROLE my_sp_owner_role;

Sending an Email Notification

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

where:

  • integration_name is the name of the notification integration.

  • email_address_1 [, ... email_address_N] is a comma-separated string of one or more unquoted email addresses that can receive notification emails from this integration.

    The email addresses in this list must be in the ALLOWED_RECIPIENTS specified in the notification integration.

  • email_subject is the subject line of the email notification. You cannot specify an empty string.

  • email_content is the body of the email. You cannot specify an empty string.

    Snowflake currently supports only plain text email.

For example:

CALL SYSTEM$SEND_EMAIL(
    'my_email_int',
    'person1@example.com, person2@example.com',
    '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'
);

Note

If any email address in the recipient list is not included in the ALLOWED_RECIPIENTS parameter for the notification integration, no emails are sent.

In the email notification message, the From: address is no-reply@snowflake.net.

Back to top