CREATE FUNCTION (SQL és Python)

A következőkre vonatkozik: Databricks SQL Databricks Runtime

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: Databricks SQL Databricks Runtime 13.3 LTS és ú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 vagy Unity Catalog-kompatibilis számítási erőforráson szükséges.

A következőkre vonatkozik: 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.

A következőre vonatkozik: Databricks SQL Databricks Runtime 16.2 és újabb

A ENVIRONMENT záradék használatával adja meg a Python környezetet a LANGUAGE PYTHON-el deklarált függvényhez. Egyéni függőségek telepítéséhez és a környezeti verzió beállításához használható.

Szintaxis

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] |
    DEFAULT COLLATION default_collation_name } |
    environment }

environment
  ENVIRONMENT ( { environment_key = environment_value } [, ...] )

Paraméterek

• 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. A meglévő függvényt nem lehet helyettesíteni más szignatúrával vagy eljárással. 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.

  A függvény nevének egyedinek kell lennie a séma összes rutinja (eljárása és függvénye) esetében.

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

  • adat_típus

    Bármilyen támogatott adattípus. Python esetében a data_type egy Python-adattípusra van konvertálva a nyelvi leképezés szerint.

    STRING data_type esetében az alapértelmezett rendezés a default_collation_name függvény.

  • ALAPÉRTELMEZETT default_expression

    A következőkre vonatkozik: Databricks SQL Databricks Runtime 10.4 LTS és ú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_expression paraméternek rendelhetőnek kell lennie típussá data_type. 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 támogatott csak a LANGUAGE SQL esetén.

  • Megjegyzés megjegyzés

    A paraméter opcionális leírása. comment-nak/nak STRING literálnak kell lennie.

• Visszatérési adat_típus

  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 data_typemegadott adattípussal. 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.

• VISSZATÉR TABLE [ (column_spec [,...] ) ]

  Ez a kifejezés táblafüggvényként definiálja a függvényt. Opcionálisan megadhatja a táblafüggvény eredményének szignatúráját is. Ha nincs megadva column_spec, az az SQL UDF törzséből lesz származtatva.

  RETURNS TABLE támogatott csak a LANGUAGE SQL esetén.

  • column_name

    Az oszlop nevének egyedinek kell lennie az aláíráson belül.

  • adat_típus

    Bármilyen támogatott adattípus.

  • KOMMENTÁR column_comment

    Az oszlop opcionális leírása. comment-nak/nak STRING literálnak kell lennie.

• 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ény esetében csak egy 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 explode

  A függvény törzsében hivatkozhat a paraméterre annak minősítés nélküli nevével, vagy minősítve azt a függvény nevével.

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

  Példák:

  $$
    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 DETERMINISTIC-ként, ha a teste nem az, és fordítva is megteheti. Ennek egyik oka az lehet, hogy ösztönzi vagy visszatartja a lekérdezésoptimalizálást, például a konstans összevonást vagy a lekérdezés gyorsítótárazását. Ha nem adja meg ezt a beállítást, az a függvény törzséből lesz származtatva.

  • MEGJEGYZÉS function_comment

    Megjegyzés a függvényhez. function_comment Sztringkonstansnak kell lennie.

  • TARTALMAZ SQL vagy OLVASSA AZ SQL ADATOKAT

    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.

  • ALAPÉRTELMEZETT BETŰREND default_collation_name

    A következőre vonatkozik:ellenőrizze, hogy a Databricks Runtime 17.0-s vagy újabb verziója

    A következőhöz használandó alapértelmezett rendezést határozza meg:

    • STRING a függvény paraméterei, RETURNS adattípusa és RETURNS TABLE mezői.
    • DEFAULT kifejezés.
    • Az SQL-függvény törzse.

    Ha nincs megadva, az alapértelmezett rendezés UTF8_BINARY.

• környezet

  A LANGUAGE PYTHON-val deklarált függvény Python környezetét adja meg. A ENVIRONMENT záradék az SQL-függvények esetében nem támogatott.

  • Függőségek

    Sztringek JSON-tömbje, amely megadja a függvényhez szükséges Python-csomagokat vagy kerékfájlokat. A dependencies kulcs nem tesz különbséget kis- és nagybetűk között. Támogatott formátumok:

    • PyPI-csomag opcionális verzióval, például: "simplejson==3.19.3"
    • Egy Unity Catalog kötetben tárolt whl fájl abszolút elérési útja, például: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
    • HTTPS URL-cím egy külső tárolóban lévő wheel fájlhoz, például: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."

  • environment_version

    A Python-környezet verzióját meghatározó sztring. Az alapértelmezett Python-környezet használatára használható None . Ha nincs megadva, a rendszer az alapértelmezett környezetet használja.

    • Jelenleg csak az érték None támogatott.

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)]
$$

Alapértelmezés szerint 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
karakterkészlet-normalizáló	2.0.4
defusedxml	0.7.1
googleapis-közös-protokollok	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
Pandák	1.3.4
Patsy	0.5.2
protobuf	4.21.5
pyarrow	7.0.0
pyparsing (egy Python könyvtár a szövegelemzéshez)	3.0.9
python-dateutil	2.8.2
pytz (egy Python könyvtár az időzóna számításokhoz)	2021. március
scikit-learn (egy Python gépi tanulási könyvtár)	0.24.2”
scipy (tudományos és műszaki számítási Python könyvtár)	1.7.1”
setuptools	65.2.0
Hat	1.16.0
threadpoolctl	3.1.0
web kódolások	0.5.1
felhasználói ügynökök	2.2.0
kriptográfia	38.0.4

Egyéni függőségek a Python UDF-ekben

Ha további függőségeket szeretne használni a standard kódtáron és a támogatott beépített csomagokon kívül, adja meg őket a ENVIRONMENT záradékban.

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
• Egyéni függőségek definiálása Python-függvényekben

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;
 1.0
 1.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

Feljegyzés

Nem cserélhet egy meglévő függvényt más szignatúrával.

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)

Hozz létre Python-függvényeket

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

Egyéni függőségek definiálása Python-függvényekben

-- Create a Python function with additional dependencies using the ENVIRONMENT clause.
> CREATE FUNCTION main.default.dump_json(data STRING)
    RETURNS STRING
    LANGUAGE PYTHON
    ENVIRONMENT (
      dependencies = '["simplejson==3.19.3", "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl", "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=2043167927&Signature=abcd"]',
      environment_version = 'None'
    )
    AS $$
      import simplejson as json
      import custom_package
      return json.dumps(custom_package.process(data))
    $$;

-- Use the Python function in a query.
> SELECT dump_json('{"foo": "bar"}');

Kapcsolódó cikkek

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