Snowflakeで外部ステージを作成する

<やりたいこと>

「外部ステージ」は、SnowflakeからAWSのS3バケット上の特定のフォルダを参照します。
「外部ステージ」を使って、S3バケット上のファイルをCOPYコマンドでSnowflakeのテーブルにロードできるようになります。

ただし、COPYを実行するには、S3バケットに対する読込権限が必要です。
「ストレージ統合」を使うことで、S3バケットに対する読込を許可するIAMロールの権限を取得できます。

現場業務で、AWSのRedshiftからSnowflakeへのデータ移行を行うために「外部ステージ」と「ストレージ統合」を設定しました。
Redshiftから移行対象のデータをS3バケットにUnloadし、SnowflakeのCOPYコマンドを使ってSnowflakeのテーブルにロードしました。
今後の業務のためにも「外部ステージ」と「ストレージ統合」の作成手順をまとめておきたいと思います。

<前提条件>

1.SnowflakeのエディションはStandard以上であること。
2.AWSとSnowflakeが同じリージョンであること

<作業手順>

① 「外部ステージ」の参照先のS3バケットの情報を取得します。
② AWSで「ストレージ統合」に設定するIAMロールを作成します。
③ Snowflakeで「ストレージ統合」を作成します。
④ 「ストレージ統合」から得たIAMユーザーと外部IDの情報を基にIAMロールを修正します。
⑤ Snowflakeで「外部ステージ」を作成します。

① 「外部ステージ」の参照先のS3バケットの情報を取得します

AWSのS3コンソールを開き、「外部ステージ」で参照するS3バケットの以下の情報を取得します。

・ARN：arn:aws:s3:::[S3バケットの名称] (例：arn:aws:s3:::muramatsu-snowflake)
・S3 URI：s3://[S3バケットの名称]/ (例：s3://muramatsu-snowflake/)

② Snowflakeで「ストレージ統合」を作成します。

②-1 S3バケットに対しての読込/書込を許可するIAMポリシーを作成します。

AWSのIAMコンソールを開きます。
JSONエディターを開き、以下のポリシーをエディターの画面に貼り付けます。
[S3バケットのARN]に手順①-1で取得したS3バケットのARNに書き換えます。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action":[
                "s3:PutObject",
                "s3:GetObject",
                "s3:GetObjectVersion",
                "s3:DeleteObject",
                "s3:DeleteObjectVersion"
                ],
            "Resource":"[S3バケットのARN]/*",
            "Effect":"Allow"
        },
        {
            "Action":[
                "s3:ListBucket",
                "s3:GetBucketLocation"
                ],
            "Resource":"[S3バケットのARN]",
            "Effect":"Allow"
        }
        ]
}

②-2 IAMロールを作成します。

②-2-1 信頼関係にて「アカウントID」と「外部ID」にダミー値を入力します。（後述の手順で修正します）

IAMロールの使用をどのAWSアカウントに許可するか、またその条件（外部ID）を指定します。
「AWSアカウント」、「外部ID」ともにダミー値を入力します。
「ストレージ統合」を作成すると、Snowflakeが使用するAWSのIAMユーザーと「外部ID」を取得できます。
後述の手順で、「AWSアカウント」の部分にSnowflakeが使用するAWSのIAMユーザー、「外部ID」の部分にストレージ統合で使うIDに書き換えます。

②-2-2 上記手順②-1で作成したIAMポリシーを紐付けます。

②-3 作成したIAMロールの「ARN」を取得し、メモ帳に控えておきます。

③ Snowflakeで「ストレージ統合」を作成します。

③-1 SnowflakeでCreate Storage Integrationを実行して「ストレージ統合」を作成します。

SnowflakeのWebインターフェイスにログインし、以下のSQLを実行します。

CREATE STORAGE INTEGRATION <ストレージ統合の名称>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<手順②-3で取得したIAMロールのARN>'
  STORAGE_ALLOWED_LOCATIONS = ('<手順②-1で取得したS3バケットのS3 URI>');

③-2 DESC INTEGRATIONを実行し、Snowflakeが使用するAWSの「IAMユーザー」と「外部ID」を取得します。

DESC INTEGRATION <手順③-1で作成した「ストレージ統合」の名称>;

後述の手順で、取得した「IMAユーザー」と「外部ID」の値を使って手順②で作成したIAMロールの信頼関係を修正します。

STORAGE_AWS_IAM_USER_ARN：arn:aws:iam::908533587514:user/jkzw-s-jpss9116
STORAGE_AWS_EXTERNAL_ID：EF87670_SFCRole=2_3icWNYHDb0nMxAm0eQZVRijkMzg=

④ 「ストレージ統合」から得たIAMユーザーと外部IDの情報を基にIAMロールを修正します。

手順②-2で作成したIAMロールの「信頼関係」では、「アカウントID」と「外部ID」にダミー値を設定しているため修正する必要があります。

⑤ Snowflakeで「外部ステージ」を作成します。

⑤-1 AWSのS3コンソールを開き、「外部ステージ」の参照先フォルダの「S3 URI」を取得します。

s3://muramatsu-snowflake/trial_db/trial_schema/
(外部ステージの参照先のS3のURL)

⑤-2 Snowflakeで「ストレージ統合」の使用権限をACCOUNTADMIN以外の他のロールに付与します。

SnowflakeのWebインターフェイスにログインし、「外部ステージ」を使うロールに対して、「ストレージ統合」の使用権限を付与します。

GRANT USAGE ON <ストレージ統合の名称> TO ROLE <外部ステージを使うロール>;

⑤-3 CREATE STAGEを実行し、「外部ステージ」を作成します。

create stage <作成する外部ステージの名称>
  url='<手順⑤-1で取得した外部ステージの参照先フォルダのS3 URL>'
  storage_integration = <ストレージ統合の名称>;

<検証>

Snowflakeから「外部ステージ」として設定したS3バケット上のフォルダ内のファイルを参照できるか確認します。
具体的には、外部ステージが参照するフォルダにファイルをアップロードし、SnowflakeからSelectを実行して、ファイル内のデータを参照できることを確認します。

確認手順① 検証用のテキストファイルを作り、Selectでデータを抽出できるように構造化データを入力します。

↓外部ステージ上のファイルに対してSelectができるか確認する

確認手順② 外部ステージの設定先のフォルダにテキストファイルをアップロードします。

確認手順③ SnowflakeのWebインターフェイスにログインし、以下のSQLを実行してフォルダ内のファイルのデータを参照できることを確認します。

select $1,$2 from @<外部ステージの名称>;

ファイル内の列(フィールド)を$を使って識別します。　1列目が$1、2列目が$2、3列目が$3となり、4列以降も同様です。

おわりに

「外部ステージ」と「ストレージ統合」により、S3バケット上のフォルダに保存されたファイルのデータをCOPYコマンドでSnowflakeにロードできるようになりました。
「外部ステージ」の作成によりSnowpipeも使えるようになりますので、こちらの機能も検証してみたいと思います。

ご案内

株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/?utm_source=qiita&utm_medium=referral&utm_campaign=qiita_zeuscloud_content-area