Funções definidas pelo usuário no SQL legado

Neste documento, você aprenderá a usar funções de JavaScript definidas pelo usuário na sintaxe da consulta do SQL legado. A sintaxe de consulta de preferência do BigQuery é o GoogleSQL. Para informações sobre as funções definidas pelo usuário no GoogleSQL, consulte Funções definidas pelo usuário do GoogleSQL.

O SQL legado do BigQuery é compatível com funções definidas pelo usuário (UDFs, na sigla em inglês) escritas em JavaScript. As UDFs são semelhantes à função "Map" em um MapReduce: utilizam apenas uma linha como entrada e produzem zero ou mais linhas como saída. A saída pode apresentar um esquema diferente em relação ao da entrada.

Para informações sobre funções definidas pelo usuário no GoogleSQL, consulte Funções definidas pelo usuário no GoogleSQL.

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

Voltar ao início

Estrutura das UDFs

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

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

• row: uma linha de entrada.
• emit: um gancho usado pelo BigQuery para coletar dados de saída. A função emit usa um parâmetro: um objeto JavaScript em que representa uma linha de dados de saída. A função emit pode ser chamada mais de uma vez, como em um loop, para gerar várias linhas de dados.

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

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

Como registrar a UDF

Registre um nome para a função, de maneira que ela possa ser invocada pelo SQL do BigQuery. O nome registrado não precisa corresponder ao nome usado na função no 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 precisam ser iguais aos nomes (ou aliases, se for o caso) das colunas na tabela de entrada ou na subconsulta.

Para as colunas de entrada que sejam registros, especifique (na lista de colunas de entrada) os campos-folha que quer acessar pelo registro.

Por exemplo, se tiver um registro que armazena o nome e a idade de alguém:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

O especificador de entrada do nome e da idade será:

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

O uso de ['person'] sem o nome ou a idade gera um erro.

A saída resultante corresponderá ao esquema. Você terá uma matriz de objetos JavaScript, em que cada objeto apresenta as propriedades "name" e "age". Exemplo:

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

Esquema de saída

Digite, no BigQuery, o esquema ou a estrutura dos registros produzidos pela UDF, representado como JSON. O esquema pode ter qualquer tipo de dados do BigQuery compatível, como registros aninhados. Os especificadores de tipo compatíveis são os seguintes:

• booleano
• float
• número inteiro
• registro
• string
• timestamp

O exemplo de código a seguir mostra a sintaxe de registros no esquema de saída. Cada campo de saída exige os atributos name e type. Os campos aninhados também precisam 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 é compatível com os seguintes valores:

• nullable: este é o padrão e é possível omiti-lo.
• required: se especificado, o campo indicado precisa ser definido como um valor, e a definição não pode ser desfeita.
• repeated: se especificado, é necessário que o campo indicado seja uma matriz.

É necessário que as linhas passadas para a função emit() correspondam aos tipos de dados do esquema de saída. Os campos representados no esquema de saída omitidos na função emit serão produzidos como nulos.

Definição ou referência de UDFs

Se preferir, defina a UDF in-line em bigquery.defineFunction. 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});
  }
);

Também é possível definir a UDF em separado e passar uma referência para a função em bigquery.defineFunction. 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
);

Tratamento de erros

Se uma exceção ou um erro for lançado durante o processamento de uma UDF, toda a consulta falhará. Use um bloco try-catch para lidar com erros. 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
);

Como executar uma consulta com uma UDF

É possível usar UDFs no SQL legado com a ferramenta de linha de comando bq ou a API BigQuery. O console do Google Cloud não é compatível com UDFs no SQL legado.

Como usar a ferramenta de linha de comando bq

Para executar uma consulta que contenha uma ou mais UDFs, especifique a flag --udf_resource na ferramenta de linha de comando bq da CLI do Google Cloud. O valor da flag pode ser um URI do Cloud Storage (gs://...) ou o caminho de um arquivo local. Para especificar vários arquivos de recursos da UDF, repita essa sinalização.

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

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

O exemplo a seguir executa uma consulta que usa uma UDF e uma consulta SQL armazenadas em um arquivo local.

Como criar a UDF

Armazene a UDF no Cloud Storage ou como um arquivo de texto local. Por exemplo, para armazenar a seguinte UDF urlDecode, crie um arquivo chamado urldecode.js e cole o seguinte código JavaScript no arquivo antes de salvar o arquivo.

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

Como criar a consulta

Também é possível armazenar a consulta em um arquivo para evitar que a linha de comando seja muito detalhada. Por exemplo, é possível criar um arquivo local chamado query.sql e colar a seguinte instrução do BigQuery no arquivo.

#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

Depois de salvar o arquivo, é possível referenciar o arquivo na linha de comando.

Como executar a consulta

Depois de definir a UDF e a consulta em arquivos separados, é possível referenciá-los na linha de comando. Por exemplo, o comando a seguir executa a consulta que você salvou como o arquivo nomeado query.sql e faz referência à UDF criada.

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

Como usar a API do BigQuery

configuration.query

Consultas que usam UDFs precisam ter elementos userDefinedFunctionResources que forneçam o código, ou locais para codificar recursos, a serem usados na consulta. O código fornecido precisa incluir invocações de função do registro para qualquer UDF referenciada pela consulta.

Recursos de código

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

Os blobs in-line do código JavaScript são preenchidos na seção inlineCodede um elemento userDefinedFunctionResource. No entanto, o código que será reutilizado ou referenciado em várias consultas precisa permanecer no Cloud Storage e ser referenciado como um recurso externo.

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

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

Exemplo

O exemplo JSON a seguir ilustra uma solicitação de consulta que faz referência a dois recursos UDF: um blob de código in-line e um arquivo lib.js para ser lido no Cloud Storage. Neste exemplo, myFunc e a invocação de registro para myFunc são fornecidas 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

Como desenvolver a UDF

É possível usar nossa ferramenta de teste de UDFs para testar e depurar a UDF sem sobrecarregar o BigQuery.

Pré-filtrar a entrada

Se for possível filtrar a entrada com facilidade antes de passá-la para uma UDF, a consulta provavelmente será mais rápida e mais barata.

No exemplo de execução de uma consulta, uma subconsulta é transmitida como entrada para urlDecode, em vez de uma tabela completa. A tabela [fh-bigquery:wikipedia.pagecounts_201504] tem cerca de 5,6 bilhões de linhas e, se executarmos a UDF em toda a tabela, o framework de JavaScript precisará processar 21 vezes mais de linhas do que faria com a subconsulta filtrada.

Evitar o estado mutável permanente

Não armazene nem acesse o estado mutável em chamadas à UDF. O exemplo de código a seguir descreve esse 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 se comportará conforme esperado, porque o BigQuery fragmenta a consulta em muitos nós. Cada nó tem um ambiente de processamento em JavaScript independente que acumula valores separados para numRows.

Usar a memória com eficiência

O ambiente de processamento do JavaScript tem uma memória disponível por consulta limitada. As consultas de UDFs que acumulam muito estado local podem falhar por causa do esgotamento da memória.

Expandir consultas selecionadas

É preciso listar explicitamente as colunas selecionadas de uma UDF. SELECT * FROM <UDF name>(...) não é compatível.

Para examinar a estrutura dos dados da linha de entrada, use 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

• O volume de dados de saída da UDF durante o processamento de uma única linha deve ser de aproximadamente 5 MB ou menos.
• Ao mesmo tempo, cada usuário tem a limitação de aproximadamente seis consultas UDF em um projeto específico. Se você receber um erro dizendo que está acima do limite de consulta simultâneo, aguarde alguns minutos e tente novamente.
• Uma UDF pode atingir o tempo limite e evitar que a consulta seja concluída. O tempo limite pode ter até cinco minutos, embora isso possa variar de acordo com diversos fatores, inclusive o tempo de CPU do usuário consumido pela função e o tamanho de entradas e saídas da função JS.
• Um job de consulta pode ter, no máximo, 50 recursos de UDF, isto é, blobs de código in-line ou arquivos externos.
• Cada blob de código in-line está limitado a um tamanho máximo de 32 KB. Para usar recursos de código maiores, armazene o código no Cloud Storage e o referencie como um recurso externo.
• Cada recurso de código externo está limitado ao tamanho máximo de 1 MB.
• O tamanho cumulativo de todos os recursos de código externo está limitado a um tamanho máximo de 5 MB.

Voltar ao início

Limitações

• Os objetos DOM Window, Document e Node, assim como as funções que os usam, não são compatíveis.
• As funções JavaScript que dependem de código nativo não são compatíveis.
• As operações Bitwise em JavaScript lidam apenas com os 32 bits mais importantes.
• Por causa da natureza não determinista, as consultas que invocam funções definidas pelo usuário não podem usar resultados armazenados em cache.

Voltar ao início