CREATE FUNCTION (SQL en Python)

Artikel
03/01/2024

Van toepassing op: check marked yes Databricks SQL Databricks Runtime 9.1 en hoger

Hiermee maakt u een SCALAire SQL- of tabelfunctie die een set argumenten gebruikt en een scalaire waarde of een set rijen retourneert.

Van toepassing op: check marked yes Databricks SQL Databricks Runtime 13.2 en hoger

Hiermee maakt u een Scalaire Python-functie die een set argumenten gebruikt en een scalaire waarde retourneert.

Python UDF's vereisen Unity Catalog in serverloze of pro SQL-warehouses, of een gedeeld of één gebruiker Unity Catalog-cluster.

Van toepassing op: check marked yes Databricks SQL Databricks Runtime 14.1 en hoger

Naast het aanroepen van positionele parameters kunt u ook SQL en Python UDF aanroepen met behulp van benoemde parameteraanroepen.

Syntaxis

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

OF VERVANGEN

Indien opgegeven, wordt de functie vervangen door dezelfde naam en handtekening (aantal parameters en parametertypen). U kunt een bestaande functie niet vervangen door een andere handtekening. Dit is vooral handig om de hoofdtekst van de functie en het retourtype van de functie bij te werken. U kunt deze parameter niet opgeven met IF NOT EXISTS.
TIJDELIJKE

Het bereik van de functie die wordt gemaakt. Wanneer u opgeeft TEMPORARY, is de gemaakte functie geldig en zichtbaar in de huidige sessie. Er wordt geen permanente vermelding gemaakt in de catalogus.
ALS DEZE NIET BESTAAT

Indien opgegeven, wordt de functie alleen gemaakt wanneer deze niet bestaat. Het maken van de functie slaagt (er wordt geen fout opgetreden) als de opgegeven functie al bestaat in het systeem. U kunt deze parameter niet opgeven met OR REPLACE.
function_name

Een naam voor de functie. Voor een permanente functie kunt u desgewenst de functienaam kwalificeren met een schemanaam. Als de naam niet is gekwalificeerd, wordt de permanente functie gemaakt in het huidige schema.
function_parameter

Hiermee geeft u een parameter van de functie.
- parameter_name
  
  De parameternaam moet uniek zijn binnen de functie.
- data_type
  
  Elk ondersteund gegevenstype. Voor Python data_type wordt naar een Python-gegevenstype gecast volgens deze taaltoewijzing.
- STANDAARD default_expression
  
  Van toepassing op: Databricks SQL Databricks Runtime 10.4 en hoger
  
  Een optionele standaardwaarde die moet worden gebruikt wanneer een functieaanroep geen argument toewijst aan de parameter. default_expression moet worden gegoten naar data_type. De expressie mag niet verwijzen naar een andere parameter of een subquery bevatten.
  
  Wanneer u een standaardwaarde voor één parameter opgeeft, moeten alle volgende parameters ook een standaardwaarde hebben.
  
  DEFAULT wordt alleen ondersteund voor LANGUAGE SQL .
- OPMERKING opmerking
  
  Een optionele beschrijving van de parameter. comment moet een STRING letterlijke naam zijn.
Retourneert data_type

Het retourgegevenstype van de scalaire functie. Voor Python UDF's moeten retourwaarden exact overeenkomen met het gegevenstype zoals opgegeven in data_type. Anders mislukt de functie om onverwachte typeconversies te voorkomen.

Voor SQL UDF is deze component optioneel. Het gegevenstype wordt afgeleid van de hoofdtekst van de functie als het niet is opgegeven.
RETOURNEERT TABEL [ (column_spec [,...] ) ]

Deze component markeert de functie als een tabelfunctie. Desgewenst geeft het ook de handtekening op van het resultaat van de tabelfunctie. Als er geen column_spec is opgegeven, wordt deze afgeleid van de hoofdtekst van de SQL UDF.

RETURNS TABLE wordt alleen ondersteund voor LANGUAGE SQL .
- column_name
  
  De kolomnaam moet uniek zijn binnen de handtekening.
- data_type
  
  Elk ondersteund gegevenstype.
- OPMERKING column_comment
  
  Een optionele beschrijving van de kolom. comment moet een STRING letterlijke naam zijn.
RETURN { expressiequery | }

De hoofdtekst van de functie. Voor een scalaire functie kan dit een query of een expressie zijn. Voor een tabelfunctie kan dit alleen een query zijn. De expressie mag niet het volgende bevatten:
- Statistische functies
- Vensterfuncties
- Classificatiefuncties
- Rij die functies produceert, zoals exploderen
In de hoofdtekst van de functie kunt u verwijzen naar de parameter door de niet-gekwalificeerde naam of door de parameter te kwalificeren met de functienaam.
AS-dollar_quoted_definition

dollar_quoted_definition is de Python-functie body tussen twee overeenkomende $[tag]$body$[tag]$functies. tag kan een lege tekenreeks zijn.

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

$py$
  return "Hello World"
$py$
```
Karakteristiek

Alle kenmerkcomponenten zijn optioneel. U kunt een willekeurig aantal opgeven in elke volgorde, maar u kunt elke component slechts één keer opgeven.
- LANGUAGE SQL of LANGUAGE PYTHON
  
  De taal van de functie-implementatie.
- [NIET] DETERMINISTISCHE
  
  Of de functie deterministisch is. Een functie is deterministisch wanneer er slechts één resultaat wordt geretourneerd voor een bepaalde set argumenten. U kunt een functie markeren als DETERMINISTIC wanneer het lichaam niet is en omgekeerd. Een reden hiervoor kan zijn om queryoptimalisaties aan te moedigen of te ontmoedigen, zoals constant vouwen of opslaan van query's. Als u de optie ths niet opgeeft, wordt deze afgeleid van de hoofdtekst van de functie.
- OPMERKING function_comment
  
  Een opmerking voor de functie. function_comment moet letterlijk tekenreeks zijn.
- BEVAT SQL- of LEESBEWERKINGEN VOOR SQL-GEGEVENS
  
  Of een functie gegevens rechtstreeks of indirect uit een tabel of een weergave leest. Wanneer de functie SQL-gegevens leest, kunt u niet opgeven CONTAINS SQL. Als u geen van beide componenten opgeeft, wordt de eigenschap afgeleid van de hoofdtekst van de functie.

Ondersteunde bibliotheken in Python UDF's

Als u afhankelijkheden wilt gebruiken, gebruikt import <package> u deze in de hoofdtekst van de functie. Zie bijvoorbeeld het volgende:

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

Afhankelijkheden zijn beperkt tot de standaardBibliotheek voor Python en de volgende bibliotheken:

Pakket	Versie
Bleekmiddel	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
Verpakking	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
Zes	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
Cryptografie	38.0.4

Voorbeelden

Een SCALAire SQL-functie maken en gebruiken
Een functie maken en gebruiken die GEBRUIKMAAKT van DEFAULTs
Een SQL-tabelfunctie maken
Een SQL-functie vervangen
Een SQL-functie beschrijven
Python-functies maken

Een SCALAire SQL-functie maken en gebruiken

> 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

Een functie maken en gebruiken die GEBRUIKMAAKT van 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

Een SQL-tabelfunctie maken

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

Een SQL-functie vervangen

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

Notitie

U kunt een bestaande functie niet vervangen door een andere handtekening.

Een SQL-functie beschrijven

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

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