Exemples de requêtes GoogleSQL pour Bigtable

Remarque : Cette page inclut des fonctionnalités disponibles uniquement dans l'édition Enterprise Plus, telles que Data Boost pour SQL. Pour en savoir plus, consultez la présentation des éditions.

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êtes 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.

Remarque : Une version fait référence à une valeur horodatée pour une colonne et une ligne données. Chaque version est stockée dans une cellule. Pour en savoir plus, consultez la section Modèle de stockage Bigtable.

Les exemples de cette page utilisent des ID et des valeurs semblables à ceux de Données pour les exemples.

Modèles 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 semblables qui appellent l'API Bigtable Data, consultez les sections Exemples de lecture et Utiliser des filtres. Pour obtenir des exemples de requêtes sur des clés de ligne structurées, consultez la section Requêtes de clés de ligne structurées.

Récupérer la dernière version de toutes les colonnes pour une clé de ligne donnée.

  SELECT * FROM myTable WHERE _key = 'r1'

Récupérer 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érer 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érer 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érer 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érer 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érer toutes les versions de toutes les colonnes pour plusieurs clés de ligne à l'aide d'une autre approche.

  SELECT *
  FROM analytics(with_history => TRUE)
  WHERE _key IS IN ('phone#4c410523#20190501', 'phone#4c410523#20190502')

Récupérer 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érer les clés de ligne et les trois dernières versions de toutes les colonnes d'une famille de colonnes pour toutes les lignes de la table. 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érer 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érer 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 d'effectuer de conversion pour cette comparaison, car les agrégations Bigtable sont numériques.

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

Récupérer 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érer 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 est mappé en tant que paire clé-valeur. Cette requête SQL est semblable à l'utilisation d'un filtre de valeur de suppression 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 lorsque vous effectuez 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 voir 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.

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 JSON fonctions.

  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 des sessions à l'aide de la dernière valeur de deux cellules d'agrégation Bigtable , qui sont numériques, pour chaque ligne de la table.

  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 en tant que qualificatif de colonne. Vous pouvez également écrire cette requête sous la forme d'une série de comparaisons individuelles, telles que 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 en tant que qualificatifs de colonne. Vous pouvez également écrire cette requête sous la forme d'une série de comparaisons individuelles, telles que 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 en tant que 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 de row key et de temperature pour les capteurs météorologiques 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 répondent aux 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 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-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)

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 city:Savannah ou city:Nashville à tout moment.

  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, la conversion n'est pas requise. 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

Analyse SQL à haut débit avec Data Boost

Dans l'édition Enterprise Plus, pour les charges de travail analytiques à haut débit, vous pouvez utiliser Data Boost pour exécuter des requêtes qui analysent de grandes quantités de données sans affecter les performances de vos clusters de production. Ces requêtes sont optimales pour l'analyse en temps réel et la génération d'insights à partir de données historiques ou de séries temporelles. Pour exécuter ces requêtes, vous devez utiliser un profil d'application configuré pour Data Boost.

Les exemples suivants illustrent des modèles SQL pour l'analyse à haut débit à l'aide de l'informatique sans serveur.

Analyser des données de séries temporelles historiques

Avec Data Boost, vous pouvez récupérer les clés de ligne et toutes les versions des métriques pour toutes les lignes d'une table au cours des dernières 24 heures. La requête suivante effectue une analyse complète de la table, qui est gérée par un calcul sans serveur isolé.

SELECT _key, metrics
FROM myTable(with_history => TRUE, after => TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR))

Agréger des ensembles de données volumineux

La requête suivante vous montre comment calculer le nombre total d'événements pour chaque appareil sur une plage de temps spécifique dans l'ensemble de la table.

SELECT
  _key AS device_id,
  COUNT(events['event_type']) AS total_events
FROM
  UNPACK((
    SELECT events['event_type']
    FROM sensor_data(with_history => TRUE, after => TIMESTAMP('2026-01-01T00:00:00Z'))
  ))
GROUP BY
  Device_id;

Analyses de plages volumineuses

La requête suivante vous montre comment récupérer toutes les colonnes pour une large plage de clés de ligne. L'utilisation de Data Boost garantit que cette requête à haut débit ne consomme pas de ressources de processeur sur les nœuds de votre cluster.

SELECT *
FROM analytics
WHERE _key >= 'user#000000' AND _key < 'user#999999'

Étape suivante

• 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.