Funções definidas pelo usuário

Uma função definida pelo usuário (UDF, na sigla em inglês) permite criar uma função usando uma expressão em SQL ou um código JavaScript. Uma UDF aceita colunas de entrada, executa ações na entrada e retorna o resultado dessas ações como um valor.

É possível definir uma UDF como permanente ou temporária. É possível reutilizar UDFs permanentes em várias consultas, enquanto as UDFs temporárias existem apenas no escopo de uma única consulta.

Observação: UDFs permanentes podem ser chamadas com segurança quando compartilhadas entre proprietários. UDFs não podem modificar dados, conversar com sistemas externos ou enviar registros para o pacote de operações do Google Cloud ou aplicativos semelhantes.

Para criar um conjunto de dados, use a instrução CREATE FUNCTION. Para excluir uma função definida pelo usuário permanente, use a instrução DROP FUNCTION. As UDFs temporárias expiram assim que a consulta é concluída. A instrução DROP FUNCTION só é compatível com UDFs temporárias em consultas de várias instruções e procedimentos.

Para informações sobre UDFs no SQL legado, consulte Funções definidas pelo usuário no SQL legado.

UDFs de SQL

O exemplo a seguir cria uma UDF em SQL temporária chamada AddFourAndDivide e a chama de uma instrução SELECT:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
  RETURNS FLOAT64
  AS ((x + 4) / y);

SELECT val, AddFourAndDivide(val, 2)
  FROM UNNEST([2,3,5,8]) AS val;

Este exemplo produz a saída a seguir:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

O próximo exemplo cria a mesma função que uma UDF permanente:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
  RETURNS FLOAT64
  AS ((x + 4) / y);

Como essa UDF é permanente, é preciso especificar um conjunto de dados para a função (mydataset neste exemplo). Depois de executar a instrução CREATE FUNCTION, é possível chamar a função de uma consulta:

SELECT val, mydataset.AddFourAndDivide(val, 2)
  FROM UNNEST([2,3,5,8,12]) AS val;

Parâmetros de UDF do SQL com modelo

Um parâmetro com um tipo igual a 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.

O exemplo a seguir mostra uma UDF em SQL que usa um parâmetro com modelo.

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;

Este exemplo produz a saída a seguir:

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

O próximo exemplo 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 lastArrayElement(x) as last_element
  FROM (SELECT [2,3,5,8,13] as x)

Este exemplo produz a saída a seguir:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Subconsultas escalares

Uma UDF em SQL pode retornar o valor de uma subconsulta escalar. Uma subconsulta escalar precisa selecionar uma única coluna.

O exemplo a seguir mostra uma UDF em SQL que usa uma subconsulta escalar para contar o número de usuários com uma determinada idade em uma tabela de usuário.

CREATE TEMP TABLE users
AS SELECT 1 id, 10 age
UNION ALL SELECT 2, 30
UNION ALL SELECT 3, 10;

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS ((SELECT COUNT(1) FROM users WHERE age = userAge));

SELECT countUserByAge(10) AS count_user_age_10,
       countUserByAge(20) AS count_user_age_20,
       countUserByAge(30) AS count_user_age_30;

Este exemplo produz a saída a seguir:

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

Projeto padrão em expressões SQL

No corpo de uma UDF em SQL, todas as referências a entidades do BigQuery, como tabelas ou visualizações, precisam incluir o ID do projeto, a menos que a entidade resida no mesmo projeto que executa a instrução CREATE FUNCTION.

Por exemplo, considere a seguinte instrução:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM mydataset.mytable))

Se você executar essa instrução em project1 e mydataset.mytable existir em project1, a instrução será bem-sucedida. No entanto, se você executar essa instrução a partir de um projeto diferente, a instrução falhará. Para corrigir o erro, inclua o ID do projeto na referência da tabela:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM project1.mydataset.mytable))

Também é possível referenciar uma entidade em um projeto ou conjunto de dados diferente daquele em que a função é criada:

CREATE FUNCTION project1.mydataset.myfunction()
  AS ((SELECT COUNT(*) FROM project2.another_dataset.another_table))

UDFs de JavaScript

Uma UDF em JavaScript permite chamar o código escrito em JavaScript a partir de uma consulta em SQL.

O exemplo a seguir mostra uma UDF em JavaScript. O código em JavaScript é citado dentro de uma string bruta.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
  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;

Este exemplo produz a saída a seguir:

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

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

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
  RETURNS FLOAT64
  LANGUAGE js AS r"""
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;

O exemplo produz a saída a seguir:

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

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, BIGNUMERIC	Se um valor NUMERIC ou BIGNUMERIC puder ser representado exatamente como um valor de ponto flutuante IEEE 754 e não tiver uma parte fracional, ele será codificado como um número. Esses valores estão no intervalo [-2^53, 2^53]. 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 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, 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 r"""
  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 a funcionalidades das UDFs de JavaScript usando a seção OPTIONS. Essa seção permite especificar bibliotecas de códigos externas 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
r"""
    // 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.

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. Por exemplo, evite o seguinte padrão:

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js AS r"""
var i = 0; // Mutable state

function dontDoThis() {
  return ++i;
}
return dontDoThis()
""";

Usar a memória de maneira eficiente

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

UDFs autorizadas

Funções autorizadas permitem compartilhar resultados de consulta com determinados usuários ou grupos sem conceder a eles acesso às tabelas subjacentes. Por exemplo, uma função autorizada pode calcular uma agregação sobre os dados ou procurar um valor de tabela e usá-lo em um cálculo.

Para mais informações, consulte Como criar funções autorizadas.

Como adicionar descrições a UDFs

Para adicionar uma descrição a uma UDF, siga estas etapas:

Console

1. Acesse a página do BigQuery no Console do Cloud.

   Ir para o BigQuery

2. No painel Explorer, expanda o projeto e o conjunto de dados e selecione a função.

3. No painel Detalhes, clique em Editar detalhes da rotina para editar o texto da descrição.

4. Insira uma nova descrição ou edite uma atual na caixa de diálogo. Clique em Atualizar para salvar o novo texto da descrição.

Você também pode usar uma consulta de SQL padrão para atualizar a descrição usando o parâmetro description do campo OPTIONS. Na caixa Editor de consultas, insira a definição da função e adicione a seguinte linha:

OPTIONS (description="DESCRIPTION");

Substitua DESCRIPTION pela descrição que você quer adicionar.

bq

Usando a sintaxe bq query de UDFs e a ferramenta de linha de comando bq, é possível editar a descrição de uma função na linha de comando. Especifique o SQL padrão com uma sinalização --nouse_legacy_sql ou -- use_legacy_sql=false e insira a definição da função. Adicione a seguinte linha à definição para configurar o parâmetro description no campo OPTIONS:

OPTIONS (description="DESCRIPTION")

Substitua DESCRIPTION pela descrição que você quer adicionar.

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.
• As funções JavaScript que dependem do código nativo podem falhar, por exemplo, se fizerem chamadas restritas do sistema.
• 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 saídas da função JavaScript.
• As operações Bitwise em JavaScript lidam apenas com os 32 bits mais importantes.
• UDFs estão sujeitas a determinados limites de taxa e cota. Para mais informações, consulte Limites de 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 ou de uma visualização lógica, é preciso qualificar o nome com o conjunto de dados. Por exemplo:
  CREATE FUNCTION mydataset.referringFunction() AS (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 e as UDFs permanentes não podem referir-se a UDFs temporárias.