自动刷新 Google Cloud Storage 的外部表¶

第 1 步：在 Snowflake 中创建云存储集成¶

使用 CREATE STORAGE INTEGRATION 命令创建集成。集成是一个 Snowflake 对象，它将外部云存储的身份验证责任委托给 Snowflake 生成的实体（即 Cloud Storage 服务账户）。为了访问 Cloud Storage 桶，Snowflake 会创建一个服务账户，该账户有权访问存储您的数据文件的桶。

单个存储集成可以支持多个外部（即 GCS）暂存区。暂存区定义中的 URL 必须与为 STORAGE_ALLOWED_LOCATIONS 参数指定的 GCS 桶（和可选路径）一致。

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/') ]

其中：

• integration_name 是新集成的名称。

• bucket 是用于存储数据文件（例如 mybucket）的 Cloud Storage 桶的名称。在创建或修改引用此集成的暂存区时，必需的 STORAGE_ALLOWED_LOCATIONS 参数和可选的 STORAGE_BLOCKED_LOCATIONS 参数分别限制或阻止对这些桶的访问。

• path 是一个可选路径，可用于提供对桶中对象的精细控制。

以下示例创建一个集成，该集成明确限制使用该集成引用两个桶和路径之一的外部暂存区。在后面的步骤中，我们将创建一个引用这些桶和路径之一的外部暂存区。

也使用此集成的其他外部暂存区可以引用允许的桶和路径：

CREATE STORAGE INTEGRATION gcs_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://mybucket1/path1/', 'gcs://mybucket2/path2/')
  STORAGE_BLOCKED_LOCATIONS = ('gcs://mybucket1/path1/sensitivedata/', 'gcs://mybucket2/path2/sensitivedata/');

Copy

第 2 步：检索 Snowflake 账户的 Cloud Storage 服务账户¶

执行 DESCRIBE INTEGRATION 命令以检索为您的 Snowflake 账户自动创建的 Cloud Storage 服务账户的 ID：

DESC STORAGE INTEGRATION <integration_name>;

其中：

• integration_name 是您在 第 1 步：在 Snowflake 中创建云存储集成 （本主题内容）中创建的集成的名称。

例如：

DESC STORAGE INTEGRATION gcs_int;

+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------+
| property                    | property_type | property_value                                                              | property_default |
+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------|
| ENABLED                     | Boolean       | true                                                                        | false            |
| STORAGE_ALLOWED_LOCATIONS   | List          | gcs://mybucket1/path1/,gcs://mybucket2/path2/                               | []               |
| STORAGE_BLOCKED_LOCATIONS   | List          | gcs://mybucket1/path1/sensitivedata/,gcs://mybucket2/path2/sensitivedata/   | []               |
| STORAGE_GCP_SERVICE_ACCOUNT | String        | service-account-id@project1-123456.iam.gserviceaccount.com                  |                  |
+-----------------------------+---------------+-----------------------------------------------------------------------------+------------------+

Copy

输出中的 STORAGE_GCP_SERVICE_ACCOUNT 属性显示为您的 Snowflake 账户创建的 Cloud Storage 服务账户（例如 service-account-id@project1-123456.iam.gserviceaccount.com）。我们会为您的整个 Snowflake 账户预置一个 Cloud Storage 服务账户。所有 Cloud Storage 集成都使用该服务账户。

第 3 步：授予服务账户访问桶对象的权限¶

以下分步说明描述了如何在 Google Cloud Platform Console 中配置 Snowflake 的 IAM 访问权限，以便您可以使用 Cloud Storage 桶来加载和卸载数据：

先决条件¶

本主题中的说明假定已创建并配置了以下项：

GCP 账户:

• 从 GCS 桶接收事件消息的 Pub/Sub 主题。有关更多信息，请参阅 创建 Pub/Sub 主题 （本主题内容）。

• 从 Pub/Sub 主题接收事件消息的订阅。有关更多信息，请参阅 创建 Pub/Sub 订阅 （本主题内容）。

有关说明，请参阅 Pub/Sub 文档 (https://cloud.google.com/pubsub/docs)。

Snowflake:

• 将加载数据的 Snowflake 数据库中的目标表。

$ gsutil notification create -t <topic> -f json gs://<bucket-name> -e OBJECT_FINALIZE -e OBJECT_DELETE

第 1 步：在 Snowflake 中创建通知集成¶

使用 CREATE NOTIFICATION INTEGRATION 命令创建通知集成。通知集成引用 Pub/Sub 订阅。Snowflake 将通知集成与为您的账户创建的 GCS 服务账户相关联。Snowflake 创建一个服务账户，该账户由 Snowflake 账户中的所有 GCS 通知集成引用。

CREATE NOTIFICATION INTEGRATION <integration_name>
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_SUBSCRIPTION_NAME = '<subscription_id>';

其中：

• integration_name 是新集成的名称。

• subscription_id 是您在 检索 Pub/Sub 订阅 ID 中记录的订阅名称。

例如：

CREATE NOTIFICATION INTEGRATION my_notification_int
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = true
  GCP_PUBSUB_SUBSCRIPTION_NAME = 'projects/project-1234/subscriptions/sub2';

第 2 步：授予 Snowflake 对 Pub/Sub 订阅的访问权限¶

1. 执行 DESCRIBE INTEGRATION 命令以检索 Snowflake 服务账户 ID：

   DESC NOTIFICATION INTEGRATION <integration_name>;
   

   Copy

   其中：

   • integration_name 是您在 第 1 步：在 Snowflake 中创建通知集成 中创建的集成的名称。

   例如：

   DESC NOTIFICATION INTEGRATION my_notification_int;
   

   Copy

2. 在 GCP_PUBSUB_SERVICE_ACCOUNT 列中记录服务账户名称，格式如下：

   <service_account>@<project_id>.iam.gserviceaccount.com
   

   Copy

3. 以项目编辑者身份登录 Google Cloud Platform Console。

4. 从主页控制面板中选择 Big Data » Pub/Sub » Subscriptions。

5. 选择要配置访问权限的订阅。

6. 点击右上角的 SHOW INFO PANEL。订阅的信息面板将滑出。

7. 点击 ADD PRINCIPAL 按钮。

8. 在 New principals 字段中，搜索您记录的服务账户名称。

9. 从 Select a role 下拉列表中，选择 Pub/Sub Subscriber。

10. 点击 Save 按钮。服务账户名称将添加到信息面板中的 Pub/Sub Subscriber 角色下拉列表中。

11. 导航到 Cloud Console 中的 Dashboard 页面，然后从下拉列表中选择您的项目。

12. 点击 ADD PEOPLE TO THIS PROJECT 按钮。

13. 添加您记录的服务账户名称。

14. 从 Select a role 下拉列表中，选择 Monitoring Viewer。

15. 点击 Save 按钮。服务账户名称将添加到 Monitoring Viewer 角色中。

第 3 步：创建暂存区（如果需要）¶

使用 CREATE STAGE 命令创建引用您的 GCS 桶的外部暂存区。Snowflake 将暂存数据文件读入外部表元数据。或者，您可以使用现有的外部暂存区。

以下示例会在活动架构中为用户会话创建一个名为 mystage 的暂存区。云存储 URL 包括路径 files。该暂存区引用名为 my_storage_int 的存储集成。

USE SCHEMA mydb.public;

CREATE STAGE mystage
  URL='gcs://load/files/'
  STORAGE_INTEGRATION = my_storage_int;

Copy

第 4 步：创建外部表¶

使用 CREATE EXTERNAL TABLE 命令创建外部表。

例如，在 mydb.public 架构中创建一个外部表，该表从 mystage 暂存区（具有 path1/ 路径）中暂存的文件读取 JSON 数据。

INTEGRATION 参数引用您在 第 1 步：在 Snowflake 中创建通知集成 中创建的 my_notification_int 通知集成。集成名称必须全部大写。

请注意，AUTO_REFRESH 参数默认为 TRUE：

CREATE OR REPLACE EXTERNAL TABLE ext_table
 INTEGRATION = 'MY_NOTIFICATION_INT'
 WITH LOCATION = @mystage/path1/
 FILE_FORMAT = (TYPE = JSON);

具有自动刷新功能的外部暂存区现已配置完毕！

将新的或已更新的数据文件添加到 GCS 桶时，事件通知会通知 Snowflake 将它们扫描到外部表元数据中。

第 5 步：手动刷新外部表元数据¶

使用带有 REFRESH 参数的 ALTER EXTERNAL TABLE 手动刷新一次外部表元数据，例如：

ALTER EXTERNAL TABLE ext_table REFRESH;

+---------------------------------------------+----------------+-------------------------------+
| file                                        | status         | description                   |
|---------------------------------------------+----------------+-------------------------------|
| files/path1/file1.json                      | REGISTERED_NEW | File registered successfully. |
| files/path1/file2.json                      | REGISTERED_NEW | File registered successfully. |
| files/path1/file3.json                      | REGISTERED_NEW | File registered successfully. |
+---------------------------------------------+----------------+-------------------------------+

Copy

此步骤将元数据与暂存区中的文件列表和外部表定义中的路径进行同步。此外，此步骤确保外部表可以读取指定暂存区和路径中的数据文件，并且外部表定义中没有缺失任何文件。

如果 file 列中的文件列表不符合您的期望，请验证外部表定义和外部暂存区定义中的路径。外部表定义中的任何路径都会追加到暂存区定义中指定的任何路径。有关更多信息，请参阅 CREATE EXTERNAL TABLE。

此步骤确保元数据与自第 4 步以来发生的文件列表的任何变更同步。此后，Pub/Sub 通知会自动触发元数据刷新。

第 6 步：配置安全性¶

对于将用于查询外部表的每个附加角色，请使用 GRANT <privileges> 授予对各种对象（即数据库、架构、暂存区和表）的足够的访问控制权限：