CREATE FUNCTION (SQL y Python)

2025-06-07

Se aplica a: casilla marcada como Sí Databricks SQL Databricks Runtime

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: casilla marcada como sí Databricks SQL Databricks Runtime 13.3 LTS y versiones posteriores

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

Las UDFs de Python requieren el catálogo de Unity en almacenes SQL sin servidor o profesionales, o bien un recurso de computación habilitado para el catálogo de Unity.

Se aplica a: casilla marcada como sí 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.

Se aplica a: marcado como sí Databricks SQL Databricks Runtime 16.2 y versiones posteriores

Use la ENVIRONMENT cláusula para especificar el entorno de Python para una función declarada con LANGUAGE PYTHON. Se usa para instalar dependencias personalizadas y establecer la versión del entorno.

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

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

Parámetros

O REEMPLAZAR

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 otra firma o procedimiento. 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.
TEMPORAL

Á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.
SI NO EXISTE

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.

El nombre de la función debe ser único para todas las rutinas (procedimientos y funciones) del esquema.
function_parameter

Especifica un parámetro de la función.
- parameter_name
  
  El nombre del parámetro debe ser único en la función.
- tipo_de_datos
  
  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.
  
  Para un STRINGdata_type, la intercalación predeterminada es la función default_collation_name.
- PREDETERMINADO default_expression
  
  Se aplica a: Databricks SQL Databricks Runtime 10.4 LTS 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.
- COMENTARIO comentario
  
  Descripción opcional del parámetro. comment debe ser un literal de STRING.
DEVUELVE 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.
- nombre_de_columna
  
  El nombre de columna debe ser único dentro de la firma.
- tipo_de_datos
  
  Cualquier tipo de datos admitidos.
- COMENTARIO 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.
Definición_cotizada_en_dólares_AS

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$
```
característica

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] DETERMINISTA
  
  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.
- comentario_de_función COMENTARIO
  
  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, entonces la propiedad se deriva del cuerpo de la función.
- INTERCALACIÓN PREDETERMINADA nombre_intercalación_predeterminada
  
  Se aplica a: Databricks Runtime 17.0 y versiones posteriores
  
  Define la intercalación predeterminada que se va a usar para:
  - STRING parámetros y tipos de datos y RETURNSRETURNS TABLE campos de la función.
  - DEFAULT expresión.
  - Cuerpo de la función SQL.
  Si no se especifica, la intercalación predeterminada es UTF8_BINARY.
medio ambiente

Especifica el entorno de Python para una función declarada con LANGUAGE PYTHON. La ENVIRONMENT cláusula no se admite para las funciones SQL.
- Dependencias
  
  Matriz JSON de cadenas que especifican los paquetes de Python o archivos de rueda necesarios para la función. La dependencies clave no distingue mayúsculas de minúsculas. Formatos compatibles:
  - Paquete PyPI con versión opcional, por ejemplo: "simplejson==3.19.3"
  - Ruta de acceso absoluta a un archivo wheel almacenado en un volumen del catálogo de Unity, por ejemplo: "/Volumes/my_catalog/my_schema/my_volume/packages/custom_package-1.0.0.whl"
  - Dirección URL HTTPS a un archivo wheel en el almacenamiento externo, por ejemplo: "https://my-bucket.s3.amazonaws.com/packages/special_package-2.0.0.whl?Expires=..."
- environment_version
  
  Cadena que especifica la versión del entorno de Python. Use None para usar el entorno de Python predeterminado. Si se omite, se usa el entorno predeterminado.
  - Actualmente solo se admite el valor None .

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

De forma predeterminada, las dependencias se limitan a la biblioteca estándar de Python y a las siguientes bibliotecas:

Paquete	Versión
blanquear	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
chivo expiatorio	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
codificaciones web	0.5.1
agentes de usuario	2.2.0
criptografía	38.0.4

Dependencias personalizadas en UDF de Python

Para usar dependencias adicionales más allá de la biblioteca estándar y los paquetes integrados admitidos, especifíquelas en la ENVIRONMENT cláusula .

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
Definición de dependencias personalizadas en 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;
 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

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

Definición de dependencias personalizadas en funciones de 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"}');

Compartir a través de