まえがき
本記事はSnowflake Advent Calendar 2020の24日目の記事となります。
今回は、Snowflakeのユーザ側で定義できるFunctionやProcedureについて紹介いたします。
少々細かい部分かと思いますが、DB側で関数を利用する際はアンチパターンの場合もあったり使い所がなかなか難しいと感じた方も多いのではないでしょうか？
この記事がユーザ定義関数を利用する上で良い判断材料や何か新しい運用アイディアにつながればと思います。
SnowflakeのUDFs, UDTFs, UDPsについて
先ずは、色々なパターンがあるので一旦表にまとめてみました。
| 種類 |
言語 |
引数 |
戻り値 |
補足 |
| UDFs |
JavaScript |
1以上 |
スカラー |
クエリが発行できない |
| SQL |
1以上 |
スカラー |
スクリプトライクな利用はできない |
| UDTFs |
JavaScript |
1以上 |
テーブル |
クエリが発行できない |
| SQL |
1以上 |
テーブル |
スクリプトライクな利用はできない |
| UDPs |
JavaScript |
0以上 |
不可（なし） |
ステートメント扱い, SQLを発行できる |
| SQL |
0以上 |
なし（なし） |
ステートメント扱い |
※UDFs = User Defined Functions, UDTFs = User Defined Table Functions, UDPs = User Defined Procedures
上記の表を参考にJavaScriptにフォーカスをして利用していきたいと思います。
因みにStored ProcedureのことをUDPsとあえて記載しておりますが、一般的ではないですのでご留意下さい。
また、公式ドキュメントは下記となります。
Functionについて
Functionは、基本的にSQLのステートメントに組み込む形で利用されます。
例として下記のようになります。
返り値がスカラーの場合は、SELECT句やWHERE句などに利用されます。
また、返り値がテーブルの場合は、FROM句でTABLE()にキャストしてから利用されます。
Procedureについて
Proceduresは、MySQLや他のクエリ言語と同じですが一つのステートメントとして実行されます。
例として下記のようになります。
ユースケースのご紹介
今回は、Procedureを利用してマスタテーブルを引数を使ってセレクトした結果を再利用するといったユースケースをご紹介します。
コードに記載の通り、大まかな流れとしては下記のようになります。
- テーブル作成
- プロシージャ作成
- プロシージャの呼び出し
- プロシージャが実行した結果を読み込みパースしてテーブルとして表示
上記のコードでは、引数に応じてスクリプトライクに条件分岐ができ、かつクエリの実行と結果の利用もできます。
今回は、RESULT_SCAN(LAST_QUERY_ID())を利用していますが、より運用面を意識するならば、TMPテーブルをCREATEして利用した後にDROPするようにすれば活用の場面が増えると考えています。
あとがき
いかがでしたでしょうか。
今回は、細かい部分でしたがある場面ではかなり実用性のあるユーザ定義関数及びプロシージャを紹介させて頂きました。
1点注意としては、2020/12/23 現在Snowflakeのブラウザコンソール上でGUIとして、関数が管理できない部分があります。
データベースやスキーマを明示しないまま意図しない場所に作成された場合は、
show user functions in account;
などを利用して確認することができます。
今後利用する予定がある場合は、ユースケースにマッチするかどうかで関数を活用していきたいと私自身も気をつけたいです。
以上「SnowflakeのProcedureとFunctionをJavascriptベースで試してみた」でした。
