Funciones definidas por el usuario

Las funciones definidas por el usuario (FDU) te permiten crear una función mediante una expresión SQL o con código JavaScript. Una FDU acepta columnas de entrada, realiza acciones en la entrada y devuelve el resultado de esas acciones como un valor.

Puedes definir FDU persistentes o temporales. Puedes reutilizar FDUs persistentes en varias consultas, mientras que las FDUs temporales solo existen en el ámbito de una consulta.

Para crear una FDU, usa la instrucción CREATE FUNCTION. Para eliminar una función definida por el usuario persistente, usa la instrucción DROP FUNCTION. Las FDUs temporales caducan en cuanto finaliza la consulta. La instrucción DROP FUNCTION solo se admite con FDUs temporales en consultas de varias instrucciones y procedimientos.

Para obtener información sobre las funciones definidas por el usuario en SQL antiguo, consulta Funciones definidas por el usuario en SQL antiguo.

UDF de SQL

En el siguiente ejemplo se crea una función definida por el usuario de SQL temporal llamada AddFourAndDivide y se llama a la función desde una instrucción 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;

Este ejemplo genera 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 como función definida por el usuario persistente:

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

Como esta FDU es persistente, debes especificar un conjunto de datos para la función (mydataset en este ejemplo). Después de ejecutar la instrucció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 funciones definidas por el usuario de SQL con plantillas

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 más de un parámetro tiene el tipo ANY TYPE, BigQuery no aplica ninguna relación de tipo entre estos argumentos.
  • El tipo de valor devuelto de la función no puede ser ANY TYPE. Se debe omitir, lo que significa que se determinará automáticamente en función de sql_expression, o bien se debe indicar un tipo explícito.
  • Si se pasan argumentos de función de tipos incompatibles con la definición de la función, se produce un error en el momento de la llamada.

En el siguiente ejemplo se muestra una función definida por el usuario de SQL que usa un parámetro de 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;

Este ejemplo genera el siguiente resultado:

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

En el siguiente ejemplo se usa un parámetro de plantilla para devolver el último elemento de una matriz 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
);

Este ejemplo genera el siguiente resultado:

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

Subconsultas escalares

Una FDU de SQL puede devolver el valor de una subconsulta escalar. Una subconsulta escalar debe seleccionar una sola columna.

En el siguiente ejemplo se muestra una función definida por el usuario (UDF) de SQL que usa una subconsulta escalar para contar el número de usuarios con una edad determinada en una tabla de usuarios:

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;

Este ejemplo genera el siguiente resultado:

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

Proyecto predeterminado en expresiones SQL

En el cuerpo de una función definida por el usuario de SQL, cualquier referencia a entidades de BigQuery, como tablas o vistas, debe incluir el ID del proyecto, a menos que la entidad se encuentre en el mismo proyecto que contiene la función definida por el usuario.

Por ejemplo, considera la siguiente afirmación:

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

Si ejecutas esta instrucción desde project1 y mydataset.mytable existe en project1, la instrucción se ejecutará correctamente. Sin embargo, si ejecutas esta instrucción desde otro proyecto, se producirá un error. Para corregir el error, incluya 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 de otro proyecto o conjunto de datos distinto de aquel en el que creas la función:

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

Funciones definidas por el usuario de JavaScript

Las FDU de JavaScript te permiten llamar a código escrito en JavaScript desde una consulta SQL. Las UDFs de JavaScript suelen consumir más recursos de ranura que las consultas de SQL estándar, lo que reduce el rendimiento de los trabajos. Si la función se puede expresar en SQL, suele ser más óptimo ejecutar el código como un trabajo de consulta de SQL estándar.

En el siguiente ejemplo se muestra una función definida por el usuario de JavaScript. El código JavaScript se incluye entre comillas en una cadena sin formato.

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;

Este ejemplo genera 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 cadena JSON proporcionada.

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;

El ejemplo 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 asignan directamente a 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 codificado en base64
FLOAT64 NUMBER
NUMERIC, BIGNUMERIC Si un valor NUMERIC o BIGNUMERIC se puede representar exactamente como un valor de coma flotante IEEE 754 y no tiene parte fraccionaria, el valor se codifica como un número. Estos valores están en el intervalo [-253, 253]. De lo contrario, el valor se codifica como una cadena.
STRING STRING
STRUCT OBJECT donde cada campo STRUCT es un campo con nombre
TIMESTAMP DATE con un campo de microsegundos que contiene la microsecond fracción de la marca de tiempo
FECHA FECHA
JSON

Los OBJETOS, ARRAYS y VALORES JSON se convierten en OBJETOS, ARRAYS y VALORES de JavaScript equivalentes.

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

Como JavaScript no admite el tipo de entero de 64 bits, INT64 no se admite como tipo de entrada para las funciones definidas por el usuario de JavaScript. En su lugar, usa FLOAT64 para representar valores enteros como un número o STRING para representar valores enteros como una cadena.

BigQuery admite INT64 como tipo de valor devuelto en las funciones definidas por el usuario de JavaScript. En este caso, el cuerpo de la función JavaScript puede devolver un número o una cadena de JavaScript. BigQuery convierte cualquiera de estos tipos en INT64.

Si el valor devuelto de la función definida por el usuario de JavaScript es un Promise, BigQuery espera a que se resuelva Promise.Promise Si el Promise pasa al estado "Completado", BigQuery devuelve el resultado. Si el Promise pasa al estado rechazado, BigQuery devuelve un error.

Reglas de presupuestos

El código JavaScript debe ir entre comillas. En el caso de los fragmentos de código de una línea, puedes usar una cadena 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;

Este ejemplo genera el siguiente resultado:

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

En los casos en los que el fragmento contenga comillas o conste de varias líneas, utiliza bloques entre 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;

Este ejemplo genera el siguiente resultado:

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

Incluir bibliotecas de JavaScript

Puede ampliar sus funciones definidas por el usuario de JavaScript con la sección OPTIONS. En esta sección puede especificar bibliotecas de código externas para la función definida por el usuario.

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 de lib1.js y lib2.js está disponible para cualquier código de la sección [external_code] de la función definida por el usuario.

Prácticas recomendadas para las funciones definidas por el usuario de JavaScript

Prefiltra tu entrada

Si la entrada se puede filtrar antes de enviarse a una función definida por el usuario de JavaScript, la consulta puede ser más rápida y económica.

Evita el estado mutable persistente

No almacenes ni accedas a estados mutables en las llamadas a funciones definidas por el usuario 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()
""";

Usar la memoria de forma eficiente

El entorno de procesamiento de JavaScript tiene una memoria limitada disponible por consulta. Las consultas de funciones definidas por el usuario de JavaScript que acumulan demasiado estado local pueden fallar debido a que se agota la memoria.

Autorizar rutinas

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

Añadir descripciones a las funciones definidas por el usuario

Para añadir una descripción a una función definida por el usuario, sigue estos pasos:

Consola

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

    Ir a BigQuery

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

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

  4. En el cuadro de diálogo, introduce una descripción o edita la que ya haya. Haz clic en Guardar para guardar el nuevo texto de descripción.

SQL

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

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

    Ir a BigQuery

  2. En el editor de consultas, introduce la siguiente instrucción:

    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, consulta Ejecutar una consulta interactiva.

Crear rutinas de enmascaramiento personalizadas

.

Puedes crear funciones definidas por el usuario para usarlas con rutinas de enmascaramiento personalizadas. Deberías crear conjuntos de datos específicos y configurar los permisos de gestión de identidades y accesos adecuados para gestionar las funciones definidas por el usuario de enmascaramiento. Las rutinas de enmascaramiento personalizadas deben cumplir los siguientes requisitos:

  • La rutina de enmascaramiento personalizada debe ser una función definida por el usuario de SQL.
  • En la función OPTIONS, la opción data_governance_type debe ser 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, con la excepción de GEOGRAPHY y STRUCT. GEOGRAPHY y STRUCT no se admiten en las rutinas de enmascaramiento personalizadas.
  • No se admiten parámetros de funciones definidas por el usuario de SQL con plantillas.
  • Cuando se proporciona una entrada, los tipos de datos de entrada y salida deben ser los mismos.
  • Se debe proporcionar un tipo de salida.
  • No se puede hacer referencia a otras funciones definidas por el usuario, subconsultas, tablas ni vistas en el cuerpo de la definición.
  • Después de crear una rutina de enmascaramiento, no se puede cambiar a una función estándar. Esto significa que, si la opción data_governance_type está definida como DATA_MASKING, no puedes cambiar data_governance_type mediante instrucciones DDL o llamadas a la API.
  • Las rutinas de enmascaramiento personalizadas admiten las instrucciones CASE y CASE expr. Se pueden usar los siguientes operadores con las instrucciones CASE y CASE expr:

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

  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 aplican hashes con salt proporcionado por el usuario mediante 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 oculta 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 siempre que sea posible para evitar exponer datos sin procesar a través de mensajes de error.

Una vez que hayas creado la rutina de enmascaramiento personalizada, estará disponible como regla de enmascaramiento en Crear políticas de datos.

Funciones aportadas por la comunidad

Las UDFs aportadas por la comunidad están disponibles en el bigquery-public-data.persistent_udfs conjunto de datos público y en el repositorio de código abierto de bigquery-utils GitHub. Puedes ver todas las UDFs de la comunidad en la consola. Para ello, marca con una estrella el proyecto bigquery-public-data en el panel Explorador y, a continuación, expande el conjunto de datos persistent_udfs anidado en ese proyecto. Google Cloud

Permitir el acceso a funciones aportadas por la comunidad dentro de un perímetro de Controles de Servicio de VPC

En los proyectos en los que Controles de Servicio de VPC está habilitado y BigQuery es un servicio protegido, debes definir una regla de salida para el proyecto bigquery-public-data (ID de proyecto: 1057666841514).

Esta regla debe habilitar las siguientes operaciones:

  • bigquery.routines.get (para usar rutinas)
  • bigquery.tables.getData (para consultar tablas de BigQuery)

El siguiente código muestra un ejemplo de configuración YAML:

  - egressFrom:
      identityType: ANY_IDENTITY
    egressTo:
      operations:
      - serviceName: 'bigquery.googleapis.com'
        methodSelectors:
        - permission: 'bigquery.routines.get'
        - permission: 'bigquery.tables.getData'
      resources:
      - projects/1057666841514 # bigquery-public-data

Si quieres contribuir a las UDFs de este repositorio, consulta las instrucciones en Contribuir a las UDFs.

Acceso unificado a rutinas en varias regiones

Para usar FDUs en consultas de varias regiones, la FDU debe estar disponible en todas las regiones en las que se ejecute una consulta que la contenga. Por lo tanto, debes crear y mantener las FDUs en cualquier región en la que puedas usar la FDU en una consulta. Aunque las tablas sean idénticas, debes mantener dos versiones de la función. Por ejemplo, si almacena sus datos de ventas en las multirregiones EU y US, debe mantener una versión de la función en cada región. Por ejemplo:

Consulta en la multirregión EU: 

  SELECT 
    id,
    europe_dataset.my_function(value)
  FROM
    sales;

Consulta en la multirregión US: 

  SELECT 
    id,
    us_dataset.my_function(value)
  FROM
    sales;

Además, cuando cambie la definición de la función, deberá actualizarla en todas las regiones.

Para que tus funciones definidas por el usuario sean independientes de la región, puedes usar la réplica de conjuntos de datos entre regiones:

  1. Crea un conjunto de datos específico, por ejemplo my_utils, para almacenar todas las funciones definidas por el usuario que necesites. Recuerde que las tablas que se añadan a este conjunto de datos se replicarán, lo que aumentará el coste. Sin embargo, la replicación de funciones y procedimientos definidos por el usuario no conlleva ningún coste adicional.
  2. Añade todas tus funciones definidas por el usuario al nuevo conjunto de datos. También se pueden incluir funciones definidas por el usuario de la comunidad, como las bqutil copiadas de GitHub.
  3. Habilita la replicación del conjunto de datos. Configure este conjunto de datos para que se replique en todas las regiones en las que necesite ejecutar consultas que llamen a estas funciones definidas por el usuario. De esta forma, tus funciones se copiarán en estas regiones y se mantendrán sincronizadas.

Cuando ejecutas una consulta, BigQuery usa automáticamente la versión local de la función definida por el usuario de la réplica del conjunto de datos local sin que tengas que especificar la región en la que se define la función, lo que hace que tus consultas se puedan usar en diferentes ubicaciones. Por ejemplo:

  SELECT 
    id,
    my_utils.my_function(value)
  FROM
    sales;

Limitaciones

Se aplican las siguientes limitaciones a las funciones definidas por el usuario temporales y persistentes:

  • No se admiten los objetos DOM Window, Document y Node, ni las funciones que los requieren.
  • Las funciones de JavaScript operan en un entorno aislado y las que dependen del código del sistema subyacente pueden fallar debido a las llamadas al sistema restringidas.
  • Una FDU de JavaScript puede agotar el tiempo de espera e impedir que se complete la consulta. Los tiempos de espera pueden ser de tan solo 5 minutos, pero pueden variar en función de varios factores, como el tiempo de CPU de usuario que consume tu función y el tamaño de las entradas y salidas de la función JavaScript.
  • Las operaciones de bit a bit en JavaScript solo gestionan los 32 bits más importantes.
  • Las funciones definidas por el usuario están sujetas a ciertos límites de frecuencia y de cuota. Para obtener más información, consulta Límites de las funciones definidas por el usuario.

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

  • Cada conjunto de datos solo puede contener una función definida por el usuario persistente con el mismo nombre. Sin embargo, puede crear una función definida por el usuario cuyo nombre sea el mismo que el de una tabla del mismo conjunto de datos.
  • Cuando hagas referencia a una función definida por el usuario persistente desde otra función definida por el usuario persistente o una vista lógica, debes calificar el nombre con el conjunto de datos. Por ejemplo:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

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

  • Al crear una UDF temporal, function_name no puede contener puntos.
  • Las vistas y las FDU persistentes no pueden hacer referencia a FDU temporales.