GoogleSQL for Bigtable-Abfrage Beispiele

Die Beispiele auf dieser Seite zeigen SQL-Abfragemuster für gängige 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.

Bevor Sie diese Seite lesen, sollten Sie den Leitfaden GoogleSQL for Bigtable-Übersicht.

Hinweis: Eine Version bezieht sich auf einen Zeitstempelwert 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.

Gängige Bigtable-SQL-Abfragemuster

Im Folgenden finden Sie Beispiele für häufige Abfragen von Bigtable-Daten. Um Beispiele für ähnliche Abfragen zu sehen, die die Bigtable-Daten aufrufen API finden Sie unter Beispiele ansehen und Verwenden Sie Filter.

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

  SELECT * FROM myTable WHERE _key = 'r1'

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

  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'

Die Zeilenschlüssel und die neueste Version mehrerer Spalten für eine bestimmte Zeilenschlüsselbereich

  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'

Alle Versionen aller Spalten für mehrere Zeilenschlüsselbereiche abrufen, 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'

Rufen Sie alle Versionen aller Spalten für mehrere Zeilenschlüssel mit einem anderen Ansatz.

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

Neueste Version aller Spalten in einer Spaltenfamilie für einen Zeilenschlüssel 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. Diese Abfrage erfordert einen vollständigen Tabellenscan und 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)

Rufen Sie die neueste Version aller Spalten mit Zeilenschlüsseln ab, die einem angegebenen regulärer Ausdruck. Diese Abfrage erfordert einen vollständigen Tabellenscan. empfohlen für Zugriffsmuster mit niedriger Latenz und hohem Durchsatz, es sei denn, Sie ein Zeilenschlüsselpräfix oder Zeilenschlüsselbereichsprädikat in der WHERE-Klausel angeben.

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

Rufen Sie die neueste Version aller Spalten mit dem übereinstimmenden Zeilenschlüsselpräfix ab und Zählerwert größer als 123 ist. Für diesen Vergleich ist kein Casting erforderlich, da Bigtable-Aggregationen numerisch sind.

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

Neueste Version aller Spalten für ein Zeilenschlüsselpräfix abrufen, wenn die Verweis-URL mit einem bestimmten Wert übereinstimmt.

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

Kategorisieren Sie eine bestimmte Zeile anhand des Werts einer bestimmten Spalte. Diese Abfrage ist ähnlich wie bei einer Bedingungs- Filter 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 angegebenen Zeilenschlüsselbereich ab. In SQL werden Spaltenfamilien durch den Datentyp „Map“ dargestellt, wobei jeder Spaltenqualifizierer und Wert als Schlüssel/Wert-Paar zugeordnet wird. Dieses Eine SQL-Abfrage ähnelt der Verwendung eines Streifenwerts Filter 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'

Erweiterte Bigtable-SQL-Abfragemuster

Die folgenden Beispiele zeigen erweiterte Muster.

Mit der folgenden Abfrage können Sie den Zeilenschlüssel und den neuesten Wert das JSON-Attribut abc in der Spaltenfamilie session. Weitere Informationen Siehe 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 Sitzungsdauer anhand des letzten Werts von zwei numerischen Bigtable-Aggregatzellen für jede Zeile in der Tabelle berechnen.

  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, wenn die Spaltenfamilie session referrer enthält, origin oder server als Spaltenqualifizierer. Alternativ kann diese Abfrage auch als Reihe einzelner Vergleiche wie session['referrer'] IS NOT NULL OR session['origin'] IS NOT NULL geschrieben werden. Für Abfragen, bei denen wird 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 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 neueste Version aller Spalten für ein bestimmtes Zeilenschlüsselpräfix, wenn die Spaltenfamilie session Folgendes enthält: com.google.search, com.google.maps oder com.google.shopping als Werte.

  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 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 zeitlicher Filterreihenfolge steht latest_n an letzter Stelle, sodass eine Abfrage wie after => X, before => y, latest_n => 3 die letzten drei Werte zurückgibt, die die „Nachher“- und „Vorher“-Bedingungen. Wenn in Ihrem Anwendungsfall latest_n Vorrang haben, können Sie latest_n als einzigen zeitlichen Filter angeben. und wenden dann die übrigen zeitlichen Filter mithilfe von Abfrageoperatoren in Ihrem SELECT wie im Beispiel gezeigt. Weitere Informationen finden Sie unter Zeitbezogene Filter.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04 23:51:00+00'))
      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 zwei neuesten 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 „Adresse“ 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 Beispiel ist kein Casting erforderlich. Daher kann die Formel 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

• Referenzdokumentation zu GoogleSQL for Bigtable
• Verwenden Sie den Bigtable Spark-Connector.