Funções de tabela

Uma função de tabela, também chamada de função de valor de tabela (TVF, na sigla em inglês), é uma função definida pelo usuário que retorna uma tabela. É possível usar uma função de tabela em qualquer lugar em que seja possível usar uma tabela. As funções de tabela se comportam de maneira semelhante às visualizações, mas uma função de tabela pode assumir parâmetros.

Criar funções de tabela

Para criar uma função de tabela, use a instrução CREATE TABLE FUNCTION. Uma função de tabela contém uma consulta que produz uma tabela. A função retorna o resultado da consulta. A função de tabela a seguir usa um parâmetro INT64 e usa esse valor dentro de uma cláusula WHERE em uma consulta em um conjunto de dados público chamado bigquery-public-data.usa_names.usa_1910_current:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE year = y
  GROUP BY year, name
);

Para filtrar de outras maneiras, você pode passar vários parâmetros para uma função de tabela. A função de tabela a seguir filtra os dados por ano e prefixo de nome:

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year_and_prefix(
  y INT64, z STRING)
AS (
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE
    year = y
    AND STARTS_WITH(name, z)
  GROUP BY year, name
);

Parâmetros da tabela

É possível definir parâmetros de TVF como tabelas. Depois do nome do parâmetro de tabela, especifique explicitamente o esquema de tabela obrigatório, da mesma forma que você especifica os campos de uma struct. O argumento de tabela transmitido para a TVF pode conter outras colunas além das especificadas no esquema de parâmetros, e as colunas podem aparecer em qualquer ordem.

A função de tabela a seguir retorna uma tabela que contém o total de vendas de item_name da tabela orders:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

Nomes de parâmetros

Se um parâmetro de função de tabela corresponder ao nome de uma coluna de tabela, ele poderá criar uma referência ambígua. Nesse caso, o BigQuery interpreta o nome como uma referência à coluna da tabela, não o parâmetro. A prática recomendada é usar nomes de parâmetros diferentes dos nomes de qualquer coluna de tabela referenciada.

Usar funções de tabela

É possível chamar uma função de tabela em qualquer contexto em que uma tabela seja válida. O exemplo a seguir chama a função mydataset.names_by_year na cláusula FROM de uma instrução SELECT:

SELECT * FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5

Os resultados têm a seguinte aparência:

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

É possível unir a saída de uma função de tabela a outra tabela:

SELECT *
  FROM `bigquery-public-data.samples.shakespeare` AS s
  JOIN mydataset.names_by_year(1950) AS n
  ON n.name = s.word

Também é possível usar uma função de tabela em uma subconsulta:

SELECT ARRAY(
  SELECT name FROM mydataset.names_by_year(1950)
  ORDER BY total DESC
  LIMIT 5)

Ao chamar uma função de tabela que tem um parâmetro de tabela, use a palavra-chave TABLE antes do nome do argumento de tabela. O argumento da tabela pode ter colunas não listadas no esquema de parâmetro da tabela:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

WITH my_orders AS (
    SELECT 1 AS sales, "apple" AS item, 0.99 AS price
    UNION ALL
    SELECT 2, "banana", 0.49
    UNION ALL
    SELECT 5, "apple", 0.99)
SELECT *
FROM mydataset.compute_sales(TABLE my_orders, "apple");

/*-------------+-------+
 | total_sales | item  |
 +-------------+-------+
 | 6           | apple |
 +-------------+-------*/

Listar funções da tabela

As funções de tabela são um tipo de rotina. Para listar todas as rotinas em um conjunto de dados, consulte Listar rotinas.

Excluir funções da tabela

Para excluir uma função de tabela, use a instrução DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Autorizar rotinas

É possível autorizar funções de tabela 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.

Limitações

  • O corpo da consulta precisa ser uma instrução SELECT e não pode modificar nada. Por exemplo, as instruções de linguagem de definição de dados (DDL, na sigla em inglês) e de linguagem de manipulação de dados (DML, na sigla em inglês) não são permitidas em funções de tabela. Se você precisar de efeitos colaterais, pense em escrever um procedimento em vez disso.

  • As funções de tabela precisam ser armazenadas no mesmo local que as tabelas a que se referem.

Cotas

Para mais informações sobre cotas e limites de funções de tabela, consulte Cotas e limites.