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.
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 emsql_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 [-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 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
Acesse a página do BigQuery no Console do Cloud.
No painel Explorer, expanda o projeto e o conjunto de dados e selecione a função.
No painel Detalhes, clique em
Editar detalhes da rotina para editar o texto da descrição.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
eNode
, 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.