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 nicht die volle 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 kundenverwaltete Verschlüsselungsschlüssel (Customer-Managed Encryption Keys, CMEK) geschützt sind.
  • Alle in einer Platzhalterabfrage referenzierten Tabellen müssen genau dieselben Tag-Schlüssel und ‑Werte haben.

  • 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'

  • Platzhaltertabellen werden von BI Engine nicht unterstützt.

  • Platzhaltertabellen werden für Tabellen mit flexiblen Spaltennamen oder umbenannten Spalten nicht unterstützt. Um diese Einschränkung zu umgehen, schließen Sie die betroffenen Spalten aus der Abfrage aus oder filtern Sie die Tabelle mit der Pseudospalte _TABLE_SUFFIX.

Hinweise

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'