Beispiele für GoogleSQL für Bigtable-Abfragen
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.
Lesen Sie vor dem Lesen dieser Seite den Artikel GoogleSQL für Bigtable – Übersicht.
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 für Bigtable-Daten. Beispiele für ähnliche Abfragen, die die Bigtable Data API aufrufen, finden Sie unter Beispiele lesen und Filter verwenden.
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 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'
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'
Alle Versionen aller Spalten für mehrere Zeilenschlüssel mit einem anderen Ansatz abrufen
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')
Ruft die neueste Version aller Spalten innerhalb einer Spaltenfamilie für ein Präfix des Zeilenschlüssels ab.
SELECT stats_summary
FROM analytics
WHERE _key LIKE 'phone#%'
Ruft die Zeilenschlüssel und die drei neuesten Versionen aller Spalten innerhalb 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)
Ruft die neueste Version aller Spalten mit Zeilenschlüsseln ab, die mit einem bestimmten regulären Ausdruck übereinstimmen. Für diese Abfrage ist ein vollständiger Tabellenscan erforderlich. Sie wird daher nicht für Zugriffsmuster mit niedriger Latenz und hoher Durchsatzleistung empfohlen, es sei denn, Sie geben in der WHERE-Klausel auch ein Zeilenschlüsselpräfix oder ein Zeilenschlüsselbereichsprädikat an.
SELECT *
FROM myTable(with_history => TRUE)
WHERE REGEXP_CONTAINS(_key, '.*#20190501$')
Ruft die neueste Version aller Spalten mit dem übereinstimmenden Präfix des Zeilenschlüssels und einem Zählerwert größer als 123 ab. Für diesen Vergleich ist kein Casting erforderlich, da Bigtable-Aggregationen numerisch sind.
SELECT *
FROM myTable
WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123
Die neueste Version aller Spalten für ein Zeilenschlüsselpräfix abrufen, wenn der Verweis mit einem bestimmten Wert übereinstimmt.
SELECT *
FROM analytics
WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'
Eine bestimmte Zeile basierend auf dem Wert einer bestimmten Spalte kategorisieren. Diese Abfrage ä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
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. Diese SQL-Abfrage ähnelt der Verwendung eines Strip-Wert-Filters 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 komplexere 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 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 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 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 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 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 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 der chronologischen Filterreihenfolge steht latest_n an letzter Stelle. Eine Abfrage wie after => X,
before => y, latest_n => 3 gibt also die letzten drei Werte zurück, die die Bedingungen nach und vor erfüllen. Wenn für Ihren Anwendungsfall latest_n Vorrang haben soll, können Sie latest_n als einzigen zeitlichen Filter angeben und dann die restlichen zeitlichen Filter 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-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