Exemples de requêtes GoogleSQL pour Bigtable

Les exemples de cette page illustrent les modèles de requête SQL courants et avancés Requêtes Bigtable. Vous pouvez exécuter des requêtes GoogleSQL dans la section É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 documentation GoogleSQL pour Présentation de Bigtable

Les exemples de cette page utilisent des identifiants et des valeurs semblables à ceux Données à titre d'exemple :

Modèles de requêtes SQL Bigtable courants

Voici des exemples de requêtes courantes pour des données Bigtable. Pour voir des exemples de requêtes similaires qui appellent l'API Bigtable Data API, consultez Consulter des exemples et Utilisez 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é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é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érer la dernière version de toutes les colonnes d'une famille de colonnes pour une clé de ligne préfixe.

  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 colonne pour toutes les lignes de la table. Cette requête nécessite une analyse complète de la table. cette option n'est 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é pour les modèles d'accès à haut débit et faible latence, sauf si fournissez 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 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 Bigtable aggregates 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 similaire à l'utilisation d'une méthode de composition filtre dans la section 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'

Schémas 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 plus d'informations, voir 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 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. Cette requête peut également être écrit sous la forme d'une série de comparaisons individuelles, par exemple 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 apparaît en dernier. Par conséquent, une requête comme after => X, before => y, latest_n => 3 renvoie les trois dernières valeurs qui respectent les avant et après les conditions. 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 Temporaire filtres.

  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 à 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, 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