CREATE FÜGGVÉNY (SQL és Python)

Cikk
03/01/2024

A következőkre vonatkozik: check marked yes Databricks SQL Databricks Runtime 9.1 vagy újabb

Létrehoz egy SQL skaláris vagy táblafüggvényt, amely argumentumokat használ, és skaláris értéket vagy sorkészletet ad vissza.

A következőkre vonatkozik: check marked yes Databricks SQL Databricks Runtime 13.2 vagy újabb

Létrehoz egy Python skaláris függvényt, amely argumentumokat használ, és skaláris értéket ad vissza.

A Python UDF-ekhez a Unity Catalog kiszolgáló nélküli vagy pro SQL-tárolókon, illetve megosztott vagy egyfelhasználós Unity Catalog-fürtökön szükséges.

A következőkre vonatkozik: check marked yes Databricks SQL Databricks Runtime 14.1 vagy újabb

A pozícióparaméter meghívása mellett meghívhatja az SQL-t és a Python UDF-et is nevesített paraméterhívással .

Syntax

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

VAGY CSERE

Ha meg van adva, az azonos nevű és aláírású függvény (paraméterek és paramétertípusok száma) lecserélődik. Meglévő függvényt nem cserélhet másik aláírásra. Ez elsősorban a függvény törzsének és a függvény visszatérési típusának frissítéséhez hasznos. Ezt a paramétert nem adhatja meg a következővel IF NOT EXISTS: .
IDEIGLENES

A létrehozott függvény hatóköre. Ha megadja TEMPORARY, a létrehozott függvény érvényes és látható az aktuális munkamenetben. A katalógusban nem történik állandó bejegyzés.
HA NEM LÉTEZIK

Ha meg van adva, csak akkor hozza létre a függvényt, ha nem létezik. A függvény létrehozása sikeres (hiba nem jelenik meg), ha a megadott függvény már létezik a rendszerben. Ezt a paramétert nem adhatja meg a következővel OR REPLACE: .
function_name

A függvény neve. Állandó függvények esetén igény szerint sémanévvel is minősítheti a függvény nevét. Ha a név nincs minősítve, az állandó függvény az aktuális sémában jön létre.
function_parameter

A függvény paraméterét adja meg.
- parameter_name
  
  A paraméter nevének egyedinek kell lennie a függvényen belül.
- data_type
  
  Bármilyen támogatott adattípus. A Python data_type esetében a nyelvleképezésnek megfelelően a Python-adattípusba kerül.
- ALAPÉRTELMEZETT default_expression
  
  A következőkre vonatkozik: Databricks SQL Databricks Runtime 10.4 vagy újabb
  
  Nem kötelező alapértelmezett érték, amelyet akkor kell használni, ha egy függvényhívás nem rendel argumentumot a paraméterhez. default_expressiona paraméternek a .-ra kell öntöttnekdata_typelennie A kifejezés nem hivatkozhat másik paraméterre, és nem tartalmazhat al lekérdezést.
  
  Ha egy paraméterhez alapértelmezett értéket ad meg, az alábbi paramétereknek is alapértelmezettnek kell lenniük.
  
  DEFAULT csak a LANGUAGE SQL támogatottak.
- MEGJEGYZÉS megjegyzés
  
  A paraméter opcionális leírása. comment literálnak kell lennie STRING .
VISSZATÉRÉSI DATA_TYPE

A skaláris függvény visszatérési adattípusa. Python UDF-ek esetén a visszaadott értékeknek pontosan meg kell egyeznie a megadott adattípussal data_type. Ellenkező esetben a nem várt típuskonverziók megakadályozása érdekében a függvény sikertelen lesz.

Az SQL UDF esetében ez a záradék nem kötelező. Ha nincs megadva, az adattípus a függvény törzséből lesz származtatva.
VISSZAADJA A TÁBLÁT [ (column_spec [,...] ) ]

Ez a záradék táblafüggvényként jelöli meg a függvényt. Opcionálisan a táblafüggvény eredményének aláírását is megadja. Ha nincs megadva column_spec, az az SQL UDF törzséből lesz származtatva.

RETURNS TABLE csak a LANGUAGE SQL támogatottak.
- column_name
  
  Az oszlop nevének egyedinek kell lennie az aláíráson belül.
- data_type
  
  Bármilyen támogatott adattípus.
- MEGJEGYZÉS column_comment
  
  Az oszlop opcionális leírása. comment literálnak kell lennie STRING .
RETURN { kifejezés | lekérdezés }

A függvény törzse. A skaláris függvények lehetnek lekérdezések vagy kifejezések. Táblafüggvények esetében ez csak lekérdezés lehet. A kifejezés nem tartalmazhat:
- Függvények összesítése
- Ablakfüggvények
- Rangsorolási függvények
- Sorgyártó függvények, például robbantás
A függvény törzsében a paramétert a nem minősített név alapján, vagy a paraméter a függvény nevével való minősítésével hivatkozhat.
AS dollar_quoted_definition

dollar_quoted_definition Az a Python-függvény body , amelyet két egyező függvény $[tag]$body$[tag]$zár be. tag lehet üres sztring.

Examples:
```
$$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$
```
Jellemző

Az összes jellemző záradék nem kötelező. Tetszőleges számú záradékot tetszőleges sorrendben megadhatja, de mindegyik záradékot csak egyszer adhatja meg.
- LANGUAGE SQL vagy LANGUAGE PYTHON
  
  A függvény implementálásának nyelve.
- [NEM] DETERMINISZTIKUS
  
  Hogy a függvény determinisztikus-e. A függvény determinisztikus, ha egy adott argumentumkészlethez csak egy eredményt ad vissza. Megjelölhet egy függvényt, mint DETERMINISTIC amikor a teste nem, és fordítva. Ennek egyik oka lehet, hogy ösztönzi vagy visszatartja a lekérdezésoptimalizálást, például az állandó összecsukást vagy a lekérdezés gyorsítótárazását. Ha nem adja meg a ths beállítást, az a függvény törzséből származik.
- MEGJEGYZÉS function_comment
  
  Megjegyzés a függvényhez. function_comment Sztringkonstansnak kell lennie.
- SQL- VAGY READS SQL-ADATOKAT TARTALMAZ
  
  Azt jelzi, hogy egy függvény közvetlenül vagy közvetve olvas be adatokat egy táblából vagy nézetből. Amikor a függvény beolvassa az SQL-adatokat, nem adhatja meg CONTAINS SQL. Ha egyik záradékot sem adja meg, a tulajdonság a függvény törzséből származik.

Támogatott kódtárak a Python UDF-ekben

A függőségek használatához használja import <package> a függvény törzsében. Lásd például a következőket:

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

A függőségek a standard Python-kódtárra és a következő kódtárakra korlátozódnak:

Csomag	Verzió
Fehérítő	4.0.0
karakterkészlet	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
Csomagolás	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
Hat	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
felhasználói ügynökök	2.2.0
Titkosítás	38.0.4

Példák

SQL skaláris függvény létrehozása és használata
DEFAULT-t használó függvény létrehozása és használata
SQL-táblafüggvény létrehozása
SQL-függvény cseréje
SQL-függvény leírása
Python-függvények létrehozása

SQL skaláris függvény létrehozása és használata

> 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-t használó függvény létrehozása és használata

-- 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-táblafüggvény létrehozása

-- 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-függvény cseréje

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

Megjegyzés:

Meglévő függvényt nem cserélhet másik aláírásra.

SQL-függvény leírása

> 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-függvények létrehozása

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