Snowflakeストレージ統合でS3からデータをロードしてみた

ストレージ統合利用した時のAWS側の設定を確認したかったの
特集

坂巻一義

2022.07.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

SnowflakeではS3のデータロードが可能です。今回は「ストレージ統合」を利用してS3からデータをロードしてみたいと思います。本ブログの手順等は以下ドキュメントを参考にしています。

Snowflakeではフリートライアルが提供されていますので、お試しされる方は以下を参考にしてください。

Snowflake側の操作（その１）

ワークシート（Webインタフェース）または、SnowSQL（CLIクライアント）を使用して、データベースの操作など、クエリを実行することが可能です。

ここでは、SnowSQLを利用しますが、セットアップ等については割愛していますので、SnowSQLを利用されるかたは、以下を参考にしてください。

ストレージ統合の作成

S3等のクラウドストレージにアクセスするオブジェクト「ストレージ統合」を作成します。

ストレージ統合を利用することで、SnowflakeからS3へのアクセスにて永続的なクレデンシャルの使用を回避できます。S3に対するアクセスはこちらの方法が推奨されています。

CREATE STORAGE INTEGRATIONコマンドでストレージ統合を作成します。 ここでは、「test_integration」という名前で作成しています。

CREATE STORAGE INTEGRATION test_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::0:role/role_name'
  STORAGE_ALLOWED_LOCATIONS = ('s3://')
;

STORAGE_AWS_ROLE_ARN、STORAGE_ALLOWED_LOCATIONSの値はダミーです。 データファイルを格納したS3のパス、そのS3へアクセス可能なIAMロールの情報が必要ですが、後ほど作成します。上記ダミーのままストレージ統合を作成しました。

正常に作成できると、以下のような結果が返ってきます。

+----------------------------------------------------+                          
| status                                             |
|----------------------------------------------------|
| Integration TEST_INTEGRATION successfully created. |
+----------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.180s

DESCRIBE INTEGRATIONコマンドで設定内容を確認します。

>DESC INTEGRATION test_integration;
+---------------------------+---------------+------------------------------------------------+------------------+
| property                  | property_type | property_value                                 | property_default |
|---------------------------+---------------+------------------------------------------------+------------------|
| ENABLED                   | Boolean       | true                                           | false            |
| STORAGE_PROVIDER          | String        | S3                                             |                  |
| STORAGE_ALLOWED_LOCATIONS | List          | s3://                                          | []               |
| STORAGE_BLOCKED_LOCATIONS | List          |                                                | []               |
| STORAGE_AWS_IAM_USER_ARN  | String        | arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 |                  |
| STORAGE_AWS_ROLE_ARN      | String        | arn:aws:iam::0:role/role_name                  |                  |
| STORAGE_AWS_EXTERNAL_ID   | String        | 1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw= |                  |
| COMMENT                   | String        |                                                |                  |
+---------------------------+---------------+------------------------------------------------+------------------+
8 Row(s) produced. Time Elapsed: 1.112s

STORAGE_AWS_IAM_USER_ARN、STORAGE_AWS_EXTERNAL_IDの値を控えておきます。この後のAWS側の操作でIAMロール作成時に利用します。（値はマスクしてます）

AWS側の操作

ここからは、自身のAWSアカウント上の操作となります。

  • データファイルが格納されたS3バケットにアクセス可能なIAMポリシーの作成
  • Snowflake側のIAMユーザーを信頼関係に設定したIAMロール
    • 作成したポリシーをアタッチ

上記、IAMポリシー、IAMロールを作成するCFnテンプレートを用意しました。

template.yml 
AWSTemplateFormatVersion: 2010-09-09
Description: Create a role for Snowflake.
Parameters:
  S3BucketName:
    Type: String
    Description: Name of the S3 bucket containing the data file.
  SnowflakeUserArn:
    Type: String
    Description: IAM user created in Snowflake's AWS account.
  SnowflakeExternalId:
    Type: String
    Description: External ID to establish trust.

Resources:
  SnowflakeS3AccessRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: test-snowflake-role
      Path: /
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              AWS: !Sub ${SnowflakeUserArn}
            Action: sts:AssumeRole
            Condition:
              StringEquals:
                sts:ExternalId: !Sub ${SnowflakeExternalId}
      ManagedPolicyArns:
        - !Ref SnowflakeS3AccessPolicy

  SnowflakeS3AccessPolicy:
    Type: AWS::IAM::ManagedPolicy
    Properties:
      ManagedPolicyName: test-snowflake-policy
      Path: /
      PolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Action:
              - s3:PutObject
              - s3:GetObject
              - s3:GetObjectVersion
              - s3:DeleteObject
              - s3:DeleteObjectVersion
            Resource: !Sub arn:aws:s3:::${S3BucketName}/*
          - Effect: Allow
            Action:
              - s3:ListBucket
              - s3:GetBucketLocation
            Resource: !Sub arn:aws:s3:::${S3BucketName}

Outputs:
  SnowflakeS3AccessRoleArn:
    Value: !GetAtt SnowflakeS3AccessRole.Arn

CFnスタックを作成します。

> aws cloudformation deploy \
  --stack-name test-snowflake-iam \
  --template-file ./template.yml \
  --capabilities CAPABILITY_NAMED_IAM \
  --parameter-overrides \
    S3BucketName=test-snowflake-datafile \
    SnowflakeUserArn=arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681 \
    SnowflakeExternalId=1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw=

Waiting for changeset to be created..
Waiting for stack create/update to complete
Successfully created/updated stack - test-snowflake-iam

スタックの作成が完了したら、作成されたIAMロールのARNを確認します。この値でストレージ統合の値を変更します。（値はマスクしてます）

> aws cloudformation describe-stacks \
--stack-name test-snowflake-iam \
--query "Stacks[].Outputs[].OutputValue" \
--output table
--------------------------------------------------------
|                    DescribeStacks                    |
+------------------------------------------------------+
|  arn:aws:iam::77777xxxxxx:role/test-snowflake-role  |
+------------------------------------------------------+

なお、ロードするデータファイルが格納されたS3バケットは既に存在していることにしています。

00

testdata.csv

toshima,nanamaru
nazo,fukuro
somefuku,chan
somei,yoshino

Snowflake側の操作（その２）

ストレージ統合更新

Snowflakeのストレージ統合のSTORAGE_AWS_ROLE_ARN、STORAGE_ALLOWED_LOCATIONSを更新します。ALTER STORAGE INTEGRATIONコマンドを実行します。

ALTER STORAGE INTEGRATION test_integration SET STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::77777xxxxxx:role/test-snowflake-role';
ALTER STORAGE INTEGRATION test_integration SET STORAGE_ALLOWED_LOCATIONS = ('s3://test-snowflake-datafile/');

修正内容を確認します。

>DESC INTEGRATION test_integration;
+---------------------------+---------------+----------------------------------------------------+------------------+
| property                  | property_type | property_value                                     | property_default |
|---------------------------+---------------+----------------------------------------------------+------------------|
| ENABLED                   | Boolean       | true                                               | false            |
| STORAGE_PROVIDER          | String        | S3                                                 |                  |
| STORAGE_ALLOWED_LOCATIONS | List          | s3://test-snowflake-datafile/                      | []               |
| STORAGE_BLOCKED_LOCATIONS | List          |                                                    | []               |
| STORAGE_AWS_IAM_USER_ARN  | String        | arn:aws:iam::555555xxxxxx:user/0pgj-s-jpss8681     |                  |
| STORAGE_AWS_ROLE_ARN      | String        | arn:aws:iam::77777xxxxxx:role/test-snowflake-role |                  |
| STORAGE_AWS_EXTERNAL_ID   | String        | 1111111_SFCRole=3_x2Oanh2e9ZLr+cK0fwV7EhwZMTw=     |                  |
| COMMENT                   | String        |                                                    |                  |
+---------------------------+---------------+----------------------------------------------------+------------------+
8 Row(s) produced. Time Elapsed: 0.809s

データベース、テーブル作成

S3データをロードする、データベースとテーブルを作成します。CREATE DATABASE、CREATE TABLEコマンドを使用します。

create database testdb;
create table testtable (first VARCHAR,last VARCHAR);

外部ステージ作成

データをロードする際のファイル置き場となるステージを作成します。データファイルはクラウド（S3バケット）にありますので、外部ステージを作成します。

CREATE STAGEコマンドで作成します。

CREATE STAGE test_s3_stage STORAGE_INTEGRATION = test_integration URL = 's3://test-snowflake-datafile/';

データロード

データをロードするには、仮想ウェアハウスが必要です。

ここではデフォルトで作成されているCOMPUTE_WHを使用します。SHOW WAREHOUSESコマンドで状態を確認します。

>show WAREHOUSES;
+------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+
| name       | state     | type     | size    | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on                    | resumed_on                    | updated_on                    | owner    | comment | resource_monitor | actives | pendings | failed | suspended | uuid    | scaling_policy |
|------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------|
| COMPUTE_WH | SUSPENDED | STANDARD | X-Small |                 1 |                 1 |                0 |       0 |      0 | Y          | Y          |          600 | true        |           |              |           |       | 2022-07-04 17:19:04.140 -0700 | 2022-07-06 08:46:33.378 -0700 | 2022-07-06 08:46:33.378 -0700 | SYSADMIN |         | null             |       0 |        0 |      0 |         1 | 8122628 | STANDARD       |
+------------+-----------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+
1 Row(s) produced. Time Elapsed: 0.130s

停止状態であればALTER WAREHOUSEで起動します。

>alter warehouse COMPUTE_WH resume;
+----------------------------------+                                            
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.298s
>show WAREHOUSES;
+------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+
| name       | state   | type     | size    | min_cluster_count | max_cluster_count | started_clusters | running | queued | is_default | is_current | auto_suspend | auto_resume | available | provisioning | quiescing | other | created_on                    | resumed_on                    | updated_on                    | owner    | comment | resource_monitor | actives | pendings | failed | suspended | uuid    | scaling_policy |
|------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------|
| COMPUTE_WH | STARTED | STANDARD | X-Small |                 1 |                 1 |                1 |       0 |      0 | Y          | Y          |          600 | true        |  100      | 0            | 0         | 0     | 2022-07-04 17:19:04.140 -0700 | 2022-07-06 21:20:55.731 -0700 | 2022-07-06 21:20:55.731 -0700 | SYSADMIN |         | null             |       1 |        0 |      0 |         0 | 8122628 | STANDARD       |
+------------+---------+----------+---------+-------------------+-------------------+------------------+---------+--------+------------+------------+--------------+-------------+-----------+--------------+-----------+-------+-------------------------------+-------------------------------+-------------------------------+----------+---------+------------------+---------+----------+--------+-----------+---------+----------------+
1 Row(s) produced. Time Elapsed: 0.172s

COPY INTOコマンドでデータをロードします。

>copy into testtable from @test_s3_stage pattern='testdata.csv';
+-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                      | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://test-snowflake-datafile/testdata.csv | LOADED |           4 |           4 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+-------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 3.511s

ロードしたデータを確認します。

>select * from testtable;
+----------+----------+                                                         
| FIRST    | LAST     |
|----------+----------|
| toshima  | nanamaru |
| nazo     | fukuro   |
| somefuku | chan     |
| somei    | yoshino  |
+----------+----------+
4 Row(s) produced. Time Elapsed: 0.321s

さいごに

S3のデータロードについては、冒頭であげたドキュメント等ありますが、少々読み取れない部分があったので実際にやってみてました。誰かのお役に立てれば幸いです。

AWS

関連記事

Snowflakeの認定試験を管理するSnowflake Certification portalの移行作業やってみた

さがら

2024.04.25

SnowflakeのリソースをTerraform×GitHub Actionsで管理するための手順をまとめてみた

さがら

2024.04.09

2024年3月にリリースされたSnowflakeの新機能・変更点のまとめ #SnowflakeDB

安原朋紀

2024.04.04

Denodo から Snowflake を参照時、利用するウェアハウスを動的に指定する

川田

2024.03.27