Refreshing directory tables automatically for 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 DeleteBlob DeleteFile SftpRemove
  

  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 DeleteBlob DeleteFile SftpRemove
  

  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 Message Processor (the minimum required role), or 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 Message Processor or Storage Queue Data Contributor (on the same dialog).

Directory table parameters (directoryTable)¶

ENABLE = TRUE | FALSE

Specifies whether to add a directory table to the stage. When the value is TRUE, a directory table is created with the stage.

Default: FALSE

AUTO_REFRESH = TRUE | FALSE

Specifies whether Snowflake should enable triggering automatic refreshes of the directory table metadata when new or updated data files are available in the named external stage specified in the URL value.

TRUE

Snowflake enables triggering automatic refreshes of the directory table metadata.

FALSE

Snowflake does not enable triggering automatic refreshes of the directory table metadata. You must manually refresh the directory table metadata periodically using ALTER STAGE … REFRESH to synchronize the metadata with the current list of files in the stage path.

Default: FALSE

Microsoft Azure

NOTIFICATION_INTEGRATION = '<notification_integration_name>'

Specifies the name of the notification integration used to automatically refresh the directory table metadata using Azure Event Grid notifications. A notification integration is a Snowflake object that provides an interface between Snowflake and third-party cloud message queuing services.

The following example creates a stage named mystage in the active schema for the user session. The cloud storage URL includes the path files. The stage references a storage integration named my_storage_int.

USE SCHEMA mydb.public;

Copy

CREATE STAGE mystage
  URL='azure://myaccount.blob.core.windows.net/load/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (
    ENABLE = true
    AUTO_REFRESH = true
    NOTIFICATION_INTEGRATION = 'MY_NOTIFICATION_INT'
  );

The NOTIFICATION_INTEGRATION parameter references the my_notification_int integration you created in Step 2: Creating the Notification Integration. The integration name must be provided in all uppercase.

When new or updated data files are added to the cloud storage location, the event notification informs Snowflake to scan them into the directory table metadata.

Syntax¶

ALTER STAGE [ IF EXISTS ] <name> REFRESH [ SUBPATH = '<relative-path>' ]

Where:

REFRESH

Accesses the staged data files referenced in the directory table definition and updates the table metadata:

• New files in the path are added to the table metadata.

• Changes to files in the path are updated in the table metadata.

• Files no longer in the path are removed from the table metadata.

Currently, it is necessary to execute this command each time files are added to the stage, updated, or dropped. This step synchronizes the metadata with the latest set of associated files in the stage definition for the directory table.

SUBPATH = '<relative-path>'

Optionally specify a relative path to refresh the metadata for a specific subset of the data files.

Examples¶

Manually refresh the directory table metadata in a stage named mystage:

ALTER STAGE mystage REFRESH;