Funções de tabela

Uma função de tabela, também denominada função de valor de tabela (TVF), é uma função definida pelo utilizador que devolve uma tabela. Pode usar uma função de tabela em qualquer lugar onde possa usar uma tabela. As funções de tabela comportam-se de forma semelhante às vistas, mas uma função de tabela pode receber parâmetros.

Crie funções de tabela

Para criar uma função de tabela, use a declaração CREATE TABLE FUNCTION. Uma função de tabela contém uma consulta que produz uma tabela. A função devolve o resultado da consulta. A seguinte função de tabela usa um parâmetro INT64 e usa este valor numa cláusula WHERE numa consulta sobre um conjunto de dados público denominado 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 formas, pode transmitir vários parâmetros a uma função de tabela. A função de tabela seguinte filtra os dados por ano e prefixo do 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 de tabela

Pode definir parâmetros TVF como tabelas. Após o nome do parâmetro da tabela, tem de especificar explicitamente o esquema da tabela necessário, da mesma forma que especifica os campos de uma struct. O argumento da tabela que transmite à TVF pode conter colunas adicionais além das especificadas no esquema de parâmetros, e as colunas podem aparecer em qualquer ordem.

A função de tabela seguinte devolve 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, pode criar uma referência ambígua. Nesse caso, o BigQuery interpreta o nome como uma referência à coluna da tabela e não ao parâmetro. A prática recomendada é usar nomes de parâmetros distintos dos nomes de quaisquer colunas de tabelas referenciadas.

Use funções de tabela

Pode chamar uma função de tabela em qualquer contexto em que uma tabela seja válida. O exemplo seguinte chama a função mydataset.names_by_year na cláusula FROM de uma declaração SELECT:

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

Os resultados têm o seguinte aspeto:

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

Pode unir o resultado de uma função de tabela com 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 pode usar uma função de tabela numa subconsulta:

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

Quando chama uma função de tabela que tem um parâmetro de tabela, tem de usar 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âmetros 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 |
 +-------------+-------*/

Apresentar funções de tabelas

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

Elimine funções de tabela

Para eliminar uma função de tabela, use a declaração: DROP TABLE FUNCTION

DROP TABLE FUNCTION mydataset.names_by_year

Autorize rotinas

Pode autorizar funções de tabela 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.

Limitações

• O corpo da consulta tem de ser uma declaração SELECT e não pode modificar nada. Por exemplo, as declarações de linguagem de definição de dados (LDD) e linguagem de manipulação de dados (LMD) não são permitidas em funções de tabela. Se precisar de efeitos secundários, considere escrever um procedimento.

• As funções de tabela têm de ser armazenadas na mesma localização que as tabelas a que fazem referência.

Quotas

Para mais informações sobre as quotas e os limites das funções de tabela, consulte o artigo Quotas e limites.