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áusulaIF NOT EXISTS. - 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 do SQL, o valor deparam_typetambém pode serANY TYPE. - [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. Consulte os Tipos de dados de UDF em JavaScript compatíveis para mais informações sobre os valores permitidos paradata_type.
- 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, 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 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.
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
- Clique no botão ESCREVER CONSULTA.
- Clique na guia Editor de consultas.
- Clique no botão Mostrar opções.
- Desmarque a caixa 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 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.
- 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 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.
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. - 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_namenão pode conter pontos. - As visualizações lógicas e as UDFs permanentes não podem fazer referência a UDFs temporárias.