1

I followed this tutorial in the snowflake documentation (Option 2) and I have a couple of questions I am hoping someone can help me with.

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3.html#option-2-configuring-amazon-sns-to-automate-snowpipe-using-sqs-notifications

  1. When I try to create a pipe with the following command
create pipe <pipe> 
    auto_ingest=true 
    aws_sns_topic='<sns topic arn>.fifo'
    as
    copy into <table>
    FROM (SELECT $1:"id", $1:"name", $1:"contact", CURRENT_TIMESTAMP::TIMESTAMP_NTZ, $1:"active" FROM @<stage>)
    FILE_FORMAT = <previously defined form which is json>

I get this error: Pipe Notifications bind failure "Invalid parameter: Invalid parameter: Endpoint Reason: Please use FIFO SQS queue (Service: Sns, Status Code: 400, Request ID: <request id>, Extended Request ID: null)"

Can anyone tell me where I am going wrong or guide me on how I can troubleshoot this error? I am new to Snowflake.

  1. Another thing I did notice about the tutorial which confused me was in Option 2 - Step 1: Subscribe the Snowflake SQS Queue to the SNS Topic.

This seemed to indicate that you should use the sns topic arn in this command

select system$get_aws_sns_iam_policy('<sns_topic_arn>');

...but then when they expand on their example then use the bucket arn

select system$get_aws_sns_iam_policy('arn:aws:sns:us-west-2:001234567890:s3_mybucket');

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SYSTEM$GET_AWS_SNS_IAM_POLICY('ARN:AWS:SNS:US-WEST-2:001234567890:S3_MYBUCKET')                                                                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"Version":"2012-10-17","Statement":[{"Sid":"1","Effect":"Allow","Principal":{"AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"},"Action":["sns:Subscribe"],"Resource":["arn:aws:sns:us-west-2:001234567890:s3_mybucket"]}]}

Which should I use for this step?

Thanks!

MountainBiker
  • 327
  • 5
  • 20
  • 2
    The **aws_sns_topic** should be set to **arn:aws:sns:us-west-2:001234567890:s3_mybucket** and that COPY INTO refers to a stage I assume and not an integration as mentioned in your case **FROM @)**? – Sergiu Nov 18 '21 at 09:42
  • It does refer to a stage. I will update to correct the typo. Thanks. I will also update the arn and see if that resolves the issue. – MountainBiker Nov 18 '21 at 14:51
  • I figured out the problem, in case someone else runs into the same issue. I was trying to subscribe Snowflake SQS Queue to an FIFO SNS topic. This is not possible. This link explains it. https://stackoverflow.com/questions/66661769/how-to-subscribe-an-sqs-fifo-queue-to-a-standard-sns-topic – MountainBiker Nov 18 '21 at 20:29
  • Also if anyone else is trying to follow the docs above and running into issues, it does appear to be telling you to use the bucket arn instead of the sns topic arn, which didn't make sense to me. This link makes it clearer. https://community.snowflake.com/s/article/How-to-setup-Snowpipe-with-SNS-configuration – MountainBiker Nov 18 '21 at 20:32

0 Answers0