Funções definidas pelo usuário do SQL padrão

O BigQuery é compatível com funções definidas pelo usuário (UDF, na sigla em inglês). Uma UDF permite criar uma função usando uma expressão SQL ou JavaScript. Essas funções aceitam colunas de entrada e executam ações, retornando o resultado dessas ações como um valor. Para informações sobre UDFs no SQL legado, consulte Funções definidas pelo usuário no SQL legado.

As UDFs podem ser permanentes ou temporárias. É possível reutilizar UDFs permanentes em várias consultas, mas as UDFs temporárias só podem ser usadas uma única vez.

Sintaxe da UDF

Para criar uma UDF permanente, use a seguinte sintaxe:

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS]
    [`project_name`.]dataset_name.function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

Para criar uma UDF temporária, use a seguinte sintaxe:

CREATE [OR REPLACE] {TEMPORARY | TEMP} FUNCTION [IF NOT EXISTS]
    function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  { sql_function_definition | javascript_function_definition }

named_parameter:
  param_name param_type

sql_function_definition:
  AS (sql_expression)

javascript_function_definition:
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Essa sintaxe é composta pelos componentes a seguir:

  • CREATE { FUNCTION | OR REPLACE FUNCTION | FUNCTION IF NOT EXISTS }. Cria ou atualiza uma função. Para substituir qualquer função atual pelo mesmo nome, use a palavra-chave OR REPLACE. Para tratar a consulta como bem-sucedida e não tomar nenhuma ação caso uma função com o mesmo nome já exista, use a cláusula IF NOT EXISTS.
  • named_parameter. Consiste em um par de param_name e param_type separado por vírgula. O valor de param_type é um tipo de dados do BigQuery. Para uma UDF do SQL, o valor de param_type também pode ser ANY TYPE.
  • [RETURNS data_type]. Especifica o tipo de dados retornado pela função.
    • Se a função estiver definida em SQL, a cláusula RETURNS será opcional. Se a cláusula RETURNS for omitida, o BigQuery deduzirá o tipo de resultado da função a partir do corpo da função SQL quando uma consulta chamar a função.
    • Se a função estiver definida em JavaScript, a cláusula RETURNS será obrigatória. Consulte os Tipos de dados de UDF em JavaScript compatíveis para mais informações sobre os valores permitidos para data_type.
  • AS (sql_expression). Especifica a expressão SQL que define a função.
  • [OPTIONS (library = library_array)]. Em uma UDF JavaScript, especifica uma matriz de bibliotecas JavaScript que serão incluídas na definição de função.
  • AS javascript_code. Especifica a definição de uma função JavaScript. javascript_code é um literal de string.

Para excluir uma função permanente definida pelo usuário, use a seguinte sintaxe:

DROP FUNCTION [IF EXISTS] [`project_name`.]dataset_name.function_name

As funções temporárias definidas pelo usuário expiram assim que a consulta é concluída, portanto, não são compatíveis com instruções DROP FUNCTION, exceto em scripts e procedimentos.

Estrutura de UDF de SQL

Crie UDFs de SQL usando a sintaxe a seguir:

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)

named_parameter:
  param_name param_type

Parâmetros de UDF do SQL com modelo

Um parâmetro com modelo param_type = ANY TYPE pode corresponder a mais de um tipo de argumento quando a função é chamada.

  • Se mais de um parâmetro tiver o tipo ANY TYPE, o BigQuery não aplicará qualquer relação entre esses argumentos.
  • O tipo de retorno da função não pode ser ANY TYPE. Ele precisa ser omitido, ou seja, determinado automaticamente com base em sql_expression, ou um tipo explícito.
  • Transmitir os argumentos de tipos da função que não são compatíveis com a definição da função resultará em um erro no tempo de chamada.

Exemplos de UDF de SQL

O exemplo a seguir mostra uma UDF que emprega uma função SQL.

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);
WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

+-----+--------+
| val | result |
+-----+--------+
| 1   | 2.5    |
| 3   | 3.5    |
| 4   | 4      |
| 5   | 4.5    |
+-----+--------+

O exemplo a seguir mostra uma UDF de SQL que usa um parâmetro com modelo. A função resultante aceita argumentos de vários tipos.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE) AS (
  (x + 4) / y
);
SELECT addFourAndDivideAny(3, 4) AS integer_output,
       addFourAndDivideAny(1.59, 3.14) AS floating_point_output;

+----------------+-----------------------+
| integer_output | floating_point_output |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

O exemplo a seguir mostra uma UDF de SQL que usa um parâmetro com modelo para retornar o último elemento de uma matriz de qualquer tipo.

CREATE TEMP FUNCTION lastArrayElement(arr ANY TYPE) AS (
  arr[ORDINAL(ARRAY_LENGTH(arr))]
);
SELECT
  names[OFFSET(0)] AS first_name,
  lastArrayElement(names) AS last_name
FROM (
  SELECT ['Fred', 'McFeely', 'Rogers'] AS names UNION ALL
  SELECT ['Marie', 'Skłodowska', 'Curie']
);

+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Fred       | Rogers    |
| Marie      | Curie     |
+------------+-----------+

Estrutura de UDF em JavaScript

Crie UDFs de JavaScript usando a estrutura a seguir.

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[`project_name`.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  LANGUAGE js
  [OPTIONS (library = library_array)]
  AS javascript_code

Exemplos de UDF em JavaScript

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x, y, multiplyInputs(x, y) as product
FROM numbers;

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

É possível transmitir o resultado de uma UDF como uma entrada para outra UDF. Por exemplo:

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMP FUNCTION divideByTwo(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x/2;
""";
WITH numbers AS
  (SELECT 1 AS x, 5 as y
  UNION ALL
  SELECT 2 AS x, 10 as y
  UNION ALL
  SELECT 3 as x, 15 as y)
SELECT x,
  y,
  multiplyInputs(divideByTwo(x), divideByTwo(y)) as half_product
FROM numbers;

+-----+-----+--------------+
| x   | y   | half_product |
+-----+-----+--------------+
| 1   | 5   | 1.25         |
| 2   | 10  | 5            |
| 3   | 15  | 11.25        |
+-----+-----+--------------+

O exemplo a seguir soma os valores de todos os campos denominados "foo" na string JSON fornecida.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS """
function SumFoo(obj) {
  var sum = 0;
  for (var field in obj) {
    if (obj.hasOwnProperty(field) && obj[field] != null) {
      if (typeof obj[field] == "object") {
        sum += SumFoo(obj[field]);
      } else if (field == "foo") {
        sum += obj[field];
      }
    }
  }
  return sum;
}
var row = JSON.parse(json_row);
return SumFoo(row);
""";

WITH Input AS (
  SELECT STRUCT(1 AS foo, 2 AS bar, STRUCT('foo' AS x, 3.14 AS foo) AS baz) AS s, 10 AS foo UNION ALL
  SELECT NULL, 4 AS foo UNION ALL
  SELECT STRUCT(NULL, 2 AS bar, STRUCT('fizz' AS x, 1.59 AS foo) AS baz) AS s, NULL AS foo
)
SELECT
  TO_JSON_STRING(t) AS json_row,
  SumFieldsNamedFoo(TO_JSON_STRING(t)) AS foo_sum
FROM Input AS t;
+---------------------------------------------------------------------+---------+
| json_row                                                            | foo_sum |
+---------------------------------------------------------------------+---------+
| {"s":{"foo":1,"bar":2,"baz":{"x":"foo","foo":3.14}},"foo":10}       | 14.14   |
| {"s":null,"foo":4}                                                  | 4       |
| {"s":{"foo":null,"bar":2,"baz":{"x":"fizz","foo":1.59}},"foo":null} | 1.59    |
+---------------------------------------------------------------------+---------+

Consulte Tipos de dados de UDF em JavaScript compatíveis para conseguir informações sobre como os tipos de dados do BigQuery mapeiam para tipos do JavaScript.

Tipos de dados de UDF em JavaScript compatíveis

Alguns tipos do SQL têm mapeamento direto para tipos do JavaScript, outros não. No BigQuery, os tipos são representados da seguinte maneira:

Tipo de dados do BigQuery Tipo de dados do JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING codificada em base64
FLOAT64 NUMBER
NUMERIC Se um valor NUMERIC puder ser representado exatamente como um valor de ponto flutuante IEEE 754 e não tiver uma parte fracionária, ele será codificado como um número. Esses valores estão no intervalo [-253, 253]. Caso contrário, ele será codificado como uma string.
STRING STRING
STRUCT OBJECT, onde cada campo STRUCT é um campo nomeado
TIMESTAMP DATE com um campo de microssegundo que contém a fração de microsecond do carimbo de data/hora
DATE DATE

Como o tipo inteiro de 64 bits não é compatível com o JavaScript, o INT64 não é aceito como um tipo de entrada para UDFs em JavaScript. Em vez disso, use FLOAT64 para representar valores inteiros como um número, ou STRING para representar valores inteiros como uma string.

O BigQuery é compatível com INT64 como um tipo de retorno para UDFs em JavaScript. Nesse caso, o corpo da função JavaScript pode retornar um número em JavaScript ou uma string. Em seguida, o BigQuery converte um desses tipos para INT64.

Se o valor de retorno da UDF em JavaScript for Promise (em inglês), o BigQuery aguardará Promise até que ela seja resolvida. Se o estado de Promise for completo, o BigQuery retornará o resultado dela. Se o estado de Promise for negativo, o BigQuery retornará um erro.

Regras de uso de aspas

Coloque o código JavaScript entre aspas. Para snippets simples com uma linha de código, use uma string entre aspas padrão:

CREATE TEMP FUNCTION plusOne(x FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS "return x+1;";
SELECT val, plusOne(val) AS result
FROM UNNEST([1, 2, 3, 4, 5]) AS val;

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 4         |
| 4         | 5         |
| 5         | 6         |
+-----------+-----------+

Quando o snippet contém aspas ou é composto por várias linhas, use blocos com aspas triplas:

CREATE TEMP FUNCTION customGreeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  var d = new Date();
  if (d.getHours() < 12) {
    return 'Good Morning, ' + a + '!';
  } else {
    return 'Good Evening, ' + a + '!';
  }
  """;
SELECT customGreeting(names) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS names;
+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

Como incluir bibliotecas JavaScript

Estenda as funcionalidades das UDFs em JavaScript usando a seção OPTIONS. Nela, especifique bibliotecas externas de código para a UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js
  OPTIONS (
    library=["gs://my-bucket/path/to/lib1.js", "gs://my-bucket/path/to/lib2.js"]
  )
  AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
""";

SELECT myFunc(3.14, 'foo');

No exemplo anterior, o código em lib1.js e lib2.js estava disponível para qualquer código na seção [external_code] da UDF.

UDFs e a IU da Web

Use a IU da Web do BigQuery para executar consultas usando uma ou mais UDFs.

Como executar uma consulta com uma UDF

  1. Clique no botão ESCREVER CONSULTA.
  2. Clique na guia Editor de consultas.
  3. Clique no botão Mostrar opções.
  4. Desmarque a caixa Usar SQL legado.
  5. Digite a instrução da UDF na área de texto do Editor de consultas. Por exemplo:

    CREATE TEMPORARY FUNCTION timesTwo(x FLOAT64)
    RETURNS FLOAT64
      LANGUAGE js AS """
      return x*2;
    """;
  6. Digite a consulta abaixo da instrução da UDF. Por exemplo:

    SELECT timesTwo(numbers) AS doubles
    FROM UNNEST([1, 2, 3, 4, 5]) AS numbers;
  7. Clique no botão EXECUTAR CONSULTA. Os resultados da consulta são exibidos abaixo dos botões.

UDFs e a ferramenta de linha de comando bq

Use a ferramenta de linha de comando bq do SDK do Google Cloud para executar uma consulta com uma ou mais UDFs.

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

bq query <statement_with_udf_and_query>

Práticas recomendadas para UDFs em JavaScript

Pré-filtrar a entrada

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

Evitar o estado mutável permanente

Não armazene nem acesse o estado mutável nas chamadas à UDF em JavaScript.

Usar a memória de maneira eficiente

O ambiente de processamento do JavaScript tem uma memória disponível por consulta limitada. As consultas da UDF em JavaScript que acumulam muitos estados locais podem ter falhas devido ao esgotamento da memória.

Limites

  • Volume de dados de saída da UDF em JavaScript durante o processamento de uma única linha: aproximadamente 5 MB ou menos.
  • Limite de taxa simultânea para consultas do SQL legado que contém UDFs: seis consultas simultâneas.
  • O limite de taxa simultânea para consultas do SQL legado que contêm UDFs inclui consultas interativas e em lote. Consultas interativas que contêm UDFs também são consideradas em relação ao limite de taxa simultânea para consultas interativas. Esse limite não se aplica a consultas do SQL padrão.

  • Número máximo de recursos de UDF em JavaScript, como blobs de código in-line ou arquivos externos em um job de consulta: 50
  • Tamanho máximo de cada blob de código in-line: 32 KB
  • Tamanho máximo de cada recurso de código externo: 1 MB

Os limites a seguir se aplicam a funções permanentes definidas pelo usuário.
  • Comprimento máximo de um nome de função: 256 caracteres
  • Número máximo de argumentos: 256
  • Comprimento máximo de um nome de argumento: 128 caracteres
  • Profundidade máxima de uma cadeia de referência de uma função definida pelo usuário: 16
  • Profundidade máxima de um argumento ou de uma resposta do tipo STRUCT: 15
  • Número máximo de campos em um argumento ou resposta do tipo STRUCT por UDF: 1.024
  • Número máximo de UDFs únicas e referências de tabela por consulta: 1.000 Após a expansão completa, cada UDF poderá consultar até 1.000 UDFs e tabelas únicas. Esse número é uma combinação desses dois elementos.
  • Número máximo de bibliotecas JavaScript na instrução CREATE FUNCTION: 50
  • Comprimento máximo de caminhos de bibliotecas JavaScript incluídos: 5.000 caracteres
  • Taxa máxima de atualização por UDF: cinco a cada 10 segundos Após a criação da função, é possível atualizar cada função até cinco vezes a cada 10 segundos.
  • Cada blob de código in-line está limitado ao tamanho máximo de 32 KB
  • Cada recurso de código JavaScript está limitado ao tamanho máximo de 1 MB

Limitações

As limitações a seguir se aplicam às funções permanentes e temporárias definidas pelo usuário.

  • 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.
  • Uma UDF em JavaScript pode alcançar o tempo limite e, assim, impedir que a consulta seja concluída. Esse tempo pode ser curto, como 5 minutos, mas pode variar de acordo com diversos fatores, inclusive o Tempo de CPU do usuário consumido pela função e o tamanho das entradas e respostas da função JS.
  • Por causa da natureza não determinista delas, as consultas que invocam funções JavaScript definidas pelo usuário não podem usar resultados armazenados em cache.
  • As operações Bitwise em JavaScript processam apenas os 32 bits mais significativos.
  • Não é possível referenciar uma tabela em uma UDF.

As seguintes limitações se aplicam a funções permanentes definidas pelo usuário:

  • Cada conjunto de dados pode conter apenas uma UDF permanente com o mesmo nome. No entanto, é possível criar uma UDF com o nome igual ao de uma tabela no mesmo banco de dados.
  • Ao fazer referência a uma UDF permanente de outra UDF permanente, é preciso qualificar o nome com o conjunto de dados. Por exemplo:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());
  • Ao fazer referência a uma UDF permanente de uma visualização lógica, é preciso qualificar o nome com o projeto e conjunto de dados. Por exemplo:
    CREATE VIEW mydataset.sample_view AS SELECT `my-project`.mydataset.referencedFunction();

Os limites a seguir se aplicam às funções temporárias definidas pelo usuário.

  • Ao criar uma UDF temporária, function_name não pode conter pontos.
  • As visualizações lógicas e as UDFs permanentes não podem fazer referência a UDFs temporárias.