Abfragen für kontinuierliche materialisierte Ansichten

Wenn Sie eine kontinuierliche materialisierte Ansicht einer Bigtable-Tabelle erstellen möchten, führen Sie eine SQL-Abfrage aus, die die kontinuierliche materialisierte Ansicht definiert.

In diesem Dokument werden Konzepte und Muster beschrieben, die Ihnen bei der Vorbereitung Ihrer SQL-Abfrage für kontinuierliche materialisierte Ansichten helfen. Bevor Sie dieses Dokument lesen, sollten Sie mit kontinuierlichen materialisierten Ansichten und GoogleSQL für Bigtable vertraut sein.

Kontinuierliche materialisierte Ansichten verwenden eine eingeschränkte SQL-Syntax. Das folgende Muster zeigt, wie Sie eine SQL-Abfrage für eine kontinuierliche materialisierte Ansicht erstellen:

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
GROUP BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

Wenn Sie eine SQL-Abfrage für eine kontinuierliche materialisierte Ansicht als asynchronen sekundären Index erstellen möchten, verwenden Sie die ORDER BY-Klausel:

SELECT
  expression AS alias [, ...]
FROM from_item
[ WHERE bool_expression ]
ORDER BY expression [, ...];

from_item:
    {
      table_name [ as_alias ]
      | field_path
      }

as_alias:
    [ AS ] alias

Abfragebeschränkungen

Die folgenden Regeln gelten für eine SQL-Abfrage, die zum Erstellen einer kontinuierlichen materialisierten Ansicht verwendet wird:

  • Muss eine SELECT-Anweisung sein.
  • Muss eine GROUP BY-Klausel oder für Abfragen für asynchrone sekundäre Indizes eine ORDER BY-Klausel enthalten, aber nicht beides.
  • Darf nur unterstützte Aggregationsfunktionen verwenden.
  • Kann mehrere Aggregationen pro Gruppe enthalten.

Unterstützte Aggregationen

Sie können die folgenden Aggregationsfunktionen in einer SQL-Abfrage verwenden, die eine kontinuierliche materialisierte Ansicht definiert:

  • COUNT
  • SUM
  • MIN
  • MAX
  • HLL_COUNT.INIT
  • HLL_COUNT.MERGE
  • HLL_COUNT.MERGE_PARTIAL
  • ANY_VALUE
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • AVG

Wenn Sie SELECT COUNT(*) verwenden, müssen Sie einen Zeilenschlüssel definieren, wie im folgenden Beispiel:

SELECT
  '*' AS _key,
  COUNT(*) AS count
FROM
  foo
GROUP BY
  _key;

Nicht unterstützte SQL-Features

Sie können die folgenden SQL-Features nicht verwenden:

  • Alle Features, die von GoogleSQL für Bigtable nicht unterstützt werden
  • ARRAY
  • ARRAY_AGG
  • ARRAY_CONCAT_AGG
  • COUNT_IF
  • CURRENT_TIME und andere nicht deterministische Funktionen
  • DATE, DATETIME als Ausgabespalten (TIMESTAMP verwenden oder einen String speichern)
  • DESC-Sortierung in der Ausgabe
  • DISTINCT-Option, wie in SUM(*DISTINCT* value)
  • LIMIT/OFFSET
  • SELECT *
  • OVER-Klausel zum Erstellen einer Fensteraggregation
  • STRUCT

Außerdem können Sie GROUP BY- oder ORDER BY-Klauseln nicht verschachteln oder Map-Spalten erstellen. Weitere Einschränkungen finden Sie unter Einschränkungen.

Ausgeschlossene Zeilen vermeiden

Eingabezeilen werden unter den folgenden Umständen aus einer kontinuierlichen materialisierten Ansicht ausgeschlossen:

  • Aus der Zeile werden mehr als 1 MiB Daten ausgewählt. Wenn Ihre Abfrage SELECT apple AS apples , SUM(banana) AS sum_bananas FROM my_table GROUP BY apples lautet, wird jede Zeile, die mehr als 1 MiB Daten in den apple und banana Spalten enthält, aus der kontinuierlichen materialisierten Ansicht ausgeschlossen.
  • Aus der Zeile werden mehr als 1 MiB Daten ausgegeben. Dies kann vorkommen, wenn Sie Abfragen wie SELECT REPEAT(apple, 1000) verwenden oder große Konstanten verwenden.
  • Es werden mehr als zehnmal so viele Daten ausgegeben als ausgewählt wurden.
  • Die Abfrage stimmt nicht mit Ihren Daten überein. Dazu gehört der Versuch, durch null zu teilen, ein Ganzzahlüberlauf oder die Erwartung eines Zeilenschlüsselformats, das nicht in jedem Zeilenschlüssel verwendet wird.

Ausgeschlossene Zeilen erhöhen den Messwert für Nutzerfehler, wenn sie zum ersten Mal verarbeitet werden. Weitere Informationen zu Messwerten, mit denen Sie Ihre kontinuierlichen materialisierten Ansichten beobachten können, finden Sie unter Messwerte.

Abfragedetails

In diesem Abschnitt wird eine Abfrage für eine kontinuierliche materialisierte Ansicht beschrieben und wie die Ergebnisse aussehen können, wenn die Ansicht abgefragt wird. Die Daten in der Quelltabelle sind die Eingabe und die Ergebnisdaten in der kontinuierlichen materialisierten Ansicht sind die Ausgabe. Ausgabedaten sind entweder aggregiert oder nicht aggregiert (im definierten Schlüssel).

SELECT-Anweisung

Mit der SELECT-Anweisung werden die Spalten und Aggregationen konfiguriert, die in der kontinuierlichen materialisierten Ansicht verwendet werden. Die Anweisung muss entweder eine GROUP BY-Klausel zum Aggregieren über Zeilen hinweg oder eine ORDER BY-Klausel zum Erstellen eines asynchronen sekundären Index verwenden.

SELECT * wird nicht unterstützt, SELECT COUNT(*) jedoch schon.

Wie in einer typischen SELECT-Anweisung können Sie mehrere Aggregationen pro gruppiertem Datensatz haben. Die nicht gruppierten Spalten müssen ein Aggregationsergebnis sein.

Hier ist ein Beispiel für eine Standard-GROUP BY-Aggregationsabfrage in SQL:

SELECT
  myfamily["node"] AS node,
  myfamily["type"] AS type,
  COUNT(clicks) AS clicks_per_key
FROM
  mytable
GROUP BY
  node,
  type

Zeilenschlüssel und nicht aggregierte Daten

Sie können _key als Zeilenschlüssel für eine kontinuierliche materialisierte Ansicht angeben. Andernfalls bilden die Spalten in der GROUP BY-Klausel den Schlüssel in der Ansicht.

Zeilenschlüssel, die durch eine _key-Spalte definiert werden

Optional können Sie eine _key-Spalte angeben, wenn Sie Ihre kontinuierliche materialisierte Ansicht definieren. Dies unterscheidet sich von der _key Spalte, die Sie erhalten, wenn Sie eine SQL-Abfrage für eine Bigtable-Tabelle ausführen. Wenn Sie _key angeben, gelten die folgenden Regeln:

  • Sie müssen nach _key gruppieren und können optional nur nach _timestamp gruppieren. Weitere Informationen finden Sie unter Zeitstempel.
  • Die Spalte _key muss vom Typ BYTES sein.

Die Angabe von _key ist nützlich, wenn Sie die Ansicht mit ReadRows und nicht mit SQL lesen möchten, da Sie so das Format des Zeilenschlüssels steuern können. Andererseits muss bei einer SQL-Abfrage für eine Ansicht mit einem definierten _key der _key möglicherweise explizit decodiert werden, anstatt nur strukturierte Schlüsselspalten zurückzugeben.

Zeilenschlüssel, die durch die GROUP BY- oder ORDER BY-Klausel definiert werden

Wenn Sie keinen _key angeben, werden die nicht aggregierten Spalten in Ihrer SELECT-Liste zum Zeilenschlüssel in der Ansicht. Sie können den Schlüsselspalten beliebige Namen zuweisen, die den SQL-Konventionen entsprechen. Verwenden Sie diesen Ansatz, wenn Sie die Ansicht mit SQL abfragen möchten und keine ReadRows-Anfrage verwenden.

Nicht aggregierte Ausgabespalten in der SELECT Liste müssen in der GROUP BY Klausel enthalten sein. Die Reihenfolge, in der die Spalten in der GROUP BY-Klausel geschrieben werden, ist die Reihenfolge, in der die Daten im Zeilenschlüssel der kontinuierlichen materialisierten Ansicht gespeichert werden. Zum Beispiel, GROUP BY a, b, c entspricht implizit ORDER BY a ASC, b ASC, c ASC.

Wenn Sie anstelle einer GROUP BY-Klausel eine ORDER BY-Klausel verwenden, um einen asynchronen sekundären Index zu erstellen, werden die Spalten in Ihrer SELECT-Liste, die Teil der ORDER BY-Klausel sind, zum Zeilenschlüssel in der Ansicht. Die Reihenfolge, in der die Spalten in der ORDER BY-Klausel geschrieben werden, ist die Reihenfolge, in der die Daten im Zeilenschlüssel der kontinuierlichen materialisierten Ansicht gespeichert werden. Bei ORDER BY a, b, c werden die Daten mit Zeilenschlüsseln gespeichert, die nach a ASC, dann nach b ASC und dann nach c ASC sortiert sind.

Ihr SQL-Filter muss potenzielle NULL-Werte oder andere ungültige Werte entfernen, die Fehler verursachen können. Eine ungültige Zeile, z. B. eine Zeile mit einer NULL-Schlüsselspalte, wird aus den Ergebnissen entfernt und im Messwert materialized_view/user_errors gezählt. Wenn Sie Nutzerfehler beheben möchten, führen Sie die SQL-Abfrage außerhalb einer kontinuierlichen materialisierten Ansicht aus.

Aggregierte Daten

Aggregatspalten in der Abfrage definieren die Berechnungen, mit denen die Daten in der kontinuierlichen materialisierten Ansicht generiert werden.

Der Alias für eine Aggregatspalte wird in der kontinuierlichen materialisierten Ansicht als Spaltenqualifizierer behandelt.

Dazu ein Beispiel:

SELECT
  fam["baz"] AS baz,
  SUM(fam["foo"]) AS sum_foo,
  SUM(fam["bar"]) AS sum_bar
FROM
  TABLE

GROUP BY
  baz;

Die Abfrageausgabe hat die folgenden Eigenschaften:

  • Die Ausgabe für jedes baz befindet sich in einer separaten Zeile in der Reihenfolge baz ASC.
  • Wenn ein bestimmtes baz mindestens ein foo hat, ist sum_foo in der Ausgabereihe ein Wert, der nicht NULL ist.
  • Wenn ein bestimmtes baz mindestens ein bar hat, ist sum_bar in der Ausgabereihe ein Wert, der nicht NULL ist.
  • Wenn ein bestimmtes baz keinen Wert für eine der beiden Spalten hat, wird es aus den Ergebnissen entfernt.

Wenn Sie die Ansicht dann mit SELECT * abfragen, sieht das Ergebnis so ähnlich aus:

baz sum_foo sum_bar
baz1 sum_foo1 sum_bar1
baz2 sum_foo2 sum_bar2

Zeitstempel

Der Standardzeitstempel für eine Ausgabezelle in einer kontinuierlichen materialisierten Ansicht ist 0 (1970-01-01 00:00:00Z). Dieser ist sichtbar, wenn Sie die Ansicht mit ReadRows lesen, aber nicht, wenn Sie sie mit SQL abfragen.

Wenn Sie einen anderen Zeitstempel in der Ausgabe verwenden möchten, können Sie der SELECT-Liste der Abfrage eine Spalte vom Typ TIMESTAMP hinzufügen und sie _timestamp nennen. Wenn Sie die kontinuierliche materialisierte Ansicht mit ReadRows abfragen, wird _timestamp zum Zeitstempel für die anderen Zellen in der Zeile.

Ein Zeitstempel darf nicht NULL sein, muss größer oder gleich null sein und muss ein Vielfaches von 1.000 sein (Millisekundengenauigkeit). Bigtable unterstützt keine Zellzeitstempel, die vor der Unix-Epoche (1970-01-01T00:00:00Z) liegen.

Betrachten Sie das folgende Beispiel, in dem aggregierte Daten nach Tag neu erfasst werden. Die Abfrage verwendet die Funktion UNPACK.

SELECT
  _key,
  TIMESTAMP_TRUNC(_timestamp, DAY) AS _timestamp,
  SUM(sum_family["sum_column"]) AS sum_column,
  SUM(sum_family["foo"]) AS second_sum_column
FROM
  UNPACK(
  SELECT
    *
  FROM
    my_table(with_history => TRUE))
GROUP BY
  1,
  2

Wenn ein bestimmter SUM-Wert für einen bestimmten Tag eine nicht leere Eingabe hat, enthält die Ausgabereihe einen aggregierten Wert mit einem Zeitstempel, der dem abgeschnittenen Tag entspricht.

Wenn Sie die Ansicht mit SELECT * abfragen, sieht das Ergebnis so ähnlich aus:

_key _timestamp sum_column second_sum_column
1 2024-05-01 00:00:00Z 23 99
2 2024-05-02 00:00:00Z 45 201
3 2024-05-03 00:00:00Z NULL 56
4 2024-05-04 00:00:00Z 8 NULL

Codierung

Wenn Sie Ihre kontinuierliche materialisierte Ansicht mit SQL abfragen, müssen Sie nicht wissen, wie aggregierte Werte codiert werden, da SQL die Ergebnisse als typisierte Spalten bereitstellt.

Wenn Sie mit ReadRows aus der Ansicht lesen, müssen Sie die aggregierten Daten in Ihrer Leseanfrage decodieren. Weitere Informationen zu ReadRows-Anfragen finden Sie unter Lesevorgänge.

Aggregierte Werte in einer kontinuierlichen materialisierten Ansicht werden mit der in der folgenden Tabelle beschriebenen Codierung gespeichert, basierend auf dem Ausgabetyp der Spalte aus der Ansichtsdefinition.

Typ Codierung
BOOL 1-Byte-Wert, 1 = true, 0 = false
BYTES Keine Codierung
INT64 (oder INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT) 64-Bit-Big-Endian
FLOAT64 64-Bit-IEEE 754, ohne NaN und +/-inf
STRING UTF-8
TIME/TIMESTAMP 64-Bit-Ganzzahl, die die Anzahl der Mikrosekunden seit der Unix-Epoche darstellt (entspricht GoogleSQL)
Weitere Informationen finden Sie in der Data API-Referenz unter Codierung.

Nächste Schritte