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

Funções definidas pelo usuário no 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.

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]

Cada UDF é formada pelos componentes a seguir:

  • CREATE { TEMPORARY | TEMP } FUNCTION: cria uma nova função. Uma função pode conter zero ou mais named_parameters, cada um consistindo de pares de param_name e param_type separados por vírgulas. Para criar uma UDF, inclua TEMPORARY ou TEMP.
  • RETURNS [data_type]: especifica o tipo de dados retornado pela função. Consulte Tipos de dados de UDF compatíveis para mais informações.
  • LANGUAGE [language]: especifica a linguagem da função. Consulte Linguagens de UDF externa compatíveis para mais informações.
  • AS [external_code]: especifica o código executado pela função. Consulte Regras de uso de aspas para mais informações sobre como adicionar um código a uma UDF.

Exemplos de UDF externas

CREATE TEMPORARY 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 TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY 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 TEMPORARY FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
  return x*y;
""";
CREATE TEMPORARY 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 TEMPORARY 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
  • STRING
  • STRUCT
  • TIMESTAMP

Codificações de tipo do SQL no JavaScript

Alguns tipos do SQL têm mapeamento direto para tipos do JavaScript, outros não.

Como o tipo inteiro de 64 bits não é compatível com o JavaScript, o INT64 não pode ser usado como tipos de entrada ou saída nas UDFs nessa linguagem. Em vez disso, use FLOAT64 para representar valores inteiros como um número, ou STRING para representar valores inteiros como uma string.

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

Cada UDF é formada pelos componentes a seguir:

  • 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. Especifica os parâmetros para a UDF como pares param_type e param_name separados por vírgulas.
  • [RETURNS data_type]. Opcional. Especifica o tipo de dados retornado pela função.
  • AS [sql_expression]. Especifica a expressão SQL avaliada e retornada pela função.

Exemplo de UDF em SQL

O exemplo a seguir mostra uma UDF que emprega uma função SQL.

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

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

  1. Clique no botão ESCREVER CONSULTA.
  2. Clique na guia Editor de consultas.
  3. Clique no botão Mostrar opções.
  4. Desmarque a caixa Usar SQL legado.
  5. 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;
    """;
  6. 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;
  7. 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

  • 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:

Enviar comentários sobre…

Precisa de ajuda? Acesse nossa página de suporte.