スカラー SQL UDFs

このトピックでは、SQL UDFs （ユーザー定義関数）に固有の概念と使用方法の詳細について説明します。

一般的な使用法

SQL UDF は、任意の SQL 式を評価し、式の結果を返します。

関数定義は、スカラー（単一）値、またはテーブル関数として定義されている場合は行のセットを返す SQL 式です。円の面積を計算するスカラー UDF の基本的な例を次に示します。

CREATE FUNCTION area_of_circle(radius FLOAT)
  RETURNS FLOAT
  AS
  $$
    pi() * radius * radius
  $$
  ;

SELECT area_of_circle(1.0);

出力：

SELECT area_of_circle(1.0);
+---------------------+
| AREA_OF_CIRCLE(1.0) |
|---------------------|
|         3.141592654 |
+---------------------+

式はクエリ式（SELECT 式）にすることができます。例：

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;

SQL UDFでクエリ式を使用する場合、クエリ式を終了するために UDF 本文内にセミコロンを含めないでください。

1つのクエリ式のみを含めることができます。式には UNION [ALL]を含めることができます。

注釈

UDF の本文には完全な SELECTステートメントを含めることができますが、 DDL ステートメントや SELECT以外の DML ステートメントを含めることはできません。

注釈

スカラー関数（UDFs）には、入力引数に500個の制限があります。

メモ化可能 UDFs

スカラー SQL UDF はメモ化可能です。メモ化可能な関数は、スカラー SQL UDF を呼び出した結果をキャッシュし、後で出力が必要になったときにキャッシュされた結果を返します。メモ化可能な関数を使用する利点は、行アクセスポリシーまたはマスキングポリシー内で参照される マッピングテーブル での複数列の検索など、複雑なクエリのパフォーマンスを向上させることです。

ポリシー所有者（たとえば、行アクセスポリシーに対する OWNERSHIP 権限を持つロール）は、ポリシー条件を更新して、マッピングテーブルのあるサブクエリをメモ化可能な関数に置き換えることができます。ユーザーが後でクエリでポリシーで保護された列を参照すると、メモ化可能な関数からキャッシュされた結果を必要に応じて使用できるようになります。

注釈

メモ化可能な関数を使用するには、:ref:`label-use_cached_result`セッションパラメーターをTRUEに設定する必要があります。

メモ化可能な関数を作成する

MEMOIZABLE キーワードを指定することにより、 CREATE FUNCTION ステートメントでスカラー SQL UDF をメモ化可能に定義できます。引数をもつ、または持たない関数に対してメモ化可能を作成できます。引数を使うことで、より自由に SQL UDF を定義することができます。メモ化可能な関数を呼び出すポリシーを記述する場合、ポリシーをどのように定義するかについて、より自由があります。

引数を指定する場合、引数は以下のデータ型のいずれかの定数値である必要があります。

  • VARCHAR とその他の文字列データ型。

  • NUMBER とその他の数値データ型。

  • TIMESTAMP とその他の日付データ型。

  • BOOLEAN

半構造化データ型 やテーブル列など、定数でない値やそのデータ型はサポートされていません。

メモ化可能な関数を記述する場合は、

  • BOOLEAN やその他のスカラデータ型を result_data_type として指定します。

    キャッシュサイズには制限があるので、 ARRAY を result_data_type に指定する場合は注意が必要です。

  • OBJECT や VARIANT のような他のデータ型は指定しないでください。

  • 他のメモ化可能関数は一切参照しないでください。

メモ化可能な関数を呼び出す

メモ化可能な関数は、SELECT ステートメントで呼び出すか、ポリシー定義に含めることができます。これにより、ポリシー条件に基づいてメモ化可能な関数が呼び出されます。

メモ化可能な関数を呼び出すときは、次の点に注意してください。

  • ARRAY データ型を返すか、非スカラー値を指定する SQL UDFs の場合、メモ化可能な関数を ARRAY_CONTAINS 関数の引数として使用します。

  • キャッシュサイズの制限:

    メモ化可能な各関数は、現在のSnowflakeセッションに対して10 KB に制限されています。

    メモ化可能な関数が結果セットのキャッシュのこの制限を超える場合、Snowflakeはメモ化可能な関数を呼び出した結果をキャッシュしません。代わりに、関数の記述方法に基づいて、UDF は通常のスカラー UDF として機能します。

  • キャッシュの使用:

    メモ化可能な関数には、クエリ環境とコンテキストが変更 されない 場合、さまざまな SQL ステートメントに対して再利用可能な結果キャッシュがあります。通常、これは結果キャッシュがさまざまな SQL ステートメントに適用されることを意味します。

    • クエリで参照されるオブジェクトと列に対するアクセス制御の認証はそのままです。

    • クエリで参照されるオブジェクトは変更されません（例: DML ステートメント経由）。

    Account Usage QUERY_HISTORY ビューの CHILD_QUERIES_WAIT_TIME 列には、メモ化可能な関数を呼び出したときにキャッシュされたルックアップを完了するまでの時間（ミリ秒単位）が記録されます。

  • 次の場合は、メモ化可能な関数がキャッシュされた結果を再利用しません。

    • 関数がテーブルまたは他のオブジェクトを参照し、参照されるテーブルに更新がある。

    • テーブルへのアクセス制御に変更がある。

    • 関数が非決定性の関数を呼び出す。

    • 関数が外部関数または SQL UDF ではない UDF を呼び出す。

例

基本的な SQL スカラー UDF の例

この例では、数学定数piのハードコードされた近似を返します。

CREATE FUNCTION pi_udf()
  RETURNS FLOAT
  AS '3.141592654::FLOAT'
  ;

SELECT pi_udf();

出力：

SELECT pi_udf();
+-------------+
|    PI_UDF() |
|-------------|
| 3.141592654 |
+-------------+

一般的な SQL の例

SELECT ステートメントを使用した式のクエリ

使用するテーブルとデータを作成します。

CREATE TABLE purchases (number_sold INTEGER, wholesale_price NUMBER(7,2), retail_price NUMBER(7,2));
INSERT INTO purchases (number_sold, wholesale_price, retail_price) VALUES 
   (3,  10.00,  20.00),
   (5, 100.00, 200.00)
   ;

UDF を作成します。

CREATE FUNCTION profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
    SELECT SUM((retail_price - wholesale_price) * number_sold)
        FROM purchases
  $$
  ;

クエリで UDF を呼び出します。

SELECT profit();

出力：

SELECT profit();
+----------+
| PROFIT() |
|----------|
|   530.00 |
+----------+

WITH 句の UDF

CREATE TABLE circles (diameter FLOAT);

INSERT INTO circles (diameter) VALUES
    (2.0),
    (4.0);

CREATE FUNCTION diameter_to_radius(f FLOAT) 
  RETURNS FLOAT
  AS 
  $$ f / 2 $$
  ;

WITH
    radii AS (SELECT diameter_to_radius(diameter) AS radius FROM circles)
  SELECT radius FROM radii
    ORDER BY radius
  ;

出力：

+--------+
| RADIUS |
|--------|
|      1 |
|      2 |
+--------+

JOIN 操作

この例では、 JOIN 操作を含むより複雑なクエリを使用します。

使用するテーブルとデータを作成します。

CREATE TABLE orders (product_ID varchar, quantity integer, price numeric(11, 2), buyer_info varchar);
CREATE TABLE inventory (product_ID varchar, quantity integer, price numeric(11, 2), vendor_info varchar);
INSERT INTO inventory (product_ID, quantity, price, vendor_info) VALUES 
  ('X24 Bicycle', 4, 1000.00, 'HelloVelo'),
  ('GreenStar Helmet', 8, 50.00, 'MellowVelo'),
  ('SoundFX', 5, 20.00, 'Annoying FX Corporation');
INSERT INTO orders (product_id, quantity, price, buyer_info) VALUES 
  ('X24 Bicycle', 1, 1500.00, 'Jennifer Juniper'),
  ('GreenStar Helmet', 1, 75.00, 'Donovan Liege'),
  ('GreenStar Helmet', 1, 75.00, 'Montgomery Python');

UDF を作成します。

CREATE FUNCTION store_profit()
  RETURNS NUMERIC(11, 2)
  AS
  $$
  SELECT SUM( (o.price - i.price) * o.quantity) 
    FROM orders AS o, inventory AS i 
    WHERE o.product_id = i.product_id
  $$
  ;

クエリで UDF を呼び出します。

SELECT store_profit();

出力：

SELECT store_profit();
+----------------+
| STORE_PROFIT() |
|----------------|
|         550.00 |
+----------------+

トピック CREATE FUNCTION には追加の例が含まれています。

さまざまな句で UDFs を使用する

スカラー UDF は、スカラー式を使用できる任意の場所で使用できます。例：

-- ----- These examples show a UDF called from different clauses ----- --

select MyFunc(column1) from table1;

select * from table1 where column2 > MyFunc(column1);

UDF での SQL 変数の使用

この例は、SQL 変数を設定し、 UDF内でその変数を使用する方法を示しています。

SET id_threshold = (SELECT COUNT(*)/2 FROM table1);

CREATE OR REPLACE FUNCTION my_filter_function()
RETURNS TABLE (id int)
AS
$$
SELECT id FROM table1 WHERE id > $id_threshold
$$
;

メモ化可能な関数

例については、次をご参照ください。