HULFT Squareを使用してSnowflakeにデータをロードしてみた

# はじめに
こんにちは、すぎもんです:smile:

企業内に点在する大量のデータを集約・蓄積し、データの統合と分析を可能にするため、データウェアハウス（DWH）の必要性が高まっています。
DWHを活用すれば、必要なデータを入れて分析を行えるため、業務の効率化に繋げられます。

# Snowflakeとは

Snowflake はクラウド上に展開で実行されるDWHです。
Amazon Web Services、Microsoft Azure、Google Cloud Storage といったクラウドサービスで使用の基盤上に構築できます。

データレイクとして想定するAWS S3等クラウドサービスのストレージにあるファイルをロード(読み込み)、およびアンロード(書き出し)を行うことができます。


# 今回やること
今回は、HULFT Squareを活用して以下の処理が実行できることを確認していきます。
・Snowflake⇒データアンロード
・ストレージ（ファイル）⇒Snowflakeのデータロード

パフォーマンスを確認するため、1GBを超えるデータのロードを実施致します。

# 環境イメージ
ファイルはAWS S3上に配置し、Snowflakeへのデータ移行を行うために「外部ステージ」と「ストレージ統合」を設定し、データ連携を実施していきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/88a1abaa-5f63-f320-cd29-95fbc23e8a7e.png)

:::note info
外部ステージとストレージ統合の利用前提条件は下記の通りです。

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

今回はSnowFlakeOnAWSとAmazonS3は共に同一のリージョンに構築しています。
また、今回の検証のウェアハウスサイズは最小の**XS**です。
:::


# 検証環境準備
## 1.Snowflake側

### 1-1.「外部ステージ」の参照先であるS3バケット情報の取得
AWSのS3コンソールを開き、「外部ステージ」で参照するS3バケットの以下の情報を取得します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/2f751f11-0e86-9090-b829-d5a64c6a68bc.png)

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

### 1-2．S3バケットに対しての読込/書込を許可するIAMポリシー作成
AWSのIAMコンソールを開きます。
JSONエディターを開き、以下のポリシーをエディターの画面に貼り付けます。

:::note warn
Snowflakeではフォルダー（およびサブフォルダー）内のファイルにアクセスするためにはS3バケットおよびフォルダーに対する次の権限が必要です。

s3:GetObject
s3:GetObjectVersion
s3:ListBucket
s3:PutObject
s3:DeleteObject
s3:DeleteObjectVersion
:::


```
{
    "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"
        }
        ]
}
```


**[S3バケットのARN]** の箇所を手順1-1で取得したS3バケットのARNに書き換えます
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/f7150513-358f-9c23-ff1f-0f4915785040.png)

識別しやすいポリシー名でポリシーを作成します。 (例：s3snowflakeaccess)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/b2b7b785-014a-66c9-c708-18dbade10260.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/483bf709-9600-2116-4dc6-83178b507c67.png)

### 1-3．IAMロール作成
**[信頼されたエンティティを選択]** 画面にて **[アカウントID]** と **[外部ID]** にダミー値を入力して作成していきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/8f09200e-81b2-22cf-b144-4b3365e4b78f.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/00951028-085a-a041-c432-f69661691add.png)

:::note info
後述の手順「1-4.ストレージ統合作成」にてSnowflakeが使用するAWSのIAMユーザーと「外部ID」を取得できます。
後述の手順で、「AWSアカウント」の部分にSnowflakeが使用するAWSのIAMユーザー、「外部ID」の部分にストレージ統合で使うIDに書き換えます。
:::

手順1-②で作成したIAMポリシーを紐付けます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/e199001e-4c76-be3f-04a1-4e73e20981b2.png)

識別しやすいロール名でロールを作成します。 (例：s3snowflakeaccessrole)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/d84e6972-f313-3cde-5b50-7ebada54ba57.png)

:::note warn
作成したIAMロールの　**[ARN]**　を取得し、控えておきます。直後の「1-4.ストレージ統合作成」にて使用します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/8663ba19-f149-fa28-b1a9-5ac4ba888bbe.png)
:::

### 1-4．ストレージ統合作成
SnowflakeのWebコンソールSnowsightにログインし、**[CREATE STORAGE INTEGRATION]** を実行します

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

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/d7a94fd8-3c46-c7e0-74ed-ac9e24cc88f5.png)

正常に作成されたことを確認後、**[DESC INTEGRATION]**　を実行し、Snowflakeが使用するAWSの **[IAMユーザー]** と **[外部ID]** を取得します。

```
DESC INTEGRATION <手順1-4で作成した「ストレージ統合」の名称>;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/99453c5d-207e-05e3-39f5-a2dc0837d99f.png)


:::note info
STORAGE_AWS_IAM_USER_ARN：**[IAMユーザー]** 
STORAGE_AWS_EXTERNAL_ID：**[外部ID]**
:::

AWSのIAMコンソールを開き、上記のIAMユーザーと外部IDの情報を基にIAMロールを修正します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/ffba0401-4ac4-cdf6-9a64-4e37fe14e7c3.png)

### 1-5．外部ステージ作成
AWSのS3コンソールを開き、「外部ステージ」の参照先フォルダの**[S3 URI**[を取得します
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/7aabc000-3700-ec1e-739a-2566d184c882.png)

SnowflakeのWebインターフェイスにログインし、「外部ステージ」を使うロールに対して、「ストレージ統合」の使用権限を付与します。
```
GRANT USAGE ON <ストレージ統合の名称> TO ROLE <外部ステージを使うロール>;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/700d96bb-ac7f-a853-f0b4-67e865745475.png)

**[CREATE STAGE]**　を実行し、「外部ステージ」を作成します。
```
create stage <作成する外部ステージの名称>
  url='<手順1-5で取得した外部ステージの参照先フォルダのS3 URL>'
  storage_integration = <ストレージ統合の名称>;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/8a342e6a-2dc0-62f5-1f81-af95be1e6cd9.png)


## 2．HULFT Square側
ここからはHULFT SquareからSnowflakeへ接続する設定を実施していきます。
### 2-1.コネクション作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/45a01051-13be-8c40-b1e7-dd52b8919e56.png)

**[名前]**　任意の名前、今回は`SnowflakeTEST`を入力して**[次へ]**をクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/1884a111-0c78-14af-67ad-df4f79208d3e.png)


**[アカウント識別子]**　等必要項目に値を入力して**[次へ]**をクリック

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/e9a54f78-f3b2-e20e-bbe3-10636d8c6c55.png)

**[接続テスト]**　の成功を確認後、**[完了]** をクリックし、コネクション作成は終了です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/5965b614-15f5-959d-0efe-613528a43474.png)


#  検証実施
## 1.データアンロード
今回まずテストデータ準備を兼ねてSnowflakeのサンプルデータをS3の外部ステージにアンロードしていきます

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/9427a8ef-c458-d162-0693-4fc615945e0c.png)

抽出対象データは下記の通りです。

 スキーマ名 |テーブル名|サイズ | レコード数 |
|:-|:-|:-|:-
|TPCH_SF100|CUSTOMER|1GB|1500万


### 1-1.データアンロード用スクリプト作成
デプロイ単位であるプロジェクトを新規作成し、配下に実行用のスクリプトを作成していきます。
以下の項目を入力後、**[次へ]** クリックします。

**[名前]**　任意の名前、今回は`Snowflakeload`を入力
**[スクリプトを作成]**　にチェックを入れる
**[スクリプト名]**　任意の名前、今回は`unload`を入力

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/cca56fba-a3c8-8e48-96ba-79d6ada09cd8.png)


**[デザイナを起動]**　をクリックし、**[HULFT Square Designer]** を起動し、スクリプトを作成していきます。

「Snowflake更新系SQL実行」アイコンの配置

**[ツールパレット]**　より　**[データベース＞Snowflake＞更新系SQL実行]**　アイコンを選択しドラッグ＆ドロップしてスクリプトキャンバスへ配置します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/fa5d5b98-36f0-5858-1c29-894976506968.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/756668dc-6d83-b79c-ab29-874cea2e5b2c.png)

**[名前]**　任意の名前、今回は`S3へのアンロード`を入力
**[接続先]**　今回は前述で作成した`SnoWflaketest`コネクションを選択
**[SQL文]**　外部ステージへのアンロードを意味する`Copy　into`文を入力

```
COPY INTO @HSQ_EXT_STAGE/unload/ from SNOWFLAKE_SAMPLE_DATA.TPCH_SF100.CUSTOMER;
```

下記のように　**[start]**　から　**[END]**　までドラッグ＆ドロップでプロセスフローの矢印を繋ぐとデータアンロード用スクリプトの完成です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/e2b825f9-eef1-f367-ca44-38b99c7133af.png)


### 1-2.データアンロード用スクリプト実行確認

デザイナ画面からスクリプトの実行を確認後、S3のコンソールへログインするとgzファイルのデータがアンロードされていることが確認できます。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/7a487347-9789-5a12-519e-c8542d7873f3.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/44d0ca22-12a6-9c53-eb3c-c91d0d19cee9.png)

:::note info
Copy　into文でアンロードを実施した場合、デフォルトでgzファイルに圧縮してS3に格納されます。
ウェアハウスサイズXSでの実行時間は約13秒でした。
:::

## 2.データロード
アンロードしたサンプルデータを別スキーマの同一構造のテーブルへロードしていきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/c9632d84-061d-f6b3-d44f-47354a24b666.png)

書き込み対象テーブルの定義は下記の通りです
```
create or replace TABLE TEST.PUBLIC.CUSTOMER (
	C_CUSTKEY NUMBER(38,0),
	C_NAME VARCHAR(25),
	C_ADDRESS VARCHAR(40),
	C_NATIONKEY NUMBER(3,0),
	C_PHONE VARCHAR(18),
	C_ACCTBAL NUMBER(6,2),
	C_MKTSEGMENT VARCHAR(25),
	C_COMMENT VARCHAR(400)
);
```
### 2-1.データロード用スクリプト作成
作成済みである`Snowflakeload`プロジェクト配下に実行用のスクリプトを作成していきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/53a27cf5-f9d0-e8ad-e16f-4ee2acbd0aad.png)

**[スクリプト名]**　任意の名前、今回は`dataload`を入力

「Snowflake更新系SQL実行」アイコンの配置

**[ツールパレット]**　より　**[データベース＞Snowflake＞更新系SQL実行]**　アイコンを選択しドラッグ＆ドロップしてスクリプトキャンバスへ配置します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/fa5d5b98-36f0-5858-1c29-894976506968.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/e36f7c19-312a-2373-248d-7d55e35a5d8f.png)


**[名前]**　任意の名前、今回は`テーブルへのデータロード`を入力
**[接続先]**　今回は前述で作成した`SnoWflaketest`コネクションを選択
**[SQL文]**　`customor`テーブルへのロードを意味する`Copy　into`文を入力

```
copy into customer from @hsq_ext_stage pattern = '.*data_0_.*';
```

:::note info
外部ステージのファイルを取得する際はpattren句と正規表現を用いて、フォルダ配下のファイルを複数取得することが可能です。
:::

下記のように　**[start]**　から　**[END]**　までドラッグ＆ドロップでプロセスフローの矢印を繋ぐとデータアンロード用スクリプトの完成です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/c8257e55-8d90-95fd-2bd9-51e36c7c2622.png)

 ### 2-2.データロード用スクリプト実行確認
デザイナ画面からスクリプトの正常終了と1500万件のデータが更新されたことが確認できます。

:::note info
ウェアハウスサイズXSでの実行時間は約17秒でした。
:::

 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/07558488-09c5-1dfb-ce5e-6c842ff2e9d2.png)

また、SnowflakeのWebインターフェイスからも対象のテーブルに1500万件のデータが反映されていることが確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/458288/5c37dd61-d90f-f23d-c02c-b40b97bcfcaa.png)

# おわりに
いかがだったでしょうか。
HULFT SquareからSnowflakeへ接続し、AWS S3とのデータ連携が実現できることを確認できました。

SnowflakeとAWS S3間で外部ステージ設定を実施した後は、GUIでデータ連携プログラムを構築できることがHULFT Squareが持つ強みといえます。
また、当ブログでは実施していませんが、Snowflakeへのデータロード前にS3へ貯めたファイルのデータ変換ができることもHULFT Squareを利用することで得られるメリットです。

当ブログが、SnowflakeとHULFT Squareとの連携構築の一助にもなれば幸いです。

ここまで読んでいただきありがとうございました。それでは、また！