0

I am new to Snowflake, Trying to notify the snowpipe when a file is placed in a s3 bucket and thru SQL event Notification the snowpipe needs to load the data into the respective table.

Below are the steps I have followed.

Step -1: created a table in Snowflake

create table Customer_Notification (Id Varchar(10),Name Varchar(25) );

Step -2: Created a File Format

create or replace file format csv_Notification type = 'csv' compression = 'auto' field_delimiter = ',' record_delimiter = '\n' skip_header = 1 field_optionally_enclosed_by = '\042' null_if = ('\\N');

Step - 3: Created an external Stage

create stage Customer_Notification_S3 url = 's3://snow-flake-test/ContinuousDataLoad/CustomerNotification/' CREDENTIALS = (AWS_KEY_ID = '*******' AWS_SECRET_KEY = '**********') file_format = csv_Notification comment = 'feed CustomerNotification files';

Step - 4: Create Pipe

create or replace pipe Customer_Notification_pipe auto_ingest = true as copy into customer_Notification from @Customer_Notification_S3 file_format = csv_Notification pattern='.*customernotification.*[.]csv' ON_ERROR = 'CONTINUE';

Step - 5 : Get the ARN Number

show pipes; -- arn:aws:sqs:us-east-2:306559659303:sf-snowpipe-AIDAUOYDBFUT7Y5DFHK4K-RdvAOUMAMjYD8diWaS1eWw

Step -6 : Open aws S3 bucket in the parent folder, click on properties to create an even notification . Event name - customer_notification

Prefix - optional - snow-flake-test/ContinuousDataLoad/CustomerNotification/

Suffix - optional - .csv

Event types - All object create events

Destination - Selected SQS queue

Enter SQS queue ARN SQS queue - arn:aws:sqs:us-east-2:306559659303:sf-snowpipe-AIDAUOYDBFUT7Y5DFHK4K-RdvAOUMAMjYD8diWaS1eWw

Finally Saved the Event Notification

I have followed the abovesteps, even after this, when a file is placed in the respective snow-flake-test/ContinuousDataLoad/CustomerNotification/ folder the pipe is not picking the file and not loading it. checked the pipe status too , it is in running state. NOTE : I am in a role Accountaadmin

Could anyone please help me to figure out what is missing.

Gayathri
  • 1
  • Try giving - alter pipe Customer_Notification_pipe refresh – Pankaj Apr 25 '22 at 12:04
  • Is a LIST showing the files if you run: **LIST @Customer_Notification_S3 pattern='.*customernotification.*[.]csv';** – Sergiu Apr 25 '22 at 12:44
  • The prefix contains the bucket name; since the stage is formed to the exact location, the prefix is not necessary. Remove it and try. – Abhi Reddy Apr 25 '22 at 13:18
  • Hello Pankaj, Regarding Try giving - alter pipe Customer_Notification_pipe refresh , i tried this alter statement, after running the alter statement the the notification has sent and the data is loaded thru pipe . but when i place again a new file in s3 bucket it is not sending the notification automatically, again if i run the alter script to refresh then only the file is picked from s3 and data is loaded, r we really need to run the refresh every time? – Gayathri Apr 26 '22 at 05:27
  • Hello Sergiu, Regarding Is a LIST showing the files if you run: LIST @Customer_Notification_S3 pattern='.*customernotification.*[.]csv'; -- yes the files placed in S3 is visible thru the list command of Stage and also i am able to query the stage too. – Gayathri Apr 26 '22 at 05:29
  • Hello Abhi Reddy, Regarding The prefix contains the bucket name; since the stage is formed to the exact location, the prefix is not necessary. Remove it and try. -- Could you please let me know in which code the prefix needs to be removed. – Gayathri Apr 26 '22 at 05:30

0 Answers0