Mehrere Tabellen mit einer Platzhaltertabelle abfragen

Mit Platzhaltertabellen können Sie mehrere Tabellen mit kurzen SQL-Anweisungen abfragen. Platzhaltertabellen sind nur in Standard-SQL verfügbar. Die entsprechende Funktion in Legacy-SQL wird unter Tabellenplatzhalter-Funktionen beschrieben.

Eine Platzhaltertabelle stellt eine Zusammenführung aller Tabellen dar, die mit dem Platzhalterausdruck übereinstimmen. Beispiel: Die folgende FROM-Klausel verwendet den Platzhalterausdruck gsod*, um alle Tabellen in dem Dataset noaa_gsod zu ermitteln, 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 einer UPDATE-Abfrage verwenden, aber nicht als Ziel des UPDATE-Vorgangs.

Hinweise

• 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

Platzhaltertabellen unterstützen nur nativen BigQuery-Speicher. Beim Abfragen einer externen Tabelle oder einer Ansicht können Sie keine Platzhalter verwenden.

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.

Wenn Sie eine Gruppe von Tabellen abfragen möchten, die ein gemeinsames Präfix haben, verwenden Sie das Tabellenplatzhaltersymbol (*) im Anschluss an das Tabellenpräfix in der FROM-Anweisung. Mit der folgenden Abfrage wird beispielsweise die höchste in den 1940er-Jahren verzeichnete Temperatur gesucht:

#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 von 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, die Unterabfragen enthalten, können nicht verwendet werden, um die Zahl 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 zum Beispiel 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 basierend auf der Filterbedingung _TABLE_SUFFIX BETWEEN '40' and '60' begrenzt, aber nicht auf der 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 auf Basis 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'

Hinweis: Die Beispielabfragen verwenden die Metatabelle __TABLES__SUMMARY.

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. Mit der folgenden Abfrage wird beispielsweise die Partition vom 1. Januar 2017 in der Tabelle my_dataset.mytable_id1 gescannt.

#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 in dem 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. Beispiel: Die folgende Abfrage verwendet 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.