CREATE FUNCTION (SQL および Python)

[アーティクル]
04/18/2024

適用対象: 「はい」のチェックマーク Databricks SQL Databricks Runtime

一連の引数を受け取り、スカラー値または行のセットを返す SQL スカラーまたはテーブルの関数を作成します。

適用対象: check marked yes Databricks SQL Databricks Runtime 13.3 LTS 以降

一連の引数を受け取り、スカラー値を返す Python スカラー関数を作成します。

Python UDF には、サーバーレスまたは pro SQL ウェアハウスの Unity Catalog、もしくは共有または単一の Unity Catalog クラスターが必要です。

適用対象: check marked yes Databricks SQL Databricks Runtime 14.1 以降

位置指定パラメーター呼び出しに加えて、名前付きパラメーター呼び出し使用して SQL および Python UDF を呼び出すこともできます。

構文

CREATE [OR REPLACE] [TEMPORARY] FUNCTION [IF NOT EXISTS]
    function_name ( [ function_parameter [, ...] ] )
    { [ RETURNS data_type ] |
      RETURNS TABLE [ ( column_spec [, ...]) ] }
    [ characteristic [...] ]
    { AS dollar_quoted_string | RETURN { expression | query } }

function_parameter
    parameter_name data_type [DEFAULT default_expression] [COMMENT parameter_comment]

column_spec
    column_name data_type [COMMENT column_comment]

characteristic
  { LANGUAGE { SQL | PYTHON } |
    [NOT] DETERMINISTIC |
    COMMENT function_comment |
    [CONTAINS SQL | READS SQL DATA] }

パラメーター

OR REPLACE

指定されている場合、同じ名前とシグネチャ (パラメーターの数とパラメーターの型) を持つ関数が置き換えられます。既存の関数を別のシグネチャに置き換えることはできません。これは主に、関数本体と関数の戻り値の型を更新する場合に便利です。 IF NOT EXISTS と一緒にこのパラメーターを指定することはできません。
TEMPORARY

作成される関数のスコープ。 TEMPORARY を指定すると、作成された関数は、現在のセッションで有効になり表示されます。永続的なエントリはカタログに作成されません。
IF NOT EXISTS

指定した場合、関数は存在しない場合にのみ作成されます。指定された関数がシステムに既に存在する場合、関数の作成は成功します (エラーはスローされません)。 OR REPLACE と一緒にこのパラメーターを指定することはできません。
function_name

関数の名前。永続関数の場合は、必要に応じて関数名をスキーマ名で修飾できます。名前が修飾されていない場合、永続関数は現在のスキーマに作成されます。
function_parameter

関数のパラメーターを指定します。
- parameter_name
  
  パラメーター名は、関数内で一意である必要があります。
- data_type
  
  サポートされるすべてのデータ型。 Python の場合、data_type はこの言語マッピングに従って Python データ型にキャストされます。
- DEFAULT default_expression
  
  適用対象: Databricks SQL Databricks Runtime 10.4 LTS 以降
  
  関数呼び出しでパラメーターに引数が割り当てられない場合に使用される省略可能な既定値。 default_expression は data_type にキャスト可能でなければなりません。この式は、別のパラメーターを参照したり、サブクエリを含めたりすることはできません。
  
  あるパラメーターに既定値を指定すると、以降のすべてのパラメーターにも既定値が必要になります。
  
  DEFAULT は LANGUAGE SQL でのみサポートされています。
- COMMENT コメント
  
  パラメーターの説明 (省略可能)。 comment は、STRING リテラルを指定する必要があります。
RETURNS data_type

スカラー関数の戻り値のデータ型。 Python UDF の場合、戻り値は data_type で指定されたデータ型と完全に一致する必要があります。それ以外の場合、予期しない型変換を防ぐために関数は失敗します。

SQL UDF の場合、この句は省略可能です。データ型が指定されていない場合、それは関数本体から派生します。
RETURNS TABLE [ (column_spec [,…] ) ]

この句は、関数をテーブル関数としてマークします。テーブル関数の結果のシグネチャをオプションで指定することもできます。 column_spec が指定されていない場合、それは SQL UDF 本体から派生します。

RETURNS TABLE は LANGUAGE SQL でのみサポートされています。
- column_name
  
  列名は、シグネチャ内で一意である必要があります。
- data_type
  
  サポートされるすべてのデータ型。
- COMMENT column_comment
  
  列の説明 (省略可能)。 comment は、STRING リテラルを指定する必要があります。
RETURN { expression | query }

関数の本体。スカラー関数の場合は、クエリまたは式にすることができます。テーブル関数の場合は、クエリのみ可能です。式に以下を含めることはできません。
- 集計関数
- ウィンドウ関数
- 順位付け関数
- explode などの行生成関数
関数の本体内では、パラメーターをその非修飾名で、またはそのパラメーターを関数名で修飾して参照できます。
AS dollar_quoted_definition

dollar_quoted_definition は、一致する 2 つの $[tag]$body$[tag]$ で囲まれた Python 関数 body です。 tag には空の文字列を指定できます。

次に例を示します。
```
$$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$
```
characteristic

characteristic 句はすべて省略可能です。これらは任意の数を任意の順序で指定できますが、各句は 1 回しか指定できません。
- LANGUAGE SQL または LANGUAGE PYTHON
  
  関数実装の言語。
- [NOT] DETERMINISTIC
  
  関数が決定論的かどうか。関数は、指定された引数のセットに対して 1 つの結果のみを返す場合に、決定論的です。関数の本体が DETERMINISTIC でなくても、その関数はそのようにマークできます。また、その逆も可能です。その理由としては、定数のたたみ込みやクエリキャッシュなどのクエリ最適化を奨励したり抑制したりすることが考えられます。このオプションを指定しない場合、それは関数本体から派生します。
- COMMENT function_comment
  
  関数のコメント。 function_comment は文字列リテラルである必要があります。
- CONTAINS SQL または READS SQL DATA
  
  関数がテーブルまたはビューから直接的または間接的にデータを読み取るかどうか。関数が SQL データを読み取る場合は、CONTAINS SQL を指定できません。どちらの句も指定しない場合、プロパティは関数本体から派生します。

Python UDF でサポートされているライブラリ

依存関係を使用するには、関数本体内で import <package> を使用します。たとえば、次のように入力します。

CREATE FUNCTION […]
AS $$
   import json
   [... (rest of function definition)]
$$

依存関係は、標準 Python ライブラリと次のライブラリに制限されています。

Package	Version
bleach	4.0.0
chardet	4.0.0
charset-normalizer	2.0.4
defusedxml	0.7.1
googleapis-common-protos	1.56.4
grpcio	1.47.0
grpcio-status	1.47.0
jmespath	0.10.0
joblib	1.1.0
numpy	1.20.3
パッケージング	21.3
pandas	1.3.4
patsy	0.5.2
protobuf	4.21.5
pyarrow	7.0.0
pyparsing	3.0.9
python-dateutil	2.8.2
pytz	2021.3
scikit-learn	0.24.2”
scipy	1.7.1”
setuptools	65.2.0
6	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
cryptography	38.0.4

SQL スカラー関数を作成して使用する

> CREATE VIEW t(c1, c2) AS VALUES (0, 1), (1, 2);

-- Create a temporary function with no parameter.
> CREATE TEMPORARY FUNCTION hello() RETURNS STRING RETURN 'Hello World!';

> SELECT hello();
  Hello World!

-- Create a permanent function with parameters.
> CREATE FUNCTION area(x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;

-- Use a SQL function in the SELECT clause of a query.
> SELECT area(c1, c2) AS area FROM t;
 0.0
 2.0

-- Use a SQL function in the WHERE clause of a query.
> SELECT * FROM t WHERE area(c1, c2) > 0;
 1  2

-- Compose SQL functions.
> CREATE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN area(x, x);

> SELECT c1, square(c1) AS square FROM t;
  0  0.0
  1  1.0

-- Create a non-deterministic function
> CREATE FUNCTION roll_dice()
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a single 6 sided die'
    RETURN (rand() * 6)::INT + 1;
-- Roll a single 6-sided die
> SELECT roll_dice();
 3

DEFAULT を使用する関数を作成して使用する

-- Extend the function to support variable number of sides and dice.
-- Use defaults to support a variable number of arguments
> DROP FUNCTION roll_dice;
> CREATE FUNCTION roll_dice(num_dice  INT DEFAULT 1 COMMENT 'number of dice to roll (Default: 1)',
                            num_sides INT DEFAULT 6 COMMENT 'number of sides per die (Default: 6)')
    RETURNS INT
    NOT DETERMINISTIC
    CONTAINS SQL
    COMMENT 'Roll a number of n-sided dice'
    RETURN aggregate(sequence(1, roll_dice.num_dice, 1),
                     0,
                     (acc, x) -> (rand() * roll_dice.num_sides)::int,
                     acc -> acc + roll_dice.num_dice);

-- Roll a single 6-sided die still works
> SELECT roll_dice();
 3

-- Roll 3 6-sided dice
> SELECT roll_dice(3);
 15

-- Roll 3 10-sided dice
> SELECT roll_dice(3, 10)
 21

-- Roll 3 10-sided dice using named parameter invocation
> SELECT roll_dice(10 => num_sides, num_dice => 3)
 17

-- Create a SQL function with a scalar subquery.
> CREATE VIEW scores(player, score) AS VALUES (0, 1), (0, 2), (1, 2), (1, 5);

> CREATE FUNCTION avg_score(p INT) RETURNS FLOAT
    COMMENT 'get an average score of the player'
    RETURN SELECT AVG(score) FROM scores WHERE player = p;

> SELECT c1, avg_score(c1) FROM t;
 0  1.5
 1  3.5

SQL テーブル関数を作成する

-- Produce all weekdays between two dates
> CREATE FUNCTION weekdays(start DATE, end DATE)
    RETURNS TABLE(day_of_week STRING, day DATE)
    RETURN SELECT extract(DAYOFWEEK_ISO FROM day), day
             FROM (SELECT sequence(weekdays.start, weekdays.end)) AS T(days)
                  LATERAL VIEW explode(days) AS day
             WHERE extract(DAYOFWEEK_ISO FROM day) BETWEEN 1 AND 5;

-- Return all weekdays
> SELECT weekdays.day_of_week, day
    FROM weekdays(DATE'2022-01-01', DATE'2022-01-14');
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07
  1     2022-01-10
  2     2022-01-11
  3     2022-01-12
  4     2022-01-13
  5     2022-01-14

-- Return weekdays for date ranges originating from a LATERAL correlation
> SELECT weekdays.*
    FROM VALUES (DATE'2020-01-01'),
                (DATE'2021-01-01'),
                (DATE'2022-01-01') AS starts(start),
         LATERAL weekdays(start, start + INTERVAL '7' DAYS);
  3     2020-01-01
  4     2020-01-02
  5     2020-01-03
  1     2020-01-06
  2     2020-01-07
  3     2020-01-08
  5     2021-01-01
  1     2021-01-04
  2     2021-01-05
  3     2021-01-06
  4     2021-01-07
  5     2021-01-08
  1     2022-01-03
  2     2022-01-04
  3     2022-01-05
  4     2022-01-06
  5     2022-01-07

SQL 関数を置換する

-- Replace a SQL scalar function.
> CREATE OR REPLACE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN x * x;

-- Replace a SQL table function.
> CREATE OR REPLACE FUNCTION getemps(deptno INT)
    RETURNS TABLE (name STRING)
    RETURN SELECT name FROM employee e WHERE e.deptno = getemps.deptno;

-- Describe a SQL table function.
> DESCRIBE FUNCTION getemps;
 Function: default.getemps
 Type:     TABLE
 Input:    deptno INT
 Returns:  id   INT
           name STRING

注意

既存の関数を別のシグネチャに置き換えることはできません。

SQL 関数を記述する

> DESCRIBE FUNCTION hello;
 Function: hello
 Type:     SCALAR
 Input:    ()
 Returns:  STRING

> DESCRIBE FUNCTION area;
 Function: default.area
 Type:     SCALAR
 Input:    x DOUBLE
           y DOUBLE
 Returns:  DOUBLE

> DESCRIBE FUNCTION roll_dice;
 Function: default.roll_dice
 Type:     SCALAR
 Input:    num_dice  INT
           num_sides INT
 Returns:  INT

> DESCRIBE FUNCTION EXTENDED roll_dice;
 Function:      default.roll_dice
 Type:          SCALAR
 Input:         num_dice  INT DEFAULT 1 'number of dice to roll (Default: 1)'
                num_sides INT DEFAULT 6 'number of sides per dice (Default: 6)'
 Returns:       INT
 Comment:       Roll a number of m-sided dice
 Deterministic: false
 Data Access:   CONTAINS SQL
 Configs:       ...
 Owner:         the.house@always.wins
 Create Time:   Sat Feb 12 09:29:02 PST 2022
 Body:          aggregate(sequence(1, roll_dice.num_dice, 1),
                      0,
                      (acc, x) -> (rand() * roll_dice.num_sides)::int,
                      acc -> acc + roll_dice.num_dice)

Python 関数を作成する

—- Hello World-like functionality using Python UDFs
> CREATE FUNCTION main.default.greet(s STRING)
  RETURNS STRING
  LANGUAGE PYTHON
  AS $$
    def greet(name):
      return "Hello " + name + "!"

    return greet(s) if s else None
  $$

—- Can import functions from std library and environment
> CREATE FUNCTION main.default.isleapyear(year INT)
  RETURNS BOOLEAN
  LANGUAGE PYTHON
  AS $$
    import calendar
    return calendar.isleap(year) if year else None
  $$

—- Must return the correct type. Otherwise will fail at runtime.
> CREATE FUNCTION main.default.a_number()
  RETURNS INTEGER
  LANGUAGE PYTHON
  AS $$
    # does not work: return "10"
    # does not work: return 3.14
    return 10
  $$

—- Deal with exceptions.
> CREATE FUNCTION main.default.custom_divide(n1 INT, n2 INT)
  RETURNS FLOAT
  LANGUAGE PYTHON
  AS $$
    try:
      return n1/n2
    except ZeroDivisionException:
    # in case of 0, we can return NULL.
    return None
  $$