GoogleSQL-Abfragebeispiele für Bigtable

Hinweis: Diese Seite enthält Funktionen, die nur in der Enterprise Plus-Version verfügbar sind, z. B. Data Boost für SQL. Weitere Informationen finden Sie in der Übersicht über die Versionen.

Die Beispiele auf dieser Seite zeigen SQL-Abfragemuster für häufige und erweiterte Bigtable-Abfragen. Sie können GoogleSQL-Abfragen im Abfrageeditor von Bigtable Studio ausführen. Sie können Abfragen auch mit der Bigtable-Clientbibliothek für Java ausführen.

Lesen Sie vor dieser Seite die Übersicht zu GoogleSQL für Bigtable.

Hinweis: Eine Version bezieht sich auf einen mit einem Zeitstempel versehenen Wert für eine bestimmte Spalte und Zeile. Jede Version wird in einer Zelle gespeichert. Weitere Informationen finden Sie unter Bigtable-Speichermodell.

In den Beispielen auf dieser Seite werden IDs und Werte verwendet, die denen in Daten für Beispiele ähneln.

Häufige SQL-Abfragemuster für Bigtable

Im Folgenden finden Sie Beispiele für häufige Abfragen für Bigtable-Daten. Beispiele für ähnliche Abfragen, die die Bigtable Data API aufrufen, finden Sie unter Beispiele lesen und Filter verwenden. Beispiele für Abfragen für strukturierte Zeilenschlüssel finden Sie unter Abfragen für strukturierte Zeilenschlüssel.

Ruft die neueste Version aller Spalten für einen bestimmten Zeilenschlüssel ab.

  SELECT * FROM myTable WHERE _key = 'r1'

Ruft alle Versionen aller Spalten für einen bestimmten Zeilenschlüssel ab.

  SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'

Ruft die neueste Version einer bestimmten Spalte aus einer bestimmten Spaltenfamilie für einen bestimmten Zeilenschlüssel ab.

  SELECT stats_summary['os_build'] AS os
  FROM analytics
  WHERE _key = 'phone#4c410523#20190501'

Ruft die Zeilenschlüssel und die neueste Version mehrerer Spalten für einen bestimmten Zeilenschlüsselbereich ab.

  SELECT
    _key,
    stats_summary['os_build'] AS os,
    stats_summary['user_agent'] AS agent
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

Ruft alle Versionen aller Spalten für mehrere Zeilenschlüsselbereiche ab, bis zu 10 Zeilen.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE
    (_key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201')
    OR (_key >= 'phone#5c10102#20190501' AND _key < 'phone#5c10102#20190601')
  LIMIT 10

Ruft alle Versionen aller Spalten für mehrere Zeilenschlüssel ab.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'

Ruft alle Versionen aller Spalten für mehrere Zeilenschlüssel mit einem anderen Ansatz ab.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

Ruft die neueste Version aller Spalten in einer Spaltenfamilie für ein Zeilenschlüsselpräfix ab.

  SELECT stats_summary
  FROM analytics
  WHERE _key LIKE 'phone#%'

Ruft die Zeilenschlüssel und die drei neuesten Versionen aller Spalten in einer Spaltenfamilie für alle Zeilen in der Tabelle ab. Für diese Abfrage ist ein vollständiger Tabellenscan erforderlich. Sie wird daher nicht für Zugriffsmuster mit niedriger Latenz und hohem Durchsatz empfohlen.

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

Ruft die neueste Version aller Spalten mit Zeilenschlüsseln ab, die einem bestimmten regulären Ausdruck entsprechen. Für diese Abfrage ist ein vollständiger Tabellenscan erforderlich. Sie wird daher nicht für Zugriffsmuster mit niedriger Latenz und hohem Durchsatz empfohlen, es sei denn, Sie geben in der WHERE-Klausel auch ein Zeilenschlüsselpräfix oder ein Prädikat für den Zeilenschlüsselbereich an.

  SELECT *
  FROM myTable(with_history => TRUE)
  WHERE REGEXP_CONTAINS(_key, '.*#20190501$')

Ruft die neueste Version aller Spalten mit dem übereinstimmenden Zeilenschlüsselpräfix und einem Zählerwert von mehr als 123 ab. Für diesen Vergleich ist keine Typumwandlung erforderlich, da Bigtable Aggregate numerisch sind.

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

Ruft die neueste Version aller Spalten für ein Zeilenschlüsselpräfix ab, wenn der Referrer einem bestimmten Wert entspricht.

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

Kategorisiert eine bestimmte Zeile basierend auf dem Wert einer bestimmten Spalte. Diese Abfrage ist ähnlich der Verwendung eines zusammengesetzten bedingten Filters in der Bigtable Data API.

  SELECT
    *,
    CASE cell_plan['data_plan']
      WHEN '10gb' THEN 'passed-filter'
      ELSE 'filtered-out'
      END
      AS label
  FROM analytics

Ruft den Zeilenschlüssel und die Spaltenqualifizierer in einer bestimmten Spaltenfamilie für einen bestimmten Zeilenschlüsselbereich ab. In SQL werden Spaltenfamilien durch den Map-Datentyp dargestellt, wobei jeder Spaltenqualifizierer und -wert als Schlüssel-Wert-Paar zugeordnet wird. Diese SQL-Abfrage ähnelt der Verwendung eines Filters zum Entfernen von Werten in der Bigtable Data API.

  SELECT _key, MAP_KEYS(cell_plan) AS keys
  FROM analytics
  WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'

Mit der Funktion UNPACK können Sie Bigtable-Daten in ein tabellarisches Zeitreihenformat umwandeln, was für die Zeitreihenanalyse nützlich ist. Nehmen wir an, Sie haben eine Spalte clicks in einer Spaltenfamilie engagement. Die folgende Abfrage verwendet UNPACK, um die Leistung bestimmter Kampagnen zu ermitteln, indem die Klicks der letzten Stunde pro Minute aggregiert werden.

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

Erweiterte SQL-Abfragemuster für Bigtable

Die folgenden Beispiele zeigen erweiterte Muster.

Mit der folgenden Abfrage können Sie den Zeilenschlüssel und den neuesten Wert des JSON-Attributs abc in der Spaltenfamilie session abrufen. Weitere Informationen finden Sie unter JSON Funktionen.

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

Mit der folgenden Abfrage können Sie den Zeilenschlüssel abrufen und die durchschnittliche Sitzungslänge berechnen. Dazu wird der neueste Wert von zwei numerischen Bigtable-Aggregatzellen für jede Zeile in der Tabelle verwendet.

  SELECT
    _key AS userid,
    session['total_minutes'] / session['count'] AS avg_session_length
  FROM analytics

Mit der folgenden Abfrage können Sie die neueste Version aller Spalten für ein bestimmtes Zeilenschlüsselpräfix abrufen, wenn die Spaltenfamilie session referrer, origin oder server als Spaltenqualifizierer enthält. Alternativ kann diese Abfrage auch als Reihe einzelner Vergleiche geschrieben werden, z. B. session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL. Für Abfragen mit einer großen Anzahl von Vergleichen wird jedoch der folgende Ansatz empfohlen.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])

Mit der folgenden Abfrage können Sie die neueste Version aller Spalten für ein bestimmtes Zeilenschlüsselpräfix abrufen, wenn die Spaltenfamilie session referrer, origin und server als Spaltenqualifizierer enthält. Alternativ kann diese Abfrage auch als Reihe einzelner Vergleiche geschrieben werden, z. B. session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ALL(MAP_KEYS(session), ['referrer', 'origin', 'server'])

Mit der folgenden Abfrage können Sie die neueste Version aller Spalten für ein bestimmtes Zeilenschlüsselpräfix abrufen, wenn die Spaltenfamilie session com.google.search, com.google.maps oder com.google.shopping als Werte enthält.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

Mit der folgenden Abfrage können Sie die neueste Version aller Spalten abrufen, wenn die Schlüssel-Wert-Paare in der Spaltenfamilie cell_plan sowohl data_plan:unlimited als auch roaming:North America enthalten.

  SELECT *
  FROM analytics
  WHERE
    ARRAY_INCLUDES_ALL(
      CAST(
        MAP_ENTRIES(cell_plan)
        AS ARRAY<STRUCT<key STRING, value STRING>>),
      [('data_plan', 'unlimited'), ('roaming', 'North America')])

Mit der folgenden Abfrage können Sie die row key- und temperature-Messwerte für Wettersensoren für Fälle abrufen, in denen die Temperatur bei den letzten sieben Messungen über 70 Grad lag.

  SELECT
    _key AS sensorid,
    ARRAY_FILTER(
      CAST(
        sensor['temperature']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> CAST(e.value AS FLOAT32) > 70) AS high_temperature
  FROM weather(with_history => TRUE, latest_n => 7)

Bei der zeitlichen Filterung kommt latest_n zuletzt. Eine Abfrage wie after => X, before => y, latest_n => 3 gibt also die letzten drei Werte zurück, die die Bedingungen „after“ und „before“ erfüllen. Wenn latest_n Vorrang haben soll, können Sie latest_n als einzigen zeitlichen Filter angeben und die restlichen zeitlichen Filter dann mit Abfrageoperatoren in der SELECT-Anweisung anwenden, wie im Beispiel gezeigt. Weitere Informationen finden Sie unter Zeitliche Filter.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      AS street_address
  FROM locations(with_history => TRUE, latest_n => 3)

Ähnlich wie im vorherigen Beispiel können Sie auf jede Spaltenfamilie in Ihrer Abfrage einen anderen zeitlichen Filter anwenden. Die folgende Abfrage gibt beispielsweise die drei neuesten Versionen der Spalte street und die beiden ältesten Versionen der Spalte state zurück.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      (e, i) -> i <= 2)
      AS street_address,
    ARRAY_FILTER(
      ARRAY_REVERSE(
        CAST(
          address['state']
          AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>)),
      (e, i) -> i <= 1)
      AS state
  FROM locations(with_history => TRUE)

Mit der folgenden Abfrage können Sie alle Versionen aller Spalten abrufen, wenn die Schlüssel-Wert-Paare in der Spaltenfamilie „address“ zu einem beliebigen Zeitpunkt sowohl city:Savannah als auch city:Nashville enthalten.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        CAST(
          MAP_ENTRIES(address)
          AS ARRAY<
            STRUCT<
              key STRING,
              value ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>>>),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

In diesem speziellen Beispiel ist keine Typumwandlung erforderlich. Die Abfrage kann also auch in der folgenden kürzeren Form geschrieben werden.

  SELECT *
  FROM locations(with_history => TRUE)
  WHERE
    ARRAY_LENGTH(
      ARRAY_FILTER(
        MAP_ENTRIES(address),
        e ->
          e.key = 'city'
          AND ARRAY_INCLUDES_ANY(
            ARRAY_TRANSFORM(e.value, k -> k.value), ['Savannah', 'Nashville'])))
    > 0

SQL-Analyse mit hohem Durchsatz mit Data Boost

In der Enterprise Plus-Version können Sie für analytische Arbeitslasten mit hohem Durchsatz Data Boost verwenden, um Abfragen auszuführen, die große Datenmengen scannen, ohne die Leistung Ihrer Produktionscluster zu beeinträchtigen. Diese Abfragen eignen sich optimal für Echtzeitanalysen und zum Generieren von Erkenntnissen aus Verlaufs- oder Zeitreihendaten. Zum Ausführen dieser Abfragen müssen Sie ein Anwendungsprofil verwenden, das für Data Boost konfiguriert ist.

Die folgenden Beispiele zeigen SQL-Muster für Analysen mit hohem Durchsatz mit serverlosem Computing.

Verlaufsdaten von Zeitreihen analysieren

Mit Data Boost können Sie die Zeilenschlüssel und alle Versionen der Messwerte für alle Zeilen in einer Tabelle der letzten 24 Stunden abrufen. Die folgende Abfrage führt einen vollständigen Tabellenscan durch, der von einem isolierten serverlosen Compute-Dienst verarbeitet wird.

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

Große Datasets aggregieren

Die folgende Abfrage zeigt, wie Sie die Gesamtzahl der Ereignisse für jedes Gerät in einem bestimmten Zeitraum in der gesamten Tabelle berechnen.

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

Scans großer Bereiche

Die folgende Abfrage zeigt, wie Sie alle Spalten für einen großen Bereich von Zeilenschlüsseln abrufen. Durch die Verwendung von Data Boost wird sichergestellt, dass diese Anfrage mit hohem Durchsatz keine CPU-Ressourcen auf Ihren Clusterknoten verbraucht.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

Nächste Schritte

• Referenzdokumentation zu GoogleSQL für Bigtable
• Bigtable-Spark-Connector verwenden
• Zeilenschlüsselschemas verwalten.