CREATE FUNCTION (SQL e Python)

Si applica a: Databricks SQL Databricks Runtime

Crea una funzione scalare o tabella SQL che accetta un set di argomenti e restituisce un valore scalare o un set di righe.

Si applica a: Databricks SQL Databricks Runtime 13.3 LTS e versioni successive

Crea una funzione scalare Python che accetta un set di argomenti e restituisce un valore scalare.

Le funzioni definite dall'utente Python richiedono il catalogo Unity nei warehouse SQL serverless o pro oppure in una risorsa di calcolo abilitata per il catalogo Unity.

Si applica a: Databricks SQL Databricks Runtime 14.1 e versioni successive

Oltre all'invocazione di parametro posizionale, è anche possibile richiamare le funzioni definite dall'utente SQL e Python usando l'invocazione di parametri nominativi.

Si applica a: Databricks SQL Databricks Runtime 16.2 e versioni successive

Usare la ENVIRONMENT clausola per specificare l'ambiente Python per una funzione dichiarata con LANGUAGE PYTHON. Usato per installare dipendenze personalizzate e impostare la versione dell'ambiente.

Sintassi

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 } [, ...] )

Parametri

• O SOSTITUISCI

  Se specificato, la funzione con lo stesso nome e la stessa firma (numero di parametri e tipi di parametro) viene sostituita. Non è possibile sostituire una funzione esistente con una firma diversa o una routine. Ciò è utile principalmente per aggiornare il corpo della funzione e il tipo restituito della funzione. Non è possibile specificare questo parametro con IF NOT EXISTS.

• TEMPORANEO

  Ambito della funzione da creare. Quando si specifica TEMPORARY, la funzione creata è valida e visibile nella sessione corrente. Nessuna voce persistente è effettuata nel catalogo.

• SE NON ESISTE

  Se specificato, crea la funzione solo quando non esiste. La creazione della funzione ha esito positivo (non viene generato alcun errore) se la funzione specificata esiste già nel sistema. Non è possibile specificare questo parametro con OR REPLACE.

• function_name

  Nome della funzione. Per una funzione permanente, facoltativamente è possibile qualificare il nome della funzione con un nome di schema. Se il nome non è qualificato, la funzione permanente viene creata nello schema corrente.

  Il nome della funzione deve essere univoco per tutte le routine (routine e funzioni) nello schema.

• function_parameter

  Specifica un parametro della funzione.

  • parameter_name

    Il nome del parametro deve essere univoco all'interno della funzione.

  • tipo_dati

    Qualsiasi tipo di dati supportato. Per Python, data_type viene eseguito il cast a un tipo di dati Python in base a questa mappatura del linguaggio.

    Per un STRINGdata_type, le regole di confronto predefinite sono la funzione default_collation_name.

  • DEFAULT default_expression

    Si applica a: Databricks SQL Databricks Runtime 10.4 LTS e versioni successive

    Valore predefinito facoltativo da utilizzare quando una chiamata di funzione non assegna un argomento al parametro . default_expression deve essere convertibile a data_type. L'espressione non deve fare riferimento a un altro parametro o contenere una sottoquery.

    Quando si specifica un valore predefinito per un parametro, anche tutti i parametri seguenti devono avere un valore predefinito.

    DEFAULT è supportato solo per LANGUAGE SQL .

  • Commento osservazione

    Descrizione facoltativa del parametro. comment deve essere un valore STRING letterale.

• RESTITUISCE data_type

  Tipo di dati restituito della funzione scalare. Per le funzioni UDF di Python, i valori restituiti devono corrispondere esattamente alla tipologia di dati specificata in data_type. In caso contrario, per evitare conversioni di tipi impreviste, la funzione avrà esito negativo.

  Per le UDF SQL, questa clausola è facoltativa. Se non viene specificato, il tipo di dati verrà derivato dal corpo della funzione.

• RETURNS TABLE [ (column_spec [,...] ) ]

  Questa clausola contrassegna la funzione come funzione di tabella. Facoltativamente, specifica anche la firma del risultato della funzione table. Se non viene specificato alcun column_spec, verrà derivato dal corpo della SQL UDF.

  RETURNS TABLE è supportato solo per LANGUAGE SQL .

  • column_name

    Il nome della colonna deve essere univoco nella firma.

  • tipo_dati

    Qualsiasi tipo di dati supportato.

  • COMMENTO column_comment

    Descrizione facoltativa della colonna. comment deve essere un valore STRING letterale.

• RETURN { expression | query }

  Corpo della funzione. Per una funzione scalare, può essere una query o un'espressione. Per una funzione di tabella, può essere solo una query. L'espressione non può contenere:

  • Funzioni di aggregazione
  • funzioni finestra
  • Funzioni di rango
  • Funzioni che generano righe come explode

  All'interno del corpo della funzione è possibile fare riferimento al parametro in base al nome non qualificato o qualificando il parametro con il nome della funzione.

• As dollar_quoted_definition

  dollar_quoted_definition è la funzione body Python racchiusa tra due elementi corrispondenti $[tag]$body$[tag]$. tag può essere una stringa vuota.

  Esempi:

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

• caratteristica

  Tutte le clausole di caratteristica sono facoltative. È possibile specificare un numero qualsiasi in qualsiasi ordine, ma è possibile specificare ogni clausola una sola volta.

  • LINGUAGGIO SQL o LINGUAGGIO PYTHON

    Linguaggio dell'implementazione della funzione.

  • NON DETERMINISTICO

    Indica se la funzione è deterministica. Una funzione è deterministica quando restituisce un solo risultato per un determinato set di argomenti. È possibile segnare una funzione come DETERMINISTIC quando il suo corpo non lo è, e viceversa. Un motivo può essere incoraggiare o scoraggiare le ottimizzazioni delle query, ad esempio la riduzione costante o la memorizzazione nella cache delle query. Se non si specifica questa opzione, essa viene derivata dal corpo della funzione.

  • COMMENTO function_comment

    Commento per la funzione. function_comment deve essere valore letterale Stringa.

  • CONTIENE SQL o LEGGE DATI SQL

    Indica se una funzione legge i dati direttamente o indirettamente da una tabella o da una vista. Quando la funzione legge i dati SQL, non è possibile specificare CONTAINS SQL. Se non si specifica alcuna clausola, la proprietà viene derivata dal corpo della funzione.

  • COLLAZIONE PREDEFINITA nome_collazione_predefinita COLLAZIONE PREDEFINITA

    Si applica a: Databricks Runtime 17.0 e versioni successive

    Definisce le regole di confronto predefinite da usare per:

    • STRING parametri e RETURNS tipi di dati e RETURNS TABLE campi della funzione.
    • DEFAULT espressione.
    • Corpo della funzione SQL.

    Se non specificato, le regole di confronto predefinite sono UTF8_BINARY.

• ambiente

  Specifica l'ambiente Python per una funzione dichiarata con LANGUAGE PYTHON. La ENVIRONMENT clausola non è supportata per le funzioni SQL.

  • Dipendenze

    Matrice JSON di stringhe che specifica i pacchetti Python o i file wheel necessari per la funzione. La dependencies chiave non fa distinzione tra maiuscole e minuscole. Formati supportati:

    • Pacchetto PyPI con versione facoltativa, ad esempio: "simplejson==3.19.3"
    • Percorso assoluto verso un file wheel memorizzato in un volume di Unity Catalog, ad esempio: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
    • URL HTTPS di un file wheel in archiviazione esterna, ad esempio: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."

  • environment_version

    Stringa che specifica la versione dell'ambiente Python. Usare None per usare l'ambiente Python predefinito. Se omesso, viene usato l'ambiente predefinito.

    • Attualmente è supportato solo il valore None .

Librerie supportate nelle UDF Python

Per usare eventuali dipendenze, usare import <package> all'interno del corpo della funzione. Ad esempio, vedere quanto segue:

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

Per impostazione predefinita, le dipendenze sono limitate alla libreria Python standard e alle librerie seguenti:

Pacchetto	Versione
candeggiare	4.0.0
chardet	4.0.0
normalizzatore di set di caratteri	2.0.4
defusedxml (una libreria per migliorare la sicurezza nell'elaborazione di XML)	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
imballaggio	21,3
Panda	1.3.4
capro espiatorio	0.5.2
protobuf	4.21.5
pyarrow	7.0.0
pyparsing	3.0.9
python-dateutil (libreria Python per la gestione delle date)	2.8.2
pytz	2021.3
scikit-learn	0.24.2”
scipy	1.7.1”
setuptools (pacchetto Python per gestire la configurazione e la distribuzione)	65.2.0
sei	1.16.0
threadpoolctl	3.1.0
codifiche web	0.5.1
agenti utente	2.2.0
criptografia	38.0.4

Dipendenze personalizzate nelle funzioni definite dall'utente (UDF) in Python

Per usare dipendenze aggiuntive oltre la libreria standard e i pacchetti predefiniti supportati, specificarli nella ENVIRONMENT clausola .

Esempi

• Creare e usare una funzione scalare SQL
• Creare e usare una funzione che usa DEFAULT
• Creare una funzione di tabella SQL
• Sostituire una funzione SQL
• Descrivere una funzione SQL
• Creare funzioni Python
• Definire dipendenze personalizzate nelle funzioni Python

Creare e usare una funzione scalare SQL

> 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

Creare e usare una funzione che usa DEFAULT

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

Creare una funzione di tabella SQL

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

Sostituire una funzione SQL

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

Nota

Non è possibile sostituire una funzione esistente con una firma diversa.

Descrivere una funzione SQL

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

Creare funzioni Python

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

Definire dipendenze personalizzate nelle funzioni Python

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

Articoli correlati

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