Funções definidas pelo utilizador no SQL antigo

Este documento detalha como usar funções definidas pelo utilizador em JavaScript na sintaxe de consulta SQL antiga. A sintaxe de consulta preferida para o BigQuery é o GoogleSQL. Para ver informações sobre as funções definidas pelo utilizador no GoogleSQL, consulte o artigo Funções definidas pelo utilizador do GoogleSQL.

O SQL antigo do BigQuery suporta funções definidas pelo utilizador (FDUs) escritas em JavaScript. Uma FDU é semelhante à função "Map" num MapReduce: recebe uma única linha como entrada e produz zero ou mais linhas como saída. A saída pode ter um esquema diferente da entrada.

Para ver informações sobre funções definidas pelo utilizador no GoogleSQL, consulte o artigo Funções definidas pelo utilizador no GoogleSQL.

Exemplo 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
);

Voltar ao início

Estrutura da FDU

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

As UDFs do BigQuery operam em linhas individuais de uma tabela ou em resultados de consultas de subseleção. A UDF tem dois parâmetros formais:

• row: uma linha de entrada.
• emit: um gancho usado pelo BigQuery para recolher dados de saída. A função emit usa um parâmetro: um objeto JavaScript que representa uma única linha de dados de saída. A função emit pode ser chamada mais do que uma vez, por exemplo, num ciclo, para gerar várias linhas de dados.

O seguinte exemplo de código mostra uma UDF básica.

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

Registar a FDU

Tem de registar um nome para a sua função para que possa ser invocada a partir do SQL do BigQuery. O nome registado não tem de corresponder ao nome que usou para a sua função em 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>
);

Colunas de entrada

Os nomes das colunas de entrada têm de corresponder aos nomes (ou aos alias, se aplicável) das colunas na tabela de entrada ou na subconsulta.

Para colunas de entrada que são registos, tem de especificar, na lista de colunas de entrada, os campos de folha que quer aceder a partir do registo.

Por exemplo, se tiver um registo que armazena o nome e a idade de uma pessoa:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

O especificador de entrada para o nome e a idade seria:

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

A utilização de ['person'] sem o nome ou a idade geraria um erro.

O resultado vai corresponder ao esquema. Vai ter uma matriz de objetos JavaScript, em que cada objeto tem uma propriedade "name" e "age". Por exemplo:

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

Esquema de saída

Tem de fornecer ao BigQuery o esquema ou a estrutura dos registos que a sua FDU produz, representados como JSON. O esquema pode conter quaisquer tipos de dados do BigQuery suportados, incluindo registos aninhados. Os especificadores de tipo suportados são:

• booleano
• flutuante
• número inteiro
• gravar
• de string
• timestamp

O seguinte exemplo de código mostra a sintaxe dos registos no esquema de saída. Cada campo de saída requer um atributo name e type. Os campos aninhados também têm de conter um atributo fields.

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

Cada campo pode conter um atributo mode opcional, que suporta os seguintes valores:

• nullable : esta é a predefinição e pode ser omitida.
• required : se for especificado, o campo indicado tem de ser definido com um valor e não pode ser indefinido.
• repeated : se for especificado, o campo indicado tem de ser uma matriz.

As linhas transmitidas à função emit() têm de corresponder aos tipos de dados do esquema de saída. Os campos representados no esquema de saída que são omitidos na função emit são apresentados como nulos.

Definição ou referência de FDU

Se preferir, pode definir a FDU inline em bigquery.defineFunction. Por exemplo:

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});
  }
);

Caso contrário, pode definir a FDU separadamente e transmitir uma referência à função em bigquery.defineFunction. Por exemplo:

// 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
);

Processamento de erros

Se for gerada uma exceção ou um erro durante o processamento de uma FDU, toda a consulta falha. Pode usar um bloco try-catch para processar erros. Por exemplo:

// 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
);

Executar uma consulta com uma FDU

Pode usar UDFs em SQL antigo com a ferramenta de linha de comandos bq ou a API BigQuery. A consola Trusted Cloud não suporta UDFs no SQL antigo.

Usar a ferramenta de linhas de comando bq

Para executar uma consulta que contenha uma ou mais UDFs, especifique a flag --udf_resource na ferramenta de linha de comandos bq da CLI Google Cloud. O valor da flag pode ser um URI do Cloud Storage (gs://...) ou o caminho para um ficheiro local. Para especificar vários ficheiros de recursos de FDU, repita esta flag.

Use a seguinte sintaxe para executar uma consulta com uma FDU:

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

O exemplo seguinte executa uma consulta que usa uma FDU armazenada num ficheiro local e uma consulta SQL que também está armazenada num ficheiro local.

Criar a FDU

Pode armazenar a FDU no Cloud Storage ou como um ficheiro de texto local. Por exemplo, para armazenar a UDF urlDecode seguinte, crie um ficheiro com o nome urldecode.js e cole o seguinte código JavaScript no ficheiro antes de o guardar.

// 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
);

Criar a consulta

Também pode armazenar a consulta num ficheiro para evitar que a linha de comandos se torne demasiado detalhada. Por exemplo, pode criar um ficheiro local denominado query.sql e colar a seguinte declaração do BigQuery no ficheiro.

#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

Depois de guardar o ficheiro, pode fazer referência ao ficheiro na linha de comandos.

Executar a consulta

Depois de definir a FDU e a consulta em ficheiros separados, pode fazer referência a eles na linha de comandos. Por exemplo, o seguinte comando executa a consulta que guardou como o ficheiro denominado query.sql e faz referência à UDF que criou.

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

Usar a API BigQuery

configuration.query

As consultas que usam UDFs têm de conter elementos userDefinedFunctionResources que fornecem o código ou as localizações dos recursos de código a usar na consulta. O código fornecido tem de incluir invocações de funções de registo para quaisquer FUDs referenciadas pela consulta.

Recursos de código

A configuração da consulta pode incluir blobs de código JavaScript, bem como referências a ficheiros de origem JavaScript armazenados no Cloud Storage.

Os blobs de código JavaScript inline são preenchidos na secção inlineCode de um elemento userDefinedFunctionResource. No entanto, o código que vai ser reutilizado ou referenciado em várias consultas deve ser mantido no Cloud Storage e referenciado como um recurso externo.

Para fazer referência a um ficheiro de origem JavaScript a partir do Cloud Storage, defina a secção resourceURI do elemento userDefinedFunctionResource para o URI gs:// do ficheiro.

A configuração da consulta pode conter vários elementos userDefinedFunctionResource. Cada elemento pode conter uma secção inlineCode ou resourceUri.

Exemplo

O seguinte exemplo JSON ilustra um pedido de consulta que faz referência a dois recursos de FDU: um blob de código inline e um ficheiro lib.js a ser lido do armazenamento na nuvem. Neste exemplo, myFunc e a invocação de registo para myFunc são fornecidos por lib.js.

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

Voltar ao início

Práticas recomendadas

Desenvolver a sua FDU

Pode usar a nossa ferramenta de teste de FDUpara testar e depurar a sua FDU sem aumentar a fatura do BigQuery.

Pré-filtre a sua entrada

Se a sua entrada puder ser facilmente filtrada antes de ser transmitida a uma FDU, é provável que a sua consulta seja mais rápida e mais barata.

No exemplo de execução de uma consulta, é transmitida uma subconsulta como entrada para urlDecode, em vez de uma tabela completa. Uma tabela pode ter milhares de milhões de linhas e, se executássemos a FDU na tabela inteira, a framework JavaScript teria de processar muito mais linhas do que com a subconsulta filtrada.

Evite um estado mutável persistente

Não armazene nem aceda ao estado mutável nas chamadas de FDU. O seguinte exemplo de código descreve este cenário:

// myCode.js
var numRows = 0;

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

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

O exemplo acima não vai funcionar como esperado, porque o BigQuery divide a sua consulta em vários nós. Cada nó tem um ambiente de processamento JavaScript autónomo que acumula valores separados para numRows.

Use a memória de forma eficiente

O ambiente de processamento JavaScript tem uma memória limitada disponível por consulta. As consultas de FDU que acumulam demasiado estado local podem falhar devido ao esgotamento da memória.

Expandir consultas selecionadas

Tem de listar explicitamente as colunas que estão a ser selecionadas a partir de uma FDU. O idioma SELECT * FROM <UDF name>(...) não é suportado.

Para examinar a estrutura dos dados das linhas de entrada, pode usar JSON.stringify() para emitir uma coluna de saída de string:

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

Voltar ao início

Limites

• A quantidade de dados que a FDU produz quando processa uma única linha deve ser de aproximadamente 5 MB ou menos.
• Cada utilizador está limitado à execução de aproximadamente 6 consultas de FDUs num projeto específico em simultâneo. Se receber um erro a indicar que excedeu o limite de consultas simultâneas, aguarde alguns minutos e tente novamente.
• Uma FDU pode exceder o tempo limite e impedir a conclusão da consulta. Os limites de tempo podem ser tão curtos quanto 5 minutos, mas podem variar consoante vários fatores, incluindo a quantidade de tempo de CPU do utilizador que a sua função consome e o tamanho das entradas e saídas da função JS.
• Uma tarefa de consulta pode ter um máximo de 50 recursos de FDU (objetos binários grandes de código inline ou ficheiros externos).
• Cada blob de código inline está limitado a um tamanho máximo de 32 KB. Para usar recursos de código maiores, armazene o seu código no Cloud Storage e faça referência ao mesmo como um recurso externo.
• Cada recurso de código externo está limitado a um tamanho máximo de 1 MB.
• O tamanho cumulativo de todos os recursos de código externos está limitado a um máximo de 5 MB.

Voltar ao início

Limitações

• Os objetos DOM Window, Document e Node, e as funções que os requerem, não são suportados.
• As funções JavaScript que dependem de código nativo não são suportadas.
• As operações bit a bit em JavaScript processam apenas os 32 bits mais significativos.
• Devido à sua natureza não determinística, as consultas que invocam funções definidas pelo utilizador não podem usar resultados em cache.

Voltar ao início