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

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)

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

• Os parâmetros precisam ser valores escalares. As funções de tabela no BigQuery não podem aceitar tabelas como parâmetros de entrada.

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