はじめに
名前付きステージを使用してCSVファイルのデータをテーブルにロードする機会があったため、情報共有のため名前付きステージの作成方法などをまとめてみました。
やりたいこと
手元にあるファイルを名前付きステージにアップロードし、指定のテーブルにデータをロードしたい
名前付きステージとは
ステージ
ステージとは、データファイルの保存場所のことです。
内部ステージ
Snowflakeには３種類の内部ステージがあります。
今回は３つめに記載している名前付きステージを使用します。
-
ユーザーステージ
各ユーザーにデフォルトで割り当てられるステージ
割り当てられた１人のユーザーのみがアクセスできる -
テーブルステージ
各テーブルにデフォルトで割り当てられるステージ
テーブルごとに用意されており、複数ユーザーがアクセス可能 -
名前付きステージ
権限が付与されている複数のテーブル、ユーザーがアクセス可能
共有フォルダみたいなイメージ
作成するもの
- テーブル、CSVファイル
- 名前付きステージ
- ストアドプロシージャ
実行手順
テーブルにCSVファイルのデータをロードする際の手順です。
1. 名前付きステージにCSVファイルをアップロードする
2. ストアドプロシージャを実行し、CSVファイルのデータをテーブルにロードする
3. テーブルにデータがロードされていることを確認する
テーブル、CSVファイル
今回はテスト用に簡易的に下記のテーブルとCSVファイルを作成しました。
テーブル
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ファイル（サンプルデータ）
00001,A,10,1000
00002,B,20,8000
00003,C,5,25000
名前付きステージ
「INSTG」という名前の名前付きステージを作成します。
取込ファイルの中身に合わせてFILE_FORMAT等の指定が必要です。
CREATE OR REPLACE STAGE DAP_YAMAMOTO.INSTG_TEST.INSTG
--CSVファイル、ヘッダーなし
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1)
;
ストアドプロシージャ
名前付きステージにアップしたCSVファイルを、テーブルにロードするストアドプロシージャです。
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ファイルをドラッグアンドドロップで格納し、「アップロード」をクリックする。
2. ストアドプロシージャを実行し、名前付きステージのCSVファイルをテーブルにロードする
call文でストアドプロシージャを実行する。
問題がなければPROCESSに「処理終了」が表示される。
3. テーブルにデータがロードされていることを確認する
テーブルのデータプレビューから、CSVファイルのデータがロードされていることが確認できました！
感想・まとめ
ファイルからのデータロードは外部ステージを使用する機会が多いかと思いますが、今回は手元ファイルを随時でロードしたかったので名前付きステージを使用しました。
ストアドプロシージャを作るのに少し時間がかかりますが、ファイル名の作成日時を追加して最新ファイルを取得する...とかも作りこめば可能かと思います。
ご案内
株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/?utm_source=qiita&utm_medium=referral&utm_campaign=qiita_zeuscloud_content-area