Présentation de GoogleSQL pour Bigtable
Vous pouvez utiliser des instructions GoogleSQL pour interroger vos données Bigtable. GoogleSQL est un langage de requête structuré (SQL) conforme à la norme ANSI, qui est également implémenté pour d'autres services Google Cloudtels que BigQuery et Spanner.
Ce document présente GoogleSQL pour Bigtable. Il fournit des exemples de requêtes SQL que vous pouvez utiliser avec Bigtable et décrit leur lien avec un schéma de table Bigtable. Avant de lire ce document, vous devez connaître le modèle de stockage Bigtable et les concepts de conception de schéma.
Vous pouvez créer et exécuter des requêtes dans Bigtable Studio dans la console Google Cloud , ou les exécuter par programmation à l'aide de la bibliothèque cliente Bigtable pour Java, Python ou Go. Pour en savoir plus, consultez Utiliser SQL avec une bibliothèque cliente Bigtable.
Les requêtes SQL sont traitées par les nœuds du cluster de la même manière que les requêtes de données NoSQL. Par conséquent, les mêmes bonnes pratiques s'appliquent lorsque vous créez des requêtes SQL à exécuter sur vos données Bigtable, comme éviter les analyses complètes de tables ou les filtres complexes. Pour en savoir plus, consultez Lectures et performances.
Vous ne pouvez pas utiliser Data Boost avec GoogleSQL pour Bigtable.
Cas d'utilisation
GoogleSQL pour Bigtable est idéal pour le développement d'applications à faible latence. De plus, l'exécution de requêtes SQL dans la consoleGoogle Cloud peut être utile pour obtenir rapidement une représentation visuelle du schéma d'une table, vérifier que certaines données ont été écrites ou déboguer d'éventuels problèmes de données.
La version actuelle de GoogleSQL pour Bigtable n'est pas compatible avec certains éléments SQL courants, y compris, mais sans s'y limiter :
- Instructions LMD (langage de manipulation de données) autres que
SELECT
, telles queINSERT
,UPDATE
ouDELETE
- Instructions LDD (langage de définition de données) telles que
CREATE
,ALTER
ouDROP
- Instructions de contrôle des accès aux données
- Syntaxe des sous-requêtes,
JOIN
,UNION
,UNNEST
etCTEs
Pour en savoir plus, y compris sur les fonctions, les opérateurs, les types de données et la syntaxe de requête compatibles, consultez la documentation de référence sur GoogleSQL pour Bigtable.
Vues
Vous pouvez utiliser GoogleSQL pour Bigtable afin de créer les ressources suivantes :
- Vue matérialisée continue : résultat précalculé d'une requête SQL exécutée en continu, y compris des données agrégées, qui se synchronise avec sa table source grâce à des mises à jour incrémentielles. Cette fonctionnalité est disponible en version bêta.
- Vue logique : requête nommée et enregistrée qui peut être interrogée comme une table.
Pour comparer ces types de vues ainsi que les vues autorisées, consultez Tables et vues.
Concepts clés
Cette section présente les concepts clés à connaître lorsque vous utilisez GoogleSQL pour interroger vos données Bigtable.
Familles de colonnes dans les réponses SQL
Dans Bigtable, une table contient une ou plusieurs familles de colonnes, qui sont utilisées pour regrouper les colonnes. Lorsque vous interrogez une table Bigtable avec GoogleSQL, le schéma de la table se compose des éléments suivants :
- Une colonne spéciale nommée
_key
qui correspond aux clés de ligne dans la table interrogée - Une seule colonne pour chaque famille de colonnes Bigtable de la table, qui contient les données de la famille de colonnes dans cette ligne
Type de données Map
GoogleSQL pour Bigtable inclut le type de données MAP<key, value>
, qui est conçu spécifiquement pour les familles de colonnes.
Par défaut, chaque ligne d'une colonne de type "map" contient des paires clé/valeur, où une clé correspond au qualificatif de colonne Bigtable dans la table interrogée et la valeur correspond à la valeur la plus récente pour cette colonne.
Voici un exemple de requête SQL qui renvoie un tableau avec la valeur de clé de ligne et la dernière valeur du qualificatif à partir d'un mappage nommé columnFamily
.
SELECT _key, columnFamily['qualifier'] FROM myTable
Si votre schéma Bigtable implique de stocker plusieurs cellules ou versions de données dans des colonnes, vous pouvez ajouter un filtre temporel, tel que with_history
, à votre instruction SQL.
Dans ce cas, les cartes représentant les familles de colonnes sont imbriquées et renvoyées sous forme de tableau. Dans le tableau, chaque valeur est elle-même une carte composée d'un code temporel comme clé et de données de cellule comme valeur. Il a le format suivant : MAP<key, ARRAY<STRUCT<timestamp, value>>>
.
L'exemple suivant renvoie toutes les cellules de la famille de colonnes "info" pour une seule ligne.
SELECT _key, info FROM users(with_history => TRUE) WHERE _key = 'user_123';
La carte renvoyée ressemble à ceci : Dans la table interrogée, info
est la famille de colonnes, user_123
est la clé de ligne, et city
et state
sont les qualificatifs de colonne. Chaque paire valeur-code temporel (STRUCT
) d'un tableau représente les cellules de ces colonnes dans cette ligne. Elles sont triées par ordre décroissant de code temporel.
/*----------+------------------------------------------------------------------+
| _key | info |
+----------+------------------------------------------------------------------+
| user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
+----------+------------------------------------------------------------------*/
Tables creuses
Le modèle de données flexible est l'une des principales caractéristiques de Bigtable. Dans une table Bigtable, si une colonne n'est pas utilisée dans une ligne, aucune donnée n'est stockée pour la colonne. Une ligne peut comporter une colonne, tandis que la ligne suivante peut en comporter 100. En revanche, dans une table de base de données relationnelle, toutes les lignes contiennent toutes les colonnes, et une valeur NULL
est généralement stockée dans la colonne d'une ligne qui ne contient aucune donnée pour cette colonne.
Toutefois, lorsque vous interrogez une table Bigtable avec GoogleSQL, une colonne inutilisée est représentée par un mappage vide et renvoyée en tant que valeur NULL
. Ces valeurs NULL
peuvent être utilisées comme prédicats de requête. Par exemple, un prédicat tel que WHERE family['column1'] IS NOT NULL
peut être utilisé pour renvoyer une ligne uniquement si column1
est utilisé dans la ligne.
Octets
Lorsque vous fournissez une chaîne, GoogleSQL effectue par défaut un cast implicite des valeurs STRING
en valeurs BYTES
. Cela signifie, par exemple, que vous pouvez fournir la chaîne 'qualifier'
plutôt que la séquence d'octets b'qualifier'
.
Comme Bigtable traite par défaut toutes les données en tant qu'octets, la plupart des colonnes Bigtable ne contiennent pas d'informations sur le type. Toutefois, avec GoogleSQL, vous pouvez définir un schéma au moment de la lecture à l'aide de la fonction CAST
. Pour en savoir plus sur le casting, consultez Fonctions de conversion.
Filtres temporels
Le tableau suivant liste les arguments que vous pouvez utiliser lorsque vous accédez aux éléments temporels d'un tableau. Les arguments sont listés dans l'ordre dans lequel ils sont filtrés. Par exemple, with_history
est appliqué avant latest_n
. Vous devez fournir un code temporel valide.
Argument | Description |
---|---|
as_of |
Timestamp. Renvoie les dernières valeurs dont les codes temporels sont inférieurs ou égaux au code temporel fourni. |
with_history |
Booléen : Contrôle si la dernière valeur doit être renvoyée sous forme de valeur scalaire ou de valeur(s) avec code temporel sous forme de STRUCT . |
after_or_equal |
Timestamp. Valeurs dont les codes temporels sont postérieurs à l'entrée (inclus). Nécessite with_history => TRUE |
before |
Timestamp. Valeurs avec des codes temporels antérieurs à l'entrée, exclus. Nécessite with_history => TRUE |
latest_n |
Nombre entier. Nombre de valeurs avec code temporel à renvoyer par qualificatif de colonne (clé de carte). La valeur doit être supérieure ou égale à 1. Nécessite with_history => TRUE . |
Pour obtenir d'autres exemples, consultez Schémas de requêtes avancés.
Requêtes fondamentales
Cette section décrit et présente des exemples de requêtes SQL Bigtable de base et de leur fonctionnement. Pour obtenir d'autres exemples de requêtes, consultez Exemples de modèles de requêtes GoogleSQL pour Bigtable.
Récupérer la dernière version
Bien que Bigtable vous permette de stocker plusieurs versions de données dans chaque colonne, GoogleSQL pour Bigtable renvoie par défaut la dernière version (la cellule la plus récente) des données pour chaque ligne.
Prenons l'exemple de l'ensemble de données suivant, qui montre que user1
a déménagé deux fois dans l'État de New York et une fois dans la ville de Brooklyn. Dans cet exemple, address
est la famille de colonnes, et les qualificatifs de colonnes sont street
, city
et state
. Les cellules d'une colonne sont séparées par des lignes vides.
adresse | |||
---|---|---|---|
_key | street | city | state |
utilisateur1 | 2023/01/10-14:10:01.000: '113 Xyz Street' 2021/12/20-09:44:31.010: '76 Xyz Street' 2005/03/01-11:12:15.112: '123 Abc Street' |
2021/12/20-09:44:31.010: 'Brooklyn' 2005/03/01-11:12:15.112: 'Queens' |
2005/03/01-11:12:15.112: 'NY' |
Pour récupérer la dernière version de chaque colonne pour user1
, vous pouvez utiliser une instruction SELECT
comme celle ci-dessous.
SELECT street, city FROM myTable WHERE _key = 'user1'
La réponse contient l'adresse actuelle, qui est une combinaison des valeurs les plus récentes de la rue, de la ville et de l'État (écrites à des moments différents) imprimées au format JSON. Les codes temporels ne sont pas inclus dans la réponse.
_key | adresse | ||
---|---|---|---|
utilisateur1 | {street:'113 Xyz Street', city:'Brooklyn', state: :'NY'} |
Récupérer toutes les versions
Pour récupérer d'anciennes versions (cellules) des données, utilisez l'indicateur with_history
. Vous pouvez également créer des alias pour les colonnes et les expressions, comme illustré dans l'exemple suivant.
SELECT _key, columnFamily['qualifier'] AS col1
FROM myTable(with_history => TRUE)
Pour mieux comprendre les événements qui ont conduit à l'état actuel d'une ligne, vous pouvez récupérer les codes temporels de chaque valeur en récupérant l'historique complet. Par exemple, pour savoir quand user1
a déménagé à son adresse actuelle et d'où il venait, vous pouvez exécuter la requête suivante :
SELECT
address['street'][0].value AS moved_to,
address['street'][1].value AS moved_from,
FORMAT_TIMESTAMP('%Y-%m-%d', address['street'][0].timestamp) AS moved_on,
FROM myTable(with_history => TRUE)
WHERE _key = 'user1'
Lorsque vous utilisez l'indicateur with_history
dans votre requête SQL, la réponse est renvoyée sous la forme MAP<key, ARRAY<STRUCT<timestamp, value>>>
. Chaque élément du tableau est une valeur horodatée pour la ligne, la famille de colonnes et la colonne spécifiées.
Les codes temporels sont classés par ordre chronologique inverse. Les données les plus récentes sont donc toujours le premier élément renvoyé.
La réponse à la requête est la suivante.
moved_to | moved_from | moved_on | ||
---|---|---|---|---|
113 rue Xyz | 76 Xyz Street | 10/01/2023 |
Vous pouvez également récupérer le nombre de versions dans chaque ligne à l'aide de fonctions de tableau, comme illustré dans la requête suivante :
SELECT _key, ARRAY_LENGTH(MAP_ENTRIES(address)) AS version_count
FROM myTable(with_history => TRUE)
Récupérer des données à partir d'un moment précis
L'utilisation d'un filtre as_of
vous permet de récupérer l'état d'une ligne à un moment donné. Par exemple, si vous souhaitez connaître l'adresse de user
au 10 janvier 2022 à 13h14, vous pouvez exécuter la requête suivante.
SELECT address
FROM myTable(as_of => TIMESTAMP('2022-01-10T13:14:00.234Z'))
WHERE _key = 'user1'
Le résultat indique ce qui aurait été la dernière adresse connue le 10 janvier 2022 à 13h14, c'est-à-dire la combinaison de la rue et de la ville de la mise à jour du 20/12/2021 à 09:44:31.010 et de l'état du 01/03/2005 à 11:12:15.112.
adresse | ||
---|---|---|
{street:'76 Xyz Street', city:'Brooklyn', state: :'NY'} |
Vous pouvez également obtenir le même résultat à l'aide d'horodatages Unix.
SELECT address
FROM myTable(as_of => TIMESTAMP_FROM_UNIX_MILLIS(1641820440000))
WHERE _key = 'user1'
Prenons l'exemple de l'ensemble de données suivant, qui indique si les détecteurs de fumée et de monoxyde de carbone sont activés ou désactivés. La famille de colonnes est alarmType
et les qualificatifs de colonnes sont smoke
et carbonMonoxide
. Les cellules de chaque colonne sont séparées par des lignes vides.
alarmType |
||
---|---|---|
_key | Fumée | carbonMonoxide |
building1#section1 | 2023/04/01-09:10:15.000: 'off' 2023/04/01-08:41:40.000: 'on' 2020/07/03-06:25:31.000: 'off' 2020/07/03-06:02:04.000: 'on' |
2023/04/01-09:22:08.000: 'off' 2023/04/01-08:53:12.000: 'on' |
building1#section2 | 2021/03/11-07:15:04.000: 'off' 2021/03/11-07:00:25.000: 'on' |
Vous pouvez trouver les sections de building1
où un détecteur de fumée était activé à 9h le 1er avril 2023 et l'état du détecteur de monoxyde de carbone à ce moment-là à l'aide de la requête suivante.
SELECT _key AS location, alarmType['carbonMonoxide'] AS CO_sensor
FROM alarms(as_of => TIMESTAMP('2023-04-01T09:00:00.000Z'))
WHERE _key LIKE 'building1%' and alarmType['smoke'] = 'on'
Le résultat est le suivant :
position | CO_sensor |
---|---|
building1#section1 | 'on' |
Interroger les données des séries temporelles
Un cas d'utilisation courant de Bigtable est le stockage de données de séries temporelles.
Prenons l'exemple de l'ensemble de données suivant, qui affiche les relevés de température et d'humidité des capteurs météo. L'ID de la famille de colonnes est metrics
et les qualificatifs de colonnes sont temperature
et humidity
. Les cellules d'une colonne sont séparées par des lignes vides, et chaque cellule représente une lecture de capteur horodatée.
métriques |
||
---|---|---|
_key | température | humidity |
sensorA#20230105 | 2023/01/05-02:00:00.000: 54 2023/01/05-01:00:00.000: 56 2023/01/05-00:00:00.000: 55 |
2023/01/05-02:00:00.000: 0.89 2023/01/05-01:00:00.000: 0.9 2023/01/05-00:00:00.000: 0.91 |
sensorA#20230104 | 2023/01/04-23:00:00.000: 56 2023/01/04-22:00:00.000: 57 |
2023/01/04-23:00:00.000: 0.9 2023/01/04-22:00:00.000: 0.91 |
Vous pouvez récupérer une plage spécifique de valeurs d'horodatage à l'aide des filtres temporels after
, before
ou after_or_equal
. L'exemple suivant utilise after
:
SELECT metrics['temperature'] AS temp_versioned
FROM
sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
WHERE _key LIKE 'sensorA%'
La requête renvoie les données au format suivant :
temp_versioned |
---|
[{timestamp: '2023/01/05-01:00:00.000', value:56} {timestamp: '2023/01/05-00:00:00.000', value: 55}] |
[{timestamp: '2023/01/04-23:00:00.000', value:56}] |
Données de séries temporelles UNPACK
Lorsque vous analysez des données de séries temporelles, il est souvent préférable de travailler avec les données au format tabulaire. La fonction UNPACK
de Bigtable peut vous aider.
UNPACK
est une fonction de valeur de table (TVF) Bigtable qui renvoie une table de sortie entière au lieu d'une seule valeur scalaire. Elle apparaît dans la clause FROM
comme une sous-requête de table. La TVF UNPACK
développe chaque valeur horodatée en plusieurs lignes (une par code temporel) et déplace le code temporel dans la colonne _timestamp
.
L'entrée de UNPACK
est une sous-requête où with_history => true
.
La sortie est un tableau développé avec une colonne _timestamp
dans chaque ligne.
Une famille de colonnes d'entrée MAP<key, ARRAY<STRUCT<timestamp, value>>>
se décompresse en MAP<key, value>
, et un qualificatif de colonne ARRAY<STRUCT<timestamp, value>>>
se décompresse en value
. Les autres types de colonnes d'entrée restent inchangés. Les colonnes doivent être sélectionnées dans la sous-requête pour pouvoir être décompressées et sélectionnées. Il n'est pas nécessaire de sélectionner la nouvelle colonne _timestamp
pour que les codes temporels soient développés.
En reprenant l'exemple de série temporelle de la section Interroger des données de séries temporelles et en utilisant la requête de cette section comme entrée, votre requête UNPACK
se présente comme suit :
SELECT temp_versioned, _timestamp
FROM
UNPACK((
SELECT metrics['temperature'] AS temperature_versioned
FROM
sensorReadings(with_history => true, after => TIMESTAMP('2023-01-04T23:00:00.000Z'),
before => TIMESTAMP('2023-01-05T01:00:00.000Z'))
WHERE _key LIKE 'sensorA%'
));
La requête renvoie les données au format suivant :
temp_versioned |
_timestamp |
---|---|
55 |
1672898400 |
55 |
1672894800 |
56 |
1672891200 |
Interroger JSON
Les fonctions JSON vous permettent de manipuler des données JSON stockées en tant que valeurs Bigtable pour les charges de travail opérationnelles.
Par exemple, vous pouvez récupérer la valeur de l'élément JSON abc
à partir de la dernière cellule de la famille de colonnes session
, ainsi que la clé de ligne, à l'aide de la requête suivante.
SELECT _key, JSON_VALUE(session['payload'],'$.abc') AS abc FROM analytics
Échapper les caractères spéciaux et les mots réservés
Bigtable offre une grande flexibilité pour nommer les tables et les colonnes. Par conséquent, dans vos requêtes SQL, vous devrez peut-être échapper les noms de vos tables en raison de caractères spéciaux ou de mots réservés.
Par exemple, la requête suivante n'est pas une requête SQL valide en raison du point dans le nom de la table.
-- ERROR: Table name format not supported
SELECT * FROM my.table WHERE _key = 'r1'
Toutefois, vous pouvez résoudre ce problème en plaçant les éléments entre des accents graves (`).
SELECT * FROM `my.table` WHERE _key = 'r1'
Si un mot clé réservé SQL est utilisé comme identifiant, il peut également être échappé.
SELECT * FROM `select` WHERE _key = 'r1'
Utiliser SQL avec une bibliothèque cliente Bigtable
Les bibliothèques clientes Bigtable pour Java, Python et Go permettent d'interroger des données avec SQL à l'aide de l'API executeQuery
. Les exemples suivants montrent comment exécuter une requête et accéder aux données :
Go
Pour utiliser cette fonctionnalité, vous devez utiliser cloud.google.com/go/bigtable
version 1.36.0 ou ultérieure. Pour en savoir plus sur l'utilisation, consultez la documentation PrepareStatement, Bind, Execute et ResultRow.
import (
"cloud.google.com/go/bigtable"
)
func query(client *bigtable.Client) {
// Prepare once for queries that will be run multiple times, and reuse
// the PreparedStatement for each request. Use query parameters to
// construct PreparedStatements that can be reused.
ps, err := client.PrepareStatement(
"SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
map[string]SQLType{
"keyParam": BytesSQLType{},
}
)
if err != nil {
log.Fatalf("Failed to create PreparedStatement: %v", err)
}
// For each request, create a BoundStatement with your query parameters set.
bs, err := ps.Bind(map[string]any{
"keyParam": []byte("mykey")
})
if err != nil {
log.Fatalf("Failed to bind parameters: %v", err)
}
err = bs.Execute(ctx, func(rr ResultRow) bool {
var byteValue []byte
err := rr.GetByName("bytesCol", &byteValue)
if err != nil {
log.Fatalf("Failed to access bytesCol: %v", err)
}
var stringValue string
err = rr.GetByName("stringCol", &stringValue)
if err != nil {
log.Fatalf("Failed to access stringCol: %v", err)
}
// Note that column family maps have byte valued keys. Go maps don't support
// byte[] keys, so the map will have Base64 encoded string keys.
var cf3 map[string][]byte
err = rr.GetByName("cf3", &cf3)
if err != nil {
log.Fatalf("Failed to access cf3: %v", err)
}
// Do something with the data
// ...
return true
})
}
Java
Pour utiliser cette fonctionnalité, vous devez utiliser la version 2.57.3 ou ultérieure de java-bigtable
. Pour en savoir plus sur l'utilisation, consultez prepareStatement, executeQuery, BoundStatement et ResultSet dans Javadoc.
static void query(BigtableDataClient client) {
// Prepare once for queries that will be run multiple times, and reuse
// the PreparedStatement for each request. Use query parameters to
// construct PreparedStatements that can be reused.
PreparedStatement preparedStatement = client.prepareStatement(
"SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key=@keyParam",
// For queries with parameters, set the parameter names and types here.
Map.of("keyParam", SqlType.bytes())
);
// For each request, create a BoundStatement with your query parameters set.
BoundStatement boundStatement = preparedStatement.bind()
.setBytesParam("keyParam", ByteString.copyFromUtf8("mykey"))
.build();
try (ResultSet resultSet = client.executeQuery(boundStatement)) {
while (resultSet.next()) {
ByteString byteValue = resultSet.getBytes("bytesCol");
String stringValue = resultSet.getString("stringCol");
Map<ByteString, ByteString> cf3Value =
resultSet.getMap("cf3", SqlType.mapOf(SqlType.bytes(), SqlType.bytes()));
// Do something with the data.
}
}
}
Python asyncio
Pour utiliser cette fonctionnalité, vous devez utiliser la version 2.30.1 ou ultérieure de python-bigtable
.
from google.cloud.bigtable.data import BigtableDataClientAsync
async def execute_query(project_id, instance_id, table_id):
async with BigtableDataClientAsync(project=project_id) as client:
query = (
"SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol,"
" cf3 FROM {table_id} WHERE _key='mykey'"
)
async for row in await client.execute_query(query, instance_id):
print(row["_key"], row["bytesCol"], row["stringCol"], row["cf3"])
Utilisation de SELECT *
Les requêtes SELECT *
peuvent rencontrer des erreurs temporaires lorsqu'une famille de colonnes est ajoutée ou supprimée de la table interrogée. C'est pourquoi, pour les charges de travail de production, nous vous recommandons de spécifier tous les ID de famille de colonnes dans votre requête plutôt que d'utiliser SELECT *
. Par exemple, utilisez SELECT cf1, cf2, cf3
au lieu de SELECT *
.
Étapes suivantes
- Explorez la documentation de référence sur GoogleSQL pour Bigtable.
- En savoir plus sur Bigtable Studio