Zenn
❄️

Snowflake EXECUTE JOB SERVICE の USING 句 で変数を扱いたい

に公開
Snowflake
tech

概要

Snowflake には、 登録したコンテナイメージをベースに処理を実行できる EXECUTE JOB SERVICE というSQLが用意されています（AWS でいう、 ECS タスクのイメージです）。

EXECUTE JOB SERVICE は、 USING 句を用いることで、コンテナランタイムの設定を動的に変更できます。

この機能により、以下のように、柔軟なコンテナ運用が可能となります。

  • 環境変数を動的に変更する
  • コンテナイメージのラベルを動的に変更する
  • など

この USING 句は文字列リテラルでは動作しますが、変数を指定するとエラーになります。

このエラーの回避方法をまとめました。

USING 句による動的な設定について

EXECUTE JOB SERVICE では、 specification と呼ばれる yaml データを使用して、実行するコンテナに詳細な設定を追加します。

この specification ファイルには、変数を設定できます。

your_specification_file.yaml
spec:
  container:
  - name: your_container_name
    image: your_image_name
    ...
    env:
      SOME_ID: {{ SOME_ID }} // 変数 'some_id' を設定
    ...

変数に渡す値は、 EXECUTE JOB SERVICE 実行時の USING 句で設定します。

  EXECUTE JOB SERVICE your_service_name
  ...
  FROM @your_stage SPECIFICATION_TEMPLATE_FILE = 'your_specification_template_file.yaml' -- '使用する specification ファイルを指定'
  USING ( SOME_ID => '01234_56789' ); -- ★ SOME_ID に '01234_56789' をセット

これにより、 コンテナランタイムの環境変数を、EXECUTE JOB SERVICE 実行時に指定する ... といった使い方が可能になります。

USING 記述方法の詳細は、以下公式ドキュメントをご確認ください。

USING 句に変数を設定するとエラーが発生する

USING 句に、変数を設定したいケースを考えます。

SQL等、何らかの方法でID情報を取得し、これを USING 句に渡してみます。

EXECUTE IMMEDIATE
$$
DECLARE
  some_id varchar;
BEGIN
  some_id := ...; -- SQLなどでID情報を取得
  EXECUTE JOB SERVICE your_service_name
  ...
  FROM @your_stage SPECIFICATION_TEMPLATE_FILE = "your_specification_template_file.yaml"
  USING ( SOME_ID => :some_id ); -- エラー
END;
$$
;

USING 句のバリュー側で変数を指定すると、エラーが発生します。

公式ドキュメントには、文字列リテラルを指定する方法のみ紹介されています。

変数を設定する方法については紹介されておらず、サポートされていない可能性があります。

対応方法

変数を展開しつつ、SQLを文字列として定義します。
その後、定義したSQL文字列を、 EXECUTE IMMEDIATE 句へ渡して、SQLとして実行します。

EXECUTE IMMEDIATE
$$
DECLARE
  some_id varchar;
BEGIN
  some_id := ...; -- SQLなどでID情報を取得
  LET sql_text := '
  EXECUTE JOB SERVICE your_service_name
  ...
  FROM @your_stage SPECIFICATION_TEMPLATE_FILE = "your_specification_template_file.yaml"
  USING ( SOME_ID => ''' || :some_id || ''' );
  '
  EXECUTE IMMEDIATE sql_text;
END;
$$
;

その他 活用方法

EXECUTE JOB SERVICE をストアドプロシージャとして定義し、 USING 句に渡したいパラメータを、ストアドプロシージャの引数経由で渡す...といった活用が考えられます。

create or replace your_procedure(
  some_id: varchar
)
LANGUAGE SQL
AS
$$
  LET sql_text := '
  EXECUTE JOB SERVICE your_service_name
  ...
  USING ( SOME_ID => ''' || :some_id || ''' );
  '
  EXECUTE IMMEDIATE sql_text;
$$;

call your_procedure(some_id);

参考文献

Discussion