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 las consultas preferidas para BigQuery es SQL estándar. Para obtener información sobre las funciones definidas por el usuario en SQL estándar, consulta Funciones de SQL estándar definidas por el usuario.

El SQL heredado de BigQuery es compatible con las funciones definidas por el usuario (UDF) escritas en JavaScript. Una UDF es similar a la función "Mapa" 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 SQL estándar, consulta Funciones definidas por el usuario en SQL estándar.

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: una fila de entrada.
  • emit: un gancho utilizado por 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. La función emit se puede llamar más de una vez, por ejemplo, repetirse 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 para a fin de que esta pueda ser invocada desde la SQL de BigQuery. El nombre registrado no tiene que coincidir con el nombre que utilizaste para su 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 deseas acceder desde el registro.

Por ejemplo, si tienes un registro que almacena el nombre y la edad de una persona:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

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

['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 donde 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 mode opcional, 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 pasadas 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, puedes 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
);

Las UDF y la IU web

Puedes utilizar la IU de BigQuery para agregar UDF y usarlas cuando ejecutes consultas.

Requisitos previos

Para utilizar la IU de BigQuery, tu cuenta debe tener acceso a un proyecto con BigQuery habilitado en Google Cloud Platform Console.

  1. Si no has utilizado GCP Console antes, ve a la consola, acepta las condiciones del servicio y crea un proyecto nuevo.

  2. Navega hacia la IU web.

Cómo añadir la UDF

  1. Haz clic en el botón REDACTAR UNA CONSULTA.

  2. Haz clic en la pestaña Editor de UDF para agregar la UDF.

  3. Copia y pega el siguiente código en el área de texto del Editor de UDF:

    // The UDF
    function urlDecode(row, emit) {
      emit({title: decodeHelper(row.title),
            requests: row.num_requests});
    }
    
    // Helper function for 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
    );
    

Cómo ejecutar una consulta en la IU web

  • Haz clic en la pestaña Editor de consultas.

  • Copia y pega la siguiente consulta en el área de texto del Editor de consultas.

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

    La consulta anterior busca los artículos de Wikipedia en francés más visitados de abril de 2015, que contienen un carácter de cedilla (ç) en el título.

  • Haz clic en el botón EJECUTAR CONSULTA. Los resultados de la consulta se muestran debajo de los botones.

Código de referencia de Google Cloud Storage

En el ejemplo anterior, agregaste la UDF directamente a la IU web de BigQuery. Como alternativa, puedes almacenar todo o parte de tu código JavaScript en Google Cloud Storage . Independientemente de dónde se encuentre el código UDF, cada UDF debe registrarse utilizando una invocación bigquery.defineFunction en tu código. Se puede proporcionar la invocación bigquery.definefunction en la IU web o en recursos de código remotos. Los archivos fuente remotos deben tener la extensión ".js".

Por ejemplo, puedes mantener bibliotecas de terceros, tu propio código UDF y llamadas a función de registro de la UDF en archivos separados. Estos archivos deberían cargarse como un recurso externo por separado en tu consulta.

Cómo hacer referencia a una UDF externa en la IU web

  1. Haz clic en el botón Mostrar opciones debajo del área de texto.

  2. Haz clic en el botón Editar junto al encabezado URI fuente de la UDF.

  3. Para cada archivo fuente remoto, haz clic en el botón Añadir URI fuente a la UDF y luego ingresa el URI de Google Cloud Storage.

    Si deseas probar el ejemplo anterior de decodificación de URL mediante una UDF externa, pega bigquery-sandbox-udf/url_decode.js en el campo del URI. Después de completar estos pasos, asegúrate de borrar los contenidos del Editor de UDF.

  4. Haz clic en el botón Aceptar.

Luego, puedes cambiar a la pestaña Editor de consultas y seguir los mismos pasos que en el ejemplo anterior para usar la UDF en una consulta.

En general, al usar UDF externas, también puedes agregar código JavaScript adicional en el área de texto del Editor de UDF, siempre y cuando las UDF adicionales estén registradas en un bloque defineFunction en la IU web o en un archivo externo.

También puede utilizar la herramienta de línea de comandos de bq para hacer referencia a una UDF almacenada en Cloud Storage. Consulta Las UDF y la herramienta de línea de comandos de bq para obtener más información.

Volver al principio

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

Puedes utilizar la herramienta de línea de comandos de bq del SDK de Google Cloud para ejecutar una consulta que contenga una o más UDF, especificando la marca --udf_resource. El valor de la marca puede ser un URI de Cloud Storage (gs://...) o la ruta a un archivo local. Puedes repetir esta marca para especificar varios archivos de recurso de la UDF.

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 UDF urlDecode mencionada en Las UDF y la IU web, 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
);

Cómo crear 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 llamado 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 separados, puedes hacer referencia a ellos en la línea de comandos. Por ejemplo, el siguiente comando 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)"

Las UDF y la API de BigQuery

configuration.query

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

Recursos del código

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

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

Para hacer referencia a un archivo fuente de JavaScript desde Google 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

El siguiente ejemplo de JSON 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 Google 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

Cómo desarrollar tu UDF

Puedes utilizar nuestra herramienta de prueba para UDF para probar y depurar tu UDF sin aumentar tu facturación de BigQuery.

Realiza un filtro previo a tu entrada

Si tu entrada se puede filtrar fácilmente 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 aproximadamente 5.6 mil millones de filas, y si ejecutamos la UDF en toda la tabla, el marco de trabajo de JavaScript deberá procesar sobre 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. El siguiente ejemplo de código describe este escenario:

// 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 divide 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 explícitamente las columnas seleccionadas de una UDF. SELECT * FROM <UDF name>(...) no es compatible.

Para 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 única fila: aproximadamente 5 MB o menos.
  • Cada usuario está limitado a ejecutar aproximadamente 6 consultas d la UDF 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 puede agotar el tiempo de espera e 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 la 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

  • Los objetos DOM Window, Document y Node, y las funciones que los requieren, no son compatibles.
  • 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 en caché.

Volver al principio

¿Te ha resultado útil esta página? Enviar comentarios:

Enviar comentarios sobre...

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