自动刷新 Amazon S3 的外部表¶

第 1 步：配置 S3 桶的访问权限¶

第 2 步：在 AWS 中创建 IAM 角色¶

要在 AWS 管理控制台中为 Snowflake 配置访问权限，请执行以下操作：

1. 在 Identity and Access Management (IAM) 控制面板的左侧导航窗格中，选择 Roles。

2. 选择 Create role。

3. 选择 AWS account 作为信任实体类型。

4. 选择 Another AWS account

   

5. 在 Account ID 字段中，暂时输入自己的 AWS 账户 ID。在稍后的步骤中，您将修改信任关系并授予对 Snowflake 的访问权限。

6. 选择 Require external ID 选项。外部 ID 用于向第三方（如 Snowflake）授予对 AWS 资源（例如 S3 桶）的访问权限。

   输入占位符 ID，例如 0000。在后续步骤中，您将修改 IAM 角色的信任关系并指定外部 ID 用于存储集成。

7. 选择 Next。

8. 选择您在 `第 1 步：配置 S3 桶访问权限`_（本主题内容）中创建的策略。

9. 选择 Next。

   

10. 输入角色的名称和描述，然后选择 Create role。

    您现在已经为一个桶创建了 IAM 策略，创建了一个 IAM 角色，并将策略附加到了该角色。

11. 在角色摘要页面上，找到 Role ARN 值并将其记录下来。在下一步中，您将创建引用此角色的 Snowflake 集成。

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

使用 CREATE STORAGE INTEGRATION 命令创建存储集成。存储集成是一个 Snowflake 对象，用于存储为 S3 云存储生成的 Identity and Access Management (IAM) 用户，以及一组可选的已允许或已阻止的存储位置（即桶）。组织中的云提供商管理员会将存储位置的权限授予生成的用户。此选项可让用户在创建暂存区或加载数据时无需提供凭据。

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

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

其中：

• integration_name 是新集成的名称。

• iam_role 是您在 `第 2 步：在 AWS 中创建 IAM 角色`_（本主题内容）中创建的角色的 Amazon 资源名称 (ARN)。

• protocol 是以下其中一项：

  • s3 是指中国境外公共 AWS 区域的 S3 存储。

  • s3china 代表中国境内公共 AWS 区域中的 S3 存储。

  • s3gov 代表 政府区域 中的 S3 存储。

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

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

下面的示例创建了一个集成，允许访问账户中的所有桶，但阻止访问定义的 sensitivedata 文件夹。

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

CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole'
  STORAGE_ALLOWED_LOCATIONS = ('*')
  STORAGE_BLOCKED_LOCATIONS = ('s3://mybucket1/mypath1/sensitivedata/', 's3://mybucket2/mypath2/sensitivedata/');

第 4 步：检索您的 Snowflake 账户的 AWS IAM 用户¶

1. 要检索自动为您的 Snowflake 账户创建的 IAM 用户的 ARN，请使用 DESCRIBE INTEGRATION。

   DESC INTEGRATION <integration_name>;
   

   Copy

   其中：

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

   例如：

   DESC INTEGRATION s3_int;
   

   Copy

   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
   | property                  | property_type | property_value                                                                 | property_default |
   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------|
   | ENABLED                   | Boolean       | true                                                                           | false            |
   | STORAGE_ALLOWED_LOCATIONS | List          | s3://mybucket1/mypath1/,s3://mybucket2/mypath2/                                | []               |
   | STORAGE_BLOCKED_LOCATIONS | List          | s3://mybucket1/mypath1/sensitivedata/,s3://mybucket2/mypath2/sensitivedata/    | []               |
   | STORAGE_AWS_IAM_USER_ARN  | String        | arn:aws:iam::123456789001:user/abc1-b-self1234                                 |                  |
   | STORAGE_AWS_ROLE_ARN      | String        | arn:aws:iam::001234567890:role/myrole                                          |                  |
   | STORAGE_AWS_EXTERNAL_ID   | String        | MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=                               |                  |
   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
   

2. 记录以下属性的值：

   属性	描述
   STORAGE_AWS_IAM_USER_ARN	为您的 Snowflake 账户创建的 AWS IAM 用户，例如 arn:aws:iam::123456789001:user/abc1-b-self1234。Snowflake 会为整个 Snowflake 账户配置单一 IAM 用户。您账户中的所有 S3 存储集成都使用该 IAM 用户。
   STORAGE_AWS_EXTERNAL_ID	Snowflake 与 AWS 建立信任关系所使用的外部 ID。如果创建存储集成时未指定外部 ID (STORAGE_AWS_EXTERNAL_ID)，Snowflake 会生成一个 ID 供您使用。

   在下一部分中需要提供这些值。

第 5 步：授予访问桶对象的 IAM 用户权限¶

以下分步说明描述了如何在 AWS 管理控制台中配置 Snowflake 的 IAM 访问权限，以便您可以使用 S3 桶来加载和卸载数据：

1. 登录 AWS 管理控制台。

2. 选择 IAM。

3. 从左侧导航窗格中选择 Roles。

4. 选择在 `第 2 步：在 AWS 中创建 IAM 角色`_（本主题内容）中创建的角色。

5. 选择 Trust relationships 选项卡。

6. 选择 Edit trust policy。

7. 使用您在 `第 4 步：检索您的 Snowflake 账户的 AWS IAM 用户`_（本主题内容）中记录的 DESC STORAGE INTEGRATION 输出值来修改策略文档。

   IAM 角色的策略文档

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Sid": "",
         "Effect": "Allow",
         "Principal": {
           "AWS": "<snowflake_user_arn>"
         },
         "Action": "sts:AssumeRole",
         "Condition": {
           "StringEquals": {
             "sts:ExternalId": "<snowflake_external_id>"
           }
         }
       }
     ]
   }
   

   Copy

   其中：

   • snowflake_user_arn 是记录的 STORAGE_AWS_IAM_USER_ARN 值。

   • snowflake_external_id 是记录的 STORAGE_AWS_EXTERNAL_ID 值。

     在此示例中，snowflake_external_id 值为 MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=。

     备注

     出于安全原因，如果您在不指定外部 ID 的情况下创建新的存储集成（或使用 CREATE OR REPLACE STORAGE INTEGRATION 语法重新创建一个现有的存储集成），新的集成具有 不同 的外部 ID，所以它不能解析信任关系，除非更新信任策略。

8. 选择 Update policy 保存更改。

（可选）第 1 步：创建暂存区¶

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

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

USE SCHEMA mydb.public;

CREATE STAGE mystage
  URL = 's3://mybucket/files'
  STORAGE_INTEGRATION = my_storage_int;

Copy

第 2 步：创建外部表¶

使用 CREATE EXTERNAL TABLE 命令创建外部表。例如，在 mydb.public 架构中创建一个外部表，从暂存文件中读取 JSON 数据。

暂存区引用包括名为 path1 的文件夹路径。外部表将此路径追加到暂存区定义中，即，外部表引用 @mystage/files/path1 中的数据文件。

AUTO_REFRESH 参数默认为 TRUE：

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

Copy

第 3 步：配置事件通知¶

为您的 S3 桶配置事件通知，以便在新数据或更新数据可读取到外部表元数据时通知 Snowflake。自动刷新功能依赖于将事件通知从 S3 传递到 Snowflake 的 SQS 队列。

为便于使用，这些 SQS 队列由 Snowflake 创建和管理。SHOW EXTERNAL TABLES 命令输出显示 SQS 队列的 Amazon Resource Name (ARN)。

1. 运行 SHOW EXTERNAL TABLES 命令：

   SHOW EXTERNAL TABLES;
   

   Copy

2. 在 notification_channel 列中，找到外部表对应的 SQS 队列 ARN，并将 ARN 复制到便捷位置。

   备注

   根据 AWS 准则，Snowflake 为每个 AWS S3 区域指定的 SQS 队列不超过一个。此 SQS 队列可由同一 AWS 账户中的多个桶共享。SQS 队列协调从同一 S3 桶读取数据文件的所有外部表的通知。当新的或修改的数据文件上传到桶时，所有与暂存区目录路径匹配的外部表定义都会将文件详细信息读入到其元数据中。

3. 登录 AWS 管理控制台。

4. 使用 Amazon S3 文档 (https://docs.aws.amazon.com/AmazonS3/latest/user-guide/enable-event-notifications.html) 中提供的说明为您的 S3 桶配置事件通知。按以下列表所示填写字段：

   • Name：事件通知的名称（例如 Auto-ingest Snowflake）。

   • Events：选择 ObjectCreate (All) 和 ObjectRemoved 选项。

   • Send to：从下拉列表中选择 SQS Queue。

   • SQS：从下拉列表中选择 Add SQS queue ARN。

   • SQS queue ARN：粘贴 SHOW EXTERNAL TABLES 输出中的 SQS 队列名称。

完成此步骤后，即已配置启用自动刷新的外部暂存区。

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

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

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

ALTER EXTERNAL TABLE ext_table REFRESH;

Copy

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

第 5 步：配置安全性¶

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

先决条件：创建 Amazon SNS 主题和订阅¶

1. 在您的 SNS 账户中创建一个 AWS 主题来处理 S3 桶上 Snowflake 暂存区位置的所有消息。

2. 将 S3 事件通知的目标目的地（例如，其他 SQS 队列或 AWS Lambda 工作负载）订阅到此主题。SNS 会向该主题的所有订阅者发布您的桶的事件通知。

有关说明，请参阅 SNS 文档 (https://aws.amazon.com/documentation/sns/)。

第 1 步：将 Snowflake SQS 队列订阅至 SNS 主题¶

1. 登录 AWS 管理控制台。

2. 从主页仪表板中选择 Simple Notification Service (SNS)。

3. 从左侧导航窗格中选择 Topics。

4. 找到您的 S3 桶主题。注意主题 ARN。

5. 使用 Snowflake 客户端，用您的 SNS 主题 ARN 查询 SYSTEM$GET_AWS_SNS_IAM_POLICY 系统函数：

   select system$get_aws_sns_iam_policy('<sns_topic_arn>');
   

   Copy

   该函数返回一个授予 Snowflake SQS 队列权限以订阅 SNS 主题的 IAM 策略。

   例如：

   select system$get_aws_sns_iam_policy('arn:aws:sns:us-west-2:001234567890:s3_mybucket');
   
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | SYSTEM$GET_AWS_SNS_IAM_POLICY('ARN:AWS:SNS:US-WEST-2:001234567890:S3_MYBUCKET')                                                                                                                                                                   |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | {"Version":"2012-10-17","Statement":[{"Sid":"1","Effect":"Allow","Principal":{"AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"},"Action":["sns:Subscribe"],"Resource":["arn:aws:sns:us-west-2:001234567890:s3_mybucket"]}]}                 |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   

   Copy

6. 返回 AWS 管理控制台。从左侧导航窗格中选择 Topics。

7. 选择您的 S3 桶的主题，然后点击 Edit 按钮。Edit 页面随即打开。

8. 点击 Access policy - Optional 以展开页面的此区域。

9. 将 SYSTEM$GET_AWS_SNS_IAM_POLICY 函数结果中添加的 IAM 策略合并到 JSON 文档中。

   例如：

   原始 IAM 策略（缩写）：

   {
     "Version":"2008-10-17",
     "Id":"__default_policy_ID",
     "Statement":[
        {
           "Sid":"__default_statement_ID",
           "Effect":"Allow",
           "Principal":{
              "AWS":"*"
           }
           ..
        }
      ]
    }
   

   Copy

   合并的 IAM 策略：

   {
     "Version":"2008-10-17",
     "Id":"__default_policy_ID",
     "Statement":[
        {
           "Sid":"__default_statement_ID",
           "Effect":"Allow",
           "Principal":{
              "AWS":"*"
           }
           ..
        },
        {
           "Sid":"1",
           "Effect":"Allow",
           "Principal":{
             "AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"
            },
            "Action":[
              "sns:Subscribe"
            ],
            "Resource":[
              "arn:aws:sns:us-west-2:001234567890:s3_mybucket"
            ]
        }
      ]
    }
   

   Copy

10. 添加额外的策略授权以允许 S3 将桶的事件通知发布到 SNS 主题。

    例如（使用 SNS 主题 ARN 以及这些说明中使用的 S3 桶）：

    {
        "Sid":"s3-event-notifier",
        "Effect":"Allow",
        "Principal":{
           "Service":"s3.amazonaws.com"
        },
        "Action":"SNS:Publish",
        "Resource":"arn:aws:sns:us-west-2:001234567890:s3_mybucket",
        "Condition":{
           "ArnLike":{
              "aws:SourceArn":"arn:aws:s3:*:*:s3_mybucket"
           }
        }
     }
    

    Copy

    合并的 IAM 策略：

    {
      "Version":"2008-10-17",
      "Id":"__default_policy_ID",
      "Statement":[
         {
            "Sid":"__default_statement_ID",
            "Effect":"Allow",
            "Principal":{
               "AWS":"*"
            }
            ..
         },
         {
            "Sid":"1",
            "Effect":"Allow",
            "Principal":{
              "AWS":"arn:aws:iam::123456789001:user/vj4g-a-abcd1234"
             },
             "Action":[
               "sns:Subscribe"
             ],
             "Resource":[
               "arn:aws:sns:us-west-2:001234567890:s3_mybucket"
             ]
         },
         {
            "Sid":"s3-event-notifier",
            "Effect":"Allow",
            "Principal":{
               "Service":"s3.amazonaws.com"
            },
            "Action":"SNS:Publish",
            "Resource":"arn:aws:sns:us-west-2:001234567890:s3_mybucket",
            "Condition":{
               "ArnLike":{
                  "aws:SourceArn":"arn:aws:s3:*:*:s3_mybucket"
               }
            }
          }
       ]
     }
    

    Copy

11. 点击 Save changes。

（可选）第 2 步：创建暂存区¶

使用 CREATE STAGE 命令创建引用您的 S3 桶的外部暂存区。Snowflake 将暂存数据文件读入外部表元数据。

或者，您可以使用现有的外部暂存区。

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

USE SCHEMA mydb.public;

CREATE STAGE mystage
  URL = 's3://mybucket/files'
  STORAGE_INTEGRATION = my_storage_int;

Copy

第 3 步：创建外部表¶

使用 CREATE EXTERNAL TABLE 创建外部表。从 先决条件：创建 Amazon SNS 主题和订阅 中识别 SNS 主题 ARN。

CREATE EXTERNAL TABLE <table_name>
 ..
 AWS_SNS_TOPIC = '<sns_topic_arn>';

其中：

AWS_SNS_TOPIC = '<sns_topic_arn>'

为 S3 桶的 SNS 主题指定 ARN。CREATE EXTERNAL TABLE 语句将 Snowflake SQS 队列订阅至指定的 SNS 话题。

例如，在 mydb.public 架构中创建一个外部表，从暂存文件中读取 JSON 数据。暂存区引用包括名为 path1 的文件夹路径。外部表将此路径追加到暂存区定义中，即，外部表引用 @mystage/files/path1 中的数据文件。AUTO_REFRESH 参数默认为 TRUE：

CREATE EXTERNAL TABLE ext_table
 WITH LOCATION = @mystage/path1/
 FILE_FORMAT = (TYPE = JSON)
 AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';

要从外部表中移除此参数，您必须使用 CREATE OR REPLACE EXTERNAL TABLE 语法重新创建外部表。

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

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

ALTER EXTERNAL TABLE ext_table REFRESH;

Copy

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

第 5 步：配置安全性¶

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