GoogleSQL für Bigtable – Abfragebeispiele

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

Bevor Sie diese Seite lesen, sollten Sie sich die Übersicht über GoogleSQL für Bigtable ansehen.

In den Beispielen auf dieser Seite werden IDs und Werte verwendet, die denen unter Beispieldaten ähneln.

Häufige Bigtable-SQL-Abfragemuster

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 für Lesezugriff 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'

Alle Versionen aller Spalten für einen bestimmten Zeilenschlüssel abrufen.

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

Ruft die aktuelle 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'

Rufen Sie 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

Alle Versionen aller Spalten für mehrere Zeilenschlüssel abrufen.

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

Alle Versionen aller Spalten für mehrere Zeilenschlüssel abrufen – mit einem anderen Ansatz.

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

Die neueste Version aller Spalten in einer Spaltenfamilie für ein Präfix des Zeilenschlüssels abrufen.

  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 ist daher nicht für Zugriffsmuster mit niedriger Latenz und hohem Durchsatz geeignet.

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

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

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

Die neueste Version aller Spalten mit dem übereinstimmenden Zeilenschlüsselpräfix und dem Zählerwert, der größer als 123 ist, abrufen. Für diesen Vergleich ist keine Typumwandlung erforderlich, da Bigtable-Zusammenfassungen numerisch sind.

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

Die neueste Version aller Spalten für ein Row-Key-Präfix abrufen, wenn der Referrer einem bestimmten Wert entspricht.

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

Eine bestimmte Zeile basierend auf dem Wert einer bestimmten Spalte kategorisieren. Diese Anfrage ähnelt 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

Rufen Sie den Zeilenschlüssel und die Spaltenqualifizierer in einer bestimmten Spaltenfamilie für einen angegebenen 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. In der folgenden Abfrage wird UNPACK verwendet, um die Leistung bestimmter Kampagnen zu ermitteln. Dazu werden die Klicks der letzten Stunde pro Minute zusammengefasst.

  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 Bigtable-SQL-Abfragemuster

Die folgenden Beispiele zeigen komplexere Muster.

Mit der folgenden Abfrage können Sie den Zeilenschlüssel und den letzten 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 anhand des letzten Werts von zwei Bigtable-Aggregatzellen berechnen, die numerisch sind, für jede Zeile in der Tabelle.

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

Mit der folgenden Abfrage können Sie die aktuelle 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 wie session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL geschrieben werden. Bei Anfragen 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 aktuelle Version aller Spalten für ein bestimmtes Präfix des Zeilenschlüssels abrufen, wenn die Spaltenfamilie session die Spaltenqualifizierer referrer, origin und server enthält. Alternativ kann diese Abfrage als Reihe einzelner Vergleiche wie session['referrer'] IS NOT NULL AND session ['origin'] IS NOT NULL geschrieben werden.

  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 aktuelle Version aller Spalten für ein bestimmtes Zeilenschlüsselpräfix abrufen, wenn die Spaltenfamilie session die Werte com.google.search, com.google.maps oder com.google.shopping 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 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 abrufen, wenn die Temperatur bei den letzten sieben Messungen 70 Grad überschritten hat.

  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)

In der Reihenfolge der temporären 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 in Ihrem Anwendungsfall latest_n Vorrang haben muss, können Sie latest_n als einzigen Zeitfilter angeben und die restlichen Zeitfilter dann mit Abfrageoperatoren in Ihrer 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 Zeitfilter 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 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

Nächste Schritte