■ はじめに

久しぶりに、業務で

https://dk521123.hatenablog.com/entry/2022/06/29/221037

でやったSnowflake の ストレージ統合 を使って、
アンロードをやったのだが、結構、ハマったので
メモっておく

なお、ハマった箇所の一部としては、以下の
ステップ4：Snowflakeアカウントの AWS IAM ユーザーを取得する
ステップ5：バケットオブジェクトにアクセスするために IAM ユーザー権限を付与する
をやらなくて、エラー「Error assuming AWS_ROLE」が発生した。
（以下の自分の過去の記事で気づけた、、、ありがとう、自分）

エラー「Error assuming AWS_ROLE」時の対応
https://dk521123.hatenablog.com/entry/2022/11/25/175912

【０】全体の流れ及び公式ドキュメント
ステップ1：S3バケットのアクセス許可を構成する
ステップ2：IAM AWS ロールを作成する
ステップ3：Snowflakeでクラウドストレージ統合を作成する
ステップ4：Snowflakeアカウントの AWS IAM ユーザーを取得する
ステップ5：バケットオブジェクトにアクセスするために IAM ユーザー権限を付与する
ステップ6：外部ステージを作成する
おまけ：アンロード

【０】全体の流れ及び公式ドキュメント

* まずは、さらっと以下を一読した方がいいかも、、、

https://docs.snowflake.com/ja/user-guide/data-load-s3-config-storage-integration

ステップ1：S3バケットのアクセス許可を構成する
ステップ2：IAM AWS ロールを作成する
ステップ3：Snowflakeでクラウドストレージ統合を作成する
ステップ4：Snowflakeアカウントの AWS IAM ユーザーを取得する
ステップ5：バケットオブジェクトにアクセスするために IAM ユーザー権限を付与する
ステップ6：外部ステージを作成する

ステップ1：S3バケットのアクセス許可を構成する

* 特にアクセス制御していない場合は、スキップできる

アクセス制御している場合

* 以下のActionを許可させる
 + s3:GetBucketLocation
 + s3:GetObject
 + s3:GetObjectVersion
 + s3:ListBucket
 + s3:PutObject (※１)
 + s3:DeleteObject (※１)

※１：Writeしたい場合（例えば、アンロードなど）

ステップ2：IAM AWS ロールを作成する

* 以下の例のようなポリシーを持ったIAMロールを作成する

例：read/write の場合（e.g. アンロード）
https://docs.snowflake.com/ja/user-guide/data-unload-s3#configuring-an-s3-bucket-for-unloading-data

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Action":[
            "s3:PutObject",
            "s3:DeleteObject",
            "s3:GetObject",
            "s3:GetObjectVersion",
            "s3:GetBucketLocation"
         ],
         "Resource":"arn:aws:s3:::your-s3-bucket",
         "Resource":"arn:aws:s3:::your-s3-bucket/*"
      }
   ]
}

ステップ3：Snowflakeでクラウドストレージ統合を作成する

* ストレージ統合に関する詳細は、以下の関連記事を参照のこと

Snowflake ～ストレージ統合～
https://dk521123.hatenablog.com/entry/2022/06/29/221037

公式ドキュメント：CREATE STORAGE INTEGRATION
https://docs.snowflake.com/ja/sql-reference/sql/create-storage-integration

例

CREATE STORAGE INTEGRATION demo_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole' -- ステップ２で作成したIAMロールのARNを指定
  STORAGE_ALLOWED_LOCATIONS = ('s3://your-s3-bucket/') -- 複数指定も可能
;

ステップ4：Snowflakeアカウントの AWS IAM ユーザーを取得する

* 「DESC INTEGRATION <integration_name>」を使って、
　以下をメモっておく

#	PROPERTY	Value例	メモ
1	STORAGE_AWS_IAM_USER_ARN	arn:aws:iam::123456789001:user/abc1-b-self1234	Snowflakeアカウント用に作成された AWS IAM ユーザ
2	STORAGE_AWS_EXTERNAL_ID	MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=	信頼関係を確立するために必要な外部 ID

コマンド例

-- DESC INTEGRATION <integration_name>;
DESC INTEGRATION demo_integration;

| 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=

ステップ5：バケットオブジェクトにアクセスするために IAM ユーザー権限を付与する

[1] AWSマネージメントコンソールにおいて
　「ステップ2： IAM AWS ロールを作成する」で作成したロールを選択
[2] [Trust relationships]-[Edit trust relationship] を選択
[3] 以下の例を参考に置き換える
 * snowflake_user_arn は、ステップ4で記録した STORAGE_AWS_IAM_USER_ARN
 * snowflake_external_id は、ステップ4で記録した STORAGE_AWS_EXTERNAL_ID

例

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::123456789001:user/abc1-b-self1234" // ★<snowflake_user_arn>
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=" // ★<snowflake_external_id>
        }
      }
    }
  ]
}

External ID を複数指定したい場合

          "sts:ExternalId": [
            "MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=",
            "MYACCOUNT_SFCRole=2_a123456/s0aEFGGUHJE|NKXXX="
          ]

ステップ6：外部ステージを作成する

* ステージに関する詳細は、以下の関連記事を参照のこと

Snowflake ～基本編 / ステージ～
https://dk521123.hatenablog.com/entry/2022/09/01/220643

公式ドキュメント：CREATE STAGE
https://docs.snowflake.com/ja/sql-reference/sql/create-stage

例

USE DATABASE demo_db
USE SCHEMA demo_schema;

-- CSVの場合
CREATE OR REPLACE STAGE demo_s3_stage
  STORAGE_INTEGRATION = demo_integration
  URL = 's3://your-s3-bucket/'
  FILE_FORMAT = (
    TYPE =CSV
    FIELD_DELIMITER = ','
  )
;

-- 確認
SELECT
  *
--  $1:user_id,
--  $1:user_name
FROM
  @demo_s3_stage;

おまけ：アンロード

-- 一旦削除
REMOVE @demo_db.demo_schema.demo_s3_stage/insert=20220704/demo_output.tsv;

-- アンロード (TSV)
COPY INTO @demo_db.demo_schema.demo_s3_stage/insert=20220704/demo_output.tsv
FROM (
  SELECT * FROM demo_bank.public.demo_trips
)
FILE_FORMAT = (
  TYPE＝CSV
  FIELD_DELIMITER = '\t'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ( '' )
  COMPRESSION = NONE
)
SINGLE=TRUE
HEADER = TRUE
;

ランキング参加中

プログラミング

プログラムの超個人的なメモ

Memo for Programming.

【Snowflake】Snowflake ～ストレージ統合の作成手順～

■ はじめに

目次

【０】全体の流れ及び公式ドキュメント

ステップ1：S3バケットのアクセス許可を構成する

ステップ2：IAM AWS ロールを作成する

ステップ3：Snowflakeでクラウドストレージ統合を作成する

ステップ4：Snowflakeアカウントの AWS IAM ユーザーを取得する

ステップ5：バケットオブジェクトにアクセスするために IAM ユーザー権限を付与する

ステップ6：外部ステージを作成する

おまけ：アンロード

関連記事