Exemples de requêtes GoogleSQL pour Bigtable

Les exemples de cette page illustrent des 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.

Schémas de requêtes SQL Bigtable courants

Voici 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 Exemples de lecture et Utiliser des filtres. Pour obtenir des exemples de requêtes sur les clés de ligne structurées, consultez Requêtes sur les clés de ligne structurées.

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ère la dernière version d'une colonne spécifique 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 en utilisant 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 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.

  SELECT _key, cell_plan FROM analytics(with_history => TRUE, latest_n => 3)

Récupère 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és 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 dont le préfixe de clé de ligne correspond et dont la valeur du compteur est supérieure à 123. Vous n'avez pas besoin de caster pour cette comparaison, car les agrégats Bigtable sont numériques.

  SELECT *
  FROM myTable
  WHERE _key LIKE 'user12%' AND counterFamily['counter'] > 123

Récupère la dernière version de toutes les colonnes pour un préfixe de clé de ligne si le referrer correspond à une valeur spécifique.

  SELECT *
  FROM analytics
  WHERE _key LIKE 'com.mysite%' AND session['referrer'] = './home'

Catégorise 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 "map", où chaque qualificatif et valeur de colonne sont mappés en tant que paire clé-valeur. Cette requête SQL est semblable à l'utilisation d'un filtre de suppression de valeur 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'

La fonction UNPACK vous permet de transformer les données Bigtable en un format de série temporelle tabulaire, ce qui est utile pour effectuer une analyse de série temporelle. Prenons l'exemple d'une colonne clicks dans une famille de colonnes engagement. La requête suivante utilise UNPACK pour afficher les performances de certaines campagnes en agrégeant les clics sur une minute au cours de la dernière heure.

  SELECT
    FORMAT_TIMESTAMP('%M', _timestamp) AS minute,
    COUNT(clicks) AS total_clicks
  FROM
    UNPACK((
      SELECT engagement['clicks'] as clicks
      FROM metrics(with_history => true, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))
      WHERE _key = @campaign_id
    ))
  GROUP BY
    minute;

Modèles de requêtes SQL Bigtable avancés

Les exemples suivants illustrent des modèles plus avancés.

La requête suivante vous permet de 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

La requête suivante vous permet de récupérer la clé de ligne et de calculer la durée moyenne des sessions à 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

La requête suivante vous permet de 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 en tant que 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'])

La requête suivante vous permet de 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. Cette requête peut également être rédigée 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'])

La requête suivante vous permet de 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 les valeurs com.google.search, com.google.maps ou com.google.shopping.

  SELECT *
  FROM analytics
  WHERE
    _key LIKE 'com.abc%'
    AND ARRAY_INCLUDES_ANY(
      MAP_VALUES(session),
      ['com.google.search', 'com.google.maps', 'com.google.shopping'])

La requête suivante vous permet de 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')])

La requête suivante vous permet de récupérer les lectures row key et temperature pour les capteurs météo dans les cas où la température a dépassé 70 degrés lors 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 arrive en dernier. Par conséquent, une requête telle que after => X, before => y, latest_n => 3 renvoie les trois dernières valeurs qui satisfont les conditions "after" et "before". Si votre cas d'utilisation nécessite que latest_n soit prioritaire, 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 indiqué dans l'exemple. Pour en savoir plus, consultez Filtres temporels.

  SELECT
    ARRAY_FILTER(
      CAST(
        address['street']
        AS ARRAY<STRUCT<timestamp TIMESTAMP, value STRING>>),
      e -> e.timestamp > TIMESTAMP('2021-01-04T23:51:00.000Z'))
      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)

La requête suivante vous permet de récupérer toutes les versions de toutes les colonnes si les paires clé/valeur de la famille de colonnes d'adresse incluent 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. Vous pouvez donc également l'écrire sous la forme abrégée 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

Étapes suivantes

• Parcourez la documentation de référence sur GoogleSQL pour Bigtable.
• Utilisez le connecteur Bigtable Spark.
• Gérez les schémas de clés de ligne.