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:
- Configurer un flux de modifications Bigtable compatible tableau.
- Exécutez la commande Dataflow modèle qui écrit un journal des modifications dans BigQuery. Vous pouvez suivre le guide de démarrage rapide pour apprendre à le configurer.
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
Dans la console Google Cloud, accédez à la page BigQuery.
Dans le volet Explorateur, développez votre projet.
Développez votre ensemble de données.
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
- En savoir plus sur les modifications apportées à Bigtable flux.
- Consultez la page de référence du modèle de flux de modifications Bigtable vers BigQuery.
- Découvrez comment configurer le flux de modifications Bigtable vers le modèle BigQuery.