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 di tabella ovunque tu possa utilizzare una tabella. Le funzioni di tabella si comportano in modo simile alle viste, ma una funzione di tabella può accettare parametri.

Creare funzioni di tabella

Per creare una funzione di tabella, utilizza l'istruzione CREATE TABLE FUNCTION. Una funzione di tabella contiene una query che produce 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 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 della tabella

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Nota: per richiedere assistenza o fornire un 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 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
);

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 prassi consigliata è utilizzare nomi di parametri diversi dai nomi di qualsiasi colonna della tabella a cui viene fatto riferimento.

Utilizzare le funzioni di tabella

Puoi chiamare una funzione di 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 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 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 |
 +-------------+-------*/

Elenca le funzioni di tabella

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

Elimina funzioni tabella

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

DROP TABLE FUNCTION mydataset.names_by_year

Autorizzare le routine

Puoi autorizzare le funzioni di 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 di tabella e utilizzarlo in un calcolo. Per ulteriori informazioni, vedi 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 di tabella. Se hai bisogno di effetti collaterali, valuta la possibilità di scrivere una procedura in alternativa.

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

Quote

Per ulteriori informazioni su quote e limiti delle funzioni di tabella, consulta Quote e limiti.