Exemples de requêtes GoogleSQL pour Bigtable
Les exemples de cette page illustrent les modèles de requêtes SQL pour les requêtes Bigtable courantes et avancées. Vous pouvez exécuter des requêtes GoogleSQL dans l'éditeur de requête Bigtable Studio. Vous pouvez également exécuter des requêtes à l'aide de la bibliothèque cliente Bigtable pour Java.
Avant de lire cette page, consultez la présentation de GoogleSQL pour Bigtable.
Les exemples de cette page utilisent des ID et des valeurs semblables à ceux de la section Données pour les exemples.
Modèles de requêtes SQL Bigtable courants
Vous trouverez ci-dessous des exemples de requêtes courantes pour les données Bigtable. Pour voir des exemples de requêtes similaires qui appellent l'API Bigtable Data, consultez les sections Lire des exemples et Utiliser des filtres.
Récupérez la dernière version de toutes les colonnes pour une clé de ligne donnée.
SELECT * FROM myTable WHERE _key = 'r1'
Récupérez toutes les versions de toutes les colonnes pour une clé de ligne donnée.
SELECT * FROM myTable(with_history => TRUE) WHERE _key = 'r1'
Récupérez la dernière version d'une colonne spécifique à partir d'une famille de colonnes spécifique pour une clé de ligne donnée.
SELECT stats_summary['os_build'] AS os
FROM analytics
WHERE _key = 'phone#4c410523#20190501'
Récupérez les clés de ligne et la dernière version de plusieurs colonnes pour une plage de clés de ligne donnée.
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'
Récupérez toutes les versions de toutes les colonnes pour plusieurs plages de clés de ligne, jusqu'à 10 lignes.
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
Récupérez toutes les versions de toutes les colonnes pour plusieurs clés de ligne.
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key = 'phone#4c410523#20190501' OR _key = 'phone#4c410523#20190502'
Récupérez toutes les versions de toutes les colonnes pour plusieurs clés de ligne à l'aide d'une approche différente.
SELECT *
FROM analytics(with_history => TRUE)
WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')
Récupérez la dernière version de toutes les colonnes d'une famille de colonnes pour un préfixe de clé de ligne.
SELECT stats_summary
FROM analytics
WHERE _key LIKE 'phone#%'
Récupérez les clés de ligne et les trois dernières versions de toutes les colonnes d'une famille de colonnes pour toutes les lignes du tableau. Cette requête nécessite un balayage complet de la table. Elle n'est donc pas recommandée pour les modèles d'accès à faible latence et à haut débit.
SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)
Récupérez la dernière version de toutes les colonnes dont les clés de ligne correspondent à une expression régulière spécifiée. Cette requête nécessite une analyse complète de la table. Il est donc déconseillé de l'utiliser pour des modèles d'accès à faible latence et à haut débit, sauf si vous fournissez également un préfixe de clé de ligne ou un prédicat de plage de clé de ligne dans la clause WHERE
.
SELECT *
FROM myTable(with_history => TRUE)
WHERE REGEXP_CONTAINS(_key, '.*#20190501$')
Récupérez la dernière version de toutes les colonnes avec le préfixe de clé de ligne correspondant et une valeur de compteur supérieure à 123
. Vous n'avez pas besoin de caster pour cette comparaison, car les agrégations Bigtable sont numériques.
SELECT *
FROM myTable
WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123
Récupérez la dernière version de toutes les colonnes pour un préfixe de clé de ligne si le référent correspond à une valeur spécifique.
SELECT *
FROM analytics
WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'
Catégoriser une ligne donnée en fonction de la valeur d'une colonne donnée Cette requête est semblable à l'utilisation d'un filtre conditionnel de composition dans l'API Bigtable Data.
SELECT
*,
CASE cell_plan['data_plan']
WHEN '10gb' THEN 'passed-filter'
ELSE 'filtered-out'
END
AS label
FROM analytics
Récupérez la clé de ligne et les qualificatifs de colonne dans une famille de colonnes spécifique pour une plage de clés de ligne spécifiée. En SQL, les familles de colonnes sont représentées par le type de données de mappage, où chaque qualificatif et valeur de colonne est mappé en tant que paire clé-valeur. Cette requête SQL est semblable à l'utilisation d'un filtre de valeur de bande dans l'API Bigtable Data.
SELECT _key, MAP_KEYS(cell_plan) AS keys
FROM analytics
WHERE _key >= 'phone#4c410523#20190501' AND _key < 'phone#4c410523#201906201'
Modèles de requête SQL Bigtable avancés
Les exemples suivants illustrent des modèles plus avancés.
Avec la requête suivante, vous pouvez récupérer la clé de ligne et la valeur la plus récente de l'attribut JSON abc
dans la famille de colonnes session
. Pour en savoir plus, consultez la section Fonctions JSON
.
SELECT _key, JSON_VALUE(session['payload'], '$.abc') AS abc FROM analytics
Avec la requête suivante, vous pouvez récupérer la clé de ligne et calculer la durée moyenne de session à l'aide de la dernière valeur de deux cellules agrégées Bigtable, qui sont numériques, pour chaque ligne du tableau.
SELECT
_key AS userid,
session['total_minutes'] / session['count'] AS avg_session_length
FROM analytics
Avec la requête suivante, vous pouvez récupérer la dernière version de toutes les colonnes pour un préfixe de clé de ligne donné si la famille de colonnes session
contient referrer
, origin
ou server
comme qualificatif de colonne. Cette requête peut également être écrite sous la forme d'une série de comparaisons individuelles, comme session['referrer']
IS NOT NULL OR session['origin'] IS NOT NULL
. Toutefois, pour les requêtes impliquant un grand nombre de comparaisons, l'approche suivante est recommandée.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ANY(MAP_KEYS(session), ['referrer', 'origin', 'server'])
Avec la requête suivante, vous pouvez récupérer la dernière version de toutes les colonnes pour un préfixe de clé de ligne donné si la famille de colonnes session
contient referrer
, origin
et server
comme qualificatifs de colonne. Vous pouvez également écrire cette requête sous la forme d'une série de comparaisons individuelles, comme 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'])
Avec la requête suivante, vous pouvez récupérer la dernière version de toutes les colonnes pour un préfixe de clé de ligne donné si la famille de colonnes session
contient com.google.search
, com.google.maps
ou com.google.shopping
comme valeurs.
SELECT *
FROM analytics
WHERE
_key LIKE 'com.abc%'
AND ARRAY_INCLUDES_ANY(
MAP_VALUES(session),
['com.google.search', 'com.google.maps', 'com.google.shopping'])
Avec la requête suivante, vous pouvez récupérer la dernière version de toutes les colonnes si les paires clé-valeur de la famille de colonnes cell_plan
incluent à la fois data_plan:unlimited
et roaming:North America
.
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')])
Avec la requête suivante, vous pouvez récupérer les lectures row key
et temperature
pour les capteurs météorologiques lorsque la température a dépassé 70 degrés au cours des sept dernières mesures.
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)
Dans l'ordre de filtrage temporel, latest_n
vient en dernier. Par conséquent, une requête comme after => X,
before => y, latest_n => 3
renvoie les trois dernières valeurs qui répondent aux conditions "après" et "avant". Si votre cas d'utilisation nécessite que latest_n
ait la priorité, vous pouvez fournir latest_n
comme seul filtre temporel, puis appliquer le reste des filtres temporels à l'aide d'opérateurs de requête dans votre instruction SELECT
, comme illustré dans l'exemple. Pour en savoir plus, consultez la section Filtres temporels.
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)
Comme dans l'exemple précédent, vous pouvez appliquer un filtre temporel différent à chaque famille de colonnes de votre requête. Par exemple, la requête suivante renvoie les trois versions les plus récentes de la colonne street
et les deux versions les moins récentes de la colonne state
.
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)
Avec la requête suivante, vous pouvez récupérer toutes les versions de toutes les colonnes si les paires clé-valeur de la famille de colonnes d'adresse incluent à la fois city:Savannah
ou city:Nashville
à un moment donné.
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
Dans cet exemple particulier, le casting n'est pas obligatoire. Il peut donc également être écrit sous la forme plus courte suivante.
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
Étape suivante
- Consultez la documentation de référence GoogleSQL pour Bigtable.
- Utilisez le connecteur Bigtable Spark.