ストアドプロシージャとは

ストアドプロシージャは開発者によって記述されたロジックをカプセル化し、SQL内で繰り返し呼び出すことができるようにします。ストアドプロシージャに含まれるロジックは、SQLステートメントの実行から動的にSQLステートメントの作成と実行まで、さまざまなデータベース操作を行うことができます。

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

単純なストアドプロシージャであるmyprocという名前のストアドプロシージャと、runというPythonハンドラを持つ例を見てみます。このプロシージャは、データを1つのテーブルから別のテーブルに移動するという主な機能を持ち、転送する行数に制限を設定します。

このストアドプロシージャを作成するためのSQLコードは次のとおりです。

create or replace procedure myproc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  packages = ('snowflake-snowpark-python')
  handler = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

ストアドプロシージャが作成されたら、CALLコマンドを使用してプロシージャを呼び出すことができます。

CALL myproc('table_a', 'table_b', 5);

これにより、table_aから5行のデータがtable_bに転送されます。

サポートされる言語

ストアドプロシージャはいくつかのプログラミング言語で記述することができます。言語の選択は、タスクの具体的なニーズと要件、開発者の熟練度、言語内の利用可能なライブラリや機能に依存します。

Snowflakeでは、次の言語でストアドプロシージャのハンドラ（ロジック）を記述することができます。

• Java（Snowpark APIを使用）
• JavaScript
• Python（Snowpark APIを使用）
• Scala（Snowpark APIを使用）
• Snowflake Scripting（SQL）

それぞれの言語は、言語とランタイム環境に制約のある範囲内でデータ操作が可能です。ハンドラ言語を選択する際には、特定の使用ケース、環境、および開発者の熟練度に応じて適切な選択を行うことが重要です。

ストアドプロシージャの作成

以下の手順でストアドプロシージャを作成します。

1. ハンドラコードを作成
   これはプロシージャが呼び出されたときに実行されるコードです。ストアドプロシージャが作成されるための特定のデータベース操作を処理するように設計されている必要があります。

2. ハンドラコードの場所を決定
   ハンドラコードは、CREATE PROCEDURE SQLステートメント内でインラインで保持するか、またはステージ上で参照することができます。ステージは名前付きのファイルスペースであり、テーブルステージ、ユーザーステージ、またはSnowflakeステージとして存在することができます。

3. CREATE PROCEDUREステートメントを実行
   このステートメントはSQLで実行され、プロシージャのプロパティを指定します。

以下に例を示します。

create or replace procedure myProc(fromTable STRING, toTable STRING, count INT)
returns string
language java
runtime_version = '11'
packages = ('com.snowflake:snowpark:latest')
handler = 'MyClass.myMethod'
AS
$$
  import com.snowflake.snowpark_java.*;

  public class MyClass
  {
    public String myMethod(Session session, String fromTable, String toTable, int count)
    {
      session.table(fromTable).limit(count).write().saveAsTable(toTable);
      return "Success";
    }
  }
$$;

この例では、myProcという名前のストアドプロシージャが、インラインのハンドラMyClass.myMethodを持つストアドプロシージャとして作成されています。ハンドラ言語はJavaであり、ハンドラにはSnowparkライブラリから必要なSessionオブジェクトが必要です。SnowparkライブラリはPACKAGES句で指定されています。

プロシージャを作成する際には、通常以下のプロパティが使用されます。

• プロシージャ名
  これはハンドラの名前と一致する必要はありません。CREATE PROCEDUREステートメントはプロシージャ名をハンドラと関連付けます。

• プロシージャ引数
  ランタイム時にプロシージャの引数値はハンドラの引数にバインドされます。引数のバインドは、ハンドラの言語に応じて名前の一致または引数の位置に基づいて行われる場合があります。

• 戻り値の型
  RETURNS句で指定されます。

• ハンドラ名
  HANDLER句で指定されます。必要な場合は、プロシージャが呼び出されたときに実行されるコードを含むクラスまたはメソッドの名前です。

• ハンドラに必要な依存関係
  IMPORTS句またはPACKAGES句を使用して指定されます。

• ハンドラ言語のランタイム
  RUNTIME_VERSION句で指定されます。

インラインハンドラ

次の例では、インラインのPythonハンドラ関数runを持つmy_procという名前のプロシージャが作成されます。

create or replace procedure my_proc(from_table string, to_table string, count int)
  returns string
  language python
  runtime_version = '3.8'
  packages = ('snowflake-snowpark-python')
  handler = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

ステージドハンドラ

次の例では、ステージ上のJavaハンドラメソッドMyClass.myMethodを持つmy_procという名前のプロシージャが作成されます。PACKAGES句を使用してSnowparkライブラリを参照し、IMPORTS句を使用してハンドラコードが含まれるステージ上のJARファイルを参照しています。

create or replace procedure my_proc(fromTable string, toTable string, count int)
  returns string
  language java
  runtime_version = '11'
  packages = ('com.snowflake:snowpark:latest')
  imports = ('@mystage/myjar.jar')
  handler = 'MyClass.myMethod';

ストアドプロシージャの呼び出し

ストアドプロシージャを作成したら、次のステップはそれを呼び出して利用することです。

ストアドプロシージャを呼び出すには、CALLコマンドを使用します。基本的な構文は次のとおりです。

CALL procedure_name(argument1, argument2, ...);

ストアドプロシージャの呼び出しに対する特権

ストアドプロシージャを呼び出すためには、ユーザーのロールにストアドプロシージャのUSAGE特権が必要です。USAGE特権はロールがストアドプロシージャを実行することを許可しますが、管理することはできません。

引数の指定

ストアドプロシージャに引数がある場合、引数を名前または位置で指定することができます。

例えば、次のような3つの引数を受け取るストアドプロシージャを考えてみます。

CREATE OR REPLACE PROCEDURE sp_concatenate_strings(
    first VARCHAR,
    second VARCHAR,
    third VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN first || second || third;
  END;
  $$;

プロシージャを呼び出す際には、引数を名前で指定できます。

CALL sp_concatenate_strings(
  first => 'one',
  second => 'two',
  third => 'three');

引数を名前で指定する場合、引数の順序を特定する必要はありません。

CALL sp_concatenate_strings(
  third => 'three',
  first => 'one',
  second => 'two');

引数を位置で指定することもできます。

CALL sp_concatenate_strings(
  'one',
  'two',
  'three');

参考

https://docs.snowflake.com/en/sql-reference/stored-procedures-overview
https://docs.snowflake.com/en/sql-reference/stored-procedures-creating-sql
https://docs.snowflake.com/en/sql-reference/stored-procedures-calling