使用 SYSTEM$SEND_SNOWFLAKE_NOTIFICATION 发送通知¶

Note

Email notifications are processed through Snowflake’s Amazon Web Services (AWS) deployments, using AWS Simple Email Service (SES). The content of an email message sent using AWS may be retained by Snowflake for up to thirty days to manage the delivery of the message. After this period, the message content is deleted.

If you need to send notifications to an email address, webhook, or a queue provided by a cloud service (Amazon SNS, Google Cloud PubSub, or Azure Event Grid), use the SYSTEM$SEND_SNOWFLAKE_NOTIFICATION stored procedure.

只需调用一次此存储过程，您就可以：

向多种类型的目的地（电子邮件地址、Webhook 和队列）发送消息。
向多个电子邮件地址、Webhook 和队列发送消息。
Send a message in a specified format, according to the type of notification integration (plain text or HTML for email, JSON for queues).

例如，只需一次调用，您就可以向多个电子邮件地址和多个 SNS、PubSub 以及事件网格主题，发送纯文本、HTML 和 JSON 格式的消息。

您可以使用多个通知集成，将通知发送到不同的队列。您还可以创建具有不同电子邮件地址和主题行的多个电子邮件通知集成，从而更轻松地为不同的收件人配置电子邮件消息。

发送通知

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.

以下是调用此存储过程的示例：

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"]
    }
  }'
);

如上例所示，您传入 JSON 格式的字符串作为实参，来指定要发送的消息和要使用的通知集成。

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.

替换电子邮件通知集成中的默认值

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

要在电子邮件消息中包含“Cc:”和“Bcc:”行，需传递额外的实参，这些实参是以电子邮件地址数组的形式给出的：

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.

发送 HTML、纯文本和 JSON 消息¶

要发送 HTML、纯文本或 JSON 格式的消息，请传入一个包含消息类型作为属性名、消息内容作为属性值的 JSON 对象：

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

"message_type" can be one of the following values:

"text/html"
"text/plain"
"application/json"

例如：

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

以下示例发送纯文本消息，使用相同的集成：

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:

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

使用多个集成发送通知

在以下情况下，您可以使用多个集成来发送消息：

您希望在同一函数调用中发送电子邮件消息并发送到一个主题。
您想要向不同电子邮件通知集成指定的不同电子邮件地址发送消息。

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.

例如，要向队列和在不同通知集成中配置的电子邮件地址发送纯文本消息：

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

Note

该数组不能包含同一通知集成的多个对象。

如果您更喜欢使用辅助函数来构建集成配置，则可以将辅助函数返回的值传递给 ARRAY_CONSTRUCT 函数。例如：

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

以下示例将不同格式的消息发送到队列和电子邮件地址：

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