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). Com uma UDF, crie uma função usando outra expressão SQL ou outra linguagem de programação, como JavaScript. Nessas funções, as colunas são aceitas como entrada, e o resultado das ações executadas é retornado como um valor. Para informações sobre UDFs no SQL legado, consulte Funções definidas pelo usuário no SQL legado.

As UDFs são temporárias. Isso significa que só é possível usá-las na consulta ou sessão de linha de comando atual. Não use a guia Editor de UDF na IU da web para criar uma função definida pelo usuário utilizada nas consultas em SQL padrão. Na guia Editor de UDF, use o SQL legado.

Sintaxe geral de UDF

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

CREATE  { TEMPORARY | TEMP }  FUNCTION
  function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  { [LANGUAGE language AS """body"""] | [AS (function_definition)] };

named_parameter:
  param_name param_type

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. Para criar uma UDF, inclua TEMPORARY ou TEMP.
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 e o BigQuery inferirá o tipo de resultado da função do corpo da função SQL. Se a função estiver definida em um idioma externo, a cláusula RETURNS será necessária. Consulte os Tipos de dados de UDF compatíveis para saber mais sobre valores permitidos para data_type.
[LANGUAGE language AS """body"""]. Especifica a linguagem externa para a função e o código que define a função.
AS (function_definition). Especifica o código SQL que define a função. function_definition é uma expressão SQL.

Estrutura da UDF externa

Crie UDFs externas usando a estrutura a seguir.

CREATE  { TEMPORARY | TEMP }  FUNCTION function_name ([named_parameter[, ...]])
  [RETURNS data_type]
  [LANGUAGE language]
  AS external_code

Exemplos de UDF externa

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    |
+---------------------------------------------------------------------+---------+

Linguagens de UDF externa compatíveis

Nas UDFs externas, há suporte ao código em JavaScript, o que é especificado usando js como LANGUAGE. Por exemplo:

CREATE TEMP FUNCTION greeting(a STRING)
RETURNS STRING
LANGUAGE js AS """
  return "Hello, " + a + "!";
  """;
SELECT greeting(name) as everyone
FROM UNNEST(["Hannah", "Max", "Jakob"]) AS name;

+----------------+
| everyone       |
+----------------+
| Hello, Hannah! |
| Hello, Max!    |
| Hello, Jakob!  |
+----------------+

Consulte Codificações de tipo do SQL no JavaScript para mais informações sobre mapeamento entre tipos de dados BigQuery e tipos JavaScript.

Tipos de dados de UDF externa compatíveis

Para UDFs externas, 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 externo 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

Estenda a funcionalidades das UDFs externas usando a seção OPTIONS. Nessa seção, especifique bibliotecas externas de código para a UDF.

CREATE TEMP FUNCTION myFunc(a FLOAT64, b STRING)
  RETURNS STRING
  LANGUAGE js AS
"""
    // Assumes 'doInterestingStuff' is defined in one of the library files.
    return doInterestingStuff(a, b);
"""
OPTIONS (
  library="gs://my-bucket/path/to/lib1.js",
  library=["gs://my-bucket/path/to/lib2.js", "gs://my-bucket/path/to/lib3.js"]
);

SELECT myFunc(3.14, 'foo');

No exemplo anterior, os códigos de lib1.js, lib2.js e lib3.js estão disponíveis para qualquer código na seção [external_code] da UDF. Note que os arquivos de biblioteca podem ser especificados com a sintaxe para elemento único ou para matriz.

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 Google Cloud SDK 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.
O limite de execução de cada usuário em um projeto específico é de aproximadamente 6 consultas de UDF em JavaScript simultâneas. Se você receber um erro dizendo que está acima do limite de consulta simultânea, aguarde alguns minutos e tente novamente.
Uma UDF em JavaScript pode alcançar o tempo limite e isso pode 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: Julho 16, 2019.