Funzioni tabella

Una funzione di tabella, chiamata anche funzione con valori di tabella (TVF), è una funzione definita dall'utente che restituisce una tabella. Puoi utilizzare una funzione di tabella ovunque sia possibile utilizzare una tabella. Le funzioni con valori di tabella si comportano in modo simile alle viste, ma una funzione con valori di tabella può accettare parametri.

Crea funzioni di tabella

Per creare una funzione di tabella, utilizza l'istruzione CREATE TABLE FUNCTION. Una funzione di tabella contiene una query che genera una tabella. La funzione restituisce il risultato della query. La seguente funzione di tabella accetta un parametro INT64 e utilizza questo valore all'interno di una clausola WHERE in una query su un set di dati pubblico denominato 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
);

Per filtrare in altri modi, puoi passare più parametri a una funzione di tabella. La seguente funzione di tabella filtra i dati in base all'anno e al prefisso del 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
);

Parametri della tabella

Puoi impostare i parametri TVF in modo che siano tabelle. Dopo il nome del parametro della tabella, devi specificare esplicitamente lo schema della tabella richiesto, nello stesso modo in cui specifichi i campi di una struct. L'argomento della tabella che passi alla TVF può contenere colonne aggiuntive oltre a quelle specificate nello schema del parametro e le colonne possono essere visualizzate in qualsiasi ordine.

La seguente funzione con valore di tabella restituisce una tabella che contiene le vendite totali per item_name dalla tabella 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
);

L'utilizzo del tipo ANY TABLE in una TVF consente di creare funzioni generiche che accettano tabelle di qualsiasi struttura. A differenza delle tabelle fisse, in cui devi definire ogni colonna e tipo di dati, il tipo ANY TABLE funge da segnaposto, rendendo la funzione riutilizzabile con set di dati diversi. Tuttavia, la tabella che passi deve comunque contenere le colonne specifiche utilizzate all'interno della funzione. Ad esempio, se la funzione filtra in base a created_date, la tabella di input deve avere questa colonna, altrimenti la query non andrà a buon fine.

-- Simple pass-through (accepts any table, as used in your devstack)
CREATE OR REPLACE TABLE FUNCTION dataset_test.MyTVF(input_table ANY TABLE)
AS
SELECT * FROM input_table;

-- Generic filter (accepts any table, but implicitly requires a 'status' column)
CREATE TEMP TABLE FUNCTION FilterByStatus(input_table ANY TABLE, status_val STRING)
AS
SELECT * FROM input_table
WHERE status = status_val;

Nomi dei parametri

Se un parametro della funzione di tabella corrisponde al nome di una colonna della tabella, può creare un riferimento ambiguo. In questo caso, BigQuery interpreta il nome come riferimento alla colonna della tabella, non al parametro. La best practice consigliata è utilizzare nomi di parametri diversi dai nomi delle colonne della tabella di riferimento.

Utilizza le funzioni di tabella

Puoi chiamare una funzione di tabella in qualsiasi contesto in cui una tabella è valida. L'esempio seguente chiama la funzione mydataset.names_by_year nella clausola FROM di un'istruzione SELECT:

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

I risultati sono simili ai seguenti:

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

Puoi unire l'output di una funzione di tabella a un'altra tabella:

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

Puoi anche utilizzare una funzione di tabella in una subquery:

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

Quando chiami una funzione di tabella con un parametro di tabella, devi utilizzare la parola chiave TABLE prima del nome dell'argomento della tabella. L'argomento della tabella può avere colonne non elencate nello schema del parametro della tabella:

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

Utilizza le variabili di sistema con le TVF

Le variabili di sistema @@session_id e @@location sono supportate con le TVF. Puoi includere queste variabili di sistema in qualsiasi punto dell'istruzione di creazione della funzione per restituire l'ID sessione o la località della query corrente. Tutte le altre variabili di sistema non sono supportate.

Elenca le funzioni tabella

Le funzioni di tabella sono un tipo di routine. Per elencare tutte le routine in un set di dati, consulta Elenca le routine.

Elimina funzioni di tabella

Per eliminare una funzione di tabella, utilizza l'istruzione DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Autorizza routine

Puoi autorizzare le funzioni con valori di tabella come routine. Le routine autorizzate ti consentono di condividere i risultati delle query con utenti o gruppi specifici senza concedere l'accesso diretto alle tabelle sottostanti. Ad esempio, una routine può calcolare aggregazioni o cercare un valore di tabella e utilizzarlo in un calcolo. Per saperne di più, consulta Routine autorizzate.

Limitazioni

• Il corpo della query deve essere un'istruzione SELECT e non può modificare nulla. Ad esempio, le istruzioni Data Definition Language (DDL) e data manipulation language (DML) non sono consentite nelle funzioni di tabella. Se hai bisogno di effetti collaterali, valuta la possibilità di scrivere una procedura.

• Le funzioni di tabella devono essere archiviate nella stessa posizione delle tabelle a cui fanno riferimento.

Quote

Per saperne di più su quote e limiti delle funzioni di tabella, consulta Quote e limiti.