Snowflakeのセッション変数を使う

Snowflakeでセッション変数を設定しながら、SQLを発行する。 それによりSQLのパラメーターを動的に変更する。
2021.07.08

データアナリティクス事業本部、池田です。
Snowflakeセッション変数(SQL変数)を使っていろいろ試す機会があったので、 ブログにします。
公式のドキュメントは↓
SQL 変数

セッション変数関連のクエリ

こんな感じで使えます。

  • 変数の設定
    SET {変数名} = '{値}';
    SET ({変数名1}, {変数名2}, {変数名3}) = ('{値1}', '{値2}', '{値3}');
  • 変数の確認
    SHOW VARIABLES;
  • 変数の削除
    UNSET {変数名};

呼び出す場合は $VAR_1 のようなかたちで「$」を使います

名前の通りセッション内でのみ有効です。 以前のブログでワークシート上の変数がいつ削除されるのか調べています。

Snowflakeのワークシートでのトランザクションと変数の挙動まとめ(小ネタ)

また、今回調べた限り、セッション外で宣言/初期化しておける変数は無さそうです。
(私は、変数と パラメーター と混同してしまっていたのですが、パラメーターはアカウント/ユーザー/セッションごとに設定ができます。 例えば、ユーザー単位でタイムゾーン( TIMEZONE )パラメーターのデフォルト値を変えておいて、 更にクエリの発行時にセッション内で再度タイムゾーンパラメーターを上書きする、 ということも可能です。変数ではそういったことができなそうです。

セッション変数を呼び出して使う

このように、SQLのパラメータに使うこともできます。

SET PREFIX_REGEX = (SELECT 'snowflake/input/' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') || '/.*'); -- 変数の初期化

COPY INTO DEVIO_SAMPLE_TBL -- (例)作成済みステージ&テーブルを使ったロード
FROM @DEVIO_EXT_STAGE_S3
PATTERN = $PREFIX_REGEX -- 変数を使用
;

(1行目はSELECT文にしなくても、結合と関数だけでも使用できます。)

5行目の PATTERN で関数を直接使用できない( PATTERN = TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') のような書き方ができない)ので、 こういうことがしたい場合は変数を使うことになりそうです。


また、テーブル名などに使う場合は、 IDENTIFIER()TABLE() 関数を噛ませて 識別子 として認識させる必要があります。 以前のブログ で利用していました。例↓

-- ファイル形式名・テーブル名・ビュー名
SET (format_name, table_name, view_name) = ('{ファイル形式名}', '{テーブル名}', '{ビュー名}');

-- …(略)…

-- ビュー
CREATE VIEW IDENTIFIER($view_name)
AS
    SELECT
        V:id_str::VARCHAR AS "ID_STR",
        V:str::VARCHAR AS "STR",
        V:date::TIMESTAMP_NTZ AS "DATE",
        V:int::INT AS "INT",
        V:str_ja::VARCHAR AS "STR_JA"
    FROM TABLE($table_name)
    WHERE
        V:import_datetime = (
            SELECT MAX(V:import_datetime)
            FROM TABLE($table_name)
        )
;

タスクで変数は使えるの…?

実は私が最終的にやりたかったことは↓のように タスク と組み合わせての利用です。

-- タスク(作成できない)
CREATE TASK DEVIO_DAILY_COPY_TASK
    WAREHOUSE = DEVIO_WH
    SCHEDULE = 'USING CRON 0 1 * * * Asia/Tokyo'
AS
    SET PREFIX_REGEX = (SELECT 'snowflake/input/' || TO_VARCHAR(CURRENT_DATE(), 'YYYYMMDD') || '/.*');
    COPY INTO PUBLIC.DEVIO_SAMPLE_TBL
    FROM @DEVIO_EXT_STAGE_S3
    PATTERN = $PREFIX_REGEX;
;

↑このSQLはエラーになります。

現在、タスクは、ストアドプロシージャの呼び出しを含む単一の SQL ステートメントを実行できます。

とのことなので、AS句の中に複数SQLを定義することはできないようです。
また、前述の通りセッション外(タスクの外)から変数を渡す方法も無さそうなので、 こういうことがしたい場合は、 ストアドプロシージャ を使う必要がありそうです。 (タスク上ではストアドプロシージャをCALLするだけ。)
その辺は今度ブログにします。たぶん。

おわりに

便利ですね。 各ドライバーでの接続時にも使えるようなので活用の幅は広そうです。

関連情報/参考にさせていただいたページ