Funções definidas pelo usuário do SQL padrão

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. Para informações sobre UDFs permanentes, consulte a documentação CREATE FUNCTION.

Sintaxe da UDF

As funções definidas pelo usuário no BigQuery usam a seguinte sintaxe:

CREATE { [TEMPORARY | TEMP] FUNCTION | OR REPLACE [TEMPORARY | TEMP] FUNCTION |
    [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 consiste nos seguintes componentes:

CREATE { TEMPORARY | TEMP } FUNCTION. Cria uma nova função. Uma função pode conter zero ou mais named_parameters. É preciso incluir TEMPORARY ou TEMP ao criar uma UDF temporária.
named_parameter. Consiste em um par de param_name e param_type separado por vírgulas. O valor de param_type é um tipo de dados do BigQuery. Para uma UDF do SQL, o valor de param_type também pode ser ANY TYPE.
[RETURNS data_type]. Especifica o tipo de dados retornado pela função.
- Se a função estiver definida em SQL, a cláusula RETURNS será opcional. Se a cláusula RETURNS for 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. O tipo de retorno inferido não permanecerá com a função. Se a definição da função referenciar uma outra, o tipo de retorno inferido poderá depender do tipo de retorno da função referenciada e será atualizado automaticamente quando a função referenciada for atualizada.
- Se a função estiver definida em JavaScript, a cláusula RETURNS será obrigatória. Consulte os Tipos de dados de UDF do JavaScript compatíveis para mais informações sobre os valores permitidos para data_type.
AS [sql_expression]. Especifica a expressão SQL que define a função.
AS javascript_code. Especifica a definição de uma função do JavaScript. javascript_code é uma string literal.

Estrutura de UDF em JavaScript

Crie UDFs de JavaScript usando a estrutura a seguir.

CREATE  { TEMPORARY | TEMP }  FUNCTION 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           |
+-----+-----+--------------+

Crie várias UDFs antes de uma consulta. 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(x, y) as product,
  divideByTwo(x) as half_x,
  divideByTwo(y) as half_y
FROM numbers;

+-----+-----+--------------+--------+--------+
| x   | y   | product      | half_x | half_y |
+-----+-----+--------------+--------+--------+
| 1   | 5   | 5            | 0.5    | 2.5    |
| 2   | 10  | 20           | 1      | 5      |
| 3   | 15  | 45           | 1.5    | 7.5    |
+-----+-----+--------------+--------+--------+

Passe o resultado de uma UDF como 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 Codificações de tipo do SQL no JavaScript para mais informações sobre mapeamento dos tipos de dados do BigQuery nos tipos do JavaScript.

Tipos de dados de UDF em JavaScript compatíveis

Para UDFs de JavaScript, os seguintes tipos de dados são compatíveis no BigQuery:

ARRAY
BOOL
BYTES
DATE
FLOAT64
NUMERIC
STRING
STRUCT
TIMESTAMP

Codificações de tipo do SQL no JavaScript

Alguns tipos do SQL têm mapeamento direto para tipos do JavaScript, mas outros nã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 em UDFs JavaScript. Nesse caso, o corpo da função JavaScript pode retornar um número JavaScript ou uma string. Em seguida, o BigQuery converte um desses tipos para INT64.

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 [-2⁵³, 2⁵³]. 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 contendo a fração de `microsecond` do timestamp
DATE	DATE

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 múltiplas 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!  |
+-----------------------+

Estrutura da UDF em SQL

Crie UDFs de SQL usando a sintaxe a seguir:

CREATE  { TEMPORARY | TEMP }  FUNCTION 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 pode corresponder a mais de um tipo de argumento no tempo da chamada de função. Se uma assinatura de função incluir um parâmetro com modelo, o BigQuery permitirá que as chamadas de função transmitam um dos vários tipos de argumento para a função.

As assinaturas SQL de função definidas pelo usuário podem conter o seguinte valor com modelo param_type:

ANY TYPE. A função aceita uma entrada de qualquer tipo para este argumento. Se mais de um parâmetro tiver o tipo ANY TYPE, o BigQuery não aplicará qualquer relação entre esses argumentos no momento da criação da função. No entanto, passando os argumentos da função de tipos que são incompatí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     |
+------------+-----------+

Como incluir bibliotecas externas

Use a seção OPTIONS para estender as funcionalidades das UDFs de JavaScript. 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 está 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

O volume de dados de saída da UDF em JavaScript durante o processamento de uma única linha precisa ser de aproximadamente 5 MB ou menos.
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, embora possa 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 JS.
Um job de consulta pode ter no máximo 50 recursos de UDF em JavaScript, ou seja, blobs de código inline ou arquivos externos.
Cada blob de código inline está limitado a um tamanho máximo de 32 KB.
Cada recurso de código externo está limitado a um tamanho máximo de 1 MB.

Limitações

Para UDFs temporárias, function_name não pode conter pontos.
Os objetos DOM Window, Document e Node, bem como as funções que usam esses objetos, não são compatíveis.
Funções JavaScript que dependem de código nativo não são compatíveis.
As operações Bitwise em JavaScript lidam apenas com os 32 bits mais significativos.
As consultas que invocam funções definidas pelo usuário não podem usar resultados armazenados em cache devido à natureza não determinista delas.
Não é possível referenciar uma tabela em uma UDF.

Esta página foi útil? Conte sua opinião sobre:

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Última atualização: Outubro 1, 2019.