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. Elle n'est donc pas recommandée pour les 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 des 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 prenne le pas, 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 nécessaire. 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