Snowpipe not auto-ingesting from Azure Blob Storage-CodePudding

I have spent much of the last few days trying my hardest to create an automated Snowpipe between our Azure Blob Storage and Snowflake. My storage integration works but my issue lies with auto-ingesting and the notification integration.

On the Snowflake side, I have set up the notification integration, the external stage, the pipe, and the table, all of which were created following the Snowflake documentation on how to automate Snowpipe for Azure Storage.

On the Azure side, the Snowflake enterprise application user has the required storage queue permission. I have also configured the Events and Message Queue in Azure so that when a new blob is uploaded to the container, a message is successfully created in the queue.

However, when I put a blob into cloud storage, a message is created but the blob is not auto-ingested into my table. If I manually refresh the pipe, the files are loaded. I did see a Snowflake community post seemingly referencing this issue and I followed the steps to re-configure the Azure-side permissions but to no avail. When looking at the Snowpipe troubleshooting documentation I was pointed to the SYSTEM$PIPE_STATUS function and this is where I've finally hit a dead-end. I received the following response:

{"executionState":"RUNNING","pendingFileCount":0,
"notificationChannelName":"https://<storage_account>.queue.core.windows.net/<queue_name>",
"numOutstandingMessagesOnChannel":0,
"lastReceivedMessageTimestamp":"2022-02-24T11:11:15.031Z",
"channelErrorMessage":"downloadAttributes error:Queue not found for channel Name=https://<storage_account>.queue.core.windows.net/<queue_name>,
AccountId=6741, NotificationChannelID=147585,
IntegrationID=1725812",
"lastErrorRecordTimestamp":"2022-02-28T20:23:51.631Z"}

When searching for the error found above "downloadAttributes error:Queue not found for channel Name..", I found absolutely nothing anywhere except for another StackOverflow question with the same issue but unfortunately no responses.

Any help would be immensely appreciated as this functionality is essential for this project. Thank you very much for your help!

CodePudding user response：

There are certain scenarios that can lead to that error message, most often due to the fact that either stage or pipe refer to old integrations (which for example were recreated using create or replace and same name but got a new unique id).

There are 2 ways to fix this:

recreate everything from scratch (not the easiest way)

open a case to Snowflake support so they can enable a parameter to overcome the situation where stage/pipe refer to old integrations.