2023-01-23

SnowflakeにおけるストアドプロシージャとUDFの比較

はじめに

ストアドプロシージャとUDFはデータベースプログラミングにおいて不可欠な要素であり、再利用可能なデータベースのロジックをカプセル化することで効率的なコーディング手法を促進します。しかし、それぞれ異なる目的と使用方法を持つため、これらの違いを理解することが効果的に機能を活用する鍵となります。

この記事では、SnowflakeにおけるストアドプロシージャとUDFの主な違い、それぞれの目的、適切な使用ケースについて解説します。また、それぞれの特徴的な動作や、特定のニーズに合った適切なツールの選択方法について説明します。

ストアドプロシージャの目的

データベースの世界では、ストアドプロシージャには特定の役割があります。通常、ストアドプロシージャはSQLステートメントを実行することによって管理操作を実行するために使用されます。ストアドプロシージャは、一連のSQLステートメントを1つのルーチンにカプセル化し、これらの操作が実行される必要があるときに呼び出すことができる手段を提供します。

ストアドプロシージャの本体は値を明示的に返すことが許可されていますが、必須ではありません。この値はエラー指示子、ステータスコード、または手続きが出力するデータポイントなど、手続きが設計されたデータを指すことができます。明示的に返す値が指定されていない場合、NULL値が暗黙的に返されます。戻り値の柔軟性により、ストアドプロシージャは単なる関数以上のものとなり、堅牢なデータベース管理システムの重要な一部となり、さまざまな管理機能と制御を提供します。

ユーザー定義関数（UDF）の目的

SnowflakeのUDFはストアドプロシージャとは別の役割を果たします。UDFの主な目的は値の計算と返却です。UDFの場合、戻り値はオプションではなく、明示的に値を指定する必要があります。これにより、UDFは計算やデータ変換などのタスクに価値を持つものとなります。

例えば、JavaScriptのUDFの場合、このUDFの本体には値を指定するreturnステートメントが必要です。これは計算結果、変換されたデータポイント、または関数の内部ロジックから派生した他の値などです。そのため、UDFは通常、SQLステートメント内のモジュール化された再利用可能なロジックとして機能し、データベースコードの効率性と保守性を向上させます。

ストアドプロシージャまたはUDFを作成するタイミング

ストアドプロシージャとUDFの目的を理解することで、それぞれを作成して使用するタイミングを判断する基盤が築けます。以下は一般的な推奨事項です。

ストアドプロシージャを作成する場合

次の場合にはストアドプロシージャを作成することを検討してください。

他のアプリケーションやシステムから既存のストアドプロシージャを移行する必要がある場合。手続き内のロジックを維持することは、移行プロセスを簡素化し機能の一貫性を確保するためによく行われます。
管理的なデータベース操作を実行する必要がある場合。これには、典型的なクエリやデータ操作言語（DML）タスク（SELECTやUPDATEなど）や、一時テーブルの削除、特定期間よりも古いデータの削除、ユーザーの追加などのデータ定義言語（DDL）操作が含まれます。

UDFを作成する場合

次の場合にはUDFを作成することを検討してください。

他のアプリケーションやシステムから既存のUDFを移行する必要がある場合。このアプローチにより、ロジックが関数にカプセル化され、元のアプリケーションのデザインの構造的な整合性が保たれます。
SQLステートメントの一部として呼び出すことができ、そのステートメントで使用する値を返す関数が必要な場合には、UDFの作成を検討してください。
出力には、入力の各行またはグループごとに値を含める必要があります。例えば、select MyFunction(col1) from table1;やselect MyAggregateFunction(col1) from table1 group by col2;のようなSQLステートメントでUDFを使用する場合があります。

使用方法と動作の違い

ストアドプロシージャとUDFにはいくつかの動作上の違いがあり、Snowflakeにおける異なるタスクに対して使用する際の適合性に影響を与えます。これらの違いには、戻り値、SQLでの使用方法、呼び出しの文脈、一度に呼び出せる数、データベースへのアクセスの能力などが含まれます。

UDFは値を返し、ストアドプロシージャは必須ではない

ストアドプロシージャと関数の両方が値を返すことができます。ただし、ストアドプロシージャの本体は値を明示的に返すことが許可されますが、必須ではありません（エラー指示子など）。

UDFの戻り値は直接SQLで使用可能、ストアドプロシージャの戻り値はそうではない

ストアドプロシージャが返す値は、関数が返す値とは異なり、直接SQLで使用することはできません。ストアドプロシージャが値を返しても、CALLコマンドの構文では返された値を保存する場所やそれに対して操作を行ったり、他の操作に渡したりする方法は提供されていません。ただし、ストアドプロシージャの戻り値を使用するための間接的な方法はあります。

UDFは他のステートメントの文脈で呼び出すことができ、ストアドプロシージャは独立して呼び出される

ストアドプロシージャは値に評価されず、一般的な式が使用できる全ての文脈で使用することはできません。例えば、SELECT my_stored_procedure()...というような実行はできません。一方、UDFは値に評価され、SELECT my_function() ...のような一般的な式が使用できる文脈で使用することができます。

1つの文で複数のUDFを呼び出せるが、1つのストアドプロシージャは1つの文で呼び出される

1つの実行可能な文は1つのストアドプロシージャのみを呼び出すことができます。一方、1つのSQLステートメントで複数の関数を呼び出すことができます。同様に、関数とは異なり、ストアドプロシージャは式の一部として呼び出すことはできません。ただし、ストアドプロシージャは別のストアドプロシージャを呼び出すことや、自己再帰的に呼び出すことができます。

UDFはデータベースにアクセスできないが、ストアドプロシージャは可能

ストアドプロシージャ内では、SELECT、UPDATE、CREATEなどのデータベース操作を実行することができます。一方、UDFはデータベース操作を実行するためのAPIへのアクセス権限を持っていません。この大きな違いにより、ストアドプロシージャはデータベースの管理や操作に強力なツールとなりますが、UDFは計算と値の返却というより特定の目的に特化した役割を果たします。