Private connectivity to external stages and Snowpipe automation for Microsoft Azure¶

This topic provides configuration details to set up outbound private connectivity for the following Snowflake features:

• 使用外部暂存区从 Microsoft Azure 批量加载。
• 为 Microsoft Azure Blob 存储自动化 Snowpipe。

为专用连接配置批量加载和 Snowpipe 自动化与为公共网络流量配置批量加载和 Snowpipe 自动化之间的区别包括以下内容：

• Setting USE_PRIVATELINK_ENDPOINT = TRUE for the required storage integration, stage, or notification integration.
• 为外部暂存区（批量加载和 Snowpipe 自动化）创建专用连接端点。
• 为通知集成（仅限 Snowpipe 自动化）创建专用连接端点。

出站专用连接成本

注意事项

You can configure outbound public connectivity and outbound private connectivity for the same storage account. If you want to do this, create a dedicated storage integration for outbound public connectivity and specify USE_PRIVATELINK_ENDPOINT = FALSE.

专用连接属性

The USE_PRIVATELINK_ENDPOINT property of a storage integration or external stage determines whether it is accessed through private connectivity or by traversing the public network. To use private connectivity, set USE_PRIVATELINK_ENDPOINT = TRUE.

A stage that references a storage integration that specifies USE_PRIVATELINK_ENDPOINT = TRUE inherits the private endpoint configuration. As a result, if you are using a storage integration that is configured to use private connectivity, you do not need to specify the USE_PRIVATELINK_ENDPOINT property in the stage, and you cannot modify the stage to set the USE_PRIVATELINK_ENDPOINT property.

配置外部暂存区访问

These steps are unique to using outbound private connectivity with a storage integration to unload data to an external stage on Microsoft Azure. You need to modify the flow if you are using the stage’s CREDENTIALS property instead of referencing a storage integration.

批量加载和 Snowpipe 自动化都需要以下步骤。

1. In Snowflake, call the SYSTEM$PROVISION_PRIVATELINK_ENDPOINT system function to provision a private connectivity endpoint in your Snowflake VNet to enable Snowflake to connect to your external Blob storage account using private connectivity:

   Copy codeExpand code block

   USE ROLE ACCOUNTADMIN;
   
   SELECT SYSTEM$PROVISION_PRIVATELINK_ENDPOINT(
     '/subscriptions/cc2909f2-ed22-4c89-8e5d-bdc40e5eac26/resourceGroups/mystorage/providers/Microsoft.Storage/storageAccounts/storagedemo',
     'mystorageaccount.blob.core.windows.net',
     'blob'
   );

   Show lessSee more

   Scroll to top

此函数将专用端点与主机名绑定，从而使存储集成可以使用专用端点连接到存储位置。

2. In the Azure Portal and as the owner of the Microsoft Azure Blob storage resource, approve the private endpoint. For details, see the approval process (https://learn.microsoft.com/en-us/azure/private-link/manage-private-endpoint?tabs=manage-private-link-powershell#private-endpoint-connections).

3. In Snowflake, call the SYSTEM$GET_PRIVATELINK_ENDPOINTS_INFO function.

   When the output of the function includes "status": "APPROVED", your connection from Snowflake to your storage account will be able to use private connectivity (after the other necessary Snowflake objects are enabled for outbound private connectivity).

   You can continue with the next steps while waiting for the "APPROVED" status.

4. Create a storage integration and be sure to specify the USE_PRIVATELINK_ENDPOINT property:

   Copy codeExpand code block

   CREATE OR REPLACE STORAGE INTEGRATION outbound_private_link_int
     TYPE = EXTERNAL_STAGE
     STORAGE_PROVIDER = AZURE
     AZURE_TENANT_ID = 'cc2909f2-ed22-4c89-8e5d-bdc40e5eac26'
     STORAGE_ALLOWED_LOCATIONS = ('azure://mystorageaccount.blob.core.windows.net/mycontainer/snowflake_privatelink_external_stage_test/')
     USE_PRIVATELINK_ENDPOINT = TRUE
     ENABLED = TRUE;

   Show lessSee more

   Scroll to top

   Note

   After you create the storage integration, you must grant Snowflake access to your storage locations. For more information, see Configuring a Snowflake storage integration.

5. 创建引用存储集成的外部暂存区：

   Copy codeExpand code block

   CREATE OR REPLACE STAGE my_storage_private_stage
     URL = 'azure://mystorageaccount.blob.core.windows.net/mycontainer/snowflake_privatelink_external_stage_test/'
     STORAGE_INTEGRATION = outbound_private_link_int;

   Show lessSee more

   Scroll to top

6. After the private endpoint has an "APPROVED" status, test unloading data from Snowflake to the external stage:

   Copy codeExpand code block

   COPY INTO @my_storage_private_stage
     FROM mytable
     FILE_FORMAT = (FORMAT_NAME = my_csv_format);

   Show lessSee more

   Scroll to top

7. View the result in your Microsoft Azure stage.

通知集成的语法更新

为 Microsoft Azure Blob 存储自动化 Snowpipe 需要您创建通知集成。以下语法更新允许您为专用连接配置通知集成。

CREATE [ OR REPLACE ] NOTIFICATION INTEGRATION [ IF NOT EXISTS ] <name>
  ...
  USE_PRIVATELINK_ENDPOINT = { TRUE | FALSE }

配置 Snowpipe 自动化¶

This section modifies the procedures described in Automating Snowpipe for Microsoft Azure Blob Storage to highlight how to implement Snowpipe automation with private connectivity. The only differences are provisioning private connectivity endpoints and configuring the USE_PRIVATELINK_ENDPOINT property of the storage integration and notification integration.

1. Create a storage integration and stage, along with its dedicated private connectivity endpoint, as described earlier in this document.

2. Grant Snowflake access to the storage locations, as described in the Automating Snowpipe for Microsoft Azure Blob Storage topic.

3. Configure the Event Grid Subscription, as described in the Automating Snowpipe for Microsoft Azure Blob Storage topic.

4. In Snowflake, call the SYSTEM$PROVISION_PRIVATELINK_ENDPOINT system function to provision a private endpoint in your Snowflake VNet to enable Snowflake to connect to your Azure queue using private connectivity:

   Copy codeExpand code block

   USE ROLE ACCOUNTADMIN;
   
   SELECT SYSTEM$PROVISION_PRIVATELINK_ENDPOINT(
     '/subscriptions/cc2909f2-ed22-4c89-8e5d-bdc40e5eac26/resourceGroups/mystorage/providers/Microsoft.Storage/storageAccounts/mystorageaccount',
    'mystorageaccount.queue.core.windows.net',
    'queue'
   );

   Show lessSee more

   Scroll to top

5. In the Azure Portal and as the owner of the Microsoft Azure Storage resource, approve the private endpoint. For information, see the approval process (https://learn.microsoft.com/en-us/azure/private-link/manage-private-endpoint?tabs=manage-private-link-powershell#private-endpoint-connections).

6. In Snowflake, call the SYSTEM$GET_PRIVATELINK_ENDPOINTS_INFO function.

   When the output of the function includes "status": "APPROVED", your connection from Snowflake to your storage account will be able to use private connectivity (after the other necessary Snowflake objects are enabled for outbound private connectivity).

   Important

   You must wait until the status is APPROVED before continuing with the next step.

7. Retrieve the storage queue URL and tenant ID, as described in the Automating Snowpipe for Microsoft Azure Blob Storage topic.

8. Create a notification integration and be sure to specify the USE_PRIVATELINK_ENDPOINT property:

   Copy codeExpand code block

   CREATE OR REPLACE NOTIFICATION INTEGRATION ni_pl
     ENABLED = TRUE
     TYPE = QUEUE
     NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE
     AZURE_STORAGE_QUEUE_PRIMARY_URI = "https://storageaccount.queue.core.windows.net/queuename"
     AZURE_TENANT_ID = '00000000-0000-0000-0000-000000000000'
     USE_PRIVATELINK_ENDPOINT = TRUE;

   Show lessSee more

   Scroll to top

9. Grant Snowflake access to the storage queue, as described in the Automating Snowpipe for Microsoft Azure Blob Storage topic.

10. Create a pipe with auto-ingest enabled, as described in the Automating Snowpipe for Microsoft Azure Blob Storage topic.

禁用专用连接

禁用专用连接的过程各不相同，具体取决于是为存储集成、外部暂存区还是为通知集成配置了端点。

Storage integration/external stage

If you no longer need the private connectivity endpoint for the external stage, unset the USE_PRIVATELINK_ENDPOINT property on the stage or storage integration, and then call the SYSTEM$DEPROVISION_PRIVATELINK_ENDPOINT system function.

Notification integration

Unlike storage integrations and external stages, you cannot unset the USE_PRIVATELINK_ENDPOINT property of a notification integration. If you no longer need private connectivity, you need to drop the notification integration, then create a new one. After recreating the notification integration, you can call the SYSTEM$DEPROVISION_PRIVATELINK_ENDPOINT system function to deprovision the endpoint.