GoogleSQL für Bigtable – Übersicht

Sie können GoogleSQL-Anweisungen verwenden, um Ihre Bigtable-Daten abzufragen. GoogleSQL ist eine ANSI-konforme strukturierte Abfragesprache (SQL), die auch für andere Google CloudDienste wie BigQuery und Spanner implementiert ist.

Dieses Dokument bietet einen Überblick über GoogleSQL für Bigtable. Es enthält Beispiele für SQL-Abfragen, die Sie mit Bigtable verwenden können, und beschreibt, wie sie sich auf ein Bigtable-Tabellenschema beziehen. Bevor Sie dieses Dokument lesen, sollten Sie sich mit dem Bigtable-Speichermodell und den Konzepten für das Schemadesign vertraut machen.

Sie können Abfragen in Bigtable Studio in der Google Cloud Console erstellen und ausführen oder sie programmatisch mit der Bigtable-Clientbibliothek für Java, Python oder Go ausführen. Weitere Informationen finden Sie unter SQL mit einer Bigtable-Clientbibliothek verwenden.

SQL-Abfragen werden von Clusternknoten auf dieselbe Weise wie NoSQL-Datenanfragen verarbeitet. Daher gelten beim Erstellen von SQL-Abfragen für Ihre Bigtable-Daten dieselben Best Practices, z. B. das Vermeiden von vollständigen Tabellenscans oder komplexen Filtern. Weitere Informationen finden Sie unter Lesevorgänge und Leistung.

Data Boost kann nicht mit GoogleSQL für Bigtable verwendet werden.

Anwendungsfälle

GoogleSQL für Bigtable ist optimal für die Anwendungsentwicklung mit niedriger Latenz. Außerdem kann es hilfreich sein, SQL-Abfragen in derGoogle Cloud -Konsole auszuführen, um schnell eine visuelle Darstellung des Schemas einer Tabelle zu erhalten, zu prüfen, ob bestimmte Daten geschrieben wurden, oder mögliche Datenprobleme zu beheben.

Die aktuelle Version von GoogleSQL for Bigtable unterstützt einige gängige SQL-Konstrukte nicht, darunter:

• Anweisungen der Datenbearbeitungssprache (Data Manipulation Language, DML) über SELECT hinaus, z. B. INSERT, UPDATE oder DELETE
• DDL-Anweisungen (Data Definition Language, Datendefinitionssprache) wie CREATE, ALTER oder DROP
• Anweisungen zur Datenzugriffssteuerung
• Abfragesyntax für Unterabfragen, JOIN, UNION, UNNEST und CTEs

Weitere Informationen, einschließlich unterstützter Funktionen, Operatoren, Datentypen und Abfragesyntax, finden Sie in der GoogleSQL for Bigtable-Referenzdokumentation.

Aufrufe

Mit GoogleSQL für Bigtable können Sie die folgenden Ressourcen erstellen:

• Materialisierte Ansicht: Ein vorausberechnetes Ergebnis einer kontinuierlich ausgeführten SQL-Abfrage, einschließlich aggregierter Daten, das mit der Quelltabelle durch inkrementelle Aktualisierungen synchronisiert wird.
• Logische Ansicht: Eine gespeicherte, benannte Abfrage, die wie eine Tabelle abgefragt werden kann.

Einen Vergleich dieser Ansichtstypen sowie autorisierter Ansichten finden Sie unter Tabellen und Ansichten.

Wichtige Konzepte

In diesem Abschnitt werden wichtige Konzepte erläutert, die Sie kennen sollten, wenn Sie GoogleSQL verwenden, um Ihre Bigtable-Daten abzufragen.

Spaltenfamilien in SQL-Antworten

In Bigtable enthält eine Tabelle eine oder mehrere Spaltenfamilien, mit denen Spalten gruppiert werden. Wenn Sie eine Bigtable-Tabelle mit GoogleSQL abfragen, besteht das Schema für die Tabelle aus Folgendem:

• Eine spezielle Spalte mit dem Namen _key, die den Zeilenschlüsseln in der abgefragten Tabelle entspricht.
• Eine einzelne Spalte für jede Bigtable-Spaltenfamilie in der Tabelle, die die Daten der Spaltenfamilie in dieser Zeile enthält.

Datentyp für Karten

GoogleSQL für Bigtable enthält den Datentyp MAP<key, value>, der speziell für Spaltenfamilien entwickelt wurde.

Standardmäßig enthält jede Zeile in einer Map-Spalte Schlüssel/Wert-Paare, wobei ein Schlüssel der Bigtable-Spaltenqualifizierer in der abgefragten Tabelle und der Wert der neueste Wert für diese Spalte ist.

Im folgenden Beispiel wird mit einer SQL-Abfrage eine Tabelle mit dem Zeilenschlüsselwert und dem neuesten Wert des Qualifizierers aus einer Map namens columnFamily zurückgegeben.

  SELECT _key, columnFamily['qualifier'] FROM myTable

Wenn in Ihrem Bigtable-Schema mehrere Zellen oder Versionen der Daten in Spalten gespeichert werden, können Sie Ihrer SQL-Anweisung einen zeitlichen Filter wie with_history hinzufügen.

In diesem Fall werden Maps, die Spaltenfamilien darstellen, verschachtelt und als Array zurückgegeben. Im Array ist jeder Wert selbst eine Map, die aus einem Zeitstempel als Schlüssel und Zellendaten als Wert besteht. Das Format dafür ist MAP<key, ARRAY<STRUCT<timestamp, value>>>.

Im folgenden Beispiel werden alle Zellen in der Spaltenfamilie „info“ für eine einzelne Zeile zurückgegeben.

  SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';

Die zurückgegebene Karte sieht so aus: In der abgefragten Tabelle ist info die Spaltenfamilie, user_123 der Zeilenschlüssel und city und state die Spaltenqualifizierer. Jedes Zeitstempel-Wert-Paar (STRUCT) in einem Array steht für Zellen in diesen Spalten in dieser Zeile. Die Paare sind nach Zeitstempel absteigend sortiert.

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

Sparsity-Tabellen

Ein wichtiges Merkmal von Bigtable ist das flexible Datenmodell. Wenn eine Spalte in einer Bigtable-Tabelle in einer Zeile nicht verwendet wird, werden keine Daten für die Spalte gespeichert. Eine Zeile kann eine Spalte und die nächste Zeile 100 Spalten enthalten. In einer relationalen Datenbanktabelle enthalten dagegen alle Zeilen alle Spalten und ein NULL-Wert wird in der Regel in der Spalte einer Zeile gespeichert, die keine Daten für diese Spalte enthält.

Wenn Sie eine Bigtable-Tabelle mit GoogleSQL abfragen, wird eine nicht verwendete Spalte jedoch mit einer leeren Map dargestellt und als NULL-Wert zurückgegeben. Diese NULL-Werte können als Abfrageprädikate verwendet werden. Mit einem Prädikat wie WHERE family['column1'] IS NOT NULL kann beispielsweise eine Zeile nur zurückgegeben werden, wenn column1 in der Zeile verwendet wird.

Byte

Wenn Sie einen String angeben, wandelt GoogleSQL STRING-Werte standardmäßig implizit in BYTES-Werte um. Das bedeutet beispielsweise, dass Sie den String 'qualifier' anstelle der Bytefolge b'qualifier' angeben können.

Da in Bigtable standardmäßig alle Daten als Bytes behandelt werden, enthalten die meisten Bigtable-Spalten keine Typinformationen. Mit GoogleSQL können Sie jedoch ein Schema zur Lesezeit mit der Funktion CAST definieren. Weitere Informationen zum Umwandeln von Datentypen finden Sie unter Konvertierungsfunktionen.

Zeitliche Filter

In der folgenden Tabelle sind die Argumente aufgeführt, die Sie beim Zugriff auf temporale Elemente einer Tabelle verwenden können. Die Argumente werden in der Reihenfolge aufgeführt, in der sie gefiltert werden. Beispiel: with_history wird vor latest_n angewendet. Sie müssen einen gültigen Zeitstempel angeben.

Weitere Beispiele finden Sie unter Erweiterte Abfragemuster.

Grundlegende Anfragen

In diesem Abschnitt werden grundlegende Bigtable-SQL-Abfragen beschrieben und anhand von Beispielen veranschaulicht. Weitere Beispielabfragen finden Sie unter GoogleSQL für Bigtable – Beispiele für Abfragemuster.

Neueste Version abrufen

In Bigtable können Sie zwar mehrere Versionen von Daten in jeder Spalte speichern, aber GoogleSQL for Bigtable gibt standardmäßig die neueste Version – die aktuelle Zelle – der Daten für jede Zeile zurück.

Betrachten Sie das folgende Beispiel-Dataset, aus dem hervorgeht, dass user1 zweimal im Bundesstaat New York und einmal in Brooklyn umgezogen ist. In diesem Beispiel ist address die Spaltenfamilie und die Spaltenqualifizierer sind street, city und state. Zellen in einer Spalte werden durch leere Zeilen getrennt.

Wenn Sie die neueste Version jeder Spalte für user1 abrufen möchten, können Sie eine SELECT-Anweisung wie die folgende verwenden.

   SELECT street, city FROM myTable WHERE _key = 'user1'

Die Antwort enthält die aktuelle Adresse, die eine Kombination aus den letzten Werten für Straße, Stadt und Bundesstaat (zu unterschiedlichen Zeiten geschrieben) ist, die als JSON ausgegeben werden. Zeitstempel sind nicht in der Antwort enthalten.

Alle Versionen abrufen

Verwenden Sie das Flag with_history, um ältere Versionen (Zellen) der Daten abzurufen. Sie können auch Aliasse für Spalten und Ausdrücke erstellen, wie im folgenden Beispiel gezeigt.

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

Wenn Sie die Ereignisse, die zum aktuellen Status einer Zeile geführt haben, besser nachvollziehen möchten, können Sie die Zeitstempel für jeden Wert abrufen, indem Sie den vollständigen Verlauf abrufen. Wenn Sie beispielsweise wissen möchten, wann user1 an die aktuelle Adresse gezogen ist und woher, können Sie die folgende Abfrage ausführen:

  SELECT
    address['street'][0].value AS moved_to,
    address['street'][1].value AS moved_from,
    FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
  FROM myTable(with_history => TRUE)
  WHERE _key = 'user1'

Wenn Sie das Flag with_history in Ihrer SQL-Abfrage verwenden, wird die Antwort als MAP<key, ARRAY<STRUCT<timestamp, value>>> zurückgegeben. Jedes Element im Array ist ein Zeitstempelwert für die angegebene Zeile, Spaltenfamilie und Spalte. Zeitstempel sind in umgekehrter chronologischer Reihenfolge sortiert. Die neuesten Daten sind also immer das erste zurückgegebene Element.

Die Antwort auf die Abfrage lautet:

Sie können die Anzahl der Versionen in jeder Zeile auch mit Arrayfunktionen abrufen, wie in der folgenden Abfrage gezeigt:

  SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
  FROM myTable(with_history => TRUE)

Daten für einen bestimmten Zeitraum abrufen

Mit einem as_of-Filter können Sie den Status einer Zeile zu einem bestimmten Zeitpunkt abrufen. Wenn Sie beispielsweise die Adresse von user am 10. Januar 2022 um 13:14 Uhr wissen möchten, können Sie die folgende Abfrage ausführen.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022-01-10T13:14:00.234Z'))
  WHERE _key = 'user1'

Das Ergebnis zeigt, was die letzte bekannte Adresse am 10. Januar 2022 um 13:14 Uhr gewesen wäre. Das ist die Kombination aus Straße und Stadt aus dem Update vom 20.12.2021 um 09:44:31.010 und dem Bundesstaat vom 01.03.2005 um 11:12:15.112.

Dasselbe Ergebnis kann auch mit Unix-Zeitstempeln erzielt werden.

  SELECT address
  FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
  WHERE _key = 'user1'

Sehen Sie sich das folgende Dataset an, in dem der Ein- oder Aus-Status von Rauch- und Kohlenmonoxidmeldern dargestellt ist. Die Spaltenfamilie ist alarmType und die Spaltenqualifizierer sind smoke und carbonMonoxide. Die Zellen in jeder Spalte sind durch leere Zeilen getrennt.

Mit der folgenden Abfrage können Sie Abschnitte von building1 finden, in denen am 1. April 2023 um 9:00 Uhr ein Rauchmelder aktiviert war, und den Status des Kohlenmonoxidmelders zu diesem Zeitpunkt abrufen.

  SELECT _key AS location, alarmType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
  WHERE _key LIKE 'building1%' and alarmType['smoke'] = 'on'

Das Ergebnis lautet:

Zeitreihendaten abfragen

Ein häufiger Anwendungsfall für Bigtable ist das Speichern von Zeitachsendaten. Sehen Sie sich das folgende Beispiel-Dataset an, das Temperatur- und Luftfeuchtigkeitsmesswerte für Wettersensoren enthält. Die Spaltenfamilien-ID ist metrics und die Spaltenqualifizierer sind temperature und humidity. Die Zellen in einer Spalte sind durch Leerzeilen getrennt und jede Zelle steht für einen Sensorwert mit Zeitstempel.

Mit den Zeitfiltern after, before oder after_or_equal können Sie einen bestimmten Bereich von Zeitstempelwerten abrufen. Im folgenden Beispiel wird after verwendet:

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
         before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
   WHERE _key LIKE 'sensorA%'

Die Abfrage gibt die Daten in diesem Format zurück:

Zeitreihendaten für UNPACK

Wenn Sie Zeitreihendaten analysieren, ist es oft besser, die Daten in einem Tabellenformat zu verwenden. Die Bigtable-Funktion UNPACK kann hier helfen.

UNPACK ist eine Bigtable-Tabellenwertfunktion (Table-valued Function, TVF), die anstelle eines einzelnen skalaren Werts eine gesamte Ausgabetabelle zurückgibt und in der FROM-Klausel wie eine Tabellenunterabfrage angezeigt wird. Die TVF UNPACK erweitert jeden Zeitstempelwert in mehrere Zeilen – eine pro Zeitstempel – und verschiebt den Zeitstempel in die Spalte _timestamp.

Die Eingabe für UNPACK ist eine Unterabfrage, in der with_history => true.

Die Ausgabe ist eine erweiterte Tabelle mit einer _timestamp-Spalte in jeder Zeile.

Eine Eingabespaltenfamilie MAP<key, ARRAY<STRUCT<timestamp, value>>> wird zu MAP<key, value> entpackt und ein Spaltenqualifizierer ARRAY<STRUCT<timestamp, value>>> wird zu value entpackt. Andere Eingabespalten werden nicht geändert. Die Spalten müssen in der Unterabfrage ausgewählt werden, damit sie entpackt und ausgewählt werden können. Die neue Spalte „_timestamp“ muss nicht ausgewählt sein, damit die Zeitstempel entpackt werden.

Wenn Sie das Zeitreihenbeispiel unter Zeitreihendaten abfragen erweitern und die Abfrage in diesem Abschnitt als Eingabe verwenden, wird Ihre UNPACK-Abfrage so formatiert:

  SELECT temp_versioned, _timestamp
  FROM
  UNPACK((
    SELECT metrics['temperature'] AS temperature_versioned
    FROM
    sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
          before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
    WHERE _key LIKE 'sensorA%'
  ));

Die Abfrage gibt die Daten in diesem Format zurück:

JSON-Abfrage

Mit JSON-Funktionen können Sie JSON-Daten, die als Bigtable-Werte für operative Arbeitslasten gespeichert sind, bearbeiten.

Mit der folgenden Abfrage können Sie beispielsweise den Wert für das JSON-Element abc aus der letzten Zelle in der Spaltenfamilie session zusammen mit dem Zeilenschlüssel abrufen.

  SELECT _key, JSON_VALUE(session['payload'],'$.abc') AS abc FROM analytics

Sonderzeichen und reservierte Wörter mit Escapezeichen versehen

Bigtable bietet hohe Flexibilität bei der Benennung von Tabellen und Spalten. Daher müssen Tabellennamen in Ihren SQL-Abfragen möglicherweise aufgrund von Sonderzeichen oder reservierten Wörtern maskiert werden.

Die folgende Abfrage ist beispielsweise aufgrund des Punkts im Tabellennamen kein gültiges SQL.

  -- ERROR: Table name format not supported

  SELECT * FROM my.table WHERE _key = 'r1'

Sie können dieses Problem jedoch beheben, indem Sie die Elemente in Graviszeichen (`) einschließen.

  SELECT * FROM `my.table` WHERE _key = 'r1'

Wenn ein reserviertes SQL-Keyword als Kennung verwendet wird, kann es auf ähnliche Weise maskiert werden.

  SELECT * FROM `select` WHERE _key = 'r1'

SQL mit einer Bigtable-Clientbibliothek verwenden

Die Bigtable-Clientbibliotheken für Java, Python und Go unterstützen das Abfragen von Daten mit SQL über die executeQuery API. Die folgenden Beispiele zeigen, wie Sie eine Abfrage ausführen und auf die Daten zugreifen:

  import (
    "cloud.google.com/go/bigtable"
  )

  func query(client *bigtable.Client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    ps, err := client.PrepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      map[string]SQLType{
        "keyParam": BytesSQLType{},
      }
    )
    if err != nil {
      log.Fatalf("Failed to create PreparedStatement: %v", err)
    }

    // For each request, create a BoundStatement with your query parameters set.
    bs, err := ps.Bind(map[string]any{
      "keyParam": []byte("mykey")
    })
    if err != nil {
      log.Fatalf("Failed to bind parameters: %v", err)
    }

    err = bs.Execute(ctx, func(rr ResultRow) bool {
      var byteValue []byte
      err := rr.GetByName("bytesCol", &byteValue)
      if err != nil {
        log.Fatalf("Failed to access bytesCol: %v", err)
      }
      var stringValue string
      err = rr.GetByName("stringCol", &stringValue)
      if err != nil {
        log.Fatalf("Failed to access stringCol: %v", err)
      }
      // Note that column family maps have byte valued keys. Go maps don't support
      // byte[] keys, so the map will have Base64 encoded string keys.
      var cf3 map[string][]byte
      err = rr.GetByName("cf3", &cf3)
      if err != nil {
        log.Fatalf("Failed to access cf3: %v", err)
      }
      // Do something with the data
      // ...
      return true
    })
  }

  static void query(BigtableDataClient client) {
    // Prepare once for queries that will be run multiple times, and reuse
    // the PreparedStatement for each request. Use query parameters to
    // construct PreparedStatements that can be reused.
    PreparedStatement preparedStatement = client.prepareStatement(
      "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
      // For queries with parameters, set the parameter names and types here.
      Map.of("keyParam", SqlType.bytes())
    );

    // For each request, create a BoundStatement with your query parameters set.
    BoundStatement boundStatement = preparedStatement.bind()
      .setBytesParam("keyParam", ByteString.copyFromUtf8("mykey"))
      .build();

    try (ResultSet resultSet = client.executeQuery(boundStatement)) {
      while (resultSet.next()) {
        ByteString byteValue = resultSet.getBytes("bytesCol");
        String stringValue = resultSet.getString("stringCol");
        Map<ByteString, ByteString> cf3Value =
            resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
        // Do something with the data.
      }
    }
  }

  from google.cloud.bigtable.data import BigtableDataClientAsync

  async def execute_query(project_id, instance_id, table_id):
      async with BigtableDataClientAsync(project=project_id) as client:
          query = (
            "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
            " cf3 FROM {table_id} WHERE _key='mykey'"
          )
          async for row in await client.execute_query(query, instance_id):
            print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])

Nutzung für SELECT *

Bei SELECT *-Abfragen kann es zu vorübergehenden Fehlern kommen, wenn einer abgefragten Tabelle eine Spaltenfamilie hinzugefügt oder daraus gelöscht wird. Aus diesem Grund empfehlen wir für Produktionsarbeitslasten, alle Spaltenfamilien-IDs in Ihrer Abfrage anzugeben, anstatt SELECT * zu verwenden. Verwenden Sie z. B. SELECT cf1, cf2, cf3 statt SELECT *.

Nächste Schritte

• Referenzdokumentation zu GoogleSQL für Bigtable
• Weitere Informationen zu Bigtable Studio