GoogleSQL für Bigtable – Übersicht

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

Dieses Dokument bietet einen Überblick über GoogleSQL for 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 mit dem Bigtable-Speichermodell und den Konzepten des Schemadesigns vertraut sein.

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 ausführen. Weitere Informationen finden Sie unter SQL mit einer Bigtable-Clientbibliothek verwenden.

SQL-Abfragen werden von Clusterknoten genauso verarbeitet wie NoSQL-Datenanfragen. Daher gelten dieselben Best Practices beim Erstellen von SQL-Abfragen, die auf Ihre Bigtable-Daten angewendet werden, z. B. die Vermeidung von vollständigen Tabellenscans oder komplexen Filtern. Weitere Informationen finden Sie unter Lesevorgänge und Leistung.

Sie können Data Boost nicht mit GoogleSQL for Bigtable verwenden.

Anwendungsfälle

GoogleSQL für Bigtable eignet sich optimal für die Entwicklung von Anwendungen mit niedriger Latenz. Außerdem können Sie SQL-Abfragen in der Google Cloud Console ausfü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:

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

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

Wichtige Konzepte

In diesem Abschnitt werden die wichtigsten Konzepte beschrieben, die Sie beachten sollten, wenn Sie Ihre Bigtable-Daten mit GoogleSQL abfragen.

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 der Tabelle aus den folgenden Elementen:

• 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

Kartendatentyp

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

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

Im folgenden Beispiel wird eine SQL-Abfrage verwendet, die eine Tabelle mit dem Zeilenschlüsselwert und dem neuesten Wert des Qualifiers aus einer Zuordnung namens columnFamily zurückgibt.

  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 sind die Karten, die Spaltenfamilien darstellen, verschachtelt und werden als Array zurückgegeben. Im Array ist jeder Schlüssel selbst eine Zuordnung, die aus einem Zeitstempel als Schlüssel und Zellendaten als Wert besteht. Das Format 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 und wird absteigend nach Zeitstempel sortiert.

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

Sparse Tabellen

Ein wichtiges Merkmal von Bigtable ist sein flexibles Datenmodell. Wenn eine Spalte in einer Bigtable-Tabelle in einer Zeile nicht verwendet wird, werden für die Spalte keine Daten gespeichert. Eine Zeile kann eine Spalte und die nächste Zeile 100 Spalten haben. 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 jedoch eine Bigtable-Tabelle mit GoogleSQL abfragen, wird eine nicht verwendete Spalte durch eine leere 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, werden in GoogleSQL standardmäßig implizit STRING-Werte in BYTES-Werte umgewandelt. Das bedeutet, dass Sie beispielsweise den String 'qualifier' anstelle der Byte-Sequenz b'qualifier' angeben können.

Da Bigtable standardmäßig alle Daten als Bytes behandelt, enthalten die meisten Bigtable-Spalten keine Typinformationen. In GoogleSQL können Sie jedoch mit der Funktion CAST ein Schema zum Zeitpunkt des Lesens definieren. Weitere Informationen zu Umwandlungen finden Sie unter Konvertierungsfunktionen.

Zeitliche Filter

In der folgenden Tabelle sind die Argumente aufgeführt, die Sie beim Zugriff auf zeitliche Elemente einer Tabelle verwenden können. Argumente werden in der Reihenfolge aufgelistet, 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 Abfragen

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

Aktuelle Version abrufen

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

Im folgenden Beispieldatensatz ist zu sehen, dass sich user1 zweimal im Bundesstaat New York und einmal innerhalb der Stadt Brooklyn umgezogen hat. In diesem Beispiel ist address die Spaltenfamilie und die Spaltenqualifizierer sind street, city und state. Zellen in einer Spalte sind 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 * FROM myTable WHERE _key = 'user1'

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

Alle Versionen abrufen

Wenn Sie ältere Versionen (Zellen) der Daten abrufen möchten, verwenden Sie das Flag with_history. Sie können auch Aliasse für Spalten und Ausdrücke verwenden, wie im folgenden Beispiel gezeigt.

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

Wenn Sie die Ereignisse besser nachvollziehen möchten, die zum aktuellen Status einer Zeile geführt haben, 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 seine aktuelle Adresse umgezogen ist und woher er umgezogen ist, 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 Wert mit Zeitstempel für die angegebene Zeile, Spaltenfamilie und Spalte. Zeitstempel werden in umgekehrter chronologischer Reihenfolge sortiert, sodass die neuesten Daten immer der erste zurückgegebene Artikel sind.

Die Abfrageantwort sieht so aus:

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

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

Daten ab einem bestimmten Zeitpunkt 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 vom 10. Januar 2022, 13:14 Uhr, abrufen möchten, können Sie die folgende Abfrage ausführen.

  SELECT address
  FROM myTable(as_of => TIMESTAMP('2022/01/10-13:14:00'))
  WHERE _key = 'user1'

Das Ergebnis zeigt die letzte bekannte Adresse vom 10. Januar 2022 um 13:14 Uhr. Das ist die Kombination aus Straße und Ort aus der Aktualisierung vom 20. Dezember 2021 um 09:44:31.010 und dem Bundesland aus der Aktualisierung vom 01. März 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'

Betrachten Sie den folgenden Datensatz, der den Ein-/Aus-Status von Rauch- und Kohlenmonoxidmeldern zeigt. 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 Bereiche in building1 ermitteln, in denen am 1. April 2023 um 9:00 Uhr ein Rauchmelder aktiviert war, und den Status des Kohlenmonoxidmelders zu diesem Zeitpunkt.

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

Das Ergebnis lautet:

Zeitreihendaten abfragen

Ein häufiger Anwendungsfall für Bigtable ist das Speichern von Zeitreihendaten. Betrachten Sie den folgenden Beispieldatensatz mit Temperatur- und Feuchtigkeitswerten für Wettersensoren. Die Spaltenfamilien-ID ist metrics und die Spaltenqualifizierer sind temperature und humidity. Die Zellen in einer Spalte sind durch leere Zeilen getrennt. Jede Zelle stellt eine Sensormessung mit Zeitstempel dar.

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(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   WHERE _key LIKE 'sensorA%'

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

JSON abfragen

Mit JSON-Funktionen können Sie JSON-Daten, die als Bigtable-Werte gespeichert sind, für betriebliche Arbeitslasten 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 eine hohe Flexibilität beim Benennen von Tabellen und Spalten. Daher müssen die 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-Statement.

  -- ERROR: Table name format not supported

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

Sie können das 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 ebenfalls mit einem Backslash auskommentiert werden.

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

SQL mit einer Bigtable-Clientbibliothek verwenden

Die Bigtable-Clientbibliotheken für Java und Python unterstützen das Abfragen von Daten mit SQL über die executeQuery API. In den folgenden Beispielen wird gezeigt, wie Sie eine Abfrage stellen und auf die Daten zugreifen:

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      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"])

Nächste Schritte

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