自动刷新 Amazon S3 的目录表¶

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

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

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

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

2. 选择 Create role。

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

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

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

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

6. 选择 Next。

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

8. 选择 Next。

   

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

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

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

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

使用 CREATE STORAGE INTEGRATION 命令创建存储集成。存储集成是一个 Snowflake 对象，用于存储生成的身份和 S3 云存储访问管理 (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 = ('s3://<bucket>/<path>/', 's3://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('s3://<bucket>/<path>/', 's3://<bucket>/<path>/') ]

其中：

• integration_name 是新集成的名称。

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

• 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/');

Copy

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

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

   DESC INTEGRATION <integration_name>;
   

   Copy

   其中：

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

   例如：

   DESC INTEGRATION s3_int;
   
   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
   | 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=                               |                  |
   +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
   

   Copy

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 将暂存数据文件读入目录表元数据。或者，您可以使用现有的外部暂存区。

-- External stage
CREATE [ OR REPLACE ] [ TEMPORARY ] STAGE [ IF NOT EXISTS ] <external_stage_name>
      <cloud_storage_access_settings>
    [ FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
    [ directoryTable ]
    [ COPY_OPTIONS = ( copyOptions ) ]
    [ COMMENT = '<string_literal>' ]

其中：

directoryTable (for Amazon S3) ::=
  [ DIRECTORY = ( ENABLE = { TRUE | FALSE }
                  [ AUTO_REFRESH = { TRUE | FALSE } ] ) ]

Copy

CREATE STAGE mystage
  URL='s3://load/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (
    ENABLE = true
    AUTO_REFRESH = true
  );

第 2 步：配置事件通知¶

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

为便于使用，这些 SQS 队列由 Snowflake 创建和管理。DESCRIBE STAGE 命令输出显示 SQS 队列的 Amazon 资源名称 (ARN)。

1. 执行 DESCRIBE STAGE 命令：

   DESC STAGE <stage_name>;
   

   Copy

   例如：

   DESC STAGE mystage;
   

   Copy

   注意 directory_notification_channel 字段中目录表 SQS 队列的 ARN。将 ARN 复制到方便的位置。

   备注

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

2. 登录 AWS 管理控制台。

3. 使用 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：粘贴 DESC STAGE 输出中的 SQS 队列名称。

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

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

第 3 步：手动刷新目录表元数据¶

使用 ALTER STAGE 命令手动刷新目录表中的元数据。

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

其中：

REFRESH

访问目录表定义中引用的暂存数据文件并更新表元数据：

• 路径中的新文件将添加到表元数据中。

• 对路径中文件的更改将在表元数据中更新。

• 路径中不再存在的文件将从表元数据中移除。

目前，每次将文件添加到暂存区、更新或删除文件时，都需要执行此命令。此步骤将元数据与目录表的暂存区定义中的最新关联文件集同步。

SUBPATH = '<relative-path>'

（可选）指定相对路径以刷新数据文件的特定子集的元数据。

例如，在名为 mystage 的暂存区中手动刷新目录表元数据：

ALTER STAGE mystage REFRESH;

第 4 步：配置安全性¶

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

先决条件：创建 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 将暂存数据文件读入目录表元数据。或者，您可以使用现有的外部暂存区。

-- External stage
CREATE [ OR REPLACE ] [ TEMPORARY ] STAGE [ IF NOT EXISTS ] <external_stage_name>
      <cloud_storage_access_settings>
    [ FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
    [ directoryTable ]
    [ COPY_OPTIONS = ( copyOptions ) ]
    [ COMMENT = '<string_literal>' ]

其中：

directoryTable (for Amazon S3) ::=
  [ DIRECTORY = ( ENABLE = { TRUE | FALSE }
                  [ AUTO_REFRESH = { TRUE | FALSE } ]
                  [ AWS_SNS_TOPIC = '<sns_topic_arn>' ] ) ]

Copy

CREATE STAGE mystage
  URL='s3://load/files/'
  STORAGE_INTEGRATION = my_storage_int
  DIRECTORY = (
    ENABLE = true
    AUTO_REFRESH = true
    AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket'
  );

第 3 步：手动刷新目录表元数据¶

使用 ALTER STAGE 命令手动刷新目录表中的元数据。

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

其中：

REFRESH

访问目录表定义中引用的暂存数据文件并更新表元数据：

• 路径中的新文件将添加到表元数据中。

• 对路径中文件的更改将在表元数据中更新。

• 路径中不再存在的文件将从表元数据中移除。

目前，每次将文件添加到暂存区、更新或删除文件时，都需要执行此命令。此步骤将元数据与目录表的暂存区定义中的最新关联文件集同步。

SUBPATH = '<relative-path>'

（可选）指定相对路径以刷新数据文件的特定子集的元数据。

例如，在名为 mystage 的暂存区中手动刷新目录表元数据：

ALTER STAGE mystage REFRESH;

第 4 步：配置安全性¶

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