Snowflakeでストアドプロシージャを作成してみた

定型的な処理のストアドプロシージャ作るとすごい便利！

みなさん、ストアドプロシージャ使っていますか？私は最近その存在を知りました。
「ストアドプロシージャとは？」について、Snowflakeのドキュメントでは以下のように説明されています。

ストアドプロシージャには、記述したロジックが含まれているため、 SQL から呼び出すことができます。通常、ストアドプロシージャのロジックは、 SQL ステートメントを実行することによってデータベース操作を実行します。
〜〜〜〜
〜〜〜〜
複数の SQL ステートメントを必要とし、頻繁に実行されるタスクを自動化するために、ストアドプロシージャを使用することができます。たとえば、指定した日付より古いデータを削除して、データベースを「クリーンアップ」するとします。複数の DELETE ステートメントを記述できます。各ステートメントは特定のテーブルからデータを削除します。これらすべてのステートメントを単一のストアドプロシージャに入れて、カットオフ日を指定するパラメーターを渡すことができます。その後、プロシージャを呼び出すだけでデータベースをクリーンアップできます。データベースが変更されたら、プロシージャを更新して追加のテーブルをクリーンアップできます。クリーンアップコマンドを使用するユーザーが複数いる場合、すべてのテーブル名を覚えて各テーブルを個別にクリーンアップするのではなく、プロシージャ1つを呼び出すことができます。
https://docs.snowflake.com/ja/developer-guide/stored-procedure/stored-procedures-overview#what-is-a-stored-procedure

「複数の SQL ステートメントを必要とし、頻繁に実行されるタスク」をプロシージャ化すると良いということが何となく分かりました。
ストアドプロシージャと似ているものとして、ユーザー定義関数（UDF）があります。
Snowflakeのドキュメントでどちらをどのような時に利用するとよいかが詳しく書いてあるので、そちらを参照すると良いです。

作成してみた

今回はGPSログデータの解析を想定し、ログ間の移動距離、ログ間の時間、速度のカラムを追加するプロシージャの例を取り上げます。
３つのプロシージャを作成し、それらを合わせて呼び出します。

ログデータには、id, user_id, log_time, geom（ジオメトリ）のカラムがあります。

記述にはSnowflakeスクリプト（SQL）を利用しました。
書き方の詳細は公式ドキュメントをご参照ください。

ログ間の距離を計算する

まずはログ間の距離を計算していきます。
処理の流れとしては以下のとおりです。

1. 元のテーブルから距離を計算した一時テーブルを作成する
2. もとのテーブルに距離を格納するカラムを追加する
3. 一時テーブルの結果を利用して、もとのテーブルを更新する
4. 一時テーブルを削除する

プロシージャの引数として、元とするテーブルを渡します。

-- 移動距離の計算
CREATE OR REPLACE procedure calculate_distance(table_name VARCHAR)
RETURNS string
LANGUAGE SQL
AS
BEGIN
  -- 距離計算した一時テーブルの作成
  CREATE or replace temporary TABLE temp_distance AS
  select
      id,
      COALESCE( -- 前の行と現在の行でuser_idが異なる場合（初登場時）はdistance_mは-1とする
          ST_Distance(
              to_geography(ST_ASGEOJSON(geom)),
              to_geography(lag(ST_ASGEOJSON(geom)) OVER (PARTITION BY user_id ORDER BY log_time))
          ),
          -1
      ) as distance_m
  from IDENTIFIER(:table_name)
  ;

  -- 距離計算した一時テーブルを元のテーブルに反映
  ALTER TABLE IDENTIFIER(:table_name) ADD COLUMN distance_m FLOAT;

  UPDATE IDENTIFIER(:table_name) t
  SET distance_m = d.distance_m
  from temp_distance d
  where t.id = d.id
  ;

  DROP TABLE temp_distance;

  RETURN 'Calculated distance successfully';
END;
;

ログ間の時間を計算する

続いて、ログ間の時間を計算します。
こちらも、距離を計算するプロシージャと同様の処理の流れです。

-- 移動時間の計算
CREATE OR REPLACE procedure calculate_movetime(table_name VARCHAR)
RETURNS string
LANGUAGE SQL
AS
BEGIN
  -- 移動時間を計算した一時テーブルの作成
  CREATE or replace temporary TABLE temp_movetime AS
  select
      id,
      COALESCE( -- 前の行と現在の行でuser_idが異なる場合（初登場時）はmovetime_sは-1とする
          timediff('second', lag(log_time) OVER (PARTITION BY user_id ORDER BY id), log_time),
          -1
      ) as movetime_s
  from IDENTIFIER(:table_name)
  ;

  -- 計算後の移動時間を入れるカラムを追加
  ALTER TABLE IDENTIFIER(:table_name) ADD COLUMN movetime_s number;

  -- 一時テーブルを使ってUPDATE
  UPDATE IDENTIFIER(:table_name) t
  SET movetime_s = m.movetime_s
  from temp_movetime m
  where t.id = m.id;

  drop table temp_movetime;

  RETURN 'Calculated movetime successfully';
END;
;

速度を計算する

最後に、速度を計算します。
距離と時間のカラムをもとに計算して完了！

-- 移動速度の計算
CREATE OR REPLACE procedure calculate_speed(table_name VARCHAR)
RETURNS string
LANGUAGE SQL
AS
BEGIN
  ALTER TABLE IDENTIFIER(:table_name) ADD COLUMN speed_kph float;

  -- user_idが初登場時の速度は-1を入力する
  UPDATE IDENTIFIER(:table_name)
  SET speed_kph = -1 -- 前の行と現在の行でuser_idが異なる場合（初登場時）はspeed_kphは-1とする
  where
      distance_m = -1
  ;

  UPDATE IDENTIFIER(:table_name)
  SET speed_kph = distance_m / movetime_s * 3.6 -- x 3.6 は m/sをkm/hに換算
  where
      distance_m != -1
  ;

  RETURN 'Calculated movetime successfully';
END;
;

３つのプロシージャを呼び出す

1つのプロシージャに全てのプロセスを記述しても問題は無いと思いますが、処理をわかりやすくするために今回は３つに分けています。
それぞれ単体で呼び出しても良いですが、それらをまとめて呼び出すプロシージャを設定します。
このプロシージャでは、ソーステーブルと、アウトプットテーブルを引数として受け取ります。

処理の流れとしては、以下の通りです。

1. create_temp_target_tableにソーステーブルがわたり、結果出力先のテーブルを新規に作成する。（他、もろもろの処理）
2. 上記で作成した結果出力先テーブルをもとに、calculate_distanceで距離を計算しカラム追加
3. `calculate_movetime'に上記で更新されたテーブルがわたり、時間を計算
4. 最後に'calculate_speed'で速度を計算
5. 完了の文字列が返ってくる

※ 以下のプロシージャでは、もとのテーブルを更新せずに新規のテーブルを作成するようにしています。
※ create_temp_target_tableというプロシージャを別途作成し適用しています。

-- 呼び出しプロシージャの定義
CREATE OR REPLACE procedure speed_analysis(source_table_name VARCHAR, result_table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  -- 各プロシージャを呼び出し
  call create_temp_target_table(:source_table_name, :result_table_name);
  call calculate_distance(:result_table_name);
  call calculate_movetime(:result_table_name);
  call calculate_speed(:result_table_name);

  RETURN 'Speed analysis completed successfully';
END;
;

プロシージャを実際に呼び出す

呼び出し方は上記のプロシージャでも記述している　callで利用できます。
以下のように呼び出すことで、sampleスキーマのgps_log_sourceテーブルを元にして、距離・時間・速度カラムが追加された新たなテーブル、gps_log_resultが作成されます。

call speed_analysis('sample.gps_log_source', 'sample.gps_log_result');

プロシージャを利用することで繰り返しの処理が簡単になることに加えて、意図しないミスが減る、処理がわかりやすくなるといった効果があると思います。

UDFも含めて積極的に活用していきたいと思います！