Funciones de SQL estándar 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 las UDF temporales en las secuencias de comandos 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 la llama desde una declaració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;

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_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

En este ejemplo, se produce el siguiente resultado:

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 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 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;

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 ejecuta la declaración CREATE FUNCTION.

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.

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 NUMBER
NUMERIC, BIGNUMERIC Si un valor NUMERIC 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, se codifica como una String.
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

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 del UDF de JavaScript es una Promise, BigQuery espera 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 citación

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;

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
+-----------+-----------+

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;
+-----------------------+
| 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 con facilidad antes de pasarla a una UDF de JavaScript, es probable que tu consulta sea 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.

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.

UDF autorizadas

Las funciones autorizadas te permiten compartir resultados de consultas con usuarios o grupos específicos sin darles a esos usuarios o grupos acceso a las tablas subyacentes. Por ejemplo, una función 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 Crea funciones 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 Cloud Console.

    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 el ícono de lápiz junto a Descripción 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 Actualizar para guardar el nuevo texto de la descripción.

Como alternativa, puedes usar una consulta de SQL estándar a fin de actualizar la descripción mediante el parámetro description del campo OPTIONS. En el cuadro Editor de consultas, ingresa la definición de la función y, luego, agrega la siguiente línea:

OPTIONS (description="DESCRIPTION") AS """

Reemplaza DESCRIPTION por la descripción que deseas agregar.

bq

Puedes editar la descripción de una función desde la línea de comandos mediante la sintaxis bq query de las UDF y la herramienta de línea de comandos de bq. Especifica SQL estándar con una marca --nouse_legacy_sql o -- use_legacy_sql=false y, luego, ingresa la definición de tu función. Para establecer el parámetro description en el campo OPTIONS, agrega la siguiente línea a tu definición:

OPTIONS (description="DESCRIPTION") AS """

Reemplaza DESCRIPTION por la descripción que deseas agregar.

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.