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 que INSERT, UPDATE ou DELETE
• Instructions LDD (langage de définition de données) telles que CREATE, ALTER ou DROP
• Instructions de contrôle des accès aux données
• Syntaxe des sous-requêtes, JOIN, UNION, 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 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é.

Remarque : Les règles de récupération de mémoire contrôlent la durée d'expiration ou le nombre de versions conservées pour une famille de colonnes. Pour en savoir plus, consultez la présentation de la récupération de mémoire.

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