Funciones definidas por el usuario

Una función definida por el usuario (UDF) te permite crear una función mediante una expresión de SQL o un código JavaScript. Una UDF acepta columnas de entrada, realiza acciones en la entrada y muestra el resultado de esas acciones como un valor.

Puedes definir una UDF como persistente o temporal. Puedes reutilizar las UDF persistentes en varias consultas, mientras que las UDF temporales solo existen en el alcance de una única consulta.

Para crear un conjunto de datos, usa la declaración CREATE FUNCTION. Para borrar una función persistente definida por el usuario, usa la declaración DROP FUNCTION. Las UDF temporales vencen apenas finaliza la consulta. La declaración DROP FUNCTION solo es compatible con UDF temporales en consultas de varias declaraciones y procedimientos.

Para obtener más información sobre las UDF en SQL heredado, consulta Funciones definidas por el usuario en SQL heredado.

UDF de SQL

En el siguiente ejemplo, se crea una UDF de SQL temporal llamada AddFourAndDivide y se llama a la UDF desde una sentencia SELECT:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

En este ejemplo, se produce el siguiente resultado:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

En el siguiente ejemplo, se crea la misma función que una UDF persistente:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

Debido a que esta UDF es persistente, debes especificar un conjunto de datos para la función (mydataset en este ejemplo). Después de ejecutar la declaración CREATE FUNCTION, puedes llamar a la función desde una consulta:

SELECT
  val, mydataset.AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8,12]) AS val;

Parámetros de UDF de SQL con plantilla

Un parámetro con un tipo igual a ANY TYPE puede coincidir con más de un tipo de argumento cuando se llama a la función.

  • Si hay más de un parámetro con el tipo ANY TYPE, BigQuery no impone ninguna relación entre estos argumentos.
  • La función tipo de datos que se muestra no puede ser ANY TYPE. Debe omitirse, lo que significa que se determina de forma automática en función de sql_expression, o debe ser un tipo explícito.
  • Si pasas los argumentos de funciones de tipos que son incompatibles con la definición de la función, se producirá un error durante la llamada.

En el siguiente ejemplo se muestra una UDF de SQL que usa un parámetro con plantilla.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  addFourAndDivideAny(3, 4) AS integer_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

En este ejemplo, se produce el siguiente resultado:

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

En el siguiente ejemplo, se usa un parámetro con plantilla para mostrar el último elemento de un array de cualquier tipo:

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE)
AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);

SELECT
  lastArrayElement(x) AS last_element
FROM (
  SELECT [2,3,5,8,13] AS x
);

En este ejemplo, se produce el siguiente resultado:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Subconsultas escalares

Una UDF de SQL puede mostrar el valor de una subconsulta escalar. Una subconsulta escalar debe seleccionar una sola columna.

En el siguiente ejemplo, se muestra una UDF de SQL que usa una subconsulta escalar para contar la cantidad de usuarios con una edad determinada en una tabla de usuario.

CREATE TEMP TABLE users
AS (
  SELECT
    1 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS (
  (SELECT COUNT(1) FROM users WHERE age = userAge)
);

SELECT
  countUserByAge(10) AS count_user_age_10,
  countUserByAge(20) AS count_user_age_20,
  countUserByAge(30) AS count_user_age_30;

En este ejemplo, se produce el siguiente resultado:

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

Proyecto predeterminado en expresiones de SQL

En el cuerpo de una UDF de SQL, cualquier referencia a entidades de BigQuery, como tablas o vistas, debe incluir el ID del proyecto, a menos que la entidad resida en el mismo proyecto que contiene la UDF.

Por ejemplo, considera la siguiente declaración:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM mydataset.mytable)
);

Si ejecutas esta declaración desde project1 y mydataset.mytable existe en project1, entonces la instrucción se realiza de forma correcta. Sin embargo, si ejecutas esta declaración desde un proyecto diferente, la instrucción fallará. Para corregir el error, incluye el ID del proyecto en la referencia de la tabla:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project1.mydataset.mytable)
);

También puedes hacer referencia a una entidad en un proyecto o conjunto de datos diferente del que creaste la función:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project2.another_dataset.another_table)
);

UDF de JavaScript

Una UDF de JavaScript te permite llamar al código escrito en JavaScript desde una consulta de SQL. Las UDF de JavaScript suelen consumir más recursos de ranuras en comparación con las consultas en SQL estándar, lo que disminuye el rendimiento del trabajo. Si la función se puede expresar en SQL, suele ser óptimo ejecutar el código como un trabajo de consulta de SQL estándar.

En el siguiente ejemplo, se muestra una UDF de JavaScript. El código JavaScript se coloca entre comillas dentro de una string sin procesar.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  return x*y;
""";

WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) AS product
FROM numbers;

En este ejemplo, se produce el siguiente resultado:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

En el siguiente ejemplo, se suman los valores de todos los campos llamados foo en la string JSON dada.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  function SumFoo(obj) {
    var sum = 0;
    for (var field in obj) {
      if (obj.hasOwnProperty(field) && obj[field] != null) {
        if (typeof obj[field] == "object") {
          sum += SumFoo(obj[field]);
        } else if (field == "foo") {
          sum += obj[field];
        }
      }
    }
    return sum;
  }
  var row = JSON.parse(json_row);
  return SumFoo(row);
""";

WITH Input AS (
  SELECT
    STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s,
    10 AS foo
  UNION ALL
  SELECT
    NULL,
    4 AS foo
  UNION ALL
  SELECT
    STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s,
    NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;

En el ejemplo, se produce el siguiente resultado:

+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Tipos de datos de UDF de JavaScript admitidos

Algunos tipos de SQL se corresponden de forma directa con tipos de JavaScript, pero otros no. BigQuery representa los tipos de la siguiente manera:

Tipo de datos de BigQuery Tipo de datos de JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING codificada en base64
FLOAT64 NÚMERO
NUMERIC, BIGNUMERIC Si un valor NUMERIC o BIGNUMERIC se puede representar de forma exacta como un valor de punto flotante IEEE 754 y no tiene una parte fraccionaria, se codifica como un Número. Estos valores están en el rango [-253, 253]. De lo contrario, el valor se codifica como una cadena.
STRING STRING
STRUCT OBJECT en el que cada campo de STRUCT tiene nombre
TIMESTAMP DATE con un campo de microsegundos que contiene la fracción de microsecond de la marca de tiempo
DATE DATE
JSON

Los OBJECTS, ARRAY y VALUES de JSON se convierten en OBJETOS, ARRAY y VALORES de JavaScript equivalentes.

JavaScript no admite valores INT64. Solo los números JSON en el rango [-253, 253] se convierten con exactitud. De lo contrario, el valor numérico se redondea, lo que podría generar una pérdida de precisión.

Debido a que JavaScript no admite un tipo de número entero de 64 bits, INT64 no es compatible como tipo de entrada para UDF de JavaScript. En su lugar, usa FLOAT64 a fin de representar valores como un número entero, o STRING para representarlos como una string.

BigQuery admite INT64 como un tipo de datos que se muestra en las UDF de JavaScript. En este caso, el cuerpo de la función de JavaScript puede mostrar una String o un Número de JavaScript. BigQuery luego convierte cualquiera de estos tipos en INT64.

Si el valor de retorno de la UDF de JavaScript es una Promise, BigQuery espera por la Promise hasta que se establezca la Promise. Si Promise se establece en un estado entregado, BigQuery muestra su resultado. Si Promise se establece en un estado rechazado, BigQuery muestra un error.

Reglas de entrecomillado

Debes encerrar el código de JavaScript entre comillas. Para fragmentos de código simples de una línea, puedes usar una string entre comillas estándar:

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";

SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

En este ejemplo, se produce el siguiente resultado:

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

En los casos en que el fragmento contiene comillas o consta de varias líneas, usa bloques con comillas triples:

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js
AS r"""
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
""";

SELECT customGreeting(names) AS everyone
FROM UNNEST(['Hannah', 'Max', 'Jakob']) AS names;

En este ejemplo, se produce el siguiente resultado:

+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

Incluye bibliotecas de JavaScript

Puedes extender tus UDF de JavaScript con la sección OPTIONS. En esta sección, se permite especificar bibliotecas de código externas para la UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
RETURNS STRING
LANGUAGE js
  OPTIONS (
    library=['gs://my-bucket/path/to/lib1.js', 'gs://my-bucket/path/to/lib2.js'])
AS r"""
  // Assumes 'doInterestingStuff' is defined in one of the library files.
  return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

En el ejemplo anterior, el código en lib1.js y lib2.js está disponible para cualquier código en la sección [external_code] de la UDF.

Recomendaciones para las UDF de JavaScript

Realiza un filtro previo a tu entrada

Si tu entrada se puede filtrar antes de pasarla a una UDF de JavaScript, tu consulta podría ser más rápida y económica.

Evita el estado mutable persistente

No almacenes ni accedas a un estado mutable en llamadas a la UDF de JavaScript. Por ejemplo, evita el siguiente patrón:

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

Usa la memoria de manera eficaz

El entorno de procesamiento de JavaScript tiene un límite de memoria disponible por consulta. Las consultas de UDF de JavaScript que acumulan demasiado estado local pueden fallar debido al agotamiento de la memoria.

Autoriza rutinas

Puedes autorizar UDF como rutinas. Las rutinas autorizadas te permiten compartir resultados de consultas con usuarios o grupos específicos sin darles acceso a las tablas subyacentes que generaron los resultados. Por ejemplo, una rutina autorizada puede calcular una agregación en los datos o buscar un valor de tabla y usar ese valor en un cálculo. Para obtener más información, consulta Rutinas autorizadas.

Agrega descripciones a las UDF

Para agregar una descripción a una UDF, sigue estos pasos:

Console

  1. Ve a la página de BigQuery en la consola de Google Cloud.

    Ir a BigQuery

  2. En el panel Explorador, expande tu proyecto y conjunto de datos y, luego, selecciona la función.

  3. En el panel Detalles, haz clic en Editar detalles de rutina para editar el texto de la descripción.

  4. En el diálogo, ingresa una descripción en la casilla o edita la descripción existente. Haz clic en Guardar para guardar el nuevo texto de la descripción.

SQL

Para actualizar la descripción de una función, vuelve a crearla con la CREATE FUNCTIONdeclaración DDL y establece el campo description en la lista OPTIONS:

  1. En la consola de Google Cloud, ve a la página de BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, escribe la siguiente sentencia:

    CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

  3. Haz clic en Ejecutar.

Para obtener más información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Crea rutinas de enmascaramiento personalizadas

Puedes crear UDF para usar con rutinas de enmascaramiento personalizadas. Las rutinas de enmascaramiento personalizadas deben cumplir con los siguientes requisitos:

  • La rutina de enmascaramiento personalizada debe ser una UDF de SQL.
  • En la función OPTIONS, la opción data_governance_type debe configurarse como DATA_MASKING.
  • Las rutinas de enmascaramiento personalizadas admiten las siguientes funciones:
  • Las rutinas de enmascaramiento personalizadas pueden aceptar ninguna entrada o una entrada de tipos de datos de BigQuery, excepto GEOGRAPHY y STRUCT. GEOGRAPHY y STRUCT no son compatibles con las rutinas de enmascaramiento personalizadas.
  • No se admiten los parámetros de UDF de SQL con plantilla.
  • Cuando se proporciona una entrada, los tipos de datos de entrada y de salida deben ser los mismos.
  • Se debe proporcionar un tipo de salida.
  • En el cuerpo de la definición, no se puede hacer referencia a otras UDF, subconsultas, tablas o vistas.
  • Después de crear una rutina de enmascaramiento, esta no se puede cambiar a una función estándar. Esto significa que si la opción data_governance_type se configura como DATA_MASKING, no puedes cambiar data_governance_type mediante sentencias de DDL ni llamadas a la API.

Por ejemplo, una rutina de enmascaramiento que reemplaza el número de seguridad social de un usuario por XXX-XX-XXXX podría verse de la siguiente manera:

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

En el siguiente ejemplo, se genera un hash con el sal proporcionado por el usuario, a través de la función SHA256:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

En el siguiente ejemplo, se enmascara una columna DATETIME con un valor constante:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

Como práctica recomendada, utiliza el prefijo SAFE cuando sea posible, para evitar exponer datos sin procesar a través de mensajes de error.

Después de crear la rutina de enmascaramiento personalizada, estará disponible como una regla de enmascaramiento en Crea políticas de datos.

Funciones que aporta la comunidad

Las UDF que aporta la comunidad están disponibles en el conjunto de datos públicos bigquery-public-data.persistent_udfs y en el repositorio de GitHub bigquery-utils de código abierto. Para ver todas las UDF de la comunidad en la consola de Google Cloud, destaca el proyecto bigquery-public-data en el panel Explorador y, luego, expande el conjunto de datos anidado persistent_udfs dentro de ese proyecto.

Si deseas contribuir a las UDF de este repositorio, consulta Cómo contribuir con UDF para obtener instrucciones.

Limitaciones

Las siguientes limitaciones se aplican a funciones temporales y persistentes definidas por el usuario:

  • No se admiten los objetos DOM Window, Document y Node, ni las funciones que los requieren.
  • Las funciones de JavaScript que dependen del código nativo pueden fallar, por ejemplo, si realizan llamadas restringidas al sistema.
  • Una UDF de JavaScript puede agotar el tiempo de espera y evitar que se complete tu consulta. Los tiempos de espera pueden ser de tan solo 5 minutos, pero pueden variar según distintos factores, incluidos el tiempo de CPU del usuario que consume tu función y el tamaño de las entradas y salidas en la función de JavaScript.
  • Las operaciones a nivel de bits en JavaScript manejan solo los 32 bits más significativos
  • Las UDF están sujetas a ciertos límites de frecuencia y límites de cuota. Para obtener más información, consulta Límites de UDF.

Las siguientes limitaciones se aplican a las funciones persistentes definidas por el usuario:

  • Cada conjunto de datos solo puede contener una UDF persistente con el mismo nombre. Sin embargo, puedes crear una UDF cuyo nombre sea igual al de una tabla en el mismo conjunto de datos.
  • Cuando hagas referencia a una UDF persistente desde otra UDF persistente o una vista lógica, debes calificar el nombre con el conjunto de datos. Por ejemplo:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

Las limitaciones siguientes se aplican a las funciones temporales definidas por el usuario.

  • Cuando creas una UDF temporal, el function_name no puede contener puntos.
  • Las vistas lógicas y las UDF persistentes no pueden hacer referencia a las UDF temporales.