CREATE FUNCTION （SQL 和 Python）

發行項
04/18/2024

適用於： 檢查標示為是 Databricks SQL Databricks Runtime

建立採用一組自變數並傳回純量值或一組數據列的SQL純量或數據表函式。

適用於： 檢查標示為是 Databricks SQL Databricks Runtime 13.3 LTS 和更新版本

建立 Python 純量函式，以接受一組自變數並傳回純量值。

Python UDF 需要無伺服器或 Pro SQL 倉儲上的 Unity 目錄，或共用或單一使用者 Unity 目錄叢集。

適用於： 檢查標示為是 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] }

參數

或 REPLACE

如果指定，則會取代具有相同名稱和簽章的函式（參數和參數類型的數目）。您無法以不同的簽章取代現有的函式。這主要用於更新函式主體和函式的傳回型別。您無法使用 IF NOT EXISTS指定此參數。
TEMPORARY

正在建立之函式的範圍。當您指定 TEMPORARY時，所建立的函式有效且顯示在目前的會話中。目錄中不會進行持續性專案。
如果不存在

如果指定，則只會在函式不存在時建立函式。如果指定的函式已存在於系統中，則函式的建立會成功（不會擲回錯誤）。您無法使用 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 必須是 STRING 常值。
傳回data_type

純量函式的傳回數據類型。針對 Python UDF，傳回值必須完全符合中所 data_type指定的數據類型。否則，若要防止非預期的型別轉換，函式將會失敗。

對於 SQL UDF，這個子句是選擇性的。如果未提供數據類型，則會從函式主體衍生。
RETURNS TABLE [ （column_spec [,...] ] ] ]

這個子句會將函式標示為數據表函式。您也可以選擇性地指定數據表函式結果的簽章。如果未指定任何column_spec，則會衍生自 SQL UDF 主體。

RETURNS TABLE 只支援 LANGUAGE SQL 。
- column_name
  
  數據行名稱在簽章內必須是唯一的。
- data_type
  
  任何支援的數據類型。
- 批注column_comment
  
  數據行的選擇性描述。 comment 必須是 STRING 常值。
RETURN { expression | query }

函式的主體。如果是純量函式，它可以是查詢或表達式。針對數據表函式，它只能是查詢。運算式不能包含：
- 彙總函數
- 視窗函式
- 次序函數
- 產生數據列的函式，例如爆炸
在函式的主體內，您可以透過參數的不限定名稱或將參數限定為函式名稱來參考參數。
AS dollar_quoted_definition

dollar_quoted_definition是以兩個相符 $[tag]$body$[tag]$的括住的 Python 函body式。 tag 可以是空字串。

範例：
```
$$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$
```
特徵

所有特性子句都是選擇性的。您可以依任何順序指定任意數目，但只能指定每個子句一次。
- LANGUAGE SQL 或 LANGUAGE PYTHON
  
  函式實作的語言。
- [NOT]確定性
  
  函式是否具決定性。當函式只針對一組指定的自變數傳回一個結果時，函式會具決定性。當函式主體不是時，您可以將函式標示為 DETERMINISTIC ，反之亦然。原因可能是鼓勵或勸阻查詢優化，例如常數折疊或查詢快取。如果您未指定選項，則會衍生自函式主體。
- 批注function_comment
  
  函式的批注。 function_comment 必須是 String 常值。
- CONTAINS SQL 或 READS SQL DATA
  
  函式會直接或間接地從數據表或檢視表讀取數據。當函式讀取 SQL 資料時，您無法指定 CONTAINS SQL。如果您未指定任一子句，屬性會衍生自函式主體。

Python UDF 中支持的連結庫

若要使用任何相依性，請在函式主體內使用 import <package> 。例如，請參閱下列內容：

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

相依性僅限於標準 Python 連結庫和下列連結庫：

套件	版本
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
六次	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
密碼編譯	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
  $$

共用方式為

CREATE FUNCTION （SQL 和 Python）

語法

參數

Python UDF 中支持的連結庫

範例

建立和使用 SQL 純量函式

建立並使用使用 DEFAULT 的函式

建立 SQL 數據表函式

取代 SQL 函式

描述 SQL 函式

建立 Python 函式

其他資源

共用方式為

CREATE FUNCTION （SQL 和 Python）

語法

參數

Python UDF 中支持的連結庫

範例

建立和使用 SQL 純量函式

建立並使用使用 DEFAULT 的函式

建立 SQL 數據表函式

取代 SQL 函式

描述 SQL 函式

建立 Python 函式

相關文章

其他資源