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 consulta preferida do BigQuery é o SQL padrão. Para ver mais informações sobre as funções definidas pelo usuário no SQL padrão, consulte Funções definidas pelo usuário do SQL padrão.

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 ver mais informações sobre funções definidas pelo usuário em SQL padrão, consulte Funções definidas pelo usuário em SQL padrão.

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

Cadastre um nome para sua função para que ela possa ser invocada pelo SQL do BigQuery. O nome registrado não precisa ser igual ao da 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 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". Por 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:

  • boolean
  • float
  • integer
  • record
  • 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 pode ser omitido.
  • required : se especificado, o campo indicado precisa ser definido como um valor, e a definição não pode ser desfeita.
  • repeated : se especificado, o campo indicado precisa ser uma matriz.

As linhas passadas para a função emit() precisam corresponder 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. 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});
  }
);

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

Como lidar com 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. 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
);

UDFs e a IU da Web

Use a IU da Web do BigQuery para adicionar UDFs e usá-las ao gerar consultas.

Pré-requisitos

Para usar a IU da Web do BigQuery, sua conta precisa ter acesso a um projeto ativado pelo BigQuery no Console do Google Cloud.

  1. Se você nunca usou o Console do Cloud, acesse o console, aceite os Termos de Serviço e crie um novo projeto.

  2. Navegue até a IU da Web do BigQuery.

Como adicionar a UDF

  1. Clique no botão ESCREVER CONSULTA.

  2. Clique na guia Editor de UDFs para adicionar a UDF.

  3. Copie e cole o seguinte código na área de texto do Editor de UDFs:

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

Como fazer uma consulta na IU da Web

  • Clique na guia Editor de consultas.

  • Copie e cole a consulta a seguir na área de texto do 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
    

    A consulta acima procura os artigos mais visitados da Wikipédia francesa a partir de abril de 2015 que contenham um caractere cedilha (ç) no título.

  • Clique no botão EXECUTAR CONSULTA. Os resultados da consulta aparecem embaixo dos botões.

Como referenciar um código do Cloud Storage

No exemplo acima, você adicionou a UDF diretamente à IU da Web do BigQuery. Como alternativa, é possível armazenar todo o código JavaScript ou parte dele no Cloud Storage. Não importa a localização do código da UDF: toda UDF precisa ser registrada com uma invocação bigquery.defineFunction no código. A invocação bigquery.definefunction pode ser fornecida na IU da Web do BigQuery ou em recursos de código remoto. Os arquivos de origem remota precisam ter uma extensão ".js".

Por exemplo, é possível manter bibliotecas de terceiros, o próprio código da UDF e as chamadas à função de registro da UDF em arquivos separados. Esses arquivos seriam carregados como um recurso externo na consulta.

Como referenciar uma UDF externa na IU da Web do BigQuery

  1. Clique no botão Mostrar opções abaixo da área de texto.

  2. Clique no botão Editar ao lado do título URIs de origem da UDF.

  3. Para cada arquivo de origem remoto, clique no botão Adicionar URI de origem da UDF e insira o URI do Cloud Storage.

    Caso você queira testar o exemplo anterior de decodificação do URL usando uma UDF externa, cole bigquery-sandbox-udf/url_decode.js no campo do URI. Depois de concluir essas etapas, certifique-se de que o conteúdo do Editor de UDFs foi removido.

  4. Clique no botão OK.

Em seguida, alterne para a guia Editor de consultas e siga as mesmas etapas do exemplo acima para usar a UDF em uma consulta.

Em geral, ao usar UDFs externas, também é possível adicionar mais códigos JavaScript na área de texto do Editor de UDFs desde que as UDFs extras estejam registradas em um bloco defineFunction na IU da Web ou em um arquivo externo.

Também é possível usar a ferramenta de linha de comando bq para referenciar uma UDF armazenada no Cloud Storage. Consulte UDFs e a ferramenta de linha de comando bq para ver mais informações.

Voltar ao início

UDFs e a ferramenta de linha de comando bq

Use a Ferramenta de linha de comando bq no SDK do Cloud para executar uma consulta que contenha uma ou mais UDFs especificando a sinalização --udf_resource. O valor da sinalização pode ser um URI do Cloud Storage (gs://...) ou o caminho de um arquivo local. Repita essa sinalização para especificar vários arquivos de recurso UDF.

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 UDF urlDecode discutida em UDFs e a IU da Web, 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)"

UDFs e a API 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. De qualquer maneira, 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, é provável que a consulta 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 tem de ser de aproximadamente 5 MB ou menos.
  • Ao mesmo tempo, cada usuário tem o limite de aproximadamente seis consultas UDF no mesmo projeto. 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 ao tamanho máximo de 5 MB.

Voltar ao início

Limitações

  • Os objetos DOM Window, Document e Node, bem como as funções que usam esses objetos, 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.
  • Em razão 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