0

I am trying to refresh external table using SNS in snowflake. I have followed this tutorial to refresh.

https://www.youtube.com/watch?v=PCNa3d6rMO0

it is working as expected.but when I use same topic to trigger another table in another S3 bucket. Can't I use the same topic and create event notifications in Bucket2?

Here is my Access Policy :

{
  "Version": "2008-10-17",
  "Id": "__default_policy_ID",
  "Statement": [
    {
      "Sid": "__default_statement_ID",
      "Effect": "Allow",
      "Principal": {
        "AWS": "*"
      },
      "Action": [
        "SNS:Publish",
        "SNS:RemovePermission",
        "SNS:SetTopicAttributes",
        "SNS:DeleteTopic",
        "SNS:ListSubscriptionsByTopic",
        "SNS:GetTopicAttributes",
        "SNS:AddPermission",
        "SNS:Subscribe"
      ],
      "Resource": "arn:aws:sns:us-west-1:58:snowflake-dev-SNS",
      "Condition": {
        "StringEquals": {
          "AWS:SourceOwner": "55"
        }
      }
    },
    {
      "Sid": "__console_pub_0",
      "Effect": "Allow",
      "Principal": {
        "AWS": "*"
      },
      "Action": "SNS:Publish",
      "Resource": "arn:aws:sns:us-west-1:55:snowflake-dev-SNS"
    },
    {
      "Sid": "1",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::2:user/b6m8-s-p2s9"
      },
      "Action": "sns:Subscribe",
      "Resource": "arn:aws:sns:us-west-1:55:snowflake-dev-SNS"
    }
  ]
}

Thanks, Xi

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Xi12
  • 939
  • 2
  • 14
  • 27

1 Answers1

0

I'm not a Snowflake person, but it appears that the linked demonstration is Configuring Amazon SNS to Automate Snowpipe Using SQS Notifications .

According to that documentation, the following code is used to create the pipe:

create pipe snowpipe_db.public.mypipe
  auto_ingest=true
  aws_sns_topic='<sns_topic_arn>'
  as
    copy into snowpipe_db.public.mytable
    from @snowpipe_db.public.mystage
  file_format = (type = 'JSON');

The copy into snowpipe_db.public.mytable seems to be hard-coded for a destination table. It seems that each Snowpipe can only be used to load data into a single table.

Therefore, you would likely need to use a different Snowpipe, and therefore a different SNS Topic and SQS queue, if you wish to load data into a different table.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • Sorry I am not creating a pipe. I want it to work as a external table – Xi12 Apr 03 '22 at 23:27
  • The linked video shows an Amazon SQS subscription to the Amazon SNS topic [at 11:18](https://youtu.be/PCNa3d6rMO0?t=678) and the endpoint for SQS mentions `snowpipe`. – John Rotenstein Apr 04 '22 at 00:57
  • Did you added both your bucket locations in your Storage integration object (remember to modify your ARN policy if you are modifying/recreating your storage integration object )? - https://docs.snowflake.com/en/sql-reference/sql/create-storage-integration.html#syntax – Pankaj Apr 04 '22 at 01:21