CREATE FUNCTION(SQL, Python, Scala en Java)

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.

Van toepassing op: Databricks Databricks Runtime 18.2 en hoger

Hiermee maakt u een Scala- of Java scalaire functie die gecompileerde JVM-logica uitvoert die als JAR is verpakt. Scala en Java UDF's vereisen Unity Catalog. U verwijst naar de gecompileerde code met de HANDLER component en verstrekt de JAR via de sleutel van java_dependencies de ENVIRONMENT component. Zie Scala en Java door de gebruiker gedefinieerde functies (UDF's) in Unity Catalog voor de volledige werkstroom, waaronder het bouwen van de JAR en het registreren van de functie.

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 } | HANDLER handler_name }

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 | SCALA | JAVA } |
    [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 ondersteund voor LANGUAGE SQL en voor LANGUAGE PYTHONpolymorfe UDFS (Databricks Runtime 18.1 en hoger).

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

• HANDLER-handler_name

  Van toepassing op: Databricks Databricks Runtime 18.2 en hoger

  Het volledig gekwalificeerde pad naar de methode die een LANGUAGE SCALA of LANGUAGE JAVA functie implementeert als letterlijke STRING methode. Gebruik de indeling 'package.Object.method' voor Scala en 'package.ClassName.method' voor Java. De gecompileerde code moet worden opgegeven als JAR via de java_dependencies sleutel van de ENVIRONMENT component.

• 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, LANGUAGE PYTHON, LANGUAGE SCALA of LANGUAGE JAVA

    De taal van de functie-implementatie. LANGUAGE SCALA en LANGUAGE JAVA vereist Unity Catalog en een HANDLER component die verwijst naar gecompileerde code die via de ENVIRONMENT component wordt geleverd. Zie Scala en Java door de gebruiker gedefinieerde functies (UDF's) in Unity Catalog.

  • [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 SQL 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 dit niet is opgegeven, wordt de standaardsortering afgeleid van het schema waarin de functie wordt gemaakt.

• milieu

  Hiermee geeft u de omgeving voor een functie gedeclareerd met LANGUAGE PYTHON, LANGUAGE SCALAof LANGUAGE JAVA. De ENVIRONMENT component wordt niet ondersteund voor SQL-functies.

  • Afhankelijkheden

    Een JSON-matrix met tekenreeksen die de vereiste Python pakketten of wielbestanden voor een LANGUAGE PYTHON functie aangeeft. 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=...'

  • java_dependencies

    Van toepassing op: Databricks Databricks Runtime 18.2 en hoger

    Een JSON-matrix met tekenreeksen die de JAR-bestanden opgeven die de gecompileerde code voor een LANGUAGE SCALA of LANGUAGE JAVA functie bevatten. Elke vermelding is een absoluut pad naar een JAR-bestand dat is opgeslagen in een Unity Catalog-volume, bijvoorbeeld: '/Volumes/my_catalog/my_schema/my_volume/packages/my-udf-assembly-0.1.0.jar'. Verpak de code als een fat JAR die alle afhankelijkheden van derden bevat die door de UDF worden gebruikt.

  • environment_version

    Een tekenreeks die de omgevingsversie opgeeft.

    • Alleen LANGUAGE PYTHONde waarde None wordt momenteel ondersteund. Hierbij wordt de standaardomgeving Python gebruikt. Als u dit weglaat, wordt de standaardomgeving gebruikt.
    • Voor LANGUAGE SCALA en LANGUAGE JAVA, de waarde moet of hoger zijn '4' . Omgevingsversie 4 specificeert Scala 2.13.16 en JDK 17. Zie Serverloze Omgevingsversies.

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
• Scala- en Java-functies maken

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

Scala- en Java-functies maken

In de volgende voorbeelden wordt een UDF geregistreerd van gecompileerde JVM-code die is verpakt als JAR in een Unity Catalog-volume. De HANDLER component verwijst naar de methode in het JAR en de java_dependencies sleutel verwijst naar de JAR. Zie Scala en Java door de gebruiker gedefinieerde functies (UDF's) in Unity Catalog voor de volledige werkstroom, waaronder het bouwen van de JAR.

-- Register a Scala UDF from a JAR in a Unity Catalog volume.
> CREATE OR REPLACE FUNCTION my_catalog.my_schema.add_one_scala(x INT)
    RETURNS INT
    LANGUAGE SCALA
    DETERMINISTIC
    ENVIRONMENT (
      java_dependencies = '["/Volumes/my_catalog/my_schema/udf_jars/my-udf-assembly-0.1.0.jar"]',
      environment_version = '4'
    )
    HANDLER 'com.example.ScalaUDF.addOne';

-- Register a Java UDF from a JAR in a Unity Catalog volume.
> CREATE OR REPLACE FUNCTION my_catalog.my_schema.add_one_java(x INT)
    RETURNS INT
    LANGUAGE JAVA
    DETERMINISTIC
    ENVIRONMENT (
      java_dependencies = '["/Volumes/my_catalog/my_schema/udf_jars/my-udf-1.0.jar"]',
      environment_version = '4'
    )
    HANDLER 'com.example.JavaUDF.addOne';

-- Use the functions in a query.
> SELECT my_catalog.my_schema.add_one_scala(5);
 6
> SELECT my_catalog.my_schema.add_one_java(5);
 6

Verwante artikelen:

• Scala en Java door de gebruiker gedefinieerde functies (UDF's) in Unity Catalog
• DROP FUNCTION
• SHOW FUNCTIONS
• DESCRIBE FUNCTION
• GRANT
• REVOKE