Snowflakeでセッション変数を使ってみた

分析屋

2023年12月27日 10:02

分析屋の中田(ナカタ)です。
Snowflakeでセッション変数を使ったストアドプロシージャを作成してみました。

セッション変数とは

Snowflakeのセッションが有効な間、使用できる変数です。
SQLワークシートをまたいで使うことはできませんが、ウェアハウスをまたいでの使用は可能です。

今回やること

ストアドプロシージャを作成します。
ストアド内部では、引数をセッション変数に渡します。

環境

Snowflakeのエディション：エンタープライズ版
クラウド：AWS（東京リージョン）

事前準備

ストアドプロシージャ内で使用するセッション変数を先に初期化しておきます。
以下のクエリを実行します。

SET (_BEGIN_DATE, _DAYS) = ('1991-03-16' ::DATE, 32);

上記の1文で、「_BEGIN_DATE」「_DAYS」の2つのセッション変数が作成されました。
右辺の値には特に意味はありません。初期化できればいいので、何を入れてもOKです。

CREATE PROCEDURE文でストアドプロシージャを作成する予定なのですが
先に上記を実行しておかないと以下の画像のように怒られます。

ストアドプロシージャのDDL

いきなりですが完成品のストアドDDLを貼ります。

CREATE OR REPLACE PROCEDURE TRAIN.KNAKATA.CREATE_TABLE("BEGIN_DATE" VARCHAR, "DAYS" INTEGER)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS $$
BEGIN
    SET (_BEGIN_DATE, _DAYS) = (:BEGIN_DATE ::DATE, :DAYS);
    
    CREATE OR REPLACE TABLE TRAIN.KNAKATA.NEW_YEAR AS 
    SELECT DATEADD(DAY, SEQ4(), $_BEGIN_DATE) AS YMD
    FROM TABLE(GENERATOR(ROWCOUNT => $_DAYS));
END;
$$
;

上記を実行するとストアドプロシージャCREATE_TABLEが作成されます。
"TRAIN.KNAKATA"の部分はご自身の環境に合わせてデータベース名・スキーマ名を指定してください。
作成したストアドプロシージャを実行すると、第１引数を１日目として第２引数の日数分の連続した日付のテーブルNEW_YEARが作成されます。

何を書いてるんだかピンと来にくいのでストアドのCALL文と実行結果を見てみましょう。

CALL TRAIN.KNAKATA.CREATE_TABLE('2024-01-01',5);

↓実行結果

ストアドに戻り値を設定していないので、このような表示になります。

SELECT文で作成されたテーブルNEW_YEARを確認します。

SELECT * FROM TRAIN.KNAKATA.NEW_YEAR;

↓実行結果

第1引数（2024-01-01）を1日目として、第2引数（5）の日数分のテーブルができています。

DDL解説

ここからはストアドプロシージャの中身を分解して解説します。

CREATE OR REPLACE PROCEDURE TRAIN.KNAKATA.CREATE_TABLE("BEGIN_DATE" VARCHAR, "DAYS" INTEGER)

CREATE_TABLEという名前でストアドプロシージャを作成しています。
第１引数はBEGIN_DATEという名前でVARCHAR型にしています。
第２引数はDAYSという名前でINTEGER型にしています。

RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER

上から順に

戻り値はSTRING型（戻り値自体を設定してないけど）
ストアド内部の言語はSQL
実行権限は呼び出し元

としています。
最後のEXECUTE AS CALLERについては省略した場合
EXECUTE AS OWNER（実行権限は所有者）となります。
この場合、セッション変数へのアクセスができなくなります。

AS $$
BEGIN
    SET (_BEGIN_DATE, _DAYS) = (:BEGIN_DATE ::DATE, :DAYS);
    
    CREATE OR REPLACE TABLE TRAIN.KNAKATA.NEW_YEAR AS 
    SELECT DATEADD(DAY, SEQ4(), $_BEGIN_DATE) AS YMD
    FROM TABLE(GENERATOR(ROWCOUNT => $_DAYS));
END;
$$
;

ストアドプロシージャ内のロジック部分です。
$$で囲むことで、特殊文字列を含めて見たまんまの文字列にしてくれます。

まずはSETでセッション変数２つの初期化をしています。
引数の前にはコロン（：）を書く必要があります。
引数BEGIN_DATEではいったん文字列リテラルとしていた日付を、セッション変数_BEGIN_DATEに渡す際にDATE型にCASTしています。

以降はCREATE TABLE～AS SELECT～文（いわゆるCTAS）です。
ここではセッション変数を使用して連続する日付を作成しています。
セッション変数の値を取得する場合は、セッション変数名の前に$マークを書きます。

連番生成の関数部分は本題と外れるので省略します。
Snowflake独自の書き方で、通常はWITH RECURSIVEで再帰クエリにて実装します。

最後に

幾度となくコンパイルエラーになりましたがどうにかストアドプロシージャのテンプレート的なものができました。
動的なSQL文を生成する際に役立てば幸いです。

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

これまでの記事はこちら！

株式会社分析屋について

弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。

ホームページはこちら。

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！

【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。

【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。

【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。

【SES】
SESサービスも行っております。