Using SYSTEM$SEND_SNOWFLAKE_NOTIFICATION to send notifications to email addresses and cloud provider queues¶

Sending a notification¶

Before you send a notification, you must have a notification integration that you will use to send the notification. If you are sending an email notification, you must also validate the email addresses of the recipients. For details, see Notifications in Snowflake.

To send a notification to email addresses or queues, call the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure, specifying the messages and the notification integrations to use.

The following is an example of a call to this stored procedure:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
   -- Message type and content.
  '{ "text/html": "<p>This is a message.</p>" }',
  -- Integration used to send the notification and values used for the subject and recipients.
  -- These values override the defaults specified in the integration.
  '{
    "my_email_int": {
      "subject": "Status update",
      "toAddress": ["person_a@example.com", "person_b@example.com"],
      "ccAddress": ["person_c@example.com"],
      "bccAddress": ["person_d@example.com"]
    }
  }'
);

As shown in the example above, you pass in JSON-formatted strings as arguments to specify the message to send and the notification integration to use.

For the syntax for these strings, see SYSTEM$SEND_SNOWFLAKE_NOTIFICATION.

To construct these JSON-formatted strings, you can call helper functions like TEXT_HTML to specify the message and EMAIL_INTEGRATION_CONFIG to specify the notification integration, subject line, and email addresses. For example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_HTML('<p>a message</p>'),
  SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
    'my_email_int',
    'Status update',
    ARRAY_CONSTRUCT('person_a@example.com', 'person_b@example.com'),
    ARRAY_CONSTRUCT('person_c@example.com'),
    ARRAY_CONSTRUCT('person_d@example.com')
  )
);

For the list of helper functions that you can use, see SYSTEM$SEND_SNOWFLAKE_NOTIFICATION.

Overriding the default values in the email notification integration¶

To use a different set of recipients or a different subject line from the default specified in the email notification integration, set the following properties of the integration configuration object that you pass to SYSTEM$SEND_SNOWFLAKE_NOTIFICATION:

• subject (this cannot exceed 256 characters in length)

• toAddress

• ccAddress

• bccAddress

For example, to use the email notification integration my_email_int and override the subject line, “To:” line, “Cc:” line, and “Bcc:” line:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{ "text/html": "<p>This is a message.</p>" }',
  '{
    "my_email_int": {
      "subject": "Status update",
      "toAddress": ["person_a@example.com", "person_b@example.com"],
      "ccAddress": ["person_c@example.com"],
      "bccAddress": ["person_d@example.com"]
    }
  }'
);

To construct the JSON-formatted string for the integration configuration, you can call the EMAIL_INTEGRATION_CONFIG helper function.

For example, to send the email message to oncall-a@snowflake.com and oncall-b@snowflake.com with the subject line “Service down”, execute the following statement:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('Your message'),
  SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
    'my_email_int,
    'Service down',
    ARRAY_CONSTRUCT('oncall-a@example.com', 'oncall-b@example.com')
  )
);

To include the “Cc:” and “Bcc:” lines in the email message, pass in additional arguments with arrays of email addresses for those lines:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('Your message'),
  SNOWFLAKE.NOTIFICATION.EMAIL_INTEGRATION_CONFIG(
    'my_email_int,
    'Service down',
    ARRAY_CONSTRUCT('oncall-a@example.com', 'oncall-b@example.com'),
    ARRAY_CONSTRUCT('cc-a@example.com', 'cc-b@example.com'),
    ARRAY_CONSTRUCT('bcc-a@example.com', 'bcc-b@example.com')
  )
);

If you only want to set the “Cc:” or “Bcc:” line (not both), pass in an empty array or NULL for the corresponding arguments. If you are constructing the JSON object without using the helper function, omit the ccAddress or bccAddress property from the JSON object.

Sending HTML, plain text, and JSON messages¶

To send a message in HTML, plain text, or JSON, pass in a JSON object that contains the message type as the name of the property and the message as the value of the property:

'{ "<message_type>": "<message>" }'

"message_type" can be one of the following values:

• "text/html"

• "text/plain"

• "application/json"

For example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{ "text/html": "<p>This is a message.</p>" }',
  '{ "my_email_int": {} }'
);

To construct the JSON object for the message, you can use the following helper functions:

• For an HTML message, call TEXT_HTML.

• For a plain text message, call TEXT_HTML.

• For a JSON message, call APPLICATION_JSON.

The following example sends an HTML message, using the my_email_int email notification integration:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_HTML('<p>a message</p>'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
);

The following example sends a plain text message, using the same integration:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
);

The following example sends a JSON message to the queue specified by the my_queue_int notification integration. For instructions on creating a notification integration for a queue, see the following topics:

• Creating a notification integration to send notifications to an Amazon SNS topic

• Creating a notification integration to send notifications to a Microsoft Azure Event Grid topic

• Creating a notification integration to send notifications to a Google Cloud Pub/Sub topic

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.APPLICATION_JSON('{ "name": "value" }'),
  SNOWFLAKE.NOTIFICATION.INTEGRATION('my_sns_int')
);

Sending a notification using multiple integrations¶

You can use multiple integrations to send messages when:

• You want to send a message in email and to a topic in the same function call.

• You want to send a message to different email addresses specified by different email notification integrations.

To use multiple integrations, call the ARRAY_CONSTRUCT function to construct an array of integration configurations, and pass the array as the second argument of the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.

For example, to send a plain text message to a queue and email addresses configured in different notification integrations:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  '{"text/plain":"A message"}',
  ARRAY_CONSTRUCT(
    '{"my_sns_int":{}}',
    '{"my_email_int":{}}',
 )
);

If you prefer to use the helper functions to construct the integration configurations, you can pass the values returned by the helper functions to the ARRAY_CONSTRUCT function. For example:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message'),
  ARRAY_CONSTRUCT(
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_sns_int'),
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
  )
);

The following example sends messages in different formats to a queue and email addresses:

CALL SYSTEM$SEND_SNOWFLAKE_NOTIFICATION(
  ARRAY_CONSTRUCT(
    SNOWFLAKE.NOTIFICATION.TEXT_PLAIN('A message'),
    SNOWFLAKE.NOTIFICATION.TEXT_HTML('<p>A message</p>'),
    SNOWFLAKE.NOTIFICATION.APPLICATION_JSON('{ "name": "value" }')
  ),
  ARRAY_CONSTRUCT(
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_sns_int'),
    SNOWFLAKE.NOTIFICATION.INTEGRATION('my_email_int')
  )
);