CREAR FUNCIONES (SQL y Python)

Artículo
03/01/2024

Se aplica a: check marked yes Databricks SQL Databricks Runtime 9.1 y versiones posteriores

Crea un escalar o una función de tabla de SQL que toma un conjunto de argumentos y devuelve un valor escalar o un conjunto de filas.

Se aplica a: check marked yes Databricks SQL Databricks Runtime 13.2 y versiones posteriores

Crea una función escalar de Python que toma un conjunto de argumentos y devuelve un valor escalar.

Las UDF de Python requieren Unity Catalog en almacenes sin servidor o pro SQL, o un clúster de Unity Catalog de usuario compartido o único.

Se aplica a: check marked yes Databricks SQL Databricks Runtime 14.1 y versiones posteriores

Además de la invocación de parámetros posicionales, también puede invocar las UDF de Python y SQL mediante la invocación de parámetros con nombre.

Sintaxis

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

Parámetros

OR REPLACE

Si se especifica, se reemplaza la función con el mismo nombre y firma (número de parámetros y tipos de parámetros). No se puede reemplazar una función existente por una firma diferente. Esto es especialmente útil para actualizar el cuerpo de la función y el tipo de valor devuelto de la función. No se puede especificar este parámetro con IF NOT EXISTS.
TEMPORARY

Ámbito de la función que se va a crear. Cuando se especifica TEMPORARY, la función creada es válida y visible en la sesión actual. No se realiza ninguna entrada persistente en el catálogo.
IF NOT EXISTS

Si se especifica, crea la función solo cuando no existe. La creación de la función se realiza correctamente (no se produce ningún error) si la función especificada ya existe en el sistema. No se puede especificar este parámetro con OR REPLACE.
function_name

Nombre de la función. Para una función permanente, opcionalmente puede calificar el nombre de la función con un nombre de esquema. Si el nombre no está calificado, la función permanente se crea en el esquema actual.
function_parameter

Especifica un parámetro de la función.
- parameter_name
  
  El nombre del parámetro debe ser único en la función.
- data_type
  
  Cualquier tipo de datos admitidos. Para Python, data_type se convierte en un tipo de datos de Python según esta asignación de lenguaje.
- DEFAULT default_expression
  
  Se aplica a: Databricks SQL Databricks Runtime 10.4 y versiones posteriores
  
  Un valor predeterminado opcional que se usará cuando una invocación de función no asigne un argumento al parámetro. default_expression debe ser convertible para data_type. La expresión no debe hacer referencia a otro parámetro ni contener una subconsulta.
  
  Cuando se especifica un valor predeterminado para un parámetro, todos los parámetros siguientes también deben tener un valor predeterminado.
  
  DEFAULT solo se admite para LANGUAGE SQL.
- COMMENT comment
  
  Descripción opcional del parámetro. comment debe ser un literal de STRING.
RETURNS data_type

Tipo de datos devuelto de la función escalar. En el caso de las UDF de Python, los valores devueltos deben coincidir exactamente con el tipo de datos especificado en data_type. De lo contrario, para evitar conversiones de tipos imprevistos, se producirá un error en la función.

Para UDF de SQL, esta cláusula es opcional. El tipo de datos se derivará del cuerpo de la función si no se proporciona.
RETURNS TABLE [ (column_spec [,…] ) ]

Esta cláusula marca la función como una función de tabla. Opcionalmente, también especifica la firma del resultado de la función table. Si no se especifica ningún column_spec, se derivará del cuerpo de la UDF de SQL.

RETURNS TABLE solo se admite para LANGUAGE SQL.
- column_name
  
  El nombre de columna debe ser único dentro de la firma.
- data_type
  
  Cualquier tipo de datos admitidos.
- COMMENT column_comment
  
  Una descripción opcional de la columna. comment debe ser un literal de STRING.
RETURN { expression | query }

Cuerpo de la función. Para una función escalar, puede ser una consulta o una expresión. Para una función de tabla, solo puede ser una consulta. La expresión de columna no puede contener:
- Funciones de agregado
- Funciones de ventana
- Funciones de categoría
- Funciones de generación de filas, como expandir
En el cuerpo de la función, puede hacer referencia al parámetro por su nombre no completo o calificando el parámetro con el nombre de la función.
AS dollar_quoted_definition

dollar_quoted_definition es la función body de Python entre dos coincidencias de $[tag]$body$[tag]$. tag puede ser una cadena vacía.

Ejemplos:
```
$$
  return “Hello world”
$$

$py$
  return "Hello World"
$py$
```
characteristic

Todas las cláusulas de características son opcionales. Puede especificar cualquier número en cualquier orden, pero puede especificar cada cláusula solo una vez.
- LENGUAJE SQL o LENGUAJE PYTHON
  
  Lenguaje de la implementación de la función.
- [NOT] DETERMINISTIC
  
  Si la función es determinista. Una función es determinista cuando solo devuelve un resultado para un conjunto determinado de argumentos. Puede marcar una función como DETERMINISTIC cuando su cuerpo no es y viceversa. Un motivo para esto puede ser fomentar o desalentar las optimizaciones de consultas, como el plegado constante o el almacenamiento en caché de consultas. Si no especifica la opción ths, se deriva del cuerpo de la función.
- COMMENT function_comment
  
  Comentario de la función. function_comment debe ser un valor STRING literal.
- CONTAINS SQL o READS SQL DATA
  
  Indica si una función lee datos de forma directa o indirecta desde una tabla o una vista. Cuando la función lee datos de SQL, no puede especificar CONTAINS SQL. Si no especifica ninguna cláusula, la propiedad se deriva del cuerpo de la función.

Bibliotecas admitidas en UDF de Python

Para usar las dependencias, use import <package> dentro del cuerpo de la función. Por ejemplo, consulte la información que se muestra a continuación:

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

Las dependencias se limitan a la biblioteca estándar de Python y a las siguientes bibliotecas:

Paquete	Versión
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
empaquetado	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
six (seis)	1.16.0
threadpoolctl	3.1.0
webencodings	0.5.1
user-agents	2.2.0
criptografía	38.0.4

Ejemplos

Creación y uso de una función scalar de SQL
Creación y uso de una función que usa DEFAULTs
Creación de una función de tabla de SQL
Reemplazo de una función de SQL
Descripción de una función de SQL
Crear funciones de Python

Creación y uso de una función scalar de 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

Creación y uso de una función que usa 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

Creación de una función de tabla de 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

Reemplazo de una función de 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:

No se puede reemplazar una función existente por una firma diferente.

Descripción de una función de 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)

Crear funciones de 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
  $$