Funciones definidas por el usuario de SQL estándar

BigQuery admite funciones definidas por el usuario (UDF). Una UDF te permite crear una función con otra expresión SQL o con otro lenguaje de programación, como JavaScript. Estas funciones aceptan columnas de entrada y realizan acciones para luego mostrar el resultado de esas acciones como un valor. Para obtener información sobre las funciones definidas por el usuario en el SQL heredado, consulta Funciones definidas por el usuario en el SQL heredado.

Las UDF son temporales. Esto significa que solo puedes usarlas para la consulta o sesión de línea de comandos actual. No uses la pestaña Editor de UDF en la IU web cuando se crea una función definida por el usuario para usar con consultas de SQL estándar. La pestaña Editor de UDF se usa con SQL heredado.

Sintaxis general de UDF

Las funciones definidas por el usuario en BigQuery usan la siguiente sintaxis general:

CREATE  { TEMPORARY | TEMP }  FUNCTION
  function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  { [LANGUAGE language AS """body"""] | [AS (function_definition)] };

named_parameter:
  param_name param_type

Esta sintaxis consta de los siguientes componentes:

  • CREATE { TEMPORARY | TEMP } FUNCTION. Crea una función nueva. Una función puede contener cero o más named_parameter. Debes incluir TEMPORARY o TEMP cuando crees una UDF.
  • named_parameter. Consiste en un par de param_name y param_type separados por comas. El valor de param_type es un tipo de datos de BigQuery. Para una UDF de SQL, el valor de param_type también puede ser ANY TYPE.
  • [RETURNS data_type]. Especifica el tipo de datos que muestra la función. Si la función está definida en SQL, entonces la cláusula RETURNS es opcional y BigQuery deduce el tipo de resultado de la función a partir del cuerpo de la función SQL. Si la función está definida en un lenguaje externo, la cláusula RETURNS es obligatoria. Consulta Tipos de datos de UDF admitidos a fin de obtener más información sobre los valores permitidos para data_type.
  • [LANGUAGE lenguaje AS """body"""]. Especifica el lenguaje externo para la función y el código que la define.
  • AS (function_definition). Especifica el código SQL que define la función. function_definition es una expresión SQL.

Estructura de UDF externas

Crea UDF externas con la siguiente estructura.

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  [LANGUAGE language]
  AS external_code

Ejemplos de UDF externas

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  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;

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

Puedes crear múltiples UDF antes de una consulta. Por ejemplo:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x / 2;
""";
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,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

Puedes pasar el resultado de una UDF como entrada para otra UDF. Por ejemplo:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x/2;
""";
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(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

El siguiente ejemplo suma los valores de todos los campos llamados “foo” en la string de JSON dada.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS """
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;
+---------------------------------------------------------------------+---------+
| 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    |
+---------------------------------------------------------------------+---------+

Lenguajes de UDF externas admitidos

Las UDF externas admiten el código escrito en JavaScript, que se especifica con js como LANGUAGE. Por ejemplo:

CREATE TEMP FUNCTION greeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  return "Hello, " + a + "!";
  """;
SELECT greeting(name) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS name;

+----------------+
| everyone       |
+----------------+
| Hello, Hannah! |
| Hello, Max!    |
| Hello, Jakob!  |
+----------------+

Consulta las Codificaciones de tipos de SQL en JavaScript para obtener información sobre cómo los tipos de datos de BigQuery se corresponden con los tipos de JavaScript.

Tipos de datos de UDF externas admitidos

Para UDF externas, BigQuery admite los siguientes tipos de datos:

  • ARRAY
  • BOOL
  • BYTES
  • DATE
  • FLOAT64
  • NUMERIC
  • STRING
  • STRUCT
  • TIMESTAMP

Codificaciones de tipos de SQL en JavaScript

Algunos tipos de SQL se corresponden de forma directa con tipos de JavaScript, pero otros no.

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 enteros como un número, o STRING para representarlos como una string.

BigQuery admite INT64 como un tipo mostrado 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.

BigQuery representa los tipos de la siguiente manera:

Tipos de datos de BigQuery Tipo de datos de JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING codificada en base64
FLOAT64 NUMBER
NUMERIC 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

Reglas de citación

Debes encerrar el código externo 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 de código contiene comillas o consta de varias líneas, usa bloques con comillas triples:

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  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!  |
+-----------------------+

Estructura de UDF de SQL

Crea UDF de SQL con la siguiente sintaxis:

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Parámetros de UDF de SQL con plantilla

Un parámetro con plantilla puede coincidir con más de un tipo de argumento en el tiempo de llamada de una función. Si una firma de función incluye un parámetro con plantilla, BigQuery permite que las llamadas de función pasen uno de varios tipos de argumentos a la función.

Las firmas de funciones definidas por el usuario de SQL pueden contener el siguiente valor de param_type con plantilla:

  • ANY TYPE. La función aceptará una entrada de cualquier tipo para este argumento. Si hay más de un parámetro con el tipo ANY TYPE, BigQuery no impone ninguna relación entre estos argumentos en el momento de la creación de la función. Sin embargo, pasar los argumentos de la función de tipos que son incompatibles con la definición de la función dará lugar a un error en el momento de la llamada.

Ejemplos de UDF de SQL

En el siguiente ejemplo, se muestra una UDF que emplea una función de SQL.

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);
WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

+-----+--------+
| val | result |
+-----+--------+
| 1   | 2.5    |
| 3   | 3.5    |
| 4   | 4      |
| 5   | 4.5    |
+-----+--------+

En el siguiente ejemplo, se muestra una UDF de SQL que usa un parámetro con plantilla. La función resultante acepta argumentos de varios tipos.

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;

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

En el siguiente ejemplo, se muestra una UDF de SQL que usa un parámetro con plantilla para mostrar el último elemento de un arreglo de cualquier tipo.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);
SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

Incluye bibliotecas externas

Puedes ampliar tus UDF externas mediante la sección OPTIONS. Esta sección te permite especificar bibliotecas de código externas para la UDF.

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

SELECT myFunc(3.14, 'foo');

En el ejemplo anterior, el código en lib1.js, lib2.js y lib3.js está disponible para cualquier código de la sección [external_code] de la UDF. Ten en cuenta que puedes especificar archivos de biblioteca mediante la sintaxis de un solo elemento o de arreglo.

Las UDF y la IU web

Puedes usar la IU web de BigQuery para ejecutar consultas con una o más UDF.

Ejecuta una consulta con una UDF

  1. Haz clic en el botón REDACTAR CONSULTA.
  2. Haz clic en la pestaña Editor de consultas.
  3. Haz clic en el botón Mostrar opciones.
  4. Desmarca la casilla de verificación Usar SQL heredado.
  5. Escribe la declaración de UDF en el área de texto del Editor de consultas. Por ejemplo:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. Escribe tu consulta debajo de la declaración de UDF. Por ejemplo:

    SELECT timesTwo(numbers) as doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. Haz clic en el botón EJECUTAR CONSULTA. Los resultados de la consulta se muestran debajo de los botones.

Las UDF y la herramienta de línea de comandos de bq

Puedes usar la herramienta de línea de comandos bq del SDK de Google Cloud para ejecutar una consulta que contenga una o más UDF.

Usa la siguiente sintaxis para ejecutar una consulta con una UDF:

bq query <statement_with_udf_and_query>

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 memoria limitada disponible por consulta. Las consultas de las UDF de JavaScript que acumulan demasiado estado local pueden fallar debido al agotamiento de la memoria.

Límites

  • La cantidad de datos que genera tu UDF de JavaScript cuando procesa una fila individual debe ser de alrededor de 5 MB o menos.
  • Cada usuario está limitado a ejecutar alrededor de 6 consultas de UDF de JavaScript en un proyecto específico al mismo tiempo. Si recibes un error que indica que superas el límite de consultas simultáneas, espera unos minutos y vuelve a intentarlo.
  • Una UDF de JavaScript puede agotar el tiempo de espera y evitar que se complete tu consulta. Los tiempos de espera pueden ser de 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 tus entradas y salidas en la función de JS.
  • Un trabajo de consulta puede tener un máximo de 50 recursos de UDF de JavaScript (archivos externos o blobs de código intercalados).
  • Cada blob de códigos intercalados tiene un límite de tamaño de 32 KB.
  • Cada recurso de código externo tiene un límite de tamaño de 1 MB.

Limitaciones

  • Para las UDF temporales, function_name no puede contener puntos.
  • No se admiten los objetos DOM Window, Document y Node, ni las funciones que los requieren.
  • Las funciones de JavaScript que se basan en el código nativo no son compatibles.
  • Las operaciones a nivel de bits en JavaScript manejan solo los 32 bits más significativos.
  • Debido a su naturaleza no determinista, las consultas que invocan funciones definidas por el usuario no pueden usar resultados almacenados en caché.
  • No puedes hacer referencia a una tabla en una UDF.
¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

Si necesitas ayuda, visita nuestra página de asistencia.