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

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

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

現場業務で、AWSのRedshiftからSnowflakeへのデータ移行を行うために「外部ステージ」と「ストレージ統合」を設定しました。
Redshiftから移行対象のデータをS3バケットにUnloadし、SnowflakeのCOPYコマンドを使ってSnowflakeのテーブルにロードしました。
今後の業務のためにも「外部ステージ」と「ストレージ統合」の作成手順をまとめておきたいと思います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/72b8dee8-b4af-689d-7dc1-03aa590fe133.png)

# <前提条件>
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/)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/ad2a4781-4ad6-d737-5604-10046d5a6666.png)

## ② 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"
        }
        ]
}
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/d27495cc-e05f-fe47-29b2-4be9bff45ff4.png)

### ②-2 IAMロールを作成します。
#### ②-2-1 信頼関係にて「アカウントID」と「外部ID」にダミー値を入力します。（後述の手順で修正します）
 
 IAMロールの使用をどのAWSアカウントに許可するか、またその条件（外部ID）を指定します。
 「AWSアカウント」、「外部ID」ともにダミー値を入力します。
 「ストレージ統合」を作成すると、Snowflakeが使用するAWSのIAMユーザーと「外部ID」を取得できます。
 後述の手順で、「AWSアカウント」の部分にSnowflakeが使用するAWSのIAMユーザー、「外部ID」の部分にストレージ統合で使うIDに書き換えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/a0962cbb-ed3a-d77b-091c-93f54838fbaa.png)

#### ②-2-2 上記手順②-1で作成したIAMポリシーを紐付けます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/2ecca19e-b24a-2c57-f35a-dc05e901003e.png)

### ②-3 作成したIAMロールの「ARN」を取得し、メモ帳に控えておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/06316815-28f4-99f4-a433-13926cdda227.png)

## ③ 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>');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/2d12c3d4-f875-c846-fae8-e838a1129af4.png)

### ③-2 DESC INTEGRATIONを実行し、Snowflakeが使用するAWSの「IAMユーザー」と「外部ID」を取得します。
```
DESC INTEGRATION <手順③-1で作成した「ストレージ統合」の名称>;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/313c17b5-530e-6665-30b4-fd4f63b4bca4.png)
後述の手順で、取得した「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」にダミー値を設定しているため修正する必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/f2007810-a77a-f7d7-c316-0fe32c507720.png)


## ⑤ Snowflakeで「外部ステージ」を作成します。
### ⑤-1 AWSのS3コンソールを開き、「外部ステージ」の参照先フォルダの「S3 URI」を取得します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/5d8d94a0-862c-52e4-47fc-88f192ee27d5.png)
s3://muramatsu-snowflake/trial_db/trial_schema/
(外部ステージの参照先のS3のURL)

### ⑤-2 Snowflakeで「ストレージ統合」の使用権限をACCOUNTADMIN以外の他のロールに付与します。
SnowflakeのWebインターフェイスにログインし、「外部ステージ」を使うロールに対して、「ストレージ統合」の使用権限を付与します。
```
GRANT USAGE ON <ストレージ統合の名称> TO ROLE <外部ステージを使うロール>;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/fbe858c5-c3b8-95e4-df27-f7fe37746337.png)

### ⑤-3 CREATE STAGEを実行し、「外部ステージ」を作成します。
```
create stage <作成する外部ステージの名称>
  url='<手順⑤-1で取得した外部ステージの参照先フォルダのS3 URL>'
  storage_integration = <ストレージ統合の名称>;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/405c38f2-d427-a1bd-6e03-f779af46828e.png)

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

### 確認手順① 検証用のテキストファイルを作り、Selectでデータを抽出できるように構造化データを入力します。
↓外部ステージ上のファイルに対してSelectができるか確認する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/62dc4757-8cae-8abd-f490-c7e4a86f6279.png)

### 確認手順② 外部ステージの設定先のフォルダにテキストファイルをアップロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/ce5fe91b-0ed8-5b48-5140-fb38e0c5eaac.png)

### 確認手順③ SnowflakeのWebインターフェイスにログインし、以下のSQLを実行してフォルダ内のファイルのデータを参照できることを確認します。
```
select $1,$2 from @<外部ステージの名称>;
```
ファイル内の列(フィールド)を$を使って識別します。　1列目が$1、2列目が$2、3列目が$3となり、4列以降も同様です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1925708/c544261d-a393-74af-e63f-5a5a69e510fd.png)

# おわりに
「外部ステージ」と「ストレージ統合」により、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