Funções definidas pelo utilizador

Uma função definida pelo utilizador (FDU) permite-lhe criar uma função através de uma expressão SQL ou código JavaScript. Uma FDU aceita colunas de entrada, realiza ações na entrada e devolve o resultado dessas ações como valor.

Pode definir FDUs como persistentes ou temporárias. Pode reutilizar FDUs persistentes em várias consultas, enquanto as FDUs temporárias existem apenas no âmbito de uma única consulta.

Para criar uma FDU, use a declaração CREATE FUNCTION. Para eliminar uma função definida pelo utilizador persistente, use a declaração DROP FUNCTION. As FDUs temporárias expiram assim que a consulta terminar. A declaração DROP FUNCTION só é suportada para FDUs temporárias em consultas com várias declarações e procedimentos.

Para obter informações sobre FDUs no SQL antigo, consulte o artigo Funções definidas pelo utilizador no SQL antigo.

UDFs de SQL

O exemplo seguinte cria uma FDU de SQL temporária denominada AddFourAndDivide e chama a FDU a partir de uma declaraçã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 o seguinte resultado:

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

O exemplo seguinte cria a mesma função como uma FDU persistente:

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

Uma vez que esta FDU é persistente, tem de especificar um conjunto de dados para a função (mydataset neste exemplo). Depois de executar a declaração CREATE FUNCTION, pode chamar a função a partir de uma consulta:

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

Parâmetros de UDFs de SQL baseados em modelos

Um parâmetro com um tipo igual a ANY TYPE pode corresponder a mais do que um tipo de argumento quando a função é chamada.

  • Se mais do que um parâmetro tiver o tipo ANY TYPE, o BigQuery não aplica nenhuma relação de tipo entre estes argumentos.
  • O tipo de retorno da função não pode ser ANY TYPE. Tem de ser omitido, o que significa que é determinado automaticamente com base em sql_expression, ou ser um tipo explícito.
  • A transmissão dos argumentos da função de tipos incompatíveis com a definição da função resulta num erro no momento da chamada.

O exemplo seguinte mostra uma FDU de SQL que usa um parâmetro baseado em modelos.

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 o seguinte resultado:

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

O exemplo seguinte usa um parâmetro baseado em modelos para devolver 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 o seguinte resultado:

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

Subconsultas escalares

Uma FDU SQL pode devolver o valor de uma subconsulta escalar. Uma subconsulta escalar tem de selecionar uma única coluna.

O exemplo seguinte mostra uma UDF de SQL que usa uma subconsulta escalar para contar o número de utilizadores com uma determinada idade numa tabela de utilizadores:

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 o seguinte resultado:

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

Projeto predefinido em expressões SQL

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

Por exemplo, considere a seguinte declaração:

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

Se executar esta declaração a partir de project1 e mydataset.mytable existir em project1, a declaração é bem-sucedida. No entanto, se executar esta declaração a partir de um projeto diferente, a declaração falha. 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 pode fazer referência a uma entidade num projeto ou conjunto de dados diferente daquele em que cria a função:

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

UDFs JavaScript

Uma FDU de JavaScript permite-lhe chamar código escrito em JavaScript a partir de uma consulta SQL. Normalmente, as FDUs JavaScript consomem mais recursos de espaços em comparação com as consultas SQL padrão, o que diminui o desempenho das tarefas. Se a função puder ser expressa em SQL, é frequentemente mais otimizado executar o código como uma tarefa de consulta SQL padrão.

O exemplo seguinte mostra uma FUD JavaScript. O código JavaScript está entre aspas numa string não processada.

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 o seguinte resultado:

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

O exemplo seguinte 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 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 o seguinte resultado:

+---------------------------------------------------------------------+---------+
| 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 FDU JavaScript suportados

Alguns tipos de SQL têm um mapeamento direto para tipos de JavaScript, mas outros não. O BigQuery representa os tipos da seguinte forma:

Tipo de dados do BigQuery Tipo de dados JavaScript
ARRAY ARRAY
BOOL BOOLEAN
BYTES STRING codificado em base64
FLOAT64 NÚMERO
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 fracionária, o valor é codificado como um número. Estes valores estão no intervalo [-253, 253]. Caso contrário, o valor é codificado como uma string.
STRING STRING
STRUCT OBJECTO em que cada campo STRUCT é um campo com nome
TIMESTAMP DATE com um campo de microssegundos que contém a microsecond fração da data/hora
DATA DATA
JSON

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

O JavaScript não suporta valores INT64. Apenas os números JSON no intervalo [-253, 253] são convertidos exatamente. Caso contrário, o valor numérico é arredondado, o que pode resultar numa perda de precisão.

Uma vez que o JavaScript não suporta um tipo de número inteiro de 64 bits, INT64 não é suportado como um tipo de entrada para UDFs de JavaScript. Em alternativa, use FLOAT64 para representar valores inteiros como um número ou STRING para representar valores inteiros como uma string.

O BigQuery suporta INT64 como um tipo de retorno em UDFs JavaScript. Neste caso, o corpo da função JavaScript pode devolver um número ou uma string JavaScript. Em seguida, o BigQuery converte qualquer um destes tipos em INT64.

Se o valor de retorno da UDF JavaScript for um Promise, o BigQuery aguarda o Promise até que o Promise seja resolvido. Se o Promise for resolvido num estado de conclusão, o BigQuery devolve o respetivo resultado. Se o Promise for resolvido num estado rejeitado, o BigQuery devolve um erro.

Regras de orçamentos

Tem de incluir o código JavaScript entre aspas. Para fragmentos de código de uma linha, pode usar 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 o seguinte resultado:

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

Nos casos em que o fragmento contém aspas ou consiste em 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 o seguinte resultado:

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

Inclua bibliotecas JavaScript

Pode expandir as UDFs de JavaScript através da secção OPTIONS. Esta secção permite-lhe especificar bibliotecas de código externas para a FDU.

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 está disponível para qualquer código na secção [external_code] da FDU.

Práticas recomendadas para UDFs de JavaScript

Pré-filtre a sua entrada

Se a sua entrada puder ser filtrada antes de ser transmitida para uma UDF de JavaScript, a sua consulta pode ser mais rápida e mais barata.

Evite o estado mutável persistente

Não armazene nem aceda ao estado mutável nas chamadas de FDU 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()
""";

Use a memória de forma eficiente

O ambiente de processamento JavaScript tem uma memória limitada disponível por consulta. As consultas UDF JavaScript que acumulam demasiado estado local podem falhar devido ao esgotamento da memória.

Autorize rotinas

Pode autorizar FDUs como rotinas. As rotinas autorizadas permitem-lhe partilhar resultados de consultas com utilizadores ou grupos específicos sem lhes conceder acesso às tabelas subjacentes que geraram os resultados. Por exemplo, uma rotina autorizada pode calcular uma agregação sobre dados ou procurar um valor de tabela e usar esse valor num cálculo. Para mais informações, consulte o artigo Rotinas autorizadas.

Adicione descrições aos CDFs

Para adicionar uma descrição a um FDU, siga estes passos:

Consola

  1. Aceda à página do BigQuery na Google Cloud consola.

    Aceda ao BigQuery

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

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

  4. Na caixa de diálogo, introduza uma descrição na caixa ou edite a descrição existente. Clique em Guardar para guardar o novo texto de descrição.

SQL

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

  1. Na Google Cloud consola, aceda à página BigQuery.

    Aceda ao BigQuery

  2. No editor de consultas, introduza a seguinte declaração:

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

  3. Clique em Executar.

Para mais informações sobre como executar consultas, consulte o artigo Execute uma consulta interativa.

Crie rotinas de ocultação personalizadas

Pode criar FUDs para utilização com rotinas de ocultação personalizadas. Deve criar conjuntos de dados dedicados e configurar as autorizações da IAM adequadas para gerir as UDFs de ocultação. As rotinas de ocultação personalizadas têm de cumprir os seguintes requisitos:

  • A rotina de ocultação personalizada tem de ser uma UDF de SQL.
  • Na função OPTIONS, a opção data_governance_type tem de estar definida como DATA_MASKING.
  • As rotinas de ocultação personalizadas suportam as seguintes funções:
  • As rotinas de ocultação personalizadas podem não aceitar entradas ou aceitar uma entrada nos tipos de dados do BigQuery, com exceção de GEOGRAPHY e STRUCT. O GEOGRAPHY e o STRUCT não são suportados para rotinas de ocultação personalizadas.
  • Os parâmetros de UDFs SQL baseados em modelos não são suportados.
  • Quando é fornecida uma entrada, os tipos de dados de entrada e saída têm de ser iguais.
  • Tem de indicar um tipo de saída.
  • Não é possível referenciar outras FUDs, subconsultas, tabelas ou vistas no corpo da definição.
  • Depois de criar uma rotina de ocultação, não é possível alterá-la para uma função padrão. Isto significa que, se a opção data_governance_type estiver definida como DATA_MASKING, não pode alterar data_governance_type através de declarações DDL ou chamadas API.
  • As rotinas de ocultação personalizadas suportam a declaração CASE e CASE expr. Os seguintes operadores podem ser usados com declarações CASE e CASE expr:

Por exemplo, uma rotina de ocultação que substitui o número da segurança social de um utilizador por XXX-XX-XXXX pode ter o seguinte aspeto:

  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
  );

O exemplo seguinte aplica hash com o sal fornecido pelo utilizador através da 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 seguinte oculta 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 não processados através de mensagens de erro.

Depois de criar a rotina de ocultação personalizada, esta fica disponível como uma regra de ocultação em Criar políticas de dados.

Funções contribuídas pela comunidade

As UDFs contribuídas pela comunidade estão disponíveis no bigquery-public-data.persistent_udfs conjunto de dados público e no bigquery-utils repositório GitHub de código aberto. Pode ver todas as UDFs da comunidade na Google Cloud consola marcando com uma estrela o projeto bigquery-public-data no painel do Explorador e, em seguida, expandindo o conjunto de dados aninhado persistent_udfs nesse projeto.

Permita o acesso a funções contribuídas pela comunidade num perímetro do VPC Service Controls

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

Esta regra tem de permitir as seguintes operações:

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

O código seguinte 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 quiser contribuir para as FDFs neste repositório, consulte o artigo Contribuir com FDFs para ver instruções.

Acesso unificado a rotinas em várias regiões

Para usar FDUs em consultas em várias regiões, a FDU tem de estar disponível em todas as regiões onde é executada uma consulta que contenha a FDU. Por conseguinte, deve criar e manter FDUs em qualquer região onde possa usar a FDU numa consulta. Mesmo que as tabelas sejam idênticas, tem de manter 2 versões da função. Por exemplo, se armazenar os dados de vendas nas multirregiões EU e US, deve manter uma versão da função em cada região. Por exemplo:

Uma consulta numa EU multirregião: 

  SELECT 
    id,
    europe_dataset.my_function(value)
  FROM
    sales;

Uma consulta numa US multirregião: 

  SELECT 
    id,
    us_dataset.my_function(value)
  FROM
    sales;

Além disso, quando a definição da função muda, tem de a atualizar em todas as regiões.

Para tornar as suas FDUs independentes da região, pode usar a replicação de conjuntos de dados entre regiões:

  1. Crie um novo conjunto de dados dedicado, por exemplo, my_utils, para armazenar todas as UDFs necessárias. Tenha em atenção que todas as tabelas adicionadas a este conjunto de dados são replicadas, o que aumenta o custo. No entanto, a replicação de FDU e procedimentos não acarreta custos adicionais.
  2. Adicione todas as FDU ao novo conjunto de dados. Também podem incluir UDFs da comunidade, como bqutil copiadas do GitHub.
  3. Ative a replicação do conjunto de dados. Configure este conjunto de dados para ser replicado em todas as regiões onde precisa de executar consultas que chamam estas FDU. Isto copia as suas funções para estas regiões e mantém-nas sincronizadas.

Quando executa uma consulta, o BigQuery usa automaticamente a versão local da FUD do duplicado do conjunto de dados local sem que especifique a região onde a função está definida, o que torna as suas consultas portáteis em diferentes localizações. Por exemplo:

  SELECT 
    id,
    my_utils.my_function(value)
  FROM
    sales;

Limitações

As seguintes limitações aplicam-se às funções definidas pelo utilizador temporárias e persistentes:

  • Os objetos DOM Window, Document e Node, bem como as funções que os requerem, não são suportados.
  • As funções JavaScript operam num ambiente de sandbox e as funções que dependem do código do sistema subjacente podem falhar devido a chamadas de sistema restritas.
  • Uma FDU JavaScript pode exceder o tempo limite e impedir a conclusão da consulta. Os limites de tempo podem ser tão curtos quanto 5 minutos, mas podem variar consoante vários fatores, incluindo a quantidade de tempo da CPU do utilizador que a sua função consome e o tamanho das entradas e saídas da função JavaScript.
  • As operações bit a bit em JavaScript processam apenas os 32 bits mais significativos.
  • As FDU estão sujeitas a determinados limites de taxa e limites de quota. Para mais informações, consulte os limites das FDU.

As seguintes limitações aplicam-se às funções definidas pelo utilizador persistentes:

  • Cada conjunto de dados só pode conter uma UDF persistente com o mesmo nome. No entanto, pode criar uma FDU cujo nome seja igual ao nome de uma tabela no mesmo conjunto de dados.
  • Quando faz referência a uma FDU persistente a partir de outra FDU persistente ou de uma vista lógica, tem de qualificar o nome com o conjunto de dados. Por exemplo:
    CREATE FUNCTION mydataset.referringFunction() AS (mydataset.referencedFunction());

As seguintes limitações aplicam-se às funções definidas pelo utilizador temporárias.

  • Ao criar uma UDF temporária, function_name não pode conter períodos.
  • As vistas e as FDUs persistentes não podem fazer referência a FDUs temporárias.