Cette page a été traduite par l'API Cloud Translation.

Présentation de GoogleSQL pour Bigtable

Vous pouvez utiliser des instructions GoogleSQL pour interroger vos données Bigtable. GoogleSQL est un langage de requêtes structuré (SQL) conforme à la norme ANSI, qui est également implémenté pour d'autres services Google Cloud tels 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 relation avec un schéma de table Bigtable. Avant de lire ce document, vous devez connaître le modèle de stockage de Bigtable et les concepts de conception de schémas.

Vous pouvez créer et exécuter des requêtes dans Bigtable Studio dans la console Google Cloud, ou les exécuter de manière programmatique à l'aide de la bibliothèque cliente Bigtable pour Java. Pour en savoir plus, consultez Utiliser SQL avec une bibliothèque cliente Bigtable.

Les requêtes SQL sont gérées par les nœuds de 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, par exemple en évitant les analyses de table complètes ou les filtres complexes. Pour en savoir plus, consultez la section Lectures et performances.

Vous ne pouvez pas utiliser Data Boost avec GoogleSQL pour Bigtable.

Cas d'utilisation

GoogleSQL pour Bigtable est optimal pour le développement d'applications à faible latence. De plus, exécuter des requêtes SQL dans la console Google 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 certaines constructions SQL courantes, y compris, mais sans s'y limiter, les suivantes:

Instructions du langage de manipulation de données (LMD) au-delà de SELECT, telles que INSERT, UPDATE ou DELETE
Instructions de langage de définition de données (LDD) telles que CREATE, ALTER ou DROP
Instructions de contrôle des accès aux données
Syntaxe des requêtes pour les sous-requêtes, JOIN, UNION, GROUP BY, UNNEST et CTEs

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 GoogleSQL pour Bigtable.

Concepts clés

Cette section présente les concepts clés à prendre en compte 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 servent à regrouper des 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 de la carte

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 mappage contient des paires clé-valeur, où la 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 le stockage de plusieurs cellules (ou versions des 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 clé est elle-même un mappage composé 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 se présente comme suit. Dans la table interrogée, info correspond à la famille de colonnes, user_123 à la clé de ligne, et city et state aux qualificatifs de colonne. Chaque paire de valeurs-code temporel (STRUCT) dans un tableau représente les cellules de ces colonnes de cette ligne, et elles sont triées par code temporel décroissant.

/*----------+------------------------------------------------------------------+
 |   _key   |                              info                                |
 +----------+------------------------------------------------------------------+
 | user_123 | {"city":{<t5>:"Brooklyn", <t0>:"New York"}, "state":{<t0>:"NY"}} |
 +----------+------------------------------------------------------------------*/

Tables sparses

L'une des principales caractéristiques de Bigtable est son modèle de données flexible. Dans une table Bigtable, si une colonne n'est pas utilisée dans une ligne, aucune donnée n'est stockée pour cette colonne. Une ligne peut comporter une seule colonne, tandis que la suivante peut en contenir 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 une carte vide et est renvoyée sous la forme d'une 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 ne peut être utilisé pour renvoyer une ligne que si column1 est utilisé dans la ligne.

Octets

Lorsque vous fournissez une chaîne, GoogleSQL convertit implicitement les 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 avec la fonction CAST. Pour en savoir plus sur le casting, consultez la section 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 indiquer un code temporel valide.

Argument	Description
`as_of`	Timestamp. Renvoie les dernières valeurs avec des codes temporels inférieurs ou égaux au code temporel fourni.
`with_history`	Booléen : Détermine si la dernière valeur doit être renvoyée sous forme de valeur scalaire ou de valeurs avec horodatage sous forme de `STRUCT`.
`after_or_equal`	Timestamp. Valeurs avec codes temporels postérieurs à la saisie, y compris. Nécessite `with_history => TRUE`
`before`	Timestamp. Valeurs avec des codes temporels antérieurs à l'entrée, à l'exclusion de celle-ci. Nécessite `with_history => TRUE`
`latest_n`	Entier. Nombre de valeurs avec horodatage à renvoyer par qualificatif de colonne (clé de mappage). La valeur doit être supérieure ou égale à 1. Nécessite `with_history => TRUE`.

Pour en savoir plus, consultez la section Modèles de requêtes avancés.

Requêtes de base

Cette section décrit et présente des exemples de requêtes SQL Bigtable de base et leur fonctionnement. Pour obtenir d'autres exemples de requêtes, consultez la section Exemples de modèles de requêtes GoogleSQL pour Bigtable.

Récupérez 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 d'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	rue	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.

   SELECT * FROM myTable WHERE _key = 'user1'

La réponse contient l'adresse actuelle, qui est une combinaison des valeurs de rue, de ville et d'état les plus récentes (é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 ajouter des alias aux colonnes et aux 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 emménagé à son adresse actuelle et d'où il est parti, 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 dans l'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 Xyz Street			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 spécifié

L'utilisation d'un filtre as_of vous permet de récupérer l'état d'une ligne tel qu'il était à 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/10-13:14:00'))
  WHERE _key = 'user1'

Le résultat indique la dernière adresse connue le 10 janvier 2022 à 13h14, qui correspond à 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 de codes temporels 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 l'état activé ou désactivé des détecteurs de fumée et de monoxyde de carbone. 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 des 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, sensorType['carbonMonoxide'] AS CO_sensor
  FROM alarms(as_of => TIMESTAMP('2023/04/01-09:00:00.000'))
  WHERE _key LIKE 'building1%' and sensorType['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 d'ensemble de données suivant, qui affiche les mesures de température et d'humidité pour des capteurs météo. L'ID de la famille de colonnes est metrics, et les qualificatifs de colonne sont temperature et humidity. Les cellules d'une colonne sont séparées par des lignes vides, et chacune d'elles représente une lecture de capteur avec horodatage.

	métriques
_key	température	humidité
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 de code temporel à l'aide des filtres temporels after, before ou after_or_equal. L'exemple suivant utilise after:

   SELECT metrics['temperature'] AS temp_versioned
   FROM
   sensorReadings(after => TIMESTAMP('2023/01/04-23:00:00'),
         before => TIMESTAMP('2023/01/05-01:00:00'))
   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}

Requête JSON

Les fonctions JSON vous permettent de manipuler le JSON stocké 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 valide en SQL 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 délimitant les éléments par des caractères accent grave (`).

  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 et Python permettent d'interroger des données avec SQL à l'aide de l'API executeQuery. Les exemples suivants montrent comment envoyer une requête et accéder aux données:

Java

Pour utiliser cette fonctionnalité, vous devez utiliser java-bigtable version 2.41.0 ou ultérieure. Pour en savoir plus sur l'utilisation, consultez executeQuery, Statement et ResultSet dans la documentation JavaDoc.

  static void query(BigtableDataClient client) {
    try (ResultSet resultSet =
        client.executeQuery(
            Statement.of(
                "SELECT cf1['bytesCol'] AS bytesCol, CAST(cf2['stringCol'] AS STRING) AS stringCol, cf3 FROM myTable WHERE _key='mykey'"))) {
      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 python-bigtable version 2.26.0 ou ultérieure.

  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"])