Google SQL für Bigtable Übersicht

Vorschau

Dieses Produkt oder diese Funktion unterliegt den „Nutzungsbedingungen für Pre-GA-Angebote“ im Abschnitt „Allgemeine Nutzungsbedingungen“ der dienstspezifischen Nutzungsbedingungen. Pre-GA-Produkte und -Funktionen sind unverändert verfügbar und werden möglicherweise nur eingeschränkt unterstützt. Weitere Informationen finden Sie in den Beschreibungen der Startphase.

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. Sie enthält Beispiele für SQL-Abfragen, die Sie verwenden können und beschreibt, wie sie mit einer Bigtable-Tabellenschema. 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 einem Bigtable-Clientbibliothek.

Anwendungsfälle

GoogleSQL for Bigtable ist optimal für niedrige Latenz Anwendungsentwicklung. Die Ausführung von SQL-Abfragen in der Mit der Google Cloud Console erhalten Sie schnell eine visuelle Darstellung. des Schemas einer Tabelle überprüfen, ob bestimmte Daten geschrieben wurden, oder mögliche Datenprobleme.

Die aktuelle Version von GoogleSQL for Bigtable werden gängige SQL-Konstrukte, einschließlich, aber nicht beschränkt auf, Folgendes:

• DML-Anweisungen (Data Manipulation Language) ü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, GROUP BY, UNNEST und CTEs

Weitere Informationen, einschließlich unterstützter Funktionen, Operatoren, Datentypen und Abfragesyntax finden Sie im GoogleSQL for Bigtable Referenz Dokumentation .

Wichtige Konzepte

In diesem Abschnitt werden die wichtigsten Konzepte erläutert, die Sie bei der Verwendung GoogleSQL zum Abfragen Ihrer Bigtable-Daten verwenden.

Spaltenfamilien in SQL-Antworten

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

• 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, der 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 Ihr Bigtable-Schema das Speichern mehrerer Zellen – oder Versionen der Daten haben. In Spalten können Sie eine temporäre Filter, wie z. B. with_history, zu Ihrer SQL-Anweisung hinzu.

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 wie dem Schlüssel- und Zellendaten als Wert. Das Format ist MAP<key, ARRAY<STRUCT<timestamp, value>>>.

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

  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"}} |
 +----------+------------------------------------------------------------------*/

Dünnbesetzte 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 enthalten und die nächste Zeile könnte 100 Spalten enthalten. Spalten. Im Gegensatz dazu enthalten in einer relationalen Datenbanktabelle alle Zeilen Spalten und ein NULL-Wert wird normalerweise in der Spalte einer Zeile gespeichert, die keine für diese Spalte.

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

Byte

Wenn Sie einen String angeben, wandelt GoogleSQL standardmäßig implizit STRING-Werten in BYTES-Werte ändern. 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 zur Umwandlung finden Sie unter Konvertierung von Funktionen.

Zeitfilter

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. with_history wird beispielsweise vor latest_n angewendet. Du musst geben Sie einen gültigen Zeitstempel an.

Argument	Beschreibung
as_of	Timestamp. Gibt die neuesten Werte zurück, deren Zeitstempel kleiner sind oder gleich dem angegebenen Zeitstempel ist.
with_history	Boolean. Damit wird festgelegt, ob der aktuelle Wert als Skalarwert oder als Wert(e) mit Zeitstempel als STRUCT zurückgegeben wird.
after_or_equal	Timestamp. Werte mit Zeitstempeln nach der Eingabe, einschließlich. Erfordert with_history => TRUE
before	Timestamp. Werte mit Zeitstempeln vor der Eingabe, ausgenommen diese. Erfordert with_history => TRUE
latest_n	Ganzzahl. Die Anzahl der Werte mit Zeitstempeln, die pro Spaltenbegrenzer (Zuordnungsschlüssel) zurückgegeben werden sollen. Muss größer oder gleich 1 sein. Erfordert with_history => TRUE.

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-Abfragemuster für Bigtable.

Neueste Version abrufen

Obwohl Sie mit Bigtable mehrere Versionen von Daten in jede Spalte, standardmäßig GoogleSQL for Bigtable gibt 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 address ist die Spaltenfamilie und die Spaltenqualifizierer sind street, city, und state. Zellen in einer Spalte sind durch leere Zeilen getrennt.

		Adresse
_key	Straße	Ort	state
Nutzer 1	2023/01/10-14:10:01.000: '113 Xyz Street' 2021/12/20-09:44:31.010: '76 Xyz Street' 2005/03/01-11:12:15.112: „123 Abc Street“	2021/12/20-09:44:31.010: „Brooklyn“ 2005/03/01-11:12:15.112: „Queens“	2005/03/01-11:12:15.112: „NY“

Um die neueste Version jeder Spalte für user1 abzurufen, können Sie einen SELECT-Anweisung wie im Folgenden dargestellt.

   SELECT * FROM myTable WHERE _key = 'user1'

Die Antwort enthält die aktuelle Adresse, die sich aus den Letzte Werte für Straße, Stadt und Bundesland (zu unterschiedlichen Zeiten geschrieben), die als JSON Die Antwort enthält keine Zeitstempel.

_key	Adresse
Nutzer 1	{street:'113 Xyz Street', city:'Brooklyn', state: :'NY'}

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)

Um die Ereignisse, die zum aktuellen Status einer Zeile führen, besser zu verstehen, können Sie 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 gezogen 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, lautet 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 sind in umgekehrter chronologischer Reihenfolge sortiert, immer der erste zurückgegebene Artikel.

Hinweis: Mit Richtlinien für die automatische Speicherbereinigung wird die Ablaufzeit oder die Anzahl der Versionen gesteuert, die für eine Spaltenfamilie beibehalten werden. Weitere Informationen finden Sie unter Automatische Speicherbereinigung – Übersicht.

Die Abfrageantwort sieht so aus:

moved_to			moved_from	moved_on
Xyz-Straße 113			Xyz-Straße 76	10.01.2023

Sie können die Anzahl der Versionen in jeder Zeile auch mithilfe von Array-Funktionen 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.

Adresse
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'}

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 das folgende Dataset, das den An-/Aus-Zustand von Rauch und Kohlenmonoxidmelder. Die Spaltenfamilie ist alarmType und die Spaltenqualifizierer sind smoke und carbonMonoxide. Die Zellen in jeder Spalte sind durch leere Zeilen getrennt.

	alarmType
_key	rauch	carbonMonoxide
building1#section1	2023/04/01-09:10:15.000: „Aus“ 2023/04/01-08:41:40.000: „an“ 2020/07/03-06:25:31.000: „Aus“ 2020/07/03-06:02:04.000: 'an'	2023/04/01-09:22:08.000: „Aus“ 2023/04/01-08:53:12.000: 'an'
gebäude1#abschnitt2	2021/03/11-07:15:04.000: „Aus“ 2021/03/11-07:00:25.000: 'an'

Hier findest du Bereiche in „building1“, in denen im April um 09:00 Uhr ein Rauchmelder an war 01.2023 und den Status des Kohlenmonoxidmelders zum jeweiligen Zeitpunkt folgenden Abfrage aus.

  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:

location	CO_sensor
gebäude1#abschnitt1	'an'

Zeitreihendaten abfragen

Ein häufiger Anwendungsfall für Bigtable ist die Speicherung von Zeitachsendaten. Betrachten Sie das folgende Beispiel-Dataset, das Temperatur und Luftfeuchtigkeit anzeigt Messwerte für Wettersensoren. Die Spaltenfamilien-ID ist metrics und die Spaltenqualifizierer sind temperature und humidity. Zellen in einer Spalte werden durch leere Zeilen und jede Zelle stellt einen Sensorwert mit Zeitstempel dar.

	Messwerte
_key	Temperatur	Luftfeuchtigkeit
sensorA#20230105	2023/01/05-02:00:00.000: 54 2023/01/05-01:00:00.000: 56 2023/01/05-00:00:00.000: 55	2023/01/05-02:00:00.000: 0,89 2023/01/05-01:00:00.000: 0,9 2023/01/05-00:00:00.000: 0,91
sensorA#20230104	2023/01/04-23:00:00.000: 56 2023/01/04-22:00:00.000: 57	2023/01/04-23:00:00.000: 0,9 2023/01/04-22:00:00.000: 0,91

Mit den Zeitfiltern können Sie einen bestimmten Bereich von Zeitstempelwerten abrufen after, before oder after_or_equal. 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 folgendem Format zurück:

temp_versioned
{timestamp: '2023/01/05-01:00:00.000', value:56} {timestamp: '2023/01/05-00:00:00.000', value: 55}
{timestamp: '2023/01/04-23:00:00.000', value:56}

JSON abfragen

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

Beispielsweise können Sie den Wert für das JSON-Element abc aus dem letzte Zelle in der Spaltenfamilie session zusammen mit dem Zeilenschlüssel. Verwenden Sie dazu die Methode folgenden Abfrage aus.

  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 dieses Problem jedoch beheben, indem Sie die Elemente in ein Graviszeichen setzen. (`).

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

Wenn ein reserviertes SQL-Schlüsselwort als Kennung verwendet wird, kann es ähnlich ist entkommen.

  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:

Java

Für diese Funktion benötigen Sie java-bigtable-Version 2.41.0 oder höher. Für finden Sie unter executeQuery, Statement, und ResultSet in Javadoc.

  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
      }
    }
  }

Python asyncio

Für diese Funktion benötigen Sie python-bigtable-Version 2.26.0 oder höher.

  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.