Funciones definidas por el usuario en SQL antiguo

En este documento se explica cómo usar las funciones definidas por el usuario de JavaScript en la sintaxis de las consultas de SQL antiguo. 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 antiguo de BigQuery admite funciones definidas por el usuario (UDFs) escritas en JavaScript. Una función definida por el usuario es similar a la función "Map" de MapReduce: toma una sola fila como entrada y produce cero o más filas como salida. La salida puede tener un esquema diferente al de la entrada.

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

Ejemplo de FDU

// 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 FDU

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

Las UDFs de BigQuery operan en filas individuales de una tabla o en los resultados de una subconsulta. La función definida por el usuario tiene dos parámetros formales:

  • row: una fila de entrada.
  • emit: un hook que usa BigQuery para recoger datos de salida. La función emit usa 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, en un bucle, para generar varias filas de datos.

En el siguiente ejemplo de código se muestra una UDF básica.

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

Registrar la función definida por el usuario

Debe registrar un nombre para su función de forma que se pueda invocar desde SQL de BigQuery. No es necesario que el nombre registrado coincida con el que hayas usado 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 procede) de las columnas de la tabla de entrada o de la subconsulta.

En el caso de las columnas de entrada que sean registros, debes especificar en la lista de columnas de entrada los campos hoja a los que quieras acceder del registro.

Por ejemplo, si tiene 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 del nombre y la edad sería el siguiente:

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

Si se usa ['person'] sin el nombre o la edad, se generará un error.

El resultado coincidirá con el esquema: tendrás un array de objetos de JavaScript, donde cada objeto tiene una propiedad "name" y otra "age". Por ejemplo:

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

Esquema de salida

Debe proporcionar a BigQuery el esquema o la estructura de los registros que genera su función definida por el usuario, representados como JSON. El esquema puede contener cualquier tipo de datos de BigQuery admitido, incluidos los registros anidados. Los especificadores de tipo admitidos son los siguientes:

  • booleano
  • flotante
  • entero
  • registro
  • cadena
  • timestamp

En el siguiente ejemplo de código se muestra la sintaxis de los registros del esquema de salida. Cada campo de salida requiere los atributos 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 admite los siguientes valores:

  • nullable : es el valor predeterminado y se puede omitir.
  • Obligatorio : si se especifica, el campo en cuestión debe tener un valor y no puede ser indefinido.
  • Repeated : si se especifica, el campo en cuestión debe ser una matriz.

Las filas que se transfieran 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 omitan en la función emit se mostrarán como nulos.

Definición o referencia de UDF

Si lo prefieres, puedes definir la función definida por el usuario de forma insertada 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
);

Gestión de errores

Si se produce una excepción o un error durante el procesamiento de una función definida por el usuario, se producirá un error en toda la consulta. Puedes usar un bloque try-catch para gestionar 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
);

Ejecutar una consulta con una función definida por el usuario

Puedes usar funciones definidas por el usuario en SQL antiguo con la herramienta de línea de comandos bq o la API de BigQuery. La Google Cloud consola no admite funciones definidas por el usuario en SQL antiguo.

Usar la herramienta de línea de comandos bq

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

Usa la siguiente sintaxis para ejecutar una consulta con una función definida por el usuario:

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

En el siguiente ejemplo, se ejecuta una consulta que usa una función definida por el usuario almacenada en un archivo local y una consulta de SQL que también está almacenada en un archivo local.

Crear la función definida por el usuario

Puedes almacenar la función definida por el usuario en Cloud Storage o como archivo de texto local. Por ejemplo, para almacenar la siguiente urlDecode función definida por el usuario, 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
);

Crear la consulta

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

#legacySQL
SELECT requests, title
FROM
  urlDecode(
    SELECT
      title, sum(requests) AS num_requests
    FROM
      [my-project: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 a él en la línea de comandos.

Ejecutar la consulta

Después de definir la función definida por el usuario y la consulta en archivos independientes, puedes hacer referencia a ellos en la línea de comandos. Por ejemplo, el siguiente comando ejecuta la consulta que guardaste como archivo llamado query.sql y hace referencia a la función definida por el usuario que creaste.

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

Usar la API de BigQuery

configuration.query

Las consultas que usan funciones definidas por el usuario deben contener elementos userDefinedFunctionResources que proporcionen el código o las ubicaciones de los recursos de código que se van a usar en la consulta. El código proporcionado debe incluir invocaciones de funciones de registro para cualquier UDF al que haga referencia la consulta.

Recursos de código

La configuración de la consulta puede incluir blobs de código JavaScript, así como referencias a archivos de origen JavaScript almacenados en Cloud Storage.

Los blobs de código JavaScript insertado se rellenan en la sección inlineCode de un elemento userDefinedFunctionResource. Sin embargo, el código que se vaya a reutilizar o al que se vaya a hacer referencia en varias consultas debe conservarse en Cloud Storage y debe hacerse referencia a él como recurso externo.

Para hacer referencia a un archivo de origen JavaScript de Cloud Storage, define la sección resourceURI del elemento userDefinedFunctionResource con 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 una sección resourceUri.

Ejemplo

En el siguiente ejemplo de JSON se muestra una solicitud de consulta que hace referencia a dos recursos de funciones definidas por el usuario: un blob de código insertado y un archivo lib.js que se va a leer de Cloud Storage. En este ejemplo, myFunc y la invocación de registro de myFunc se proporcionan mediante lib.js.

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

Volver al principio

Prácticas recomendadas

Desarrollar tu FDU

Puedes usar nuestra herramienta de prueba de funciones definidas por el usuario para probar y depurar tu función definida por el usuario sin que aumente tu factura de BigQuery.

Prefiltrar la entrada

Si tu entrada se puede filtrar fácilmente antes de enviarse a una función definida por el usuario, es probable que tu consulta sea más rápida y económica.

En el ejemplo de ejecutar una consulta, se pasa una subconsulta como entrada a urlDecode en lugar de una tabla completa. Una tabla puede tener miles de millones de filas y, si ejecutáramos la función definida por el usuario en toda la tabla, el framework de JavaScript tendría que procesar muchas más filas que con la subconsulta filtrada.

Evita el estado mutable persistente

No almacenes ni accedas a estados mutables en las llamadas a funciones definidas por el usuario. 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 funcionará como se espera, ya que BigQuery fragmenta la consulta en muchos nodos. Cada nodo tiene un entorno de procesamiento de JavaScript independiente que acumula valores separados para numRows.

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 que acumulan demasiado estado local pueden fallar debido al agotamiento de la memoria.

Mostrar consultas seleccionadas

Debes enumerar explícitamente las columnas que se seleccionan de una función definida por el usuario. SELECT * FROM <UDF name>(...) no se admite.

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

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

Volver al principio

Límites

  • La cantidad de datos que genera tu función definida por el usuario al procesar una sola fila debe ser de unos 5 MB o menos.
  • Cada usuario solo puede ejecutar aproximadamente 6 consultas de funciones definidas por el usuario en un proyecto específico al mismo tiempo. Si recibes un error que indica que has superado el límite de consultas simultáneas, espera unos minutos y vuelve a intentarlo.
  • Una FDU 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 que consume tu función y el tamaño de las entradas y salidas de la función de JavaScript.
  • Una tarea de consulta puede tener un máximo de 50 recursos de funciones definidas por el usuario (blobs de código insertado o archivos externos).
  • Cada blob de código insertado está limitado a un tamaño máximo de 32 KB. Para usar recursos de código más grandes, almacena el código en Cloud Storage y haz referencia a él como recurso externo.
  • Cada recurso de código externo está limitado a un tamaño máximo de 1 MB.
  • El tamaño acumulado de todos los recursos de código externos está limitado a un máximo de 5 MB.

Volver al principio

Limitaciones

  • No se admiten los objetos del DOM Window, Document y Node, así como las funciones que los requieren.
  • No se admiten las funciones de JavaScript que dependen de código nativo.
  • Las operaciones de bit a bit en JavaScript solo gestionan los 32 bits más importantes.
  • 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