3
0
During this post we will see how we can configure the ALERT or EMAIL mechanism from Snowpipe. Say you have configured the Snowpipe in your account. In case of any file processing via Snowpipe, if error comes we don’t have any alert mechanism in place. Moreover, We need to monitor the status of pipe manually to confirm the status of load. To ease our monitoring process we will configure the Email notification which would trigger the mail in case of any failure.
Steps to be included:
- SNS Service: Firstly, Configure Simple Notification Service (Amazon SNS) is a managed service that provides message delivery from publishers to subscribers. Publishers communicate asynchronously with subscribers by sending messages to a Clients can subscribe to the SNS topic and receive published messages using a supported endpoint type, such as Amazon Kinesis Data Firehose, Amazon SQS
- Select Everyone for this use case in Access Policy-optional section.
- Create Topic and noted down the generated AR.
2. Secondly, Create subscription to whom mail will be sent.
3. Thirdly, and most importantly, Go to AWS Management Console and create Policy : snowflake_sns_error_notification
4. Moreover, Create Role and Associate Policy.
- Associate your previous created Policy
- Name the Role : snowpipe_SNS_Error_Role
Notification Integration:
5. Creating the Notification Integration.
- In addition, Create a notification integration using CREATE NOTIFICATION INTEGRATION. An integration is a Snowflake object that references the SNS topic you created
CREATE NOTIFICATION INTEGRATION Snowpipe_Error_Notify
ENABLED = true
TYPE = QUEUE
NOTIFICATION_PROVIDER = AWS_SNS
DIRECTION = OUTBOUND
AWS_SNS_TOPIC_ARN = 'arn:aws:sns:us-east-1:913267004595:snowpipe_alert'
AWS_SNS_ROLE_ARN = 'arn:aws:iam::913267004595:role/snowpipe_SNS_Error_Role'
- DESC notification integration Snowpipe_Error_Notify;
- Note down: SF_AWS_IAM_USER_ARN, SF_AWS_EXTERNAL_ID
6.After that, Modify the Trust Relationship in the IAM Role with above noted ARN and External ID.
7. Above all, Create PIPE and Enabling Error Notifications.
CREATE or replace PIPE demo_db.public.error_notify auto_ingest=true
ERROR_INTEGRATION = Snowpipe_Error_Notify
AS copy into CUST_INFO from @demo_db.public.ext_csv_stage/customers
8.Finally, Configure the PIPE ARN with S3 Bucket and upload the file to bucket. This will trigger the PIPE and in case of any error an email will be triggered to our mail ID.
