Interroger un journal de modifications Bigtable dans BigQuery

Cette page fournit des conseils et des exemples de requêtes pour vous aider à traiter un journal de modifications Bigtable dans BigQuery.

Cette page s'adresse aux utilisateurs qui ont effectué les opérations suivantes:

Ce guide suppose que vous connaissez BigQuery. Pour en savoir plus, consultez le guide de démarrage rapide qui explique comment charger et interroger des données.

Ouvrir la table du journal des modifications

  1. Dans la console Google Cloud, accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, développez votre projet.

  3. Développez votre ensemble de données.

  4. Cliquez sur la table avec le suffixe: _changelog.

Format de table

L'ensemble du schéma de sortie contient plusieurs colonnes. Ce guide se concentre sur la connexion des lignes à leurs colonnes et valeurs, et l'analyse des valeurs dans des formats analysables.

Requêtes de base

Les exemples de cette section utilisent une table Bigtable pour suivre les ventes par carte de crédit. La table comporte une famille de colonnes (cf) et les colonnes suivantes:

  • Clé de ligne au format credit card number#transaction timestamp
  • Marchand
  • Montant
  • Catégorie
  • Date de la transaction

Interroger une colonne

Filtrez les résultats sur une seule famille de colonnes et une seule colonne à l'aide d'une clause WHERE.

SELECT row_key, column_family, column, value, timestamp,
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="merchant"
LIMIT 1000

Analyser les valeurs

Toutes les valeurs sont stockées sous forme de chaînes ou de chaînes d'octets. Vous pouvez caster une valeur vers son type prévu à l'aide de fonctions de conversion.

SELECT row_key, column_family, column, value, CAST(value AS NUMERIC) AS amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"
LIMIT 1000

Effectuer des agrégations

Vous pouvez effectuer d'autres opérations, telles que des agrégations sur des valeurs numériques.

SELECT SUM(CAST(value AS NUMERIC)) as total_amount
FROM your_dataset.your_table
WHERE
  mod_type="SET_CELL"
  AND column_family="cf"
  AND column="amount"

Croiser les données

Pour effectuer des requêtes impliquant plusieurs colonnes Bigtable, vous devez pivoter le tableau. Chaque nouvelle ligne BigQuery inclut un enregistrement de modification de données renvoyé par le flux de modifications depuis la ligne correspondante dans votre table Bigtable. En fonction de votre schéma, vous pouvez utiliser une combinaison de la clé de ligne et du code temporel pour regrouper les données.

SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in ("merchant", "amount", "category", "transaction_date")
)

Créer un pivot avec un ensemble de colonnes dynamique

Si vous disposez d'un ensemble dynamique de colonnes, vous pouvez effectuer un traitement supplémentaire pour obtenir toutes les colonnes et les insérer dans la requête de manière programmatique.

DECLARE cols STRING;
SET cols = (
  SELECT CONCAT('("', STRING_AGG(DISTINCT column, '", "'), '")'),
  FROM your_dataset.your_table
);

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT row_key, timestamp, column, value
  FROM your_dataset.your_table
)
PIVOT (
  MAX(value)
  FOR column in %s
)""", cols);

Données JSON

Si vous définissez toutes les valeurs avec JSON, vous devez les analyser et les extraire en fonction des clés. Vous pouvez utiliser des fonctions d'analyse une fois que vous avez dérivé la valeur de l'objet JSON. Ces exemples utilisent les données de vente par carte de crédit présentées précédemment, mais au lieu d'écrire les données dans plusieurs colonnes, elles sont écrites dans une seule colonne en tant qu'objet JSON.

SELECT
  row_key,
  JSON_VALUE(value, "$.category") as category,
  CAST(JSON_VALUE(value, "$.amount") AS NUMERIC) as amount
FROM your_dataset.your_table
LIMIT 1000

Requêtes d'agrégation avec JSON

Vous pouvez effectuer des requêtes d'agrégation avec des valeurs JSON.

SELECT
  JSON_VALUE(value, "$.category") as category,
  SUM(CAST(JSON_VALUE(value, "$.amount") AS NUMERIC)) as total_amount
FROM your_dataset.your_table
GROUP BY category

Étape suivante