Partitionierte Tabellen abfragen

Partitionierte Tabellen abfragen

So können Sie partitionierte Tabellen abfragen:

• Mit der Cloud Console
• Mit dem Befehl bq query des bq-Befehlszeilentools
• Durch Aufrufen der API-Methode jobs.insert und Konfigurieren eines Abfragejobs
• Mit den Clientbibliotheken

Weitere Informationen zum Ausführen von Abfragen finden Sie unter Interaktive Abfragen und Batchabfragen ausführen.

Erforderliche Berechtigungen

Zum Abfragen einer Tabelle benötigen Sie mindestens Berechtigungen des Typs bigquery.tables.getData.

Die folgenden vordefinierten IAM-Rollen enthalten bigquery.tables.getData-Berechtigungen:

• bigquery.dataViewer
• bigquery.dataOwner
• bigquery.dataEditor
• bigquery.admin

Wenn ein Nutzer mit Berechtigungen vom Typ bigquery.datasets.create ein Dataset erstellt, hat er dafür außerdem bigquery.dataOwner-Zugriff. Mit bigquery.dataOwner-Zugriff hat der Nutzer die Möglichkeit, Tabellen und Ansichten im Dataset abzufragen.

Sie benötigen außerdem Berechtigungen des Typs bigquery.jobs.create, um Abfragejobs ausführen zu können. Die folgenden vordefinierten IAM-Rollen enthalten bigquery.jobs.create-Berechtigungen:

• bigquery.user
• bigquery.jobUser
• bigquery.admin

Weitere Informationen zu IAM-Rollen und Berechtigungen in BigQuery finden Sie unter Zugriffssteuerung.

Pseudospalten von partitionierten Tabellen mit Aufnahmezeit

Wenn Sie eine nach Aufnahmezeit partitionierte Tabelle erstellen, werden die beiden Pseudospalten _PARTITIONTIME und _PARTITIONDATE in diese Tabelle eingefügt. Die Pseudospalte _PARTITIONTIME enthält einen datumsbasierten Zeitstempel für Daten, die in die Tabelle geladen werden. Die Pseudospalte _PARTITIONDATE enthält eine Datumsdarstellung. Beide Pseudospaltennamen sind reserviert, darum können Sie in keiner Ihrer Tabellen eine Spalte mit einem der beiden Namen erstellen.

_PARTITIONTIME und _PARTITIONDATE sind nur in partitionierten Tabellen mit Aufnahmezeit verfügbar. Partitionierte Tabellen haben keine Pseudospalten. Weitere Informationen zum Abfragen von partitionierten Tabellen finden Sie unter Partitionierte Tabellen abfragen.

Pseudospalte _PARTITIONTIME

Die Pseudospalte _PARTITIONTIME enthält einen auf der UTC-Zeit basierenden Zeitstempel und gibt die Anzahl von Mikrosekunden seit der Unix-Epoche an. Wenn beispielsweise Daten am 15. April 2016 um 8:15:00 Uhr (UTC) an eine Tabelle angefügt wurden, haben alle an diesem Tag angefügten Datenzeilen die Spalte _PARTITIONTIME, die einen der folgenden Werte enthält: + TIMESTAMP("2016-04-15 08:00:00") für stündlich partitionierte Tabellen. + TIMESTAMP("2016-04-15") für täglich partitionierte Tabellen. + TIMESTAMP("2016-04-01") für monatlich partitionierte Tabellen. + TIMESTAMP("2016-01-01") für jährlich partitionierte Tabellen.

Zum Abfragen der Pseudospalte _PARTITIONTIME müssen Sie einen Alias verwenden. Mit der folgenden Abfrage wählen Sie beispielsweise _PARTITIONTIME aus. Dabei wird der Pseudospalte der Alias pt zugewiesen:

SELECT
  _PARTITIONTIME AS pt,
  column
FROM
  dataset.table

Dabei gilt:

• column ist der Name einer abzufragenden Spalte. Sie können mehrere Spalten als eine durch Kommas getrennte Liste angeben.
• dataset ist das Dataset, das die partitionierte Tabelle enthält.
• table ist die partitionierte Tabelle.

Daten im Streaming-Zwischenspeicher haben NULL-Werte in der Spalte _PARTITIONTIME.

Pseudospalte _PARTITIONDATE

Die Pseudospalte _PARTITIONDATE enthält das UTC-Datum, das dem Wert in der Pseudospalte _PARTITIONTIME entspricht. Diese Spalte wird in stündlich, monatlich oder jährlich partitionierten Tabellen nicht unterstützt.

Zum Abfragen der Pseudospalte _PARTITIONDATE müssen Sie einen Alias verwenden. Mit der folgenden Abfrage wählen Sie beispielsweise _PARTITIONDATE aus. Dabei wird der Pseudospalte der Alias pd zugewiesen:

SELECT
  _PARTITIONDATE AS pd,
  column
FROM
  dataset.table

Dabei gilt:

• column ist der Name einer abzufragenden Spalte. Sie können mehrere Spalten als eine durch Kommas getrennte Liste angeben.
• dataset ist das Dataset, das die partitionierte Tabelle enthält.
• table ist die partitionierte Tabelle.

Daten im Streaming-Zwischenspeicher haben NULL-Werte in der Spalte _PARTITIONDATE.

Partitionierte Tabellen mit Aufnahmezeit unter Verwendung von Pseudospalten abrufen

Wenn Sie Daten in partitionierten Tabellen mit Aufnahmezeit abfragen, legen Sie die Werte in der Pseudospalte _PARTITIONTIME oder _PARTITIONDATE fest, um auf bestimmte Partitionen zu verweisen. Beispiel:

• _PARTITIONTIME >= "2018-01-29 00:00:00" AND _PARTITIONTIME < "2018-01-30 00:00:00"
• _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01') AND TIMESTAMP('2016-01-02')

oder

• _PARTITIONDATE >= "2018-01-29" AND _PARTITIONDATE < "2018-01-30"
• _PARTITIONDATE BETWEEN '2016-01-01' AND '2016-01-02'

Mit Pseudospalten abgefragte Partitionen begrenzen

Sie können die Pseudospalten _PARTITIONTIME und _PARTITIONDATE verwenden, um die Anzahl der während einer Abfrage gescannten Partitionen zu begrenzen. Dies wird auch als Partitionen bereinigen bezeichnet. Das Bereinigen von Partitionen ist der Mechanismus, mit dem BigQuery unnötige Partitionen aus dem Eingabescan entfernt. Die bereinigten Partitionen werden bei der Berechnung der von der Abfrage gescannten Byte nicht berücksichtigt, wodurch die Kosten für die bedarfsgesteuerte Analyse reduziert werden. Im Allgemeinen reduziert die Bereinigung von Partitionen die Abfragekosten, wenn die Filter aus konstanten Ausdrücken bestehen, die am Anfang der Abfrage ausgewertet werden können.

Achtung: Bei Verwendung der Pseudospalten _PARTITIONTIME und _PARTITIONDATE wird die Anzahl der gescannten Partitionen nicht immer begrenzt. Weitere Informationen finden Sie unter Partitionen bereinigen/begrenzen. Beispiele für Abfragen, die Pseudospalten enthalten, jedoch nicht die Anzahl der gescannten Partitionen begrenzen, finden Sie unter Pseudospaltenabfragen, die alle Partitionen scannen.

Beispiel: Die folgende Abfrage scannt nur die Partitionen zwischen dem 1. Januar 2016 und 2. Januar 2016 in der partitionierten Tabelle:

_PARTITIONTIME

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2016-01-01')
  AND TIMESTAMP('2016-01-02')

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONDATE BETWEEN '2016-01-01'
  AND '2016-01-02'

Beispiele für Partitionsbereinigung

In diesem Beispiel wird die Anzahl der gescannten Partitionen mithilfe eines Pseudospaltenfilters in einer Unterabfrage begrenzt:

_PARTITIONTIME

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONTIME = TIMESTAMP('2016-03-28')) t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

_PARTITIONDATE

SELECT
  column1,
  column2
FROM (
  SELECT
    column1,
    column2
  FROM
    dataset.table
  WHERE
    _PARTITIONDATE = '2016-03-28') t1
CROSS JOIN
  dataset.table t2
WHERE
  t1.column2 = "one"

Durch die folgende Abfrage werden die abgefragten Partitionen basierend auf der ersten Filterbedingung in der Klausel WHERE begrenzt. Die zweite Filterbedingung in der Klausel WHERE begrenzt jedoch nicht die Partitionen, die abgefragt werden, da es sich um dynamische Tabellenwerte handelt.

_PARTITIONTIME

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2017-03-01')
  AND _PARTITIONTIME = (SELECT MAX(timestamp) from dataset.table1)

_PARTITIONDATE

SELECT
  column
FROM
  dataset.table2
WHERE
  _PARTITIONDATE BETWEEN '2017-01-01' AND '2017-03-01'
  AND _PARTITIONDATE = (SELECT MAX(date) from dataset.table1)

Pseudospaltenabfragen, die alle Partitionen scannen

In den folgenden Beispielen werden Pseudospalten verwendet, aber es werden alle Partitionen in einer nach der Zeiteinheit partitionierten Tabelle gescannt.

In Legacy-SQL funktioniert der Filter _PARTITIONTIME nur, wenn er möglichst nah am Tabellennamen angegeben wird. Beispiel: Mit der folgenden Abfrage werden alle Partitionen in table1 gescannt, obwohl der Filter _PARTITIONTIME vorhanden ist:

#legacySQL
# Scans all partitions on t1
SELECT
  t1.field1,
  t2.field1
FROM
  dataset.table1 t1
CROSS JOIN
  dataset.table2 t2
WHERE
  table1._PARTITIONTIME = TIMESTAMP('2016-03-28')
  AND t1.field1 = "one"

Nehmen Sie in den Filter _PARTITIONTIME keine anderen Spalten auf. In der folgenden Abfrage werden die gescannten Partitionen beispielsweise nicht begrenzt, da field1 eine Spalte in der Tabelle ist und BigQuery nicht im Voraus bestimmen kann, welche Partitionen ausgewählt werden sollen. Weitere Informationen finden Sie unter Partitionen bereinigen/begrenzen.

# Scans all partitions of table2
SELECT
  field1
FROM
  dataset.table2
WHERE
  _PARTITIONTIME + field1 = TIMESTAMP('2016-03-28');

Bessere Leistung mit Pseudospalten

Verwenden Sie für eine bessere Abfrageleistung nur die Pseudospalte _PARTITIONTIME auf der linken Seite eines Vergleichs. In den nächsten beiden Beispielabfragen wird die gleiche Datenmenge verarbeitet, aber das zweite Beispiel bietet möglicherweise eine bessere Leistung.

Beispiel 1: Die folgende Abfrage ist möglicherweise etwas langsamer, da sie den Wert der Pseudospalte mit anderen Vorgängen im Filter WHERE kombiniert.

Standard-SQL

#standardSQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  TIMESTAMP_ADD(_PARTITIONTIME, INTERVAL 5 DAY) > TIMESTAMP("2016-04-15")

Legacy-SQL

#legacySQL
/* Can be slower */
SELECT
  field1
FROM
  dataset.table1
WHERE
  DATE_ADD(_PARTITIONTIME, 5, "DAY") > TIMESTAMP("2016-04-15")

Beispiel 2: Die folgende Abfrage ist möglicherweise leistungsfähiger, da nur die Pseudospalte links vom Filtervergleich steht.

Standard-SQL

#standardSQL
/* Often performs better */
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > TIMESTAMP_SUB(TIMESTAMP('2016-04-15'), INTERVAL 5 DAY)

Legacy-SQL

#legacySQL
/* Often performs better */
SELECT
  field1
FROM
  dataset.table1
WHERE
  _PARTITIONTIME > DATE_ADD(TIMESTAMP('2016-04-15'), -5, "DAY")

Je nach Tabellengröße kann die zweite Abfrage, bei der nur _PARTITIONTIME links vom Vergleichsoperator > steht, eine bessere Leistung als die erste Abfrage bieten. Da von beiden Abfragen die gleiche Datenmenge verarbeitet wird, ist die Anzahl der Byte, die in Rechnung gestellt wird, in beiden Fällen identisch.

Partitionierte Tabellen mit Aufnahmezeit unter Verwendung einer Platzhaltertabelle abrufen

Sie können Pseudospalten nicht nur dazu verwenden, die Anzahl der Partitionen zu begrenzen, die während einer Abfrage gescannt werden. Sie können sie auch dazu nutzen, eine Reihe von partitionierten Tabellen mithilfe einer Platzhaltertabelle abzufragen. Informationen zum Verwenden einer Platzhaltertabelle mit partitionierten Tabellen finden Sie unter Bereich mit partitionierten Tabellen scannen, die _PARTITIONTIME verwenden.

Partitionierte Tabellen mit Aufnahmezeit unter Verwendung von Zeitzonen abrufen

Wenn das Feld ausgefüllt ist, basiert der Wert von _PARTITIONTIME auf dem UTC-Datum. Partitionen werden demnach basierend auf 12:00 Uhr UTC unterteilt. Wenn Sie Daten auf der Grundlage einer anderen Zeitzone als UTC abfragen möchten, sollten Sie eine der folgenden Optionen auswählen, bevor Sie mit dem Laden von Daten in Ihre Tabelle beginnen.

Für die Abfrage von Daten in einer partitionierten Tabelle unter Verwendung einer anderen Zeitzone als UTC stehen zwei Möglichkeiten zur Verfügung. Sie können entweder eine separate Zeitstempelspalte erstellen oder Partition-Decorators zum Laden der Daten in eine bestimmte Partition verwenden.

Bei Verwendung einer Zeitstempelspalte können Sie die standardmäßige UTC-basierte Partitionierung verwenden und Zeitzonenunterschiede in den SQL-Abfragen berücksichtigen. Wenn Sie Partitionen bevorzugen, die nach einer anderen Zeitzone als UTC gruppiert sind, verwenden Sie alternativ Partitions-Decorators zum Laden von Daten in Partitionen basierend auf einer anderen Zeitzone.

Zeitzonen mithilfe einer Zeitstempelspalte abfragen

Zur Berücksichtigung von Zeitzonen, die einen Zeitstempel verwenden, erstellen Sie eine separate Spalte, um einen Zeitstempel zu speichern, mit dem Zeilen nach Stunden oder Minuten adressiert werden.

Für die Abfrage von Daten basierend auf einer von UTC unterschiedlichen Zeitzone verwenden Sie sowohl die Pseudospalte _PARTITIONTIME als auch die benutzerdefinierte Zeitstempelspalte. Durch die Verwendung von _PARTITIONTIME wird der Scan der Tabelle auf die relevanten Partitionen eingeschränkt und der benutzerdefinierte Zeitstempel schränkt die Ergebnisse noch weiter auf die gewünschte Zeitzone ein. Im folgenden Beispiel werden aus einer partitionierten Tabelle (mydataset.partitioned_table) mit dem Zeitstempelfeld MY_TIMESTAMP_FIELD Daten abgefragt, die zwischen 2016-05-01 12:00:00 PST und 2016-05-05 14:00:00 PST in die Tabelle eingefügt wurden:

Standard-SQL

#standardSQL
SELECT
  field1
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND TIMESTAMP_ADD(MY_TIMESTAMP_FIELD, INTERVAL 8 HOUR) BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

Legacy-SQL

#legacySQL
SELECT
  field1
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP("2016-05-01")
  AND TIMESTAMP("2016-05-06")
  AND DATE_ADD(MY_TIMESTAMP_FIELD, 8, 'HOUR') BETWEEN TIMESTAMP("2016-05-01 12:00:00")
  AND TIMESTAMP("2016-05-05 14:00:00");

Ansicht mithilfe der Pseudospalten einer partitionierten Tabelle mit Aufnahmezeit erstellen

Sie erstellen eine Ansicht mit einem Filter für die Pseudospalte _PARTITIONTIME oder _PARTITIONDATE, um die Menge der von einer Abfrage gelesenen Daten auf eine Gruppe von Partitionen zu beschränken. Die folgende Abfrage kann beispielsweise verwendet werden, um eine Ansicht zu erstellen, die von einer Tabelle mit dem Namen dataset.partitioned_table nur die letzten sieben Tage an Daten einschließt:

Standard-SQL

#standardSQL
SELECT
  *
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 * 24 HOUR),DAY)
  AND TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY);

Legacy-SQL

#legacySQL
SELECT
  *
FROM
  dataset.partitioned_table
WHERE
  _PARTITIONTIME BETWEEN TIMESTAMP(UTC_USEC_TO_DAY(NOW() - 7 * 60 * 60 * 24 * 1000000))
  AND TIMESTAMP(UTC_USEC_TO_DAY(CURRENT_TIMESTAMP()));

Weitere Informationen zum Erstellen von Ansichten finden Sie unter Ansichten erstellen.

Partition _UNPARTITIONED_ von nach Aufnahmezeit partitionierten Tabellen

Die Partition __UNPARTITIONED__ enthält vorübergehend diejenigen Daten, die über den Streamingpuffer in eine partitionierte Tabelle gestreamt werden. Daten, die direkt in eine bestimmte Partition einer partitionierten Tabelle gestreamt werden, verwenden nicht die Partition __UNPARTITIONED__. Stattdessen werden die Daten direkt in die Partition gestreamt. Weitere Informationen finden Sie unter In partitionierte Tabellen streamen.

Zum Abfragen von Daten in der Partition __UNPARTITIONED__ verwenden Sie die Pseudospalte _PARTITIONTIME mit dem Wert NULL. Beispiel:

SELECT
  column
FROM
  dataset.table
WHERE
  _PARTITIONTIME IS NULL

Dabei gilt:

• column ist der Name einer abzufragenden Spalte. Sie können mehrere Spalten als eine durch Kommas getrennte Liste angeben.
• dataset ist das Dataset, das die partitionierte Tabelle enthält.
• table ist die partitionierte Tabelle.

Partitionierte Tabellen abfragen

Tabellen, die anhand der Spalte TIMESTAMP, DATE, DATETIME oder INTEGER partitioniert sind, haben keine Pseudospalten. Sie verwenden einen Prädikatfilter (WHERE-Klausel), um die Anzahl der Partitionen zu begrenzen, die beim Abfragen von partitionierten Tabellen gescannt werden. Filter für die Partitionierungsspalte werden verwendet, um die Partitionen zu beschneiden und die Abfragekosten zu reduzieren.

Stündliche, monatliche und jährliche partitionierte Tabellen können nur über Standard-SQL abgefragt werden.

Wenn Sie eine partitionierte Tabelle erstellen, können Sie die Option Partitionsfilter anfordern aktivieren und damit die Verwendung eines Prädikatfilters voraussetzen. Wenn diese Option aktiviert ist, führen Versuche, die partitionierte Tabelle ohne Angabe einer WHERE-Klausel abzufragen, zu diesem Fehler: Cannot query over table 'project_id.dataset.table' without a filter that can be used for partition elimination.

Hinweis: Es muss mindestens ein Prädikat vorhanden sein, das nur auf eine Partitionsspalte verweist, damit der Filter für die Eliminierung der Partition infrage kommt. Bei nach Aufnahmezeit partitionierten Tabellen ist dies entweder die Pseudospalte _PARTITIONTIME oder die Pseudospalte _PARTITIONDATE. Für eine nach Spalte partition_id partitionierte Tabelle mit einer zusätzlichen Spalte f erfüllen beispielsweise beide WHERE-Klauseln die Anforderung:
    WHERE partition_id = "foo"
    WHERE partition_id = "foo" AND f = "bar"

WHERE (partition_id = "foo" OR f = "bar") ist jedoch nicht ausreichend.

Weitere Informationen zum Einbinden der Option Partitionsfilter anfordern beim Erstellen einer partitionierten Tabelle finden Sie unter Partitionierte Tabellen erstellen.

Wenn Sie die Option Partitionsfilter anfordern beim Erstellen einer partitionierten Tabelle nicht aktiviert haben, können Sie die Tabelle aktualisieren und dies nachholen.

Partitionen bereinigen/begrenzen

Verwenden Sie in Ihrem Filter einen konstanten Ausdruck, um die Partitionen zu begrenzen, die in einer Abfrage gescannt werden. Wenn Sie in Ihrem Abfragefilter dynamische Ausdrücke verwenden, muss BigQuery alle Partitionen scannen.

Beispiel: Die folgende Abfrage bereinigt Partitionen, da der Filter WHERE t1.ts=CURRENT_TIMESTAMP() einen konstanten Ausdruck enthält:

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON t1.id_field = t2.field2
WHERE
  t1.ts = CURRENT_TIMESTAMP()

Die folgende Abfrage bereinigt jedoch keine Partitionen, da der Filter WHERE t1.ts = (SELECT timestamp from table where key = 2) kein konstanter Ausdruck ist. Dies hängt von den dynamischen Werten der Felder timestamp und key ab:

#standardSQL
SELECT
  t1.name,
  t2.category
FROM
  table1 t1
INNER JOIN
  table2 t2
ON
  t1.id_field = t2.field2
WHERE
  t1.ts = (SELECT timestamp from table3 where key = 2)

Partitionsspalte in Ihrem Filter isolieren

Isolieren Sie die Partitionsspalte, wenn Sie einen Filter angeben. Filter, die Daten aus mehreren Feldern zur Berechnung benötigen, bereinigen Partitionen nicht. Zum Beispiel erfolgt keine Partitionsbereinigung bei Abfragen mit einem Datumsvergleich, die die Partitionierungsspalte und ein zweites Feld verwenden, oder bei Abfragen, die einige Feldverkettungen enthalten.

Beispielsweise bereinigt der folgende Filter keine Partitionen, da er eine Berechnung basierend auf dem Feld ts der Partitionierung und einem zweiten Feld ts2 erfordert:

WHERE TIMESTAMP_ADD(ts, INTERVAL 6 HOUR) > ts2

Tipp

• Eine Übersicht über partitionierte Tabellen finden Sie unter Einführung in partitionierte Tabellen.
• Weitere Informationen zu partitionierten Tabellen mit Aufnahmezeit finden Sie unter Partitionierte Tabellen mit Aufnahmezeit erstellen und verwenden.
• Weitere Informationen zu spaltenbasierten partitionierten Tabellen finden Sie unter Nach Datum/Zeitstempel/Datums-/Uhrzeitformat partitionierte Tabellen erstellen und verwenden.
• Weitere Informationen zu nach Ganzzahlbereich partitionierten Tabellen finden Sie unter Nach Ganzzahlbereich partitionierte Tabellen erstellen und verwenden.