Funções definidas pelo usuário

Uma função definida pelo usuário (UDF, na sigla em inglês) permite criar uma função usando uma expressão em SQL ou um código JavaScript. Uma UDF aceita colunas de entrada, executa ações na entrada e retorna o resultado dessas ações como um valor.

É possível definir UDFs como permanentes ou temporárias. É possível reutilizar UDFs permanentes em várias consultas, enquanto as UDFs temporárias existem apenas no escopo de uma única consulta.

Para criar um conjunto de dados, use a instrução CREATE FUNCTION. Para excluir uma função definida pelo usuário permanente, use a instrução DROP FUNCTION. As UDFs temporárias expiram assim que a consulta é concluída. A instrução DROP FUNCTION só é compatível com UDFs temporárias em consultas de várias instruções e procedimentos.

Para informações sobre UDFs no SQL legado, consulte Funções definidas pelo usuário no SQL legado.

UDFs de SQL

No exemplo a seguir, criamos uma UDF SQL temporária chamada AddFourAndDivide e chama a UDF de dentro de uma instrução SELECT:

CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

SELECT
  val, AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8]) AS val;

Este exemplo produz a saída a seguir:

+-----+-----+
| val | f0_ |
+-----+-----+
|   2 | 3.0 |
|   3 | 3.5 |
|   5 | 4.5 |
|   8 | 6.0 |
+-----+-----+

O próximo exemplo cria a mesma função que uma UDF permanente:

CREATE FUNCTION mydataset.AddFourAndDivide(x INT64, y INT64)
RETURNS FLOAT64
AS (
  (x + 4) / y
);

Como essa UDF é permanente, é preciso especificar um conjunto de dados para a função (mydataset neste exemplo). Depois de executar a instrução CREATE FUNCTION, é possível chamar a função de uma consulta:

SELECT
  val, mydataset.AddFourAndDivide(val, 2)
FROM
  UNNEST([2,3,5,8,12]) AS val;

Parâmetros de UDF do SQL com modelo

Um parâmetro com um tipo igual a 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 em sql_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.

O exemplo a seguir mostra uma UDF em SQL que usa um parâmetro com modelo.

CREATE TEMP FUNCTION addFourAndDivideAny(x ANY TYPE, y ANY TYPE)
AS (
  (x + 4) / y
);

SELECT
  addFourAndDivideAny(3, 4) AS integer_input,
  addFourAndDivideAny(1.59, 3.14) AS floating_point_input;

Este exemplo produz a saída a seguir:

+----------------+-----------------------+
| integer_input  |  floating_point_input |
+----------------+-----------------------+
| 1.75           | 1.7802547770700636    |
+----------------+-----------------------+

O próximo exemplo 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
  lastArrayElement(x) AS last_element
FROM (
  SELECT [2,3,5,8,13] AS x
);

Este exemplo produz a saída a seguir:

+--------------+
| last_element |
+--------------+
| 13           |
+--------------+

Subconsultas escalares

Uma UDF em SQL pode retornar o valor de uma subconsulta escalar. Uma subconsulta escalar precisa selecionar uma única coluna.

O exemplo a seguir mostra uma UDF em SQL que usa uma subconsulta escalar para contar o número de usuários com uma determinada idade em uma tabela de usuário.

CREATE TEMP TABLE users
AS (
  SELECT
    1 AS id, 10 AS age
  UNION ALL
  SELECT
    2 AS id, 30 AS age
  UNION ALL
  SELECT
    3 AS id, 10 AS age
);

CREATE TEMP FUNCTION countUserByAge(userAge INT64)
AS (
  (SELECT COUNT(1) FROM users WHERE age = userAge)
);

SELECT
  countUserByAge(10) AS count_user_age_10,
  countUserByAge(20) AS count_user_age_20,
  countUserByAge(30) AS count_user_age_30;

Este exemplo produz a saída a seguir:

+-------------------+-------------------+-------------------+
| count_user_age_10 | count_user_age_20 | count_user_age_30 |
+-------------------+-------------------+-------------------+
|                 2 |                 0 |                 1 |
+-------------------+-------------------+-------------------+

Projeto padrão em expressões SQL

No corpo de uma UDF em SQL, todas as referências a entidades do BigQuery, como tabelas ou visualizações, precisam incluir o ID do projeto, a menos que a entidade resida no mesmo projeto que contém a UDF.

Por exemplo, considere a seguinte instrução:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM mydataset.mytable)
);

Se você executar essa instrução em project1 e mydataset.mytable existir em project1, a instrução será bem-sucedida. No entanto, se você executar essa instrução a partir de um projeto diferente, a instrução falhará. Para corrigir o erro, inclua o ID do projeto na referência da tabela:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project1.mydataset.mytable)
);

Também é possível referenciar uma entidade em um projeto ou conjunto de dados diferente daquele em que a função é criada:

CREATE FUNCTION project1.mydataset.myfunction()
AS (
  (SELECT COUNT(*) FROM project2.another_dataset.another_table)
);

UDFs de JavaScript

Uma UDF em JavaScript permite chamar o código escrito em JavaScript a partir de uma consulta em SQL. As UDFs de JavaScript normalmente consomem mais recursos de slot do que as consultas SQL padrão, diminuindo o desempenho do job. Se a função puder ser expressa em SQL, geralmente é mais conveniente executar o código como um job de consulta SQL padrão.

O exemplo a seguir mostra uma UDF em JavaScript. O código em JavaScript é citado dentro de uma string bruta.

CREATE TEMP FUNCTION multiplyInputs(x FLOAT64, y FLOAT64)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  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;

Este exemplo produz a saída a seguir:

+-----+-----+--------------+
| x   | y   | product      |
+-----+-----+--------------+
| 1   | 5   | 5            |
| 2   | 10  | 20           |
| 3   | 15  | 45           |
+-----+-----+--------------+

O exemplo a seguir soma os valores de todos os campos chamados foo na string em JSON fornecida.

CREATE TEMP FUNCTION SumFieldsNamedFoo(json_row STRING)
RETURNS FLOAT64
LANGUAGE js
AS r"""
  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;

O exemplo produz a saída a seguir:

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

Tipos de dados de UDF em JavaScript compatíveis

Alguns tipos do SQL têm um mapeamento direto para os tipos do JavaScript, mas 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, BIGNUMERIC Se um valor NUMERIC ou BIGNUMERIC puder ser representado exatamente como um valor de ponto flutuante IEEE 754 e não tiver uma parte fracional, ele será codificado como um número. Esses valores estão no intervalo [-253, 253]. Caso contrário, o valor é codificado como um número.
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
JSON

Os OBJETOS, MATRIZES e VALORES JSON são convertidos em OBJETOS, MATRIZES e VALORES JavaScript equivalentes.

O JavaScript não é compatível com valores INT64. Somente números JSON no intervalo [-253, 253] são convertidos exatamente. Caso contrário, o valor numérico será arredondado, o que pode resultar em perda de precisã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 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, o BigQuery aguardará Promise até que Promise 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 cotação

Coloque o código JavaScript entre aspas. Para snippets de 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;

Este exemplo produz a saída a seguir:

+-----------+-----------+
| val       | result    |
+-----------+-----------+
| 1         | 2.0       |
| 2         | 3.0       |
| 3         | 4.0       |
| 4         | 5.0       |
| 5         | 6.0       |
+-----------+-----------+

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 r"""
  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;

Este exemplo produz a saída a seguir:

+-----------------------+
| everyone              |
+-----------------------+
| Good Morning, Hannah! |
| Good Morning, Max!    |
| Good Morning, Jakob!  |
+-----------------------+

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 r"""
  // 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.

Práticas recomendadas para UDFs em JavaScript

Pré-filtrar a entrada

Se for possível filtrar a entrada antes de transmiti-la para uma UDF em JavaScript, a consulta poderá ser mais rápida e mais barata.

Evitar o estado mutável permanente

Não armazene nem acesse o estado mutável em chamadas à UDF em JavaScript. Por exemplo, evite o seguinte padrão:

-- Avoid this pattern
CREATE FUNCTION temp.mutable()
RETURNS INT64
LANGUAGE js
AS r"""
  var i = 0; // Mutable state
  function dontDoThis() {
    return ++i;
  }
  return dontDoThis()
""";

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.

Autorizar rotinas

É possível autorizar UDFs como rotinas. As rotinas autorizadas permitem compartilhar resultados de consultas com usuários ou grupos específicos sem conceder acesso às tabelas subjacentes que geraram os resultados. Por exemplo, uma rotina autorizada pode calcular uma agregação sobre os dados ou procurar um valor de tabela e usá-lo em um cálculo. Para mais informações, consulte Rotinas autorizadas.

Adicionar descrições a UDFs

Para adicionar uma descrição a uma UDF, siga estas etapas:

Console

  1. Acesse a página do BigQuery no console Google Cloud .

    Acessar o BigQuery

  2. No painel Explorer, expanda o projeto e o conjunto de dados e selecione a função.

  3. No painel Detalhes, clique em Editar detalhes da rotina para editar o texto da descrição.

  4. 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.

SQL

Para atualizar a descrição de uma função, recrie-a usando a instrução DDL CREATE FUNCTION e defina o campo description na lista OPTIONS:

  1. No console Google Cloud , acesse a página BigQuery.

    Acessar o BigQuery

  2. No editor de consultas, digite a seguinte instrução:

    CREATE OR REPLACE FUNCTION mydataset.my_function(...)
AS (
  ...
) OPTIONS (
  description = 'DESCRIPTION'
);

  3. Clique em Executar.

Para mais informações sobre como executar consultas, acesse Executar uma consulta interativa.

Criar rotinas de mascaramento personalizadas

Crie UDFs para usar com rotinas de mascaramento personalizadas. Crie conjuntos de dados dedicados e configure as permissões adequadas do IAM para gerenciar UDFs de mascaramento. As rotinas de mascaramento personalizadas precisam atender aos seguintes requisitos:

  • A rotina de mascaramento personalizada precisa ser uma UDF do SQL.
  • Na função OPTIONS, a opção data_governance_type precisa ser definida como DATA_MASKING.
  • As rotinas de mascaramento personalizadas são compatíveis com as seguintes funções:
  • As rotinas de mascaramento personalizadas podem não aceitar entradas ou uma entrada nos tipos de dados do BigQuery, exceto GEOGRAPHY e STRUCT. GEOGRAPHY e STRUCT não são compatíveis com rotinas de mascaramento personalizadas.
  • Os parâmetros de UDF do SQL com modelo não são compatíveis.
  • Quando uma entrada é fornecida, os tipos de dados de entrada e saída precisam ser os mesmos.
  • É necessário fornecer um tipo de saída.
  • Nenhuma outra UDF, subconsulta, tabela ou visualização pode ser referenciada no corpo de definição.
  • Depois de criar uma rotina de mascaramento, ela não pode ser alterada para uma função padrão. Isso significa que, se a opção data_governance_type estiver definida como DATA_MASKING, não será possível alterar o data_governance_type usando instruções DDL ou chamadas de API.
  • As rotinas de mascaramento personalizadas oferecem suporte às instruções CASE e CASE expr. Os operadores a seguir podem ser usados com as instruções CASE e CASE expr:

Por exemplo, uma rotina de mascaramento que substitui o CPF ou CNPJ de um usuário por XXX-XX-XXXX pode ter a seguinte aparência:

  CREATE OR REPLACE FUNCTION SSN_Mask(ssn STRING) RETURNS STRING
  OPTIONS (data_governance_type="DATA_MASKING") AS (
  SAFE.REGEXP_REPLACE(ssn, '[0-9]', 'X') # 123-45-6789 -> XXX-XX-XXXX
  );

Os exemplos de hash abaixo com o sal fornecido pelo usuário, usando a função SHA256:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine1`(
  ssn STRING)
RETURNS STRING OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  CAST(SHA256(CONCAT(ssn, 'salt')) AS STRING format 'HEX')
);

O exemplo a seguir mascara uma coluna DATETIME com um valor constante:

CREATE OR REPLACE FUNCTION `project.dataset.masking_routine2`(
  column DATETIME)
RETURNS DATETIME OPTIONS (data_governance_type = 'DATA_MASKING')
AS (
  SAFE_CAST('2023-09-07' AS DATETIME)
);

Como prática recomendada, use o prefixo SAFE sempre que possível para evitar a exposição de dados brutos por mensagens de erro.

Depois de criada, a rotina de mascaramento personalizada estará disponível como uma regra de mascaramento em Criar políticas de dados.

Funções com contribuição da comunidade

As UDFs com contribuição da comunidade estão disponíveis no conjunto de dados públicos bigquery-public-data.persistent_udfs e no repositório do bigquery-utils do GitHub (em inglês) de código aberto. É possível acessar todas as UDFs da comunidade no console Google Cloud . Para isso, marque com estrela o projeto bigquery-public-data no painel Explorador e depois expanda o conjunto de dados persistent_udfs aninhados nesse projeto.

Permitir o acesso a funções enviadas pela comunidade em um perímetro do VPC Service Controls

Para projetos em que o VPC Service Controls está ativado e o BigQuery é um serviço protegido, é necessário definir uma regra de saída para o projeto bigquery-public-data (ID do projeto: 1057666841514).

Essa regra precisa ativar as seguintes operações:

  • bigquery.routines.get (para usar rotinas)
  • bigquery.tables.getData (para consultar tabelas do BigQuery)

O código a seguir mostra um exemplo de configuração YAML:

  - egressFrom:
      identityType: ANY_IDENTITY
    egressTo:
      operations:
      - serviceName: 'bigquery.googleapis.com'
        methodSelectors:
        - permission: 'bigquery.routines.get'
        - permission: 'bigquery.tables.getData'
      resources:
      - projects/1057666841514 # bigquery-public-data

Se você quiser contribuir com as UDFs nesse repositório, consulte Como contribuir com UDFs para conferir instruções.

Limitações

As limitações a seguir se aplicam às funções permanentes e temporárias definidas pelo usuário.

  • Os objetos DOM Window, Document e Node, bem como as funções que os exigem, não são compatíveis.
  • As funções JavaScript operam em um ambiente de sandbox, e as funções que dependem do código do sistema subjacente podem falhar devido a chamadas restritas do sistema.
  • 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 saídas da função JavaScript.
  • As operações Bitwise em JavaScript lidam apenas com os 32 bits mais importantes.
  • UDFs estão sujeitas a determinados limites de taxa e cota. Para mais informações, consulte Limites de 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 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_name não pode conter pontos.
  • As visualizações e as UDFs permanentes não podem referir-se a UDFs temporárias.