O BigQuery é compatível com funções definidas pelo usuário (UDFs). Uma UDF permite criar uma função usando outra 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 e UDFs temporárias em uma única consulta.
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: [determinism_specifier] LANGUAGE js [OPTIONS (library = library_array)] AS javascript_code determinism_specifier: { DETERMINISTIC | NOT DETERMINISTIC }
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áusulaIF NOT EXISTS.project_name é o nome do projeto onde você está criando a função. O padrão é o projeto que executa essa consulta DDL. Se o nome do projeto tiver caracteres especiais, como dois pontos, ele deverá estar entre crases
`(exemplo:`google.com:my_project`).dataset_name é o nome do conjunto de dados onde você está criando a função. O padrão na solicitação é
defaultDataset.named_parameter. Consiste em um par de
param_nameeparam_typeseparado por vírgula. O valor deparam_typeé um tipo de dados do BigQuery. Para uma UDF SQL, o valor deparam_typetambém pode serANY TYPE.determinism_specifier. Aplica-se somente a funções JavaScript definidas pelo usuário. Fornece uma dica ao BigQuery sobre se o resultado da consulta pode ser armazenado em cache. Pode ser um dos seguintes valores:
DETERMINISTIC: a função sempre retorna o mesmo resultado quando os mesmos argumentos são transmitidos. O resultado da consulta é potencialmente armazenável em cache. Por exemplo, se a funçãoadd_one(i)sempre retornari + 1, a função será determinista.NOT DETERMINISTIC: a função nem sempre retorna o mesmo resultado quando os mesmos argumentos são transmitidos e, portanto, não é armazenável em cache. Por exemplo, seadd_random(i)retornari + rand(), a função não é determinística e o BigQuery não usará resultados armazenados em cache.Se todas as funções invocadas forem DETERMINISTIC, o BigQuery tentará armazenar o resultado em cache, a menos que os resultados não possam ser armazenados em cache por outros motivos. Para mais informações, consulte Como usar resultados de consulta armazenados em cache.
[RETURNS data_type]. Especifica o tipo de dados retornado pela função.
- Se a função estiver definida em SQL, a cláusula
RETURNSserá opcional. Se a cláusulaRETURNSfor 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
RETURNSserá obrigatória. Para mais informações sobre valores permitidos paradata_type, consulte Tipos de dados de UDF JavaScript compatíveis.
- Se a função estiver definida em SQL, a cláusula
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, use instruções DROP FUNCTION somente 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 de tipo 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.
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 [DETERMINISTIC | NOT DETERMINISTIC] 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 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 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
"""
// 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 do BigQuery
Use a IU da Web do BigQuery para executar consultas usando uma ou mais UDFs.
Como executar uma consulta com uma UDF
- Clique no botão ESCREVER CONSULTA.
- Clique na guia Editor de consultas.
- Clique no botão Mostrar opções.
- Desmarque a caixa de seleção Usar SQL legado.
-
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; """; -
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; -
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 Cloud para executar uma consulta com uma ou mais UDFs.
Use a seguinte sintaxe 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
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.
Como adicionar descrições a UDFs
Para adicionar uma descrição a uma UDF, siga estas etapas:
Console
Abra a IU da Web do BigQuery no Console do Cloud.
No painel Recursos, selecione sua função.
No painel Detalhes, clique no ícone de lápis ao lado de Descrição 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") AS """
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") AS """
Substitua DESCRIPTION pela descrição que você quer adicionar.
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 de SQL legado que contém UDFs: seis consultas simultâneas.
- 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
O limite de taxa simultânea para consultas de 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 de SQL padrão.
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,DocumenteNode, bem como as funções que os exigem, não são compatíveis. - As funções JavaScript que dependem do 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 JavaScript.
- As operações Bitwise em JavaScript lidam apenas com os 32 bits mais importantes.
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_namenão pode conter pontos. - As visualizações e as UDFs permanentes não podem referir-se a UDFs temporárias.