Interroger un journal des modifications Bigtable dans BigQuery

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

Cette page est destinée aux utilisateurs qui ont:

Ce guide suppose une certaine connaissance de BigQuery. Pour en savoir plus, vous pouvez suivre 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'intégralité de la sortie schéma 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 le suivi 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"

Faire pivoter 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. Selon votre schéma, vous pouvez utiliser un combinaison de la clé de ligne et de l'horodatage 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 extraire les 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 sur les ventes par carte de crédit présentées précédemment, mais au lieu d'écrire des données dans plusieurs colonnes, les données sont écrites sous la forme d'une colonne unique 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