Mit Platzhaltertabellen können Sie mehrere Tabellen mit kurzen SQL-Anweisungen abfragen. Tabellenplatzhalter sind nur in Standard-SQL verfügbar. Entsprechende Funktionen in Legacy-SQL finden Sie unter Tabellenplatzhalter-Funktionen.
Eine Platzhaltertabelle stellt eine Zusammenführung aller Tabellen dar, die mit dem Platzhalterausdruck übereinstimmen. Die folgende FROM
-Klausel verwendet beispielsweise den Platzhalterausdruck gsod*
, um alle Tabellen im Dataset noaa_gsod
abzugleichen, die mit dem String gsod
beginnen.
FROM
`bigquery-public-data.noaa_gsod.gsod*`
Jede Zeile in der Platzhaltertabelle umfasst eine besondere Spalte, die den dem Platzhalterzeichen entsprechenden Wert enthält.
Informationen zur Syntax von Platzhaltertabellen finden Sie unter Platzhaltertabellen in der Standard-SQL-Referenz.
Beschränkungen
Für Abfragen mit Platzhaltertabellen gelten die folgenden Beschränkungen:
- Die Platzhaltertabelle unterstützt keine Ansichten. Wenn die Platzhaltertabelle mit einer beliebigen Ansicht im Dataset übereinstimmt, gibt die Abfrage einen Fehler zurück. Dies gilt unabhängig davon, ob Ihre Abfrage eine
WHERE
-Klausel in der Pseudospalte_TABLE_SUFFIX
enthält, um die Ansicht herauszufiltern. - Im Cache gespeicherte Ergebnisse werden beim Abfragen mehrerer Tabellen mit einem Platzhalter derzeit nicht unterstützt, auch wenn die Option Im Cache gespeicherte Ergebnisse verwenden aktiviert ist. Wenn Sie die gleiche Platzhalterabfrage mehrmals ausführen, wird Ihnen jede Abfrage in Rechnung gestellt.
- Platzhaltertabellen unterstützen nur nativen BigQuery-Speicher. Beim Abfragen einer externen Tabelle oder einer Ansicht können Sie keine Platzhalter verwenden.
- Für Abfragen, die DML-Anweisungen (Data Manipulation Language) enthalten, kann als Abfrageziel keine Platzhaltertabelle verwendet werden. Zum Beispiel lässt sich eine Platzhaltertabelle in der
FROM
-Klausel einerUPDATE
-Abfrage verwenden, aber nicht als Ziel desUPDATE
-Vorgangs. - Platzhalterabfragen werden nicht für Tabellen unterstützt, die durch vom Kunden verwaltete Verschlüsselungsschlüssel (Customer-Managed Encryption Keys, CMEK) geschützt sind.
Hinweis
- Achten Sie darauf, BigQuery-Standard-SQL zu verwenden. Weitere Informationen finden Sie unter SQL-Dialekte wechseln.
- Wenn Sie Legacy-SQL verwenden, sehen Sie sich den Abschnitt Tabellenplatzhalter-Funktionen an.
- Viele Beispiele auf dieser Seite verwenden ein öffentliches Dataset der National Oceanic and Atmospheric Administration (NOAA). Weitere Informationen zu den Daten finden Sie in NOAA Global Surface Summary of the Day – Wetterdaten.
Wann werden Platzhaltertabellen verwendet?
Platzhaltertabellen sind hilfreich, wenn ein Dataset mehrere Tabellen mit ähnlichen Namen enthält, die kompatible Schemas haben. Im Allgemeinen enthalten derartige Datasets Tabellen, die Daten eines einzelnen Tages, Monats oder Jahres darstellen. Beispiel: Ein öffentliches Dataset, das von BigQuery gehostet wird, NOAA Global Surface Summary of the Day – Wetterdaten, enthält eine Tabelle für jedes Jahr von 1929 bis heute.
Eine Abfrage, die alle Tabellen-IDs von 1929 bis 1940 scannt, wäre sehr lang, wenn alle 12 Tabellen in der FROM
-Klausel benannt werden müssen (in diesem Beispiel werden die meisten Tabellen weggelassen):
#standardSQL SELECT max, ROUND((max-32)*5/9,1) celsius, mo, da, year FROM ( SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL # ... Tables omitted for brevity SELECT * FROM `bigquery-public-data.noaa_gsod.gsod1940` ) WHERE max != 9999.9 # code for missing data ORDER BY max DESC
Dieselbe Abfrage mit einer Platzhaltertabelle ist wesentlich kürzer:
#standardSQL SELECT max, ROUND((max-32)*5/9,1) celsius, mo, da, year FROM `bigquery-public-data.noaa_gsod.gsod19*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN '29' AND '40' ORDER BY max DESC
Tabellensets mit Platzhaltertabellen abfragen
Mit Platzhaltertabellen können Sie mehrere Tabellen präzise abfragen.
Beispielsweise enthält ein von BigQuery gehostetes öffentliches Dataset, NOAA Global Surface Summary of the Day – Wetterdaten, eine Tabelle für jedes Jahr von 1929 bis heute, wobei für jedes Jahr das gemeinsame Präfix gsod
gefolgt von der vierstelligen Jahreszahl verwendet wird. Die Tabellen heißen gsod1929
, gsod1930
, gsod1931
usw.
Zum Abfragen einer Gruppe von Tabellen, die ein gemeinsames Präfix haben, verwenden Sie das Tabellenplatzhaltersymbol (*) im Anschluss an das Tabellenpräfix in der FROM
-Anweisung. Beispiel: Die folgende Abfrage sucht die Höchsttemperatur, die in den 1940er-Jahren aufgezeichnet wurde:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
WHERE
max != 9999.9 # code for missing data
ORDER BY
max DESC
Ausgewählte Tabellen mit _TABLE_SUFFIX filtern
Um die Abfrage zu begrenzen, damit eine beliebige Gruppe von Tabellen gescannt wird, verwenden Sie die Pseudospalte _TABLE_SUFFIX
in der WHERE
-Klausel. Die Pseudospalte _TABLE_SUFFIX
enthält die Werte, die dem Tabellenplatzhalter entsprechen. Beispiel: Die vorherige Beispielabfrage, die alle Tabellen aus den 1940er Jahren scannt, verwendet einen Tabellenplatzhalter zur Darstellung der letzten Ziffer des Jahres:
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
Die entsprechende Pseudospalte _TABLE_SUFFIX
enthält Werte im Bereich 0
bis 9
, die die Tabellen gsod1940
bis gsod1949
darstellen. Diese _TABLE_SUFFIX
-Werte können in der WHERE
-Klausel verwendet werden, um nach bestimmten Tabellen zu filtern.
Beispiel: Um nach den Höchsttemperaturen in den Jahren 1940 und 1944 zu filtern, verwenden Sie die Werte 0
und 4
für _TABLE_SUFFIX
:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod194*`
WHERE
max != 9999.9 # code for missing data
AND ( _TABLE_SUFFIX = '0'
OR _TABLE_SUFFIX = '4' )
ORDER BY
max DESC
Mit _TABLE_SUFFIX
kann die Anzahl der gescannten Byte wesentlich reduziert werden, wodurch sich auch die Kosten für das Ausführen der Abfragen verringern.
Filter für _TABLE_SUFFIX
mit Unterabfragen können nicht verwendet werden, um die Anzahl der für eine Platzhaltertabelle gescannten Tabellen zu begrenzen. In der folgenden Abfrage werden die für die Platzhaltertabelle bigquery-public-data.noaa_gsod.gsod19*
gescannten Tabellen beispielsweise nicht begrenzt:
#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
_TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
WHERE table_id LIKE 'gsod194%')
In der folgenden Abfrage wird der Scan anhand der Filterbedingung _TABLE_SUFFIX BETWEEN '40' and '60'
begrenzt, aber nicht auf Grundlage der Bedingung der Unterabfrage:
#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
_TABLE_SUFFIX BETWEEN '40' AND '60'
AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
WHERE table_id LIKE 'gsod194%')
Um eine Begrenzung anhand der Bedingung der Unterabfrage zu erreichen, können Sie zwei separate Abfragen durchführen.
Erste Abfrage:
#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1)
FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
WHERE table_id LIKE 'gsod194%'
Zweite Abfrage:
#standardSQL
# Construct the second query based on the values from the first query
SELECT
ROUND((max-32)*5/9,1) celsius
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'
Bereich mit Tabellen mit _TABLE_SUFFIX scannen
Um einen Bereich mit Tabellen zu scannen, verwenden Sie die Pseudospalte _TABLE_SUFFIX
zusammen mit der BETWEEN
-Klausel. Beispiel: Um die Höchsttemperatur zu suchen, die in den Jahren von 1929 bis einschließlich 1935 aufgezeichnet wurde, stellen Sie die letzten beiden Ziffern des Jahres mit dem Tabellenplatzhalter dar:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.gsod19*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
max DESC
Bereich partitionierter Tabellen mit Aufnahmezeit mit _PARTITIONTIME scannen
Verwenden Sie die Pseudospalte _PARTITIONTIME
mit der Pseudospalte _TABLE_SUFFIX
, um einen Bereich partitionierter Tabellen mit Aufnahmezeit zu scannen. Beispiel: Die folgende Abfrage scannt die Partition vom 1. Januar 2017 in der Tabelle my_dataset.mytable_id1
:
#standardSQL
SELECT
field1,
field2,
field3
FROM
`my_dataset.mytable_*`
WHERE
_TABLE_SUFFIX = 'id1'
AND _PARTITIONTIME = TIMESTAMP('2017-01-01')
Alle Tabellen in einem Dataset abfragen
Um alle Tabellen in einem Dataset zu scannen, können Sie ein leeres Präfix und den Tabellenplatzhalter verwenden. Dies bedeutet, dass die Pseudospalte _TABLE_SUFFIX
vollständige Tabellennamen enthält. Beispiel: Die folgende FROM
-Klausel scannt alle Tabellen im GSOD-Dataset:
FROM
`bigquery-public-data.noaa_gsod.*`
Bei einem leeren Präfix enthält die Pseudospalte _TABLE_SUFFIX
vollständige Tabellennamen. Beispiel: Die folgende Abfrage entspricht dem vorherigen Beispiel, das die Höchsttemperatur zwischen den Jahren 1929 und 1935 ermittelt, jedoch vollständige Tabellennamen in der WHERE
-Klausel verwendet:
#standardSQL
SELECT
max,
ROUND((max-32)*5/9,1) celsius,
mo,
da,
year
FROM
`bigquery-public-data.noaa_gsod.*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
max DESC
Beachten Sie jedoch, dass längere Präfixe die Leistung im Allgemeinen steigern. Weitere Informationen finden Sie in Best Practices.
Details der Abfrageausführung
Zur Abfrageauswertung verwendetes Schema
Zur Ausführung einer Standard-SQL-Abfrage, die eine Platzhaltertabelle verwendet, leitet BigQuery das Schema für diese Tabelle automatisch ab. BigQuery verwendet das Schema für die zuletzt erstellte Tabelle, die mit dem Platzhalter als Schema für die Platzhaltertabelle übereinstimmt. Wenn das Schema bei den Tabellen, die von der Platzhaltertabelle abgeglichen werden, inkonsistent ist, gibt BigQuery einen Fehler zurück.
Best Practices
Die Leistung ist bei längeren Präfixen im Allgemeinen besser als bei kurzen. Die folgende Abfrage verwendet beispielsweise ein langes Präfix (gsod200
):
#standardSQL SELECT max FROM `bigquery-public-data.noaa_gsod.gsod200*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN '0' AND '1' ORDER BY max DESC
Die Leistung der folgenden Abfrage ist im Allgemeinen schlechter, weil sie ein leeres Präfix verwendet:
#standardSQL SELECT max FROM `bigquery-public-data.noaa_gsod.*` WHERE max != 9999.9 # code for missing data AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001' ORDER BY max DESC
Weitere Informationen
- Mehr zu Standard-SQL finden Sie in der Referenz zu Standard-SQL-Abfragen.