Présentation de GoogleSQL pour Bigtable

Preview

Ce produit ou cette fonctionnalité est soumis aux "Conditions des offres de pré-DG" dans la section "Conditions d'utilisation générales" des Conditions spécifiques au service. Les produits et fonctionnalités pré-DG sont disponibles "en l'état" et peuvent bénéficier d'une assistance limitée. Pour en savoir plus, consultez les descriptions des étapes de lancement.

Vous pouvez utiliser des instructions GoogleSQL pour interroger vos données Bigtable. GoogleSQL est un langage de programmation un langage de requête structuré (SQL) également implémenté pour d'autres 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 lien avec Schéma de la table Bigtable. Avant de lire ce document, vous devez le stockage Bigtable modèle et la conception de schémas concepts clés.

Vous pouvez créer et exécuter des requêtes dans Bigtable Studio dans la console Google Cloud, Vous pouvez aussi les exécuter par programmation à l'aide du client Bigtable bibliothèque pour Java. Pour en savoir plus, consultez la section Utiliser SQL avec une bibliothèque cliente Bigtable.

Cas d'utilisation

GoogleSQL pour Bigtable est optimal pour le développement d'applications à faible latence. De plus, vous pouvez exécuter des requêtes SQL La console Google Cloud peut être utile pour obtenir rapidement une représentation visuelle du schéma d'une table, pour vérifier que certaines données ont bien été écrites ou pour déboguer les éventuels problèmes de données.

La version actuelle de GoogleSQL pour Bigtable n'est pas compatible avec les constructions SQL courantes, y compris, mais sans s'y limiter, 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 colonne unique 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 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 une table avec le paramètre 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 mappages représentant des familles de colonnes sont imbriqués et renvoyés sous la forme d'un 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 ligne "info" pour une seule famille de colonnes 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 est famille de colonnes, user_123 est la clé de ligne, et city et state sont la colonne qualificatifs. 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 un Table Bigtable : si une colonne n'est pas utilisée sur une ligne, aucune donnée n'est stockée pour la colonne. Une ligne peut avoir une colonne et la ligne suivante peut en contenir 100 colonnes. En revanche, dans une table de base de données relationnelle, toutes les lignes contiennent colonnes, et une valeur NULL est généralement stockée dans la colonne d'une ligne qui n'a pas de les données de 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

Par défaut, lorsque vous fournissez une chaîne, GoogleSQL convertit implicitement de valeurs STRING à 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 la diffusion, consultez la section Conversion fonctions.

Filtres temporels

Le tableau suivant répertorie les arguments que vous pouvez utiliser lorsque vous accédez les éléments temporels d'un tableau. Les arguments sont listés dans l'ordre dans lequel ils sont filtrées. 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 avec moins d'horodatages ou égal 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 valeur(s) avec horodatage en tant que 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 horodatages avant l'entrée . Nécessite with_history => TRUE
latest_n	Nombre entier : Nombre de valeurs avec horodatage à renvoyer par qualificatif de colonne (clé de mappage). Doit être supérieur ou égal à 1. Nécessite with_history => TRUE.

Pour voir d'autres exemples, consultez la section Requête avancée des modèles.

Requêtes de base

Cette section décrit et présente des exemples de requêtes SQL Bigtable de base et leur fonctionnement. Pour voir d'autres exemples de requêtes, consultez Schéma de requête GoogleSQL pour Bigtable exemples.

Récupérez la dernière version

Bien que Bigtable vous permette de stocker plusieurs versions de données chaque colonne, GoogleSQL pour Bigtable par défaut renvoie la dernière version (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 été déplacé 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. Toi peuvent également créer des alias pour des colonnes et des expressions, comme illustré dans l'exemple suivant.

  SELECT _key, columnFamily['qualifier'] AS col1
  FROM myTable(with_history => TRUE)

Pour mieux comprendre les événements conduisant à 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é.

Remarque :Les règles de récupération de mémoire contrôlent le délai d'expiration ou le nombre ou versions conservées pour une famille de colonnes. Pour en savoir plus, consultez les 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 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'une heure spécifiée

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 montre quelle aurait été la dernière adresse connue du 10 janvier, 2022 13:14 (combinaison des rues et de la ville) 2021/12/20-09:44:31.010 et 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 la colonne les qualificatifs sont smoke et carbonMonoxide. Les cellules de chaque colonne sont séparées par des lignes vides.

	alarmType
_key	fumée	carbonMonoxide
bâtiment1#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: "désactivé" 2023/04/01-08:53:12.000: "on"
bâtiment1#section2	2021/03/11-07:15:04.000: 'off' 2021/03/11-07:00:25.000: 'on'

Vous trouverez les sections du site building1 où un détecteur de fumée a été activé à 9h le mois d'avril. 1er janvier 2023 et l'état actuel de l'alarme du détecteur de monoxyde de carbone à l'aide de la 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
bâtiment1#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é
capteurA#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 ans	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(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 les 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

Échappez 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, il peut être nécessaire d'échapper les noms de vos tables les caractères spéciaux ou les mots réservés.

Par exemple, la requête suivante n'est pas un code SQL valide en raison du point dans la table. son nom.

  -- 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 acceptent les requêtes des données avec SQL à l'aide de l'API executeQuery. Les exemples suivants montrent comment lancer 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 pour en savoir plus sur son utilisation, consultez executeQuery, Déclaration et ResultSet dans 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"])

Étape suivante

• Explorez la documentation de référence sur GoogleSQL pour Bigtable.
• En savoir plus sur Bigtable Studio