CREATE FUNCTION (SQL en Python)

Van toepassing op: Databricks SQL Databricks Runtime

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: Databricks SQL 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 een Unity Catalog op serverloze of pro-SQL-warehouses, of een rekenresource die Unity Catalog ondersteunt.

Van toepassing op: 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.

Van toepassing op: Databricks SQL Databricks Runtime 16.2 en hoger

Gebruik de ENVIRONMENT component om de Python-omgeving op te geven voor een functie die is gedeclareerd met LANGUAGE PYTHON. Wordt gebruikt om aangepaste afhankelijkheden te installeren en de omgevingsversie in te stellen.

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

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

Parameterwaarden

• 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 of een procedure. 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.

• TIJDELIJK

  Het bereik van de te maken functie. 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 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 aangemaakt in het huidige schema.

  De functienaam moet uniek zijn voor alle routines (procedures en functies) in het 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.

    Voor een STRINGdata_typeis de standaardsortering de functie default_collation_name.

  • STANDAARD default_expression

    Van toepassing op: Databricks SQL 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 waarde 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.

• geeft terug TABLE [ (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 waarde zijn.

• RETURN { expressie | query }

  De inhoud 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
  • Rangschikkingsfuncties
  • Rijproducerende functies zoals explode

  In het lichaam van de functie kunt u verwijzen naar de parameter met zijn onbepaalde naam of door de parameter te kwalificeren met de functienaam.

• AS-dollar_quoted_definition

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

  Voorbeelden:

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

• karakteristiek

  Alle kenmerkende clausules 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] DETERMINISTISCH

    Of de functie deterministisch is. Een functie is deterministisch wanneer er slechts één resultaat wordt geretourneerd voor een bepaalde set argumenten. nl-NL: U kunt een functie markeren als DETERMINISTIC wanneer de inhoud anders 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 deze optie niet opgeeft, wordt deze afgeleid van de hoofdtekst van de functie.

  • COMMENTAAR 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 voorwaarden opgeeft, wordt de eigenschap afgeleid van de functie-inhoud.

  • STANDAARDCOLLATIE default_collation_name

    Van toepassing op: Databricks Runtime 17.0 en hoger

    Definieert de standaardsortering die moet worden gebruikt voor:

    • STRING parameters en RETURNS gegevenstype en RETURNS TABLE velden van de functie.
    • DEFAULT uitdrukking.
    • De hoofdtekst van de SQL-functie.

    Als dat niet het geval is, is de standaardsortering UTF8_BINARY.

• milieu

  Hiermee geeft u de Python-omgeving voor een functie die is gedeclareerd met LANGUAGE PYTHON. De ENVIRONMENT component wordt niet ondersteund voor SQL-functies.

  • Afhankelijkheden

    Een JSON-matrix met tekenreeksen waarmee de vereiste Python-pakketten of wheel-bestanden voor de functie worden opgegeven. De dependencies sleutel is niet hoofdlettergevoelig. Ondersteunde indelingen:

    • PyPI-pakket met optionele versie, bijvoorbeeld: "simplejson==3.19.3"
    • Absoluut pad naar een wielbestand dat is opgeslagen in een Unity Catalog-volume, bijvoorbeeld: '/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl'
    • HTTPS-URL naar een wielbestand in externe opslag, bijvoorbeeld: 'https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=...'

  • environment_version

    Een tekenreeks die de python-omgevingsversie opgeeft. Gebruik None deze om de standaard Python-omgeving te gebruiken. Als u dit weglaat, wordt de standaardomgeving gebruikt.

    • Alleen de waarde None wordt momenteel ondersteund.

Ondersteunde bibliotheken in Python UDF's

Gebruik import <package> in de functie om afhankelijkheden te gebruiken. Zie bijvoorbeeld het volgende:

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

Afhankelijkheden zijn standaard 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
protocolbuffers	4.21.5
pyarrow	7.0.0
pyparsing	3.0.9
python-dateutil (een bibliotheek voor datum- en tijdgebaseerde functionaliteit in Python)	2.8.2
pytz	2021.3
scikit-learn	0.24.2”
Scipy (een wetenschappelijke bibliotheek voor Python)	1.7.1”
setuptools (een Python-pakket voor het beheren van installatie en distributie van pakketten)	65.2.0
Zes	1.16.0
threadpoolctl	3.1.0
webcoderingen	0.5.1
gebruikersagenten	2.2.0
cryptografie	38.0.4

Aangepaste afhankelijkheden in Python UDF's

Als u aanvullende afhankelijkheden wilt gebruiken buiten de standaardbibliotheek en de ondersteunde ingebouwde pakketten, geeft u deze op in de ENVIRONMENT component.

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
• Aangepaste afhankelijkheden definiëren in Python-functies

Een scalair 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
  $$

Aangepaste afhankelijkheden definiëren in Python-functies

-- 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"}');

Verwante artikelen:

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