Mehrere Tabellen mit einer Platzhaltertabelle abfragen

Mit Platzhaltertabellen können Sie mehrere Tabellen mit kurzen SQL-Anweisungen abfragen. Tabellenplatzhalter sind nur in GoogleSQL 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, _TABLE_SUFFIX, die den dem Platzhalterzeichen entsprechenden Wert enthält.

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 auch dann einen Fehler zurück, wenn Ihre Abfrage eine WHERE-Anweisung in der Pseudospalte _TABLE_SUFFIX enthält, um die Ansicht herauszufiltern.
  • Im Cache gespeicherte Ergebnisse werden beim Abfragen mehrerer Tabellen mit einem Platzhalter 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 integrierten BigQuery-Speicher. Zum Abfragen einer externen Tabelle oder einer Ansicht können Sie keine Platzhalter verwenden.
  • Sie können keine Platzhalterabfragen über Tabellen mit einer inkompatiblen Partitionierung oder einer Mischung aus partitionierten und nicht partitionierten Tabellen verwenden. Die abgefragten Tabellen müssen außerdem identische Clustering-Spezifikationen haben.
  • Sie können Platzhaltertabellen mit partitionierten Tabellen verwenden. Sowohl die Partitions- als auch die Clusterbereinigung werden unterstützt. Tabellen, die geclustert, aber nicht partitioniert sind, erhalten jedoch keine Clusterbereinigung.
  • Für Abfragen, die DML-Anweisungen (Datenbearbeitungssprache) 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.
  • Filter für die Pseudospalten _TABLE_SUFFIX oder _PARTITIONTIME, die benutzerdefinierte JavaScript-Funktionen enthalten, beschränken die Anzahl der in einer Platzhaltertabelle gescannten Tabellen nicht.
  • Platzhalterabfragen werden nicht für Tabellen unterstützt, die durch vom Kunden verwaltete Verschlüsselungsschlüssel (Customer-Managed Encryption Keys, CMEK) geschützt sind.
  • Alle Tabellen, auf die in einer Platzhalterabfrage verwiesen wird, müssen genau dieselben Tag-Schlüssel und ‑Werte haben.

  • Bei der Verwendung von Platzhaltertabellen werden alle Tabellen im Dataset durchsucht, die mit dem Tabellennamen vor * beginnen, auch wenn _TABLE_SUFFIX in Kombination mit REGEXP_CONTAINS verwendet wird und einen regulären Ausdruck wie ^[0-9]{2}$ enthält. Beispiel:

    SELECT *
FROM `my_project.my_dataset.my_table_*`
WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');

  • Wenn eine einzelne gescannte Tabelle eine Schemafehlanpassung aufweist (d. h. eine Spalte mit demselben Namen ist von einem anderen Typ), schlägt die Abfrage mit dem Fehler Feld vom Typ X kann nicht als Feld Y gelesen werden: column_name fehl. Alle Tabellen werden auch dann abgeglichen, wenn Sie den Gleichheitsoperator = verwenden. In der folgenden Abfrage wird beispielsweise auch die Tabelle my_dataset.my_table_03_backup gescannt. Daher kann die Abfrage aufgrund einer Schemaabweichung fehlschlagen. Wenn jedoch keine Schemaabweichung vorliegt, stammen die Ergebnisse nur wie erwartet aus der Tabelle my_dataset.my_table_03. 

    SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'

Hinweis

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 integrierten BigQuery-Speicher. Beim Abfragen einer externen Tabelle oder einer Ansicht können Sie keine Platzhalter verwenden.

Syntax der Platzhaltertabelle

Syntax der Platzhaltertabelle:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression
<project-id>
Cloud Platform-Projekt-ID. Optional, wenn Sie Ihre Standard-Projekt-ID verwenden.
<dataset-id>
BigQuery-Dataset-ID
<table-prefix>
Ein String, der für alle Tabellen gemeinsam verwendet wird, die mit dem Platzhalterzeichen übereinstimmen. Das Tabellenpräfix ist optional. Wenn das Tabellenpräfix weggelassen wird, bedeutet dies, dass alle Tabellen in dem Dataset übereinstimmen müssen.
* (Platzhalterzeichen)
Das Platzhalterzeichen "*" stellt ein oder mehrere Zeichen eines Tabellennamens dar. Das Platzhalterzeichen kann nur das letzte Zeichen eines Platzhaltertabellennamens sein.

Abfragen mit Platzhaltertabellen unterstützen die Pseudospalte _TABLE_SUFFIX in der WHERE-Klausel. Diese Spalte enthält die Werte, die mit dem Platzhalterzeichen übereinstimmen, sodass Abfragen filtern können, auf welche Tabellen zugegriffen wird. Die folgenden WHERE-Klauseln verwenden beispielsweise Vergleichsoperatoren, um die übereinstimmenden Tabellen zu filtern:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

Weitere Informationen zur Pseudospalte _TABLE_SUFFIX finden Sie unter Ausgewählte Tabellen mit _TABLE_SUFFIX filtern.

Tabellennamen mit Platzhaltern in Graviszeichen setzen

Der Platzhaltertabellenname enthält das Sonderzeichen (*). Dies bedeutet, dass Sie den Platzhaltertabellennamen in Graviszeichen (`) setzen müssen. Beispiel: Die folgende Abfrage ist gültig, weil sie Graviszeichen verwendet:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Die folgende Abfrage ist NICHT gültig, weil sie nicht korrekt in Graviszeichen steht:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Anführungszeichen können nicht verwendet werden:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Tabellen 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.

Um eine Gruppe von Tabellen abzufragen, 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

Wenn Sie eine Abfrage so einschränken möchten, dass nur eine bestimmte Gruppe von Tabellen gescannt wird, verwenden Sie die Pseudospalte _TABLE_SUFFIX in einer WHERE-Klausel mit einer Bedingung, die ein konstanter Ausdruck ist.

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 lassen.

Filter für _TABLE_SUFFIX, die keine konstanten Ausdrücke in den Bedingungen enthalten, beschränken aber nicht die Anzahl der in einer Platzhaltertabelle gescannten Tabellen. In der folgenden Abfrage werden die für die Platzhaltertabelle bigquery-public-data.noaa_gsod.gsod19* gescannten Tabellen beispielsweise nicht begrenzt, da der Filter den dynamischen Wert der Spalte table_id nutzt:

#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_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

In der folgenden Abfrage wird beispielsweise der Scan anhand der ersten Filterbedingung _TABLE_SUFFIX BETWEEN '40' and '60' begrenzt, da es sich um einen konstanten Ausdruck handelt. Die folgende Abfrage beschränkt den Scan jedoch nicht anhand der zweiten Filterbedingung _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), da es sich um einen dynamischen Ausdruck handelt:

#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_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Zur Umgehung dieses Problems können Sie stattdessen zwei separate Abfragen durchführen, z. B.:

Erste Abfrage:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name 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'

In diesen Beispielabfragen wird die Ansicht INFORMATION_SCHEMA.TABLES verwendet. Weitere Informationen zur Tabelle INFORMATION_SCHEMA finden Sie unter Metadaten einer Tabelle mit INFORMATION_SCHEMA abrufen.

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 GoogleSQL-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. Auch wenn Sie die Anzahl der Tabellen, die Sie aus der Platzhaltertabelle verwenden möchten, mithilfe der Pseudospalte _TABLE_SUFFIX in einer WHERE-Klausel einschränken, verwendet BigQuery das Schema für die zuletzt erstellte Tabelle, die mit dem Platzhalter übereinstimmt.

Wenn eine Spalte aus dem abgeleiteten Schema in einer übereinstimmenden Tabelle nicht vorhanden ist, gibt BigQuery NULL-Werte für diese Spalte in den Zeilen der Tabelle zurück, in der die Spalte fehlt.

Wenn das Schema bei den Tabellen, die von der Platzhalterabfrage abgeglichen werden, inkonsistent ist, gibt BigQuery einen Fehler zurück. Dies ist der Fall, wenn die Spalten der übereinstimmenden Tabellen unterschiedliche Datentypen haben oder wenn die Spalten, die nicht in allen übereinstimmenden Tabellen vorhanden sind, keinen Nullwert annehmen können.

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

  • Partitionierung wird gegenüber Fragmentierung empfohlen, da partitionierte Tabellen eine bessere Leistung erzielen. Fragmentierung verringert die Leistung und erstellt mehr Tabellen, die verwaltet werden müssen. Weitere Informationen finden Sie unter Partitionierung und Fragmentierung im Vergleich.

Weitere Informationen zur Kostenkontrolle in BigQuery finden Sie unter Kosten in BigQuery kontrollieren.

Nächste Schritte