CREATE FUNCTION (SQL e Python)

Articolo
04/18/2024

Si applica a: segno di spunta sì 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: segno di spunta sì 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 un cluster del catalogo Unity condiviso o utente singolo.

Si applica a: segno di spunta sì Databricks SQL Databricks Runtime 14.1 e versioni successive

Oltre alla chiamata al parametro posizionale, è anche possibile richiamare funzioni definite dall'utente SQL e Python usando la chiamata di parametri denominati.

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

Parametri

OR REPLACE

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. Ciò è utile principalmente per aggiornare il corpo della funzione e il tipo restituito della funzione. Non è possibile specificare questo parametro con IF NOT EXISTS.
TEMPORARY

Ambito della funzione da creare. Quando si specifica TEMPORARY, la funzione creata è valida e visibile nella sessione corrente. Nessuna voce persistente viene 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.
function_parameter

Specifica un parametro della funzione.
- parameter_name
  
  Il nome del parametro deve essere univoco all'interno della funzione.
- data_type
  
  Qualsiasi tipo di dati supportato. Per Python, data_type viene eseguito il cast a un tipo di dati Python in base a questo mapping del linguaggio.
- 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 castable 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 commento
  
  Descrizione facoltativa del parametro. comment deve essere un valore STRING letterale.
RESTITUISCE data_type

Tipo di dati restituito della funzione scalare. Per le funzioni definite dall'utente Python, i valori restituiti devono corrispondere esattamente al tipo di dati specificato in data_type. In caso contrario, per evitare conversioni di tipi impreviste, la funzione avrà esito negativo.

Per la funzione definita dall'utente 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 funzione definita dall'utente SQL.

RETURNS TABLE è supportato solo per LANGUAGE SQL .
- column_name
  
  Il nome della colonna deve essere univoco all'interno della firma.
- data_type
  
  Qualsiasi tipo di dati supportato.
- COMMENT 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 di produzione di righe come esplodere
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.
- [NOT] DETERMINISTICO
  
  Indica se la funzione è deterministica. Una funzione è deterministica quando restituisce un solo risultato per un determinato set di argomenti. È possibile contrassegnare una funzione come DETERMINISTIC quando il corpo non è 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 l'opzione ths, viene derivata dal corpo della funzione.
- COMMENT function_comment
  
  Commento per la funzione. function_comment deve essere valore letterale Stringa.
- CONTAINS SQL o READS SQL DATA
  
  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.

Librerie supportate nelle funzioni definite dall'utente 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)]
$$

Le dipendenze sono limitate alla libreria Python standard e alle librerie seguenti:

Pacchetto	Versione
bleach	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
creazione del pacchetto	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
sei	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
Crittografia	38.0.4

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

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

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