Funciones definidas por el usuario en SQL heredado

Este documento detalla cómo usar las funciones definidas por el usuario de JavaScript en la sintaxis de consulta de SQL heredada. La sintaxis de consulta preferida para BigQuery es GoogleSQL. Para obtener información sobre las funciones definidas por el usuario en GoogleSQL, consulta Funciones definidas por el usuario de GoogleSQL.

El SQL heredado de BigQuery es compatible con las funciones definidas por el usuario (UDF) redactadas en JavaScript. Una UDF es similar a la función “Asignar” en un MapReduce: toma una sola fila como entrada y produce cero o más filas como salida. La salida podría tener un esquema diferente al de la entrada.

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

Ejemplo de UDF

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Volver al principio

Estructura de las UDF

function name(row, emit) {
  emit(<output data>);
}

Las UDF de BigQuery operan en filas individuales de una tabla o subseleccionan los resultados de la consulta. Las UDF tienen dos parámetros formales:

row representa una fila de entrada.
emit es un enlace que usa BigQuery para recopilar datos de salida. La función emit toma un parámetro: un objeto JavaScript que representa una sola fila de datos de salida. Se puede llamar a la función emit más de una vez; por ejemplo, se puede repetir indefinidamente para generar múltiples filas de datos.

El siguiente ejemplo de código muestra una UDF básica.

function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

Cómo registrar la UDF

Debes registrar un nombre para tu función a fin de que esta pueda ser invocada desde el SQL de BigQuery. El nombre registrado no tiene que coincidir con el nombre que usaste para tu función en JavaScript.

bigquery.defineFunction(
  '<UDF name>',  // Name used to call the function from SQL

  ['<col1>', '<col2>'],  // Input column names

  // JSON representation of the output schema
  [<output schema>],

  // UDF definition or reference
  <UDF definition or reference>
);

Columnas de entrada

Los nombres de las columnas de entrada deben coincidir con los nombres (o alias, si corresponde) de las columnas en la tabla o subconsulta de entrada.

Para las columnas de entrada que son registros, debes especificar, en la lista de columnas de entrada, los campos de hoja a los que quieres acceder desde el registro.

Por ejemplo, si tienes un registro que almacena el nombre y la edad de una persona, se dan las situaciones siguientes:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

El especificador de entrada para el nombre y la edad sería el siguiente:

['person.name', 'person.age']

El uso de ['person'] sin el nombre o la edad generaría un error.

La salida resultante coincidirá con el esquema; tendrás un arreglo de objetos JavaScript, en el que cada objeto tiene una propiedad de "nombre" y "edad". Por ejemplo:

[ {name: 'alice', age: 23}, {name: 'bob', age: 64}, ... ]

Esquema de salida

Debes proporcionar a BigQuery el esquema o la estructura de los registros que produce tu UDF, representados como JSON. El esquema puede contener cualquier tipo de datos compatibles con BigQuery, incluidos los registros anidados. Los especificadores de tipo compatibles son:

booleano
flotante
número entero
registro
string
marca de tiempo

El siguiente ejemplo de código muestra la sintaxis de los registros en el esquema de salida. Cada campo de salida requiere un atributo name y type. Los campos anidados también deben contener un atributo fields.

[{name: 'foo_bar', type: 'record', fields:
  [{name: 'a', type: 'string'},
   {name: 'b', type: 'integer'},
   {name: 'c', type: 'boolean'}]
}]

Cada campo puede contener un atributo opcional mode, que es compatible con los siguientes valores:

Nulo: Este es el valor predeterminado y puede omitirse.
requerido: si se especifica, el campo dado debe configurarse en un valor y no puede ser indefinido.
Repetido: Si se especifica, el campo dado debe ser un arreglo.

Las filas que se pasan a la función emit() deben coincidir con los tipos de datos del esquema de salida. Los campos representados en el esquema de salida que se omiten en la función de emisión se mostrarán como nulos.

Definición o referencia de la UDF

Si lo prefieres, puede definir la UDF intercalada en bigquery.defineFunction. Por ejemplo:

bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  // The UDF
  function(row, emit) {
    emit({title: decodeURI(row.title),
          requests: row.num_requests});
  }
);

De lo contrario, puedes definir la UDF por separado y pasar una referencia a la función en bigquery.defineFunction. Por ejemplo:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeURI(row.title),
        requests: row.num_requests});
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Manejo de errores

Si se produce una excepción o error durante el procesamiento de una UDF, la consulta completa fallará. Puedes usar un bloque try-catch para manejar los errores. Por ejemplo:

// The UDF
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Ejecuta una consulta con una UDF

Puedes usar UDF en SQL heredado con la herramienta de línea de comandos de bq o la API de BigQuery. La consola de Google Cloud no admite UDF en SQL heredado.

Usa la herramienta de línea de comandos de bq

Para ejecutar una consulta que contenga una o más UDF, especifica la marca --udf_resource en la herramienta de línea de comandos de bq desde Google Cloud CLI. El valor de la marca puede ser una URI (gs://...) de Cloud Storage o la ruta a un archivo local. Para especificar varios archivos de recursos de la UDF, repite esta marca.

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

bq query --udf_resource=<file_path_or_URI> <sql_query>

El siguiente ejemplo ejecuta una consulta que utiliza una UDF almacenada en un archivo local y una consulta de SQL que también está almacenada en un archivo local.

Cómo crear la UDF

Puede almacenar la UDF en Cloud Storage o como un archivo de texto local. Por ejemplo, para almacenar la siguiente UDF urlDecode, crea un archivo llamado urldecode.js y pega el siguiente código JavaScript en el archivo antes de guardarlo.

// UDF definition
function urlDecode(row, emit) {
  emit({title: decodeHelper(row.title),
        requests: row.num_requests});
}

// Helper function with error handling
function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

// UDF registration
bigquery.defineFunction(
  'urlDecode',  // Name used to call the function from SQL

  ['title', 'num_requests'],  // Input column names

  // JSON representation of the output schema
  [{name: 'title', type: 'string'},
   {name: 'requests', type: 'integer'}],

  urlDecode  // The function reference
);

Crea la consulta

También puedes almacenar la consulta en un archivo para evitar que tu línea de comandos se vuelva demasiado detallada. Por ejemplo, puedes crear un archivo local con el nombre query.sql y pegar la siguiente declaración de BigQuery en él.

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [fh-bigquery:wikipedia.pagecounts_201504]
    WHERE language = 'fr'
    GROUP EACH BY title
  )
WHERE title LIKE '%ç%'
ORDER BY requests DESC
LIMIT 100

Después de guardar el archivo, puedes hacer referencia al archivo en la línea de comandos.

Cómo ejecutar la consulta

Después de definir la UDF y la consulta en archivos por separado, puedes hacer referencia a ellos en la línea de comandos. Por ejemplo, con el comando siguiente, se ejecuta la consulta que guardaste como el archivo llamado query.sql y hace referencia a la UDF que creaste.

$ bq query --udf_resource=urldecode.js "$(cat query.sql)"

Usa la API de BigQuery

configuration.query

Las consultas que usan UDF deben contener elementos de userDefinedFunctionResources que proporcionen el código o las ubicaciones de los recursos del código que se usarán en la consulta. El código suministrado debe incluir invocaciones de la función de registro para cualquier UDF a la que la consulta hizo referencia.

Recursos del código

La configuración de tu consulta puede incluir BLOB de código JavaScript y referencias a archivos fuente JavaScript almacenados en Cloud Storage.

Los BLOB de código JavaScript intercalados se propagan en la sección inlineCode de un elemento userDefinedFunctionResource. Sin embargo, el código que se volverá a usar o al que se hará referencia en múltiples consultas debe ser persistente en Cloud Storage y usarse a modo de referencia como recurso externo.

Para hacer referencia a un archivo fuente JavaScript desde Cloud Storage, configura la sección resourceURI del elemento userDefinedFunctionResource en el URI gs:// del archivo.

La configuración de la consulta puede contener varios elementos userDefinedFunctionResource. Cada elemento puede contener una sección inlineCode o resourceUri.

Ejemplo

En el siguiente ejemplo de JSON, se muestra una solicitud de consulta que hace referencia a dos recursos de la UDF: un blob de código intercalado y un archivo lib.js para leer desde Cloud Storage. En este ejemplo, lib.js proporciona myFunc y la invocación de registro para myFunc.

{
  "configuration": {
    "query": {
      "userDefinedFunctionResources": [
        {
          "inlineCode": "var someCode = 'here';"
        },
        {
          "resourceUri": "gs://some-bucket/js/lib.js"
        }
      ],
      "query": "select a from myFunc(T);"
    }
  }
}

Volver al principio

Recomendaciones

Desarrolla tu UDF

Puedes usar nuestra herramienta de prueba de UDF a fin de probar y depurar tu UDF sin aumentar tu facturación de BigQuery.

Aplica un filtro previo a tu entrada

Si tu entrada se puede filtrar con facilidad antes de pasarla a una UDF, es probable que tu consulta sea más rápida y económica.

En el ejemplo sobre cómo ejecutar una consulta, se pasa una subconsulta como la entrada de urlDecode, en lugar de una tabla completa. La tabla [fh-bigquery:wikipedia.pagecounts_201504] tiene unas 5.6 mil millones de filas, y si ejecutamos la UDF en toda la tabla, el marco de trabajo de JavaScript deberá procesar más de 21 veces más filas de lo que lo haría si la subconsulta estuviese filtrada.

Evita el estado mutable persistente

No almacenes ni accedas al estado mutable a través de llamadas de la UDF. En el siguiente ejemplo de código, se describe esta situación:

// myCode.js
var numRows = 0;

function dontDoThis(r, emit) {
  emit({rowCount: ++numRows});
}

// The query.
SELECT max(rowCount) FROM dontDoThis(t);

El ejemplo anterior no se comportará como se espera, porque BigQuery fragmenta tu consulta en muchos nodos. Cada nodo tiene un entorno de procesamiento de JavaScript independiente que acumula valores separados para numRows.

Usa la memoria de manera eficaz

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

Expande las consultas select

Debes enumerar de forma explícita las columnas que se seleccionan de una UDF. SELECT * FROM <UDF name>(...) no es compatible.

Si deseas examinar la estructura de los datos de la fila de entrada, puedes usar JSON.stringify() para emitir una columna de salida de string:

bigquery.defineFunction(
  'examineInputFormat',
  ['some', 'input', 'columns'],
  [{name: 'input', type: 'string'}],
  function(r, emit) {
    emit({input: JSON.stringify(r)});
  }
);

Volver al principio

Límites

Cantidad de datos que tu UDF genera cuando procesa una fila única: unos 5 MB o menos.
Cada usuario está limitado a ejecutar unas 6 consultas de UDF en un proyecto específico al mismo tiempo. Si recibes un error que indica que superaste el límite de consultas simultáneas, espera unos minutos y vuelve a intentarlo.
Una UDF puede agotar el tiempo de espera y, además, impedir que se complete tu consulta. Los tiempos de espera pueden durar tan poco como 5 minutos, pero pueden variar dependiendo de varios factores, incluidos el tiempo de CPU del usuario que consume tu función y el tamaño de tus entradas y salidas a la función JS.
Un trabajo de consulta puede tener un máximo de 50 recursos de UDF (blobs de códigos intercalados o archivos externos).
Cada blob de códigos intercalados tiene un límite de tamaño de 32 KB. Para utilizar recursos de código más grandes, almacena tu código en Cloud Storage y haz referencia a este como recurso externo.
Cada recurso de código externo tiene un límite de tamaño de 1 MB.
El tamaño acumulativo de todos los recursos de código externo se limita a un máximo de 5 MB.

Volver al principio

Limitaciones

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é.

Volver al principio