Delen via

CREATE FUNCTION (SQL en Python)

  • Artikel

Van toepassing op:vinkje als ja aan Databricks SQL vinkje als ja aan Databricks Runtime

Hiermee maakt u een scalaire SQL- of table-functie die een set aantal argumenten aanneemt en een scalaire waarde of een set aantal rijen retourneert.

Van toepassing op:vinkje als ja aan Databricks SQL vinkje als ja aan Databricks Runtime 13.3 LTS 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 op serverloze of pro-SQL-warehouses, of een Unity Catalog-cluster, gedeeld of voor één gebruiker.

Van toepassing op:vinkje als ja aan Databricks SQL vinkje als ja aan 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 te update. U kunt deze parameter niet opgeven met IF NOT EXISTS.

  • TIJDELIJK

    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 invoer gedaan in de catalog.

  • 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. Desgewenst kunt u voor een permanente functie de functienaam qualify met een schema naam. Als de naam niet is gespecificeerd, wordt de permanente functie gemaakt in de actuele 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:vinkje als ja aan Databricks SQL vinkje als ja aan Databricks Runtime 10.4 LTS 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 moet values 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.

  • geeft terug TABLE [ (column_spec [,...] ) ]

    Deze component markeert de functie als een table functie. Optioneel geeft het ook de handtekening op van het resultaat van de table functie. 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 column naam moet uniek zijn binnen de handtekening.

    • data_type

      Elk ondersteund gegevenstype.

    • OPMERKING column_comment

      Een optionele beschrijving van het column. 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 table-functie kan dit alleen een query zijn. De expressie mag niet het volgende bevatten:

    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 gegeven 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 leest vanuit een table of een weergave. 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
bleken 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

> 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-table-functie 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
  $$