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 UDFs como permanentes ou temporárias. É 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
No exemplo a seguir, criamos uma UDF SQL temporária chamada AddFourAndDivide
e chama a UDF de dentro 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 resulta 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_input, addFourAndDivideAny(1.59, 3.14) AS floating_point_input;
Este exemplo produz a saída a seguir:
+----------------+-----------------------+
| integer_input | floating_point_input |
+----------------+-----------------------+
| 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 AS id, 10 AS age UNION ALL SELECT 2 AS id, 30 AS age UNION ALL SELECT 3 AS id, 10 AS age ); 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 contém a UDF.
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. As UDFs de JavaScript normalmente consomem mais recursos de slot do que as consultas SQL padrão, diminuindo o desempenho do job. Se a função puder ser expressa em SQL, geralmente é mais conveniente executar o código como um job de consulta SQL padrão.
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 um mapeamento direto para os tipos do JavaScript, mas 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, o valor é codificado como um número. |
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 |
JSON |
Os OBJETOS, MATRIZES e VALORES JSON são convertidos em OBJETOS, MATRIZES e VALORES JavaScript equivalentes. O JavaScript não é compatível com valores INT64. Somente números JSON no intervalo [-253, 253] são convertidos exatamente. Caso contrário, o valor numérico será arredondado, o que pode resultar em perda de precisão. |
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 Promise
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 cotação
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;
Este exemplo produz a saída a seguir:
+-----------+-----------+
| val | result |
+-----------+-----------+
| 1 | 2.0 |
| 2 | 3.0 |
| 3 | 4.0 |
| 4 | 5.0 |
| 5 | 6.0 |
+-----------+-----------+
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;
Este exemplo produz a saída a seguir:
+-----------------------+ | everyone | +-----------------------+ | Good Morning, Hannah! | | Good Morning, Max! | | Good Morning, Jakob! | +-----------------------+
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 antes de transmiti-la para uma UDF em JavaScript, a consulta poderá 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.
Autorizar rotinas
É possível autorizar UDFs como rotinas. As rotinas autorizadas permitem compartilhar resultados de consultas com usuários ou grupos específicos sem conceder acesso às tabelas subjacentes que geraram os resultados. Por exemplo, uma rotina 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 Rotinas autorizadas.
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 Google 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.
SQL
Para atualizar a descrição de uma função, recrie-a usando
a instrução DDL CREATE FUNCTION
e defina o campo description
na lista OPTIONS
:
No Console do Google Cloud, acesse a página BigQuery.
No editor de consultas, digite a seguinte instrução:
CREATE OR REPLACE FUNCTION mydataset.my_function(...) AS ( ... ) OPTIONS ( description = 'DESCRIPTION' );
Clique em
Executar.
Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.
Criar rotinas de mascaramento personalizadas
Crie UDFs para usar com rotinas de mascaramento personalizadas. As rotinas de mascaramento personalizadas precisam atender aos seguintes requisitos:
- A rotina de mascaramento personalizada precisa ser uma UDF do SQL.
- Na função
OPTIONS
, a opçãodata_governance_type
precisa ser definida comoDATA_MASKING
. - As rotinas de mascaramento personalizadas são compatíveis com as seguintes funções:
- Função de string
REGEXP_REPLACE
- Função hash
FARM_FINGERPINT
- Função hash
MD5
- Função hash
SHA1
- Função hash
SHA256
- Função hash
SHA512
- Função de conversão
CAST
- Função de string
CONCAT
- Função de string
REPLACE
- Função de string
REGEX_EXTRACT
- Função de string
SUBSTRING
- Função de string
TO_BASE32
- Função de string
TO_BASE64
- Função de string
FROM_BASE32
- Função de string
FROM_BASE64
- Função de string
TO_HEX
- Função de string
FROM_HEX
- Função utilitária
GENERATE_UUID
- Função DATE
CURRENT_DATE
- Função DATETIME
CURRENT_DATETIME
- Função TIME
CURRENT_TIME
- Função TIMESTAMP
CURRENT_TIMESTAMP
- Função de conversão
SAFE_CAST
- Função de string
LENGTH
- Função de string
STARTS_WITH
- Função de criptografia AEAD
KEYS.KEYSET_CHAIN
- Função de criptografia AEAD
AEAD.ENCRYPT
com keyset_chain (o uso de chave bruta não é compatível) - Função de criptografia AEAD
AEAD.DECRYPT_BYTES
comKEYS.KEYSET_CHAIN
(uso de chave bruta não é aceito) - Função de criptografia AEAD
AEAD.DECRYPT_STRING,
comKEYS.KEYSET_CHAIN
(o uso de chave bruta não é compatível) - Função de criptografia AEAD
DETERMINISTIC_ENCRYPT
comKEYS.KEYSET_CHAIN
(o uso de chave bruta não é compatível) - Função de criptografia AEAD
DETERMINISTIC_DECRYPT_BYTES
comKEYS.KEYSET_CHAIN
(o uso de chave bruta não é compatível) - Função de criptografia AEAD
DETERMINISTIC_DECRYPT_STRING
comKEYS.KEYSET_CHAIN
(o uso de chave bruta não é compatível)
- Função de string
- As rotinas de mascaramento personalizadas podem não aceitar entradas ou uma entrada nos
tipos de dados do BigQuery,
exceto
GEOGRAPHY
eSTRUCT
.GEOGRAPHY
eSTRUCT
não são compatíveis com rotinas de mascaramento personalizadas. - Os parâmetros de UDF do SQL com modelo não são compatíveis.
- Quando uma entrada é fornecida, os tipos de dados de entrada e saída precisam ser os mesmos.
- É necessário fornecer um tipo de saída.
- Nenhuma outra UDF, subconsulta, tabela ou visualização pode ser referenciada no corpo de definição.
- Depois de criar uma rotina de mascaramento, ela não pode ser alterada para uma
função padrão. Isso significa que, se a opção
data_governance_type
estiver definida comoDATA_MASKING
, não será possível alterar odata_governance_type
usando instruções DDL ou chamadas de API.
Por exemplo, uma rotina de mascaramento que substitui o CPF ou CNPJ de um usuário
por XXX-XX-XXXX
pode ter a seguinte aparência:
CREATE OR REPLACE FUNCTION SSN_Mask
(ssn STRING) RETURNS STRING
OPTIONS (data_governance_type="DATA_MASKING") AS (
SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
);
Os exemplos de hash abaixo com o sal fornecido pelo usuário, usando a função SHA256
:
CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`( ssn STRING) RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING') AS ( CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX') );
O exemplo a seguir mascara uma coluna DATETIME
com um valor constante:
CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`( column DATETIME) RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING') AS ( SAFE_CAST('2023-09-07' AS DATETIME) );
Como prática recomendada, use o prefixo
SAFE
sempre que possível para evitar a exposição de dados brutos por mensagens de erro.
Depois de criada, a rotina de mascaramento personalizada estará disponível como uma regra de mascaramento em Criar políticas de dados.
Funções com contribuição da comunidade
As UDFs com contribuição da comunidade estão disponíveis no
conjunto de dados públicos bigquery-public-data.persistent_udfs
e no
repositório do bigquery-utils
do GitHub (em inglês) de código aberto.
É possível acessar todas as
UDFs da comunidade
no console do Google Cloud. Para isso, basta marcar com estrela
o projeto bigquery-public-data
no painel Explorer e depois expandir
o conjunto de dados aninhado persistent_udfs
nesse projeto.
Se você quiser contribuir com as UDFs nesse repositório, consulte Como contribuir com UDFs para conferir instruções.
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.