Tabellenfunktionen

Eine Tabellenfunktion, auch als Tabellenwertfunktion (Table-valued Function, TVF) bezeichnet, ist eine benutzerdefinierte Funktion, die eine Tabelle zurückgibt. Eine Tabellenfunktion kann überall dort verwendet werden, wo Sie eine Tabelle nutzen. Tabellenfunktionen verhalten sich ähnlich wie Ansichten, können aber auch Parameter verwenden.

Tabellenfunktionen erstellen

Zum Erstellen einer Tabellenfunktion verwenden Sie die Anweisung CREATE TABLE FUNCTION. Eine Tabellenfunktion enthält eine Abfrage, die eine Tabelle generiert. Die Funktion gibt das Abfrageergebnis zurück. Die folgende Tabellenfunktion verwendet einen INT64-Parameter in der WHERE-Klausel einer Abfrage für ein öffentliches Dataset mit dem Namen 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
);

Zum Filtern auf andere Weise können Sie mehrere Parameter an eine Tabellenfunktion übergeben. Mit der folgenden Tabellenfunktion werden die Daten nach Jahr und Namenspräfix gefiltert:

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

Parameternamen

Wenn ein Tabellenfunktionsparameter mit dem Namen einer Tabellenspalte übereinstimmt, kann dies zu einer mehrdeutigen Referenz führen. In diesem Fall interpretiert BigQuery den Namen als Referenz auf die Tabellenspalte und nicht als Parameter. Es wird empfohlen, Parameternamen zu verwenden, die sich von den Namen referenzierter Tabellenspalten unterscheiden.

Tabellenfunktionen verwenden

Sie können eine Tabellenfunktion in jedem Kontext aufrufen, in dem eine Tabelle gültig ist. Im folgenden Beispiel wird die Funktion mydataset.names_by_year in der FROM-Klausel einer SELECT-Anweisung aufgerufen:

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

Das Ergebnis sieht in etwa so aus:

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

Sie können die Ausgabe einer Tabellenfunktion mit einer anderen Tabelle verbinden:

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

Außerdem haben Sie die Möglichkeit, eine Tabellenfunktion in einer Unterabfrage zu verwenden:

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

Tabellenfunktionen auflisten

Tabellenfunktionen sind ein routinemäßiger Ablauf. Informationen zum Auflisten aller Abläufe in einem Dataset finden Sie unter Routinen auflisten.

Tabellenfunktionen löschen

Zum Löschen einer Tabellenfunktion verwenden Sie die Anweisung DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Routinen autorisieren

Sie können Tabellenfunktionen als Routinen autorisieren. Mit autorisierten Routinen können Sie Abfrageergebnisse für bestimmte Nutzer oder Gruppen freigeben, ohne ihnen Zugriff auf die zugrunde liegenden Tabellen zu gewähren, mit denen die Ergebnisse generiert wurden. Beispielsweise kann eine autorisierte Routine eine Aggregation von Daten berechnen oder einen Tabellenwert abrufen und diesen Wert in einer Berechnung verwenden. Weitere Informationen zu Autorisierte Routinen.

Beschränkungen

• Parameter müssen skalare Werte sein. Tabellenfunktionen in BigQuery können Tabellen nicht als Eingabeparameter nutzen.

• Der Abfragetext muss eine SELECT-Anweisung sein und darf keine Änderung ausführen. Beispielsweise sind Anweisungen in der Datendefinitionssprache (DDL) und in der Datenbearbeitungssprache (DML) in Tabellenfunktionen nicht zulässig. Für Nebeneffekte sollte stattdessen eine Prozedur geschrieben werden.

• Tabellenfunktionen müssen am selben Ort gespeichert werden wie die Tabellen, auf die sie verweisen.

Kontingente

Weitere Informationen zu Kontingenten und Beschränkungen für Tabellenfunktionen finden Sie unter Kontingente und Limits.