Exercice 08 : Analyse de données avec BigQuery

🎯 Objectifs

À la fin de cet exercice, vous serez capable de :

✅ Créer un dataset BigQuery et charger des données depuis Cloud Storage
✅ Écrire des requêtes SQL analytiques (GROUP BY, fenêtrage, CTEs)
✅ Utiliser les données publiques de Google pour pratiquer
✅ Estimer et optimiser le coût de vos requêtes

Durée estimée : 40 minutes

Difficulté : ⭐⭐⭐⭐☆ (Avancé)

Prérequis : Notions SQL, gcloud CLI configurée

📖 Contexte

BigQuery est l'entrepôt de données (data warehouse) serverless de GCP. Il est conçu pour analyser des pétaoctets de données en quelques secondes. Contrairement à PostgreSQL ou MySQL (OLTP - optimisé pour les transactions), BigQuery est OLAP (optimisé pour les analyses sur de gros volumes). La tarification est à la quantité de données scannées : 1 To/mois gratuit, puis 5$ par To supplémentaire.

📋 Énoncé

Créez un dataset, chargez des données de ventes et analysez-les avec des requêtes SQL avancées.

🧭 Déroulement de l'exercice

Tâche 1 : Créer le dataset et préparer les données

bash

# Activer l'API BigQuery
gcloud services enable bigquery.googleapis.com

PROJECT_ID=$(gcloud config get-value project)

# Créer un dataset BigQuery
bq mk \
  --dataset \
  --location=EU \
  --description="Dataset pour l'exercice BigQuery" \
  ${PROJECT_ID}:ventes_dataset

# Vérifier
bq ls ${PROJECT_ID}:

bash

# Créer des données de ventes en CSV
cat > ventes.csv << 'EOF'
date,produit,categorie,region,quantite,prix_unitaire,vendeur_id
2024-01-15,Laptop Pro,Informatique,Paris,2,1299.99,V001
2024-01-15,Souris sans fil,Informatique,Lyon,5,29.99,V002
2024-01-16,Écran 27",Informatique,Paris,1,449.99,V001
2024-01-16,Bureau ergonomique,Mobilier,Marseille,3,299.99,V003
2024-01-17,Laptop Pro,Informatique,Lyon,1,1299.99,V002
2024-01-17,Chaise de bureau,Mobilier,Paris,2,199.99,V001
2024-01-18,Clavier mécanique,Informatique,Paris,4,89.99,V004
2024-01-18,Bureau ergonomique,Mobilier,Lyon,1,299.99,V002
2024-01-19,Laptop Pro,Informatique,Marseille,3,1299.99,V003
2024-01-19,Souris sans fil,Informatique,Paris,8,29.99,V001
2024-01-20,Écran 27",Informatique,Lyon,2,449.99,V004
2024-01-20,Chaise de bureau,Mobilier,Marseille,5,199.99,V003
EOF

# Uploader dans Cloud Storage
BUCKET="${PROJECT_ID}-bq-data"
gcloud storage buckets create gs://$BUCKET --location=EU
gcloud storage cp ventes.csv gs://$BUCKET/

# Charger dans BigQuery depuis Cloud Storage
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --autodetect \
  ${PROJECT_ID}:ventes_dataset.ventes \
  gs://$BUCKET/ventes.csv

# Vérifier le chargement
bq show ${PROJECT_ID}:ventes_dataset.ventes
bq head --max_rows=5 ${PROJECT_ID}:ventes_dataset.ventes

Tâche 2 : Requêtes analytiques de base

bash

# Requête 1 : Chiffre d'affaires total par catégorie
bq query --use_legacy_sql=false << 'SQL'
SELECT
  categorie,
  SUM(quantite * prix_unitaire) AS chiffre_affaires,
  SUM(quantite) AS total_unites,
  COUNT(*) AS nb_transactions
FROM `PROJECT_ID.ventes_dataset.ventes`
GROUP BY categorie
ORDER BY chiffre_affaires DESC;
SQL

# Requête 2 : Top 3 produits par région
bq query --use_legacy_sql=false << 'SQL'
WITH produits_par_region AS (
  SELECT
    region,
    produit,
    SUM(quantite * prix_unitaire) AS ca,
    RANK() OVER (PARTITION BY region ORDER BY SUM(quantite * prix_unitaire) DESC) AS rang
  FROM `PROJECT_ID.ventes_dataset.ventes`
  GROUP BY region, produit
)
SELECT region, produit, ca, rang
FROM produits_par_region
WHERE rang <= 3
ORDER BY region, rang;
SQL

Tâche 3 : Requêtes sur les données publiques

GCP met à disposition des datasets publics contenant des milliards de lignes.

bash

# Requête sur les données publiques GitHub (sans coût - dataset public)
bq query --use_legacy_sql=false \
  --project_id=$PROJECT_ID << 'SQL'
-- Les langages de programmation les plus utilisés sur GitHub
SELECT
  language,
  COUNT(*) AS nb_repos,
  SUM(watch_count) AS total_stars
FROM `bigquery-public-data.github_repos.languages`
CROSS JOIN UNNEST(language) AS lang(language, bytes)
GROUP BY language
HAVING nb_repos > 1000
ORDER BY total_stars DESC
LIMIT 15;
SQL

# Requête sur les données météo mondiales
bq query --use_legacy_sql=false \
  --project_id=$PROJECT_ID << 'SQL'
-- Températures moyennes par mois en France en 2023
SELECT
  EXTRACT(MONTH FROM date) AS mois,
  ROUND(AVG(mean_temp) / 10.0, 1) AS temp_moyenne_celsius,
  ROUND(MIN(min_temperature_air) / 10.0, 1) AS temp_min,
  ROUND(MAX(max_temperature_air) / 10.0, 1) AS temp_max
FROM `bigquery-public-data.noaa_gsod.gsod2023`
JOIN `bigquery-public-data.noaa_gsod.stations` USING (usaf, wban)
WHERE country = 'FR'
  AND date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY mois
ORDER BY mois;
SQL

Indice : Les requêtes sur les datasets publics comme bigquery-public-data.* ne sont pas facturées (Google offre l'accès). Avant d'exécuter une grosse requête sur vos propres données, utilisez bq query --dry_run pour estimer la quantité de données qui sera scannée (et donc le coût).

Tâche 4 : Optimiser les coûts

bash

# Simuler le coût d'une requête AVANT exécution
bq query \
  --use_legacy_sql=false \
  --dry_run \
  "SELECT * FROM \`${PROJECT_ID}.ventes_dataset.ventes\` WHERE categorie = 'Informatique'"
# → Output : "Query successfully validated. Bytes processed: 1234"

# Optimisation 1 : Sélectionner seulement les colonnes nécessaires
# ❌ Mauvais : SELECT * (scanne toutes les colonnes)
# ✅ Bon : SELECT date, produit, quantite (scanne seulement ces colonnes)

# Optimisation 2 : Partitionner la table sur la date
bq mk \
  --table \
  --time_partitioning_field=date \
  --time_partitioning_type=DAY \
  --description="Table partitionnée par jour" \
  ${PROJECT_ID}:ventes_dataset.ventes_partitionnees \
  date:DATE,produit:STRING,categorie:STRING,region:STRING,quantite:INTEGER,prix_unitaire:FLOAT,vendeur_id:STRING

# Avec partitionnement, BigQuery ne scanne que les partitions nécessaires
bq query --use_legacy_sql=false --dry_run \
  "SELECT * FROM \`${PROJECT_ID}.ventes_dataset.ventes_partitionnees\`
   WHERE date BETWEEN '2024-01-15' AND '2024-01-17'"
# → Scanne seulement 3 jours au lieu de toute la table

Tâche 5 : Nettoyer

bash

bq rm -r -f ${PROJECT_ID}:ventes_dataset
gcloud storage rm -r gs://$BUCKET

✅ Vérification du résultat

bq ls ${PROJECT_ID}: affiche ventes_dataset
bq head ventes_dataset.ventes affiche les 12 lignes de données
La requête CA par catégorie retourne des résultats cohérents
--dry_run estime les octets scannés avant exécution

💡 À retenir

OLTP vs OLAP :

	OLTP (PostgreSQL, MySQL)	OLAP (BigQuery)
Optimisé pour	INSERT/UPDATE/DELETE rapides	SELECT sur gros volumes
Stockage	Orienté lignes	Orienté colonnes
Cas d'usage	App métier, transactions	Analytics, rapports, ML
Volume typique	Go à To	To à Po

Optimisations BigQuery :

SELECT col1, col2 plutôt que SELECT *
Filtrer sur les colonnes de partition (WHERE date = ...)
Clustering sur les colonnes fréquemment filtrées
Utiliser LIMIT lors de l'exploration (mais n'impacte pas le coût !)

✨ Solution Complète

bash

# Charger un CSV et interroger en 3 commandes
bq mk --dataset mon_projet:mon_dataset
bq load --autodetect --source_format=CSV mon_projet:mon_dataset.ma_table gs://mon-bucket/data.csv
bq query --use_legacy_sql=false "SELECT COUNT(*) FROM \`mon_projet.mon_dataset.ma_table\`"

Exercice 08 : Analyse de données avec BigQuery

🎯 Objectifs

À la fin de cet exercice, vous serez capable de :

✅ Créer un dataset BigQuery et charger des données depuis Cloud Storage
✅ Écrire des requêtes SQL analytiques (GROUP BY, fenêtrage, CTEs)
✅ Utiliser les données publiques de Google pour pratiquer
✅ Estimer et optimiser le coût de vos requêtes

Durée estimée : 40 minutes

Difficulté : ⭐⭐⭐⭐☆ (Avancé)

Prérequis : Notions SQL, gcloud CLI configurée

📖 Contexte

📋 Énoncé

Créez un dataset, chargez des données de ventes et analysez-les avec des requêtes SQL avancées.

🧭 Déroulement de l'exercice

Tâche 1 : Créer le dataset et préparer les données

bash

# Activer l'API BigQuery
gcloud services enable bigquery.googleapis.com

PROJECT_ID=$(gcloud config get-value project)

# Créer un dataset BigQuery
bq mk \
  --dataset \
  --location=EU \
  --description="Dataset pour l'exercice BigQuery" \
  ${PROJECT_ID}:ventes_dataset

# Vérifier
bq ls ${PROJECT_ID}:

bash

# Créer des données de ventes en CSV
cat > ventes.csv << 'EOF'
date,produit,categorie,region,quantite,prix_unitaire,vendeur_id
2024-01-15,Laptop Pro,Informatique,Paris,2,1299.99,V001
2024-01-15,Souris sans fil,Informatique,Lyon,5,29.99,V002
2024-01-16,Écran 27",Informatique,Paris,1,449.99,V001
2024-01-16,Bureau ergonomique,Mobilier,Marseille,3,299.99,V003
2024-01-17,Laptop Pro,Informatique,Lyon,1,1299.99,V002
2024-01-17,Chaise de bureau,Mobilier,Paris,2,199.99,V001
2024-01-18,Clavier mécanique,Informatique,Paris,4,89.99,V004
2024-01-18,Bureau ergonomique,Mobilier,Lyon,1,299.99,V002
2024-01-19,Laptop Pro,Informatique,Marseille,3,1299.99,V003
2024-01-19,Souris sans fil,Informatique,Paris,8,29.99,V001
2024-01-20,Écran 27",Informatique,Lyon,2,449.99,V004
2024-01-20,Chaise de bureau,Mobilier,Marseille,5,199.99,V003
EOF

# Uploader dans Cloud Storage
BUCKET="${PROJECT_ID}-bq-data"
gcloud storage buckets create gs://$BUCKET --location=EU
gcloud storage cp ventes.csv gs://$BUCKET/

# Charger dans BigQuery depuis Cloud Storage
bq load \
  --source_format=CSV \
  --skip_leading_rows=1 \
  --autodetect \
  ${PROJECT_ID}:ventes_dataset.ventes \
  gs://$BUCKET/ventes.csv

# Vérifier le chargement
bq show ${PROJECT_ID}:ventes_dataset.ventes
bq head --max_rows=5 ${PROJECT_ID}:ventes_dataset.ventes

Tâche 2 : Requêtes analytiques de base

bash

# Requête 1 : Chiffre d'affaires total par catégorie
bq query --use_legacy_sql=false << 'SQL'
SELECT
  categorie,
  SUM(quantite * prix_unitaire) AS chiffre_affaires,
  SUM(quantite) AS total_unites,
  COUNT(*) AS nb_transactions
FROM `PROJECT_ID.ventes_dataset.ventes`
GROUP BY categorie
ORDER BY chiffre_affaires DESC;
SQL

# Requête 2 : Top 3 produits par région
bq query --use_legacy_sql=false << 'SQL'
WITH produits_par_region AS (
  SELECT
    region,
    produit,
    SUM(quantite * prix_unitaire) AS ca,
    RANK() OVER (PARTITION BY region ORDER BY SUM(quantite * prix_unitaire) DESC) AS rang
  FROM `PROJECT_ID.ventes_dataset.ventes`
  GROUP BY region, produit
)
SELECT region, produit, ca, rang
FROM produits_par_region
WHERE rang <= 3
ORDER BY region, rang;
SQL

Tâche 3 : Requêtes sur les données publiques

GCP met à disposition des datasets publics contenant des milliards de lignes.

bash

# Requête sur les données publiques GitHub (sans coût - dataset public)
bq query --use_legacy_sql=false \
  --project_id=$PROJECT_ID << 'SQL'
-- Les langages de programmation les plus utilisés sur GitHub
SELECT
  language,
  COUNT(*) AS nb_repos,
  SUM(watch_count) AS total_stars
FROM `bigquery-public-data.github_repos.languages`
CROSS JOIN UNNEST(language) AS lang(language, bytes)
GROUP BY language
HAVING nb_repos > 1000
ORDER BY total_stars DESC
LIMIT 15;
SQL

# Requête sur les données météo mondiales
bq query --use_legacy_sql=false \
  --project_id=$PROJECT_ID << 'SQL'
-- Températures moyennes par mois en France en 2023
SELECT
  EXTRACT(MONTH FROM date) AS mois,
  ROUND(AVG(mean_temp) / 10.0, 1) AS temp_moyenne_celsius,
  ROUND(MIN(min_temperature_air) / 10.0, 1) AS temp_min,
  ROUND(MAX(max_temperature_air) / 10.0, 1) AS temp_max
FROM `bigquery-public-data.noaa_gsod.gsod2023`
JOIN `bigquery-public-data.noaa_gsod.stations` USING (usaf, wban)
WHERE country = 'FR'
  AND date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY mois
ORDER BY mois;
SQL

Indice : Les requêtes sur les datasets publics comme bigquery-public-data.* ne sont pas facturées (Google offre l'accès). Avant d'exécuter une grosse requête sur vos propres données, utilisez bq query --dry_run pour estimer la quantité de données qui sera scannée (et donc le coût).

Tâche 4 : Optimiser les coûts

bash

# Simuler le coût d'une requête AVANT exécution
bq query \
  --use_legacy_sql=false \
  --dry_run \
  "SELECT * FROM \`${PROJECT_ID}.ventes_dataset.ventes\` WHERE categorie = 'Informatique'"
# → Output : "Query successfully validated. Bytes processed: 1234"

# Optimisation 1 : Sélectionner seulement les colonnes nécessaires
# ❌ Mauvais : SELECT * (scanne toutes les colonnes)
# ✅ Bon : SELECT date, produit, quantite (scanne seulement ces colonnes)

# Optimisation 2 : Partitionner la table sur la date
bq mk \
  --table \
  --time_partitioning_field=date \
  --time_partitioning_type=DAY \
  --description="Table partitionnée par jour" \
  ${PROJECT_ID}:ventes_dataset.ventes_partitionnees \
  date:DATE,produit:STRING,categorie:STRING,region:STRING,quantite:INTEGER,prix_unitaire:FLOAT,vendeur_id:STRING

# Avec partitionnement, BigQuery ne scanne que les partitions nécessaires
bq query --use_legacy_sql=false --dry_run \
  "SELECT * FROM \`${PROJECT_ID}.ventes_dataset.ventes_partitionnees\`
   WHERE date BETWEEN '2024-01-15' AND '2024-01-17'"
# → Scanne seulement 3 jours au lieu de toute la table

Tâche 5 : Nettoyer

bash

bq rm -r -f ${PROJECT_ID}:ventes_dataset
gcloud storage rm -r gs://$BUCKET

✅ Vérification du résultat

bq ls ${PROJECT_ID}: affiche ventes_dataset
bq head ventes_dataset.ventes affiche les 12 lignes de données
La requête CA par catégorie retourne des résultats cohérents
--dry_run estime les octets scannés avant exécution

💡 À retenir

OLTP vs OLAP :

	OLTP (PostgreSQL, MySQL)	OLAP (BigQuery)
Optimisé pour	INSERT/UPDATE/DELETE rapides	SELECT sur gros volumes
Stockage	Orienté lignes	Orienté colonnes
Cas d'usage	App métier, transactions	Analytics, rapports, ML
Volume typique	Go à To	To à Po

Optimisations BigQuery :

SELECT col1, col2 plutôt que SELECT *
Filtrer sur les colonnes de partition (WHERE date = ...)
Clustering sur les colonnes fréquemment filtrées
Utiliser LIMIT lors de l'exploration (mais n'impacte pas le coût !)

✨ Solution Complète

bash

# Charger un CSV et interroger en 3 commandes
bq mk --dataset mon_projet:mon_dataset
bq load --autodetect --source_format=CSV mon_projet:mon_dataset.ma_table gs://mon-bucket/data.csv
bq query --use_legacy_sql=false "SELECT COUNT(*) FROM \`mon_projet.mon_dataset.ma_table\`"