Funzioni tabella

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

Creare funzioni tabella

Per creare una funzione tabella, utilizza l' CREATE TABLE FUNCTION istruzione. Una funzione tabella contiene una query che produce una tabella. La funzione restituisce il risultato della query. La seguente funzione tabella accetta un INT64 parametro 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 tabella. La seguente funzione tabella filtra i dati per anno e 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 tabella

Anteprima

Questa funzionalità è soggetta ai "Termini dell'offerta pre-GA" nella sezione dei Termini di servizio generali dei Termini specifici dei servizi. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per ulteriori informazioni, consulta le descrizioni della fase di lancio.

Nota: per richiedere assistenza o fornire feedback su questa funzionalità, invia un'email all'indirizzo [e-mail].

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

La seguente funzione 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
);

Nomi dei parametri

Se un parametro della funzione 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 prassi consigliata è utilizzare nomi di parametri diversi dai nomi delle colonne della tabella a cui viene fatto riferimento.

Utilizzare le funzioni tabella

Puoi chiamare una funzione tabella in qualsiasi contesto in cui una tabella è valida. Il seguente esempio 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 tabella con 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 tabella in una sottoquery:

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

Quando chiami una funzione tabella con un parametro 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 dei parametri 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 |
 +-------------+-------*/

Elencare le funzioni tabella

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

Eliminare le funzioni tabella

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

DROP TABLE FUNCTION mydataset.names_by_year

Autorizzare le routine

Puoi autorizzare le funzioni tabella come routine. Le routine autorizzate ti consentono di condividere i risultati delle query con utenti o gruppi specifici senza concedere loro l'accesso alle tabelle sottostanti che hanno generato i risultati. Ad esempio, una routine autorizzata può calcolare un'aggregazione sui dati o cercare un valore della tabella e utilizzarlo in un calcolo. Per ulteriori informazioni, consulta Routine autorizzate.

Limitazioni

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

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

Quote

Per ulteriori informazioni sulle quote e sui limiti delle funzioni tabella, consulta Quote e limiti.