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

Neste documento, você encontra detalhes sobre como usar funções JavaScript definidas pelo usuário na sintaxe da consulta SQL legado. A sintaxe de consulta recomendada no BigQuery é o SQL padrão. Para mais informações sobre funções definidas pelo usuário no SQL padrão, consulte esta página.

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

Para 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 de UDF

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

As UDFs do BigQuery funcionam em linhas individuais de uma tabela ou em resultados da consulta subselect. 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 utiliza um parâmetro: um objeto JavaScript 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 produzir 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});
}

Registro da 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 corresponder aos nomes (ou aliases, se aplicável) das colunas na tabela de entrada ou na subconsulta.

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

Por exemplo, se você tivesse um registro que armazenasse o nome e a idade de uma pessoa:

person RECORD REPEATED
  name STRING OPTIONAL
  age INTEGER OPTIONAL

O especificador de entrada do nome e da idade seria:

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

O uso de ['person'] sem o nome ou a idade geraria 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

Forneça ao BigQuery o esquema ou a estrutura dos registros produzidos pela UDF, representado como JSON. O esquema pode conter qualquer tipo de dados do BigQuery compatível, inclusive registros aninhados. Os especificadores de tipo compatíveis são estes:

  • booleano
  • flutuante
  • número inteiro
  • registro
  • string
  • carimbo de data/hora

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, compatível com os seguintes valores:

  • Anulável: este é o padrão e pode ser omitido.
  • Obrigatório: caso especificado, o campo indicado precisa ser definido como um valor, e a definição não pode ser desfeita.
  • Repetido: caso 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 UDF

Se preferir, você poderá definir a UDF 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});
  }
);

Do contrário, defina a UDF separadamente e passe 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á. Você pode usar 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 executar consultas.

Pré-requisitos

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

  1. Se você nunca usou o Console do GCP, basta acessá-lo, aceitar os termos de serviço e criar 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 executar 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 são exibidos abaixo 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. Independentemente da localização do código da UDF, a função precisa ser registrada com uma invocação bigquery.defineFunction no código. A invocação bigquery.definefunction pode ser fornecida na IU da Web ou em um recurso de código remoto. Os arquivos de origem remota precisam ter uma extensão ".js".

Por exemplo, seria 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 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 seja 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 um código JavaScript extra à área de texto do Editor de UDFs, desde que as outras UDFs estejam registradas em um bloco defineFunction na IU da Web ou em um arquivo externo.

Você também pode 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 mais informações.

Voltar ao início

UDFs e a ferramenta de linha de comando bq

É possível usar a ferramenta de linha de comando bq do SDK do Cloud para executar uma consulta que contenha uma ou mais UDFs especificando a sinalização --udf_resource. O valor do sinalizador pode ser um URI (gs://...) do Cloud Storage ou o caminho de um arquivo local. Você pode repetir esse sinalizador 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

Você pode armazenar a UDF no Cloud Storage ou como um arquivo de texto local. Por exemplo, para armazenar a UDF urlDecode mencionada em UDFs e a IU da Web, crie um arquivo chamado urldecode.js e cole o código JavaScript a seguir no arquivo antes de salvá-lo.

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

Você também pode armazenar a consulta em um arquivo para evitar que a linha de comando seja muito detalhada. Por exemplo, você pode criar um arquivo local chamado query.sql e colar a instrução BigQuery a seguir 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, você poderá referenciar o arquivo na linha de comando.

Como executar a consulta

Depois de definir a UDF e a consulta em arquivos separados, você poderá referenciá-los na linha de comando. Por exemplo, o comando a seguir executa a consulta que você salvou como o arquivo chamado query.sql e referencia a UDF criada.

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

UDFs e a BigQuery API

configuration.query

As consultas que usam UDFs precisam conter elementos userDefinedFunctionResources que forneçam o código a ser usado na consulta ou o local dos recursos dele. 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 de código JavaScript são preenchidos na seção inlineCode de 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 referenciar um arquivo de origem JavaScript do Cloud Storage, defina a seção resourceURI do elemento userDefinedFunctionResource como o arquivo gs:// URI.

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 referencia dois recursos UDF: um blob do código inline e um arquivo lib.js a ser lido no Cloud Storage. Neste exemplo, myFunc e a invocação do registro de 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

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 como executar uma consulta, uma subconsulta é transmitida como a entrada para urlDecode, em vez de uma tabela completa. A tabela [fh-bigquery:wikipedia.pagecounts_201504] tem aproximadamente 5,6 bilhões de linhas. Se executássemos a UDF em toda a tabela, a estrutura do JavaScript precisaria processar 21 vezes mais 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 JavaScript autônomo que acumula valores separados para numRows.

Usar a memória de maneira eficiente

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 de seleção

É 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, é possível usar JSON.stringify() para emitir uma coluna de saída da 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 inline ou arquivos 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 código no Cloud Storage e o referencie 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 externo está limitado a um 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 os exigem, não são compatíveis.
  • 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 significativos.
  • 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

Esta página foi útil? Conte sua opinião sobre:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.