【Snowflake】名前付きステージを使用してファイルをデータロードする

# はじめに
名前付きステージを使用してCSVファイルのデータをテーブルにロードする機会があったため、情報共有のため名前付きステージの作成方法などをまとめてみました。

# やりたいこと
手元にあるファイルを名前付きステージにアップロードし、指定のテーブルにデータをロードしたい

# 名前付きステージとは
### ステージ
ステージとは、データファイルの保存場所のことです。

### 内部ステージ
Snowflakeには３種類の内部ステージがあります。
今回は３つめに記載している名前付きステージを使用します。

- <strong>ユーザーステージ</strong>
　各ユーザーにデフォルトで割り当てられるステージ
　割り当てられた１人のユーザーのみがアクセスできる

- <strong>テーブルステージ</strong>
　各テーブルにデフォルトで割り当てられるステージ
　テーブルごとに用意されており、複数ユーザーがアクセス可能

- <strong>名前付きステージ</strong>
　権限が付与されている複数のテーブル、ユーザーがアクセス可能
　共有フォルダみたいなイメージ


https://docs.snowflake.com/ja/user-guide/data-load-local-file-system-create-stage


# 作成するもの
- テーブル、CSVファイル
- 名前付きステージ
- ストアドプロシージャ

# 実行手順
テーブルにCSVファイルのデータをロードする際の手順です。

1\. 名前付きステージにCSVファイルをアップロードする
2\. ストアドプロシージャを実行し、CSVファイルのデータをテーブルにロードする
3\. テーブルにデータがロードされていることを確認する

## テーブル、CSVファイル
今回はテスト用に簡易的に下記のテーブルとCSVファイルを作成しました。
### テーブル
```sql:TBL.ddl
create or replace table TBL
(
   id             VARCHAR(5)       NOT NULL
  ,name           VARCHAR(50)
  ,quantity       NUMBER(10,0)
  ,price          NUMBER(10,0)
  ,create_date    TIMESTAMP
);
```

### CSVファイル（サンプルデータ）
```csv:TBL.csv
00001,A,10,1000
00002,B,20,8000
00003,C,5,25000
```

## 名前付きステージ
「INSTG」という名前の名前付きステージを作成します。
取込ファイルの中身に合わせてFILE_FORMAT等の指定が必要です。

```sql:INSTG.ddl
CREATE OR REPLACE STAGE DAP_YAMAMOTO.INSTG_TEST.INSTG
    --CSVファイル、ヘッダーなし
    FILE_FORMAT = (TYPE = 'CSV'   SKIP_HEADER = 1)
;
```

https://docs.snowflake.com/ja/sql-reference/sql/create-stage

## ストアドプロシージャ
名前付きステージにアップしたCSVファイルを、テーブルにロードするストアドプロシージャです。

```sql:DATALOAD_PROC()
CREATE OR REPLACE PROCEDURE DATALOAD_PROC()
RETURNS TABLE ("return_cd" NUMBER(2,0), "process" VARCHAR(500))
LANGUAGE SQL
EXECUTE AS CALLER
AS 
DECLARE

    -- 戻り値用変数
    return_cd    NUMBER;      -- 正常・異常判定
    res          resultset;   -- 結果出力用

    csv_name     VARCHAR;     -- CSVファイル名
    process      VARCHAR;     -- 処理名

BEGIN

    -- 内部ステージに格納されている「TBL」から始まるCSVファイル名を取得（対象は１つになる想定）
    -- 取得したCSVファイル名を変数csv_nameにいれる
    process := 'CSV名取得';
    SELECT DISTINCT
         '.*TBL.*' AS csv_name
    INTO
         :csv_name
    FROM
        @INSTG
    WHERE
        METADATA$FILENAME LIKE 'TBL%'
    ;

    -- 内部ステージに対象のCSVファイルがない場合、処理終了
    IF (csv_name IS NULL) 
    THEN
        process      := 'CSVファイルなし';
        return_cd    := 0;
        res := (SELECT :return_cd, :process);
        RETURN TABLE(res);
    END IF;

    -- テーブルのデータを削除
    process := 'TBLのデータを削除';
    TRUNCATE TBL;
   
    -- TEMPORARY TABLE作成
    process := 'TEMPテーブル作成';
    CREATE OR REPLACE TEMPORARY TABLE TEMP_TBL (
        id          CHAR(5)
       ,name        CHAR(50)
       ,quantity    CHAR(10)
       ,price       CHAR(10)
    );

    -- TEMPテーブルに内部ステージの対象CSVファイルのデータをロード
    -- COPY成功時にロード済みのファイルを削除（PURGE）
    process := 'CSVファイルのデータをTEMPテーブルにロード';
    COPY INTO 
        TEMP_TBL
    FROM 
        @INSTG
    PURGE = TRUE
    FILE_FORMAT = (TYPE = 'CSV') PATTERN = :csv_name
    ;

    -- テーブルにTEMPテーブルのデータを追加
    process := 'テーブルにデータを追加';
    INSERT INTO TBL (
    SELECT
         NULLIF(TRIM(id), '') 
        ,NULLIF(TRIM(name), '') 
        ,TO_DOUBLE(NULLIF(quantity, ''))
        ,TO_DOUBLE(NULLIF(price, ''))
        ,CURRENT_TIMESTAMP()
    FROM
        TEMP_TBL
    );

    -- 更新確定・正常値返却
    COMMIT;
    process      := '処理終了';
    return_cd    := 0;
    res := (SELECT :return_cd, :process);
    RETURN TABLE(res);

EXCEPTION
  WHEN OTHER THEN
    -- 更新キャンセル・エラー内容返却
    return_cd    := -1;
    ROLLBACK;

    res := (SELECT :return_cd, :process);
    RETURN TABLE(res);
END
;
;
```
# 検証
### 1\. 名前付きステージにCSVファイルをアップロードする
右上の「＋ファイル」をクリックすると左のアップロード画面が表示される。
CSVファイルをドラッグアンドドロップで格納し、「アップロード」をクリックする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3576431/39c82894-4866-42b6-8790-76c76bcd4d2b.png)

### 2\. ストアドプロシージャを実行し、名前付きステージのCSVファイルをテーブルにロードする
call文でストアドプロシージャを実行する。
問題がなければPROCESSに「処理終了」が表示される。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3576431/80fa8e68-327e-41eb-b388-ff5e7902cb25.png)


### 3\. テーブルにデータがロードされていることを確認する
テーブルのデータプレビューから、CSVファイルのデータがロードされていることが確認できました！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3576431/215efe1a-0305-4572-a24a-ff1c49f47686.png)


# 感想・まとめ
ファイルからのデータロードは外部ステージを使用する機会が多いかと思いますが、今回は手元ファイルを随時でロードしたかったので名前付きステージを使用しました。
ストアドプロシージャを作るのに少し時間がかかりますが、ファイル名の作成日時を追加して最新ファイルを取得する...とかも作りこめば可能かと思います。


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