Automating Snowpipe for Microsoft Azure Blob Storage¶

Create a Resource Group¶

An Event Grid topic provides an endpoint where the source (i.e. Azure Storage) sends events. A topic is used for a collection of related events. Event Grid topics are Azure resources, and must be placed in an Azure resource group.

Execute the following command to create a resource group:

az group create --name <resource_group_name> --location <location>

Where:

• resource_group_name is the name of the new resource group.

• location is the location, or region in Snowflake terminology, of your Azure Storage account.

Enable the Event Grid Resource Provider¶

Execute the following command to register the Event Grid resource provider. Note that this step is only required if you have not previously used Event Grid with your Azure account:

az provider register --namespace Microsoft.EventGrid
az provider show --namespace Microsoft.EventGrid --query "registrationState"

Create a Storage Account for Data Files¶

Execute the following command to create a storage account to store your data files. This account must be either a Blob storage (i.e. BlobStorage kind) or GPv2 (i.e. StorageV2 kind) account, because only these two account types support event messages.

For example, create a Blob storage account:

az storage account create --resource-group <resource_group_name> --name <storage_account_name> --sku Standard_LRS --location <location> --kind BlobStorage --access-tier Hot

Where:

• resource_group_name is the name of the resource group you created in Create a Resource Group.

• storage_account_name is the name of the new storage account.

• location is the location of your Azure Storage account.

Create a Storage Account for the Storage Queue¶

Execute the following command to create a storage account to host your storage queue. This account must be a GPv2 account, because only this kind of account supports event messages to a storage queue.

For example, create a GPv2 account:

az storage account create --resource-group <resource_group_name> --name <storage_account_name> --sku Standard_LRS --location <location> --kind StorageV2

Where:

• resource_group_name is the name of the resource group you created in Create a Resource Group.

• storage_account_name is the name of the new storage account.

• location is the location of your Azure Storage account.

Create a Storage Queue¶

A single Azure Queue Storage queue can collect the event messages for many Event Grid subscriptions. For best performance, Snowflake recommends creating a single storage queue to accommodate all of your subscriptions related to Snowflake.

Execute the following command to create a storage queue. A storage queue stores a set of messages, in this case event messages from Event Grid:

az storage queue create --name <storage_queue_name> --account-name <storage_account_name>

Where:

• storage_queue_name is the name of the new storage queue.

• storage_account_name is the name of the storage account you created in Create a Storage Account for the Storage Queue.

Export the Storage Account and Queue IDs for Reference¶

Execute the following commands to set environment variables for the storage account and queue IDs that will be requested later in these instructions:

• Linux or macOS:

  export storageid=$(az storage account show --name <data_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
  export queuestorageid=$(az storage account show --name <queue_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
  export queueid="$queuestorageid/queueservices/default/queues/<storage_queue_name>"
  

  Copy

• Windows:

  set storageid=$(az storage account show --name <data_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
  set queuestorageid=$(az storage account show --name <queue_storage_account_name> --resource-group <resource_group_name> --query id --output tsv)
  set queueid="%queuestorageid%/queueservices/default/queues/<storage_queue_name>"
  

  Copy

Where:

• data_storage_account_name is the name of the storage account you created in Create a Storage Account for Data Files.

• queue_storage_account_name is the name of the storage account you created in Create a Storage Account for the Storage Queue.

• resource_group_name is the name of the resource group you created in Create a Resource Group.

• storage_queue_name is the name of the storage queue you created in Create a Storage Queue.

Install the Event Grid Extension¶

Execute the following command to install the Event Grid extension for Azure CLI:

az extension add --name eventgrid

Create the Event Grid Subscription¶

Execute the following command to create the Event Grid subscription. Subscribing to a topic informs Event Grid which events to track:

• Linux or macOS:

  az eventgrid event-subscription create \
  --source-resource-id $storageid \
  --name <subscription_name> --endpoint-type storagequeue \
  --endpoint $queueid \
  --advanced-filter data.api stringin CopyBlob PutBlob PutBlockList FlushWithClose SftpCommit
  

  Copy

• Windows:

  az eventgrid event-subscription create \
  --source-resource-id %storageid% \
  --name <subscription_name> --endpoint-type storagequeue \
  --endpoint %queueid% \
  -advanced-filter data.api stringin CopyBlob PutBlob PutBlockList FlushWithClose SftpCommit
  

  Copy

Where:

• storageid and queueid are the storage account and queue ID environment variables you set in Export the Storage Account and Queue IDs for Reference.

• subscription_name is the name of the new Event Grid subscription.

Retrieve the Storage Queue URL and Tenant ID¶

1. Log into the Microsoft Azure portal.

2. Navigate to Storage account » Queue service » Queues. Record the URL for the queue you created in Create a Storage Queue for reference later. The URL has the following format:

   https://<storage_account_name>.queue.core.windows.net/<storage_queue_name>
   

   Copy

3. Navigate to Azure Active Directory » Properties. Record the Tenant ID value for reference later. The directory ID, or tenant ID, is needed to generate the consent URL that grants Snowflake access to the Event Grid subscription.

Create the Notification Integration¶

Create a notification integration using the CREATE NOTIFICATION INTEGRATION command.

CREATE NOTIFICATION INTEGRATION <integration_name>
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
  AZURE_STORAGE_QUEUE_PRIMARY_URI = '<queue_URL>'
  AZURE_TENANT_ID = '<directory_ID>';

Where:

• integration_name is the name of the new integration.

• queue_URL and directory_ID are the queue URL and tenant ID you recorded in Retrieve the Storage Queue URL and Tenant ID.

For example:

CREATE NOTIFICATION INTEGRATION my_notification_int
  ENABLED = true
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
  AZURE_STORAGE_QUEUE_PRIMARY_URI = 'https://myqueue.queue.core.windows.net/mystoragequeue'
  AZURE_TENANT_ID = 'a123bcde-1234-5678-abc1-9abc12345678';

Grant Snowflake Access to the Storage Queue¶

Note that specific steps in this section require a local installation of the Azure CLI.

1. Execute the DESCRIBE INTEGRATION command to retrieve the consent URL:

   DESC NOTIFICATION INTEGRATION <integration_name>;
   

   Copy

   Where:

   • integration_name is the name of the integration you created in Create the Notification Integration.

   Note the values in the following columns:

   AZURE_CONSENT_URL:

   URL to the Microsoft permissions request page.

   AZURE_MULTI_TENANT_APP_NAME:

   Name of the Snowflake client application created for your account. In a later step in this section, you will need to grant this application the permissions necessary to obtain an access token on your allowed topic.

2. In a web browser, navigate to the URL in the AZURE_CONSENT_URL column. The page displays a Microsoft permissions request page.

3. Click the Accept button. This action allows the Azure service principal created for your Snowflake account to obtain an access token on any resource inside your tenant. Obtaining an access token succeeds only if you grant the service principal the appropriate permissions on the container (see the next step).

   The Microsoft permissions request page redirects to the Snowflake corporate site (snowflake.com).

4. Log into the Microsoft Azure portal.

5. Navigate to Azure Active Directory » Enterprise applications. Verify the Snowflake application identifier you recorded in Step 2 in this section is listed.

   Important

   If you delete the Snowflake application in Azure Active Directory at a later time, the notification integration stops working.

6. Navigate to Queues » storage_queue_name, where storage_queue_name is the name of the storage queue you created in Create a Storage Queue.

7. Click Access Control (IAM) » Add role assignment.

8. Search for the Snowflake service principal. This is the identity in the AZURE_MULTI_TENANT_APP_NAME property in the DESC NOTIFICATION INTEGRATION output (in Step 1). Search for the string before the underscore in the AZURE_MULTI_TENANT_APP_NAME property.

   Important

   • It can take an hour or longer for Azure to create the Snowflake service principal requested through the Microsoft request page in this section. If the service principal is not available immediately, we recommend waiting an hour or two and then searching again.

   • If you delete the service principal, the notification integration stops working.

9. Grant the Snowflake app the following permissions:

   • Role: Storage Queue Data Contributor

   • Assign access to: Azure AD user, group, or service principal

   • Select: The appDisplayName value.

   The Snowflake application identifier should now be listed under Storage Queue Data Contributor (on the same dialog).