CREATE FUNCTION (SQL i Python)

Dotyczy: Databricks SQL Databricks Runtime

Tworzy skalar SQL lub funkcję table, która przyjmuje set argumentów i zwraca wartość skalarną lub set wierszy.

Dotyczy: Databricks SQL Databricks Runtime 13.3 LTS i nowsze

Tworzy funkcję skalarną języka Python, która przyjmuje set argumentów i zwraca wartość skalarną.

Funkcje zdefiniowane przez użytkownika w Pythonie (UDF) wymagają Unity Catalog na magazynach bezserwerowych lub pro SQL Warehouse albo klastra Unity Catalog współużytkowanego lub z pojedynczym użytkownikiem.

Dotyczy: Databricks SQL Databricks Runtime 14.1 i nowsze

Oprócz wywołania parametru pozycyjnego można również wywołać funkcję UDF SQL i Python przy użyciu wywołania nazwanego parametru.

Składnia

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] }

Parameters

• LUB ZASTĄP

  Funkcja o tej samej nazwie i podpisie (liczba parameters i typów parametrów) zostanie zastąpiona, jeśli zostanie określona. Nie można zastąpić istniejącej funkcji innym podpisem. Jest to głównie przydatne do update treści funkcji i zwracanego typu funkcji. Nie można określić tego parametru za pomocą IF NOT EXISTSpolecenia .

• TYMCZASOWY

  Zakres tworzonej funkcji. Po określeniu TEMPORARYparametru utworzona funkcja jest prawidłowa i widoczna w bieżącej sesji. W catalognie jest wprowadzany żaden trwały wpis .

• JEŚLI NIE ISTNIEJE

  Jeśli zostanie określony, tworzy funkcję tylko wtedy, gdy nie istnieje. Utworzenie funkcji powiedzie się (nie zostanie zgłoszony błąd), jeśli określona funkcja już istnieje w systemie. Nie można określić tego parametru za pomocą OR REPLACEpolecenia .

• function_name

  Nazwa funkcji. W przypadku funkcji trwałej można opcjonalnie qualify nazwać funkcję nazwą schema. Jeśli nazwa nie jest kwalifikowana, funkcja trwała tworzy się w bieżącym schema.

• function_parameter

  Określa parametr funkcji.

  • parameter_name

    Nazwa parametru musi być unikatowa w ramach funkcji.

  • data_type

    Dowolny obsługiwany typ danych. W przypadku języka Python data_type jest rzutowane na typ danych języka Python zgodnie z tym mapowaniem języka.

  • DEFAULT_EXPRESSION DOMYŚLNE

    Dotyczy: Databricks SQL Databricks Runtime 10.4 LTS i nowsze

    Opcjonalna wartość domyślna, która ma być używana, gdy wywołanie funkcji nie przypisuje argumentu do parametru. default_expression musi być rzutowalny na data_type. Wyrażenie nie może odwoływać się do innego parametru lub zawierać podzapytania.

    Po określeniu wartości domyślnej dla jednego parametru wszystkie poniższe parameters również muszą mieć wartość domyślną.

    DEFAULT program jest obsługiwany tylko w przypadku LANGUAGE SQL programu .

  • Komentarz komentarza

    Opcjonalny opis parametru. comment musi być literałem STRING .

• ZWRACA data_type

  Zwracany typ danych funkcji skalarnej. W przypadku funkcji Pythona zdefiniowanych przez użytkownika, zwracana wartość values musi dokładnie odpowiadać typowi danych określonemu w data_type. W przeciwnym razie, aby zapobiec nieprzewidzianym konwersjom typów, funkcja zakończy się niepowodzeniem.

  W przypadku funkcji zdefiniowanej przez użytkownika SQL ta klauzula jest opcjonalna. Typ danych będzie pochodzić z treści funkcji, jeśli nie zostanie podany.

• ZWRACA TABLE [ (column_spec [,...] ) ]

  Klauzula ta przypisuje funkcji oznaczenie table. Opcjonalnie określa również sygnaturę rezultatu funkcji table. Jeśli nie określono column_spec, zostanie on uzyskany z treści funkcji zdefiniowanej przez użytkownika SQL.

  RETURNS TABLE program jest obsługiwany tylko w przypadku LANGUAGE SQL programu .

  • column_name

    Nazwa column musi być unikatowa w podpisie.

  • data_type

    Dowolny obsługiwany typ danych.

  • COLUMN_COMMENT KOMENTARZ

    Opcjonalny opis column. comment musi być literałem STRING .

• RETURN { | }

  Treść funkcji. W przypadku funkcji skalarnej może to być zapytanie lub wyrażenie. W przypadku funkcji table może to być tylko zapytanie. Wyrażenie nie może zawierać:

  • Agregujących
  • Window funkcje
  • Funkcje klasyfikowania
  • Funkcje tworzenia wierszy, takie jak eksplodowanie

  W treści funkcji można odwołać się do parametru za pomocą jego niekwalifikowanej nazwy lub kwalifikując parametr z nazwą funkcji.

• AS dollar_quoted_definition

  dollar_quoted_definition to funkcja body języka Python ujęta w dwie pasujące $[tag]$body$[tag]$wartości . tag może być pustym ciągiem.

  Przykłady:

  $$
    return “Hello world”
  $$
  
  $py$
    return "Hello World"
  $py$
  

• charakterystyczny

  Wszystkie klauzule charakterystyczne są opcjonalne. Można określić dowolną liczbę z nich w dowolnej kolejności, ale można określić każdą klauzulę tylko raz.

  • JĘZYK SQL lub JĘZYK PYTHON

    Język implementacji funkcji.

  • [NIE] DETERMINISTYCZNY

    Czy funkcja jest deterministyczna. Funkcja jest deterministyczna, gdy zwraca tylko jeden wynik dla danego set argumentów. Możesz oznaczyć funkcję tak, jak DETERMINISTIC wtedy, gdy jej treść nie jest i na odwrót. Przyczyną może być zachęcanie lub zniechęcanie do optymalizacji zapytań, takich jak stałe składanie lub buforowanie zapytań. Jeśli nie określisz opcji ths, pochodzi ona z treści funkcji.

  • FUNCTION_COMMENT KOMENTARZ

    Komentarz dla funkcji. function_comment musi być literałem ciągu.

  • ZAWIERA DANE SQL lub READS SQL

    Czy funkcja odczytuje dane bezpośrednio czy pośrednio z table lub widoku. Gdy funkcja odczytuje dane SQL, nie można określić elementu CONTAINS SQL. Jeśli nie określisz żadnej klauzuli, właściwość pochodzi z treści funkcji.

Obsługiwane biblioteki w funkcjach zdefiniowanych przez użytkownika języka Python

Aby użyć dowolnych zależności, użyj jej import <package> w treści funkcji. Zobacz na przykład następujące kwestie:

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

Zależności są ograniczone do standardowej biblioteki języka Python i następujących bibliotek:

Przykłady

• Tworzenie i używanie funkcji skalarnej SQL
• Tworzenie i używanie funkcji korzystającej z funkcji DEFAULTs
• Tworzenie funkcji table SQL
• Zastępowanie funkcji SQL
• Opisywanie funkcji SQL
• Tworzenie funkcji języka Python

Tworzenie i używanie funkcji skalarnej 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

Tworzenie i używanie funkcji korzystającej z funkcji DEFAULTs

-- 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

Utwórz funkcję SQL table

-- 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

Zastępowanie funkcji 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

Opisywanie funkcji 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)

Tworzenie funkcji języka 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
  $$

Powiązane artykuły

• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE