📈 BigQuery – Data Warehouse Serverless (SQL, Dremel & Slots)

Qu'est-ce que BigQuery ?

BigQuery (BQ) est un Data Warehouse (Entrepôt de Données) entièrement managé (SaaS) et "Serverless" (sans serveur), fourni par Google Cloud (GCP).

Il est conçu pour l'analytique (BI) à très grande échelle (Pétaoctets) en utilisant une interface SQL.

Philosophie "Serverless"

Contrairement à Redshift (legacy) ou Snowflake (Virtual Warehouses), BigQuery est (par défaut) purement Serverless.

• Il n'y a aucun cluster (VMs) à démarrer, configurer, ou éteindre.
• Il n'y a pas de "cold start" (démarrage à froid).
• Vous envoyez une requête SQL, et BQ (Dremel) provisionne automatiquement des milliers de "Slots" (unités de calcul) pour l'exécuter, puis les libère.

Tarification (Défaut)

Le modèle par défaut (On-Demand) est basé sur deux axes (séparés) :

• 1. Coût de Stockage (Storage) : (Très faible) $ par Go/mois (stocké dans Colossus).
• 2. Coût de Calcul (Compute) : (Élevé) $ par Téraoctet (To) scanné (lu) par vos requêtes SELECT.

(Piège) Une requête SELECT * FROM ma_table_10To scannera 10To, même si vous ajoutez LIMIT 10. L'optimisation (Partitioning, Clustering) est vitale pour contrôler les coûts.

BigQuery (SaaS) sépare totalement le stockage (Colossus) et le calcul (Dremel), reliés par le réseau (Jupiter).

(Requête SQL)
     │
     ▼
[ 1. Dremel (Compute Engine) ]
   (Le "Cerveau" / Moteur de Requête)
   (Lit le SQL, crée l'arbre d'exécution)
   (Dispatch les tâches aux "Slots" (Workers))
       │
       │ (Lit les données via le réseau)
       ▼
[ 2. Jupiter Network (Réseau Google) ]
   (Réseau "Petabit/s" interne de Google)
   (Permet le "Shuffle" massif)
       │
       │ (Lit les blocs de données)
       ▼
[ 3. Colossus (Storage) ]
   (Le "Disque Dur" / Stockage)
   (Successeur de GFS. Stockage Colonnaire 'Capacitor')

• Colossus : Le système de fichiers (stockage) distribué global de Google. (Stocke les tables BQ, répliquées).
• Dremel : Le moteur de requête (Compute). C'est le "cerveau" qui exécute le SQL (via des "Slots") en parallèle (MPP).
• Jupiter : Le réseau (SDN) interne de Google (très rapide) qui connecte Dremel (Compute) à Colossus (Stockage) pour le "Shuffle" (ex: GROUP BY).

L'adressage (namespace) dans BigQuery (non-UC) suit une hiérarchie à 3 niveaux.

-- Syntaxe (Standard SQL) :
SELECT * FROM `[PROJET].[DATASET].[TABLE]`

-- Exemple de requête
SELECT
  SUM(total_amount)
FROM
  `ideo-project-prod.sales_eu.invoices_2025`
WHERE
  country = 'FR'

BigQuery est un DWH "Schema-on-Write". Le Schéma (la structure) de la table est défini (strict) lors de la création de la table.

Types (Extraits)

Mode (Contrainte)

• NULLABLE (Défaut) : La colonne peut contenir NULL.
• REQUIRED : La colonne ne peut pas contenir NULL. (NOT NULL).

La force de BigQuery (comparé aux DWH SQL traditionnels) est sa capacité à gérer nativement des données dénormalisées (JSON/Protobuf) via les types STRUCT et ARRAY.

STRUCT (Objet)

Un STRUCT est un "conteneur" (un objet) qui regroupe des champs.

ARRAY (Répété)

Un ARRAY est une liste (un champ "répété").

Exemple (Schéma Dénormalisé)

Au lieu de 3 tables (commandes, lignes_commande, client), on peut tout stocker dans 1 table (commandes) :

Table: 'commandes'
[
  {
    "commande_id": "1001",
    "date": "2025-01-15",
    
    "client": { (STRUCT)
      "nom": "Alice",
      "email": "alice@mail.com"
    },
    
    "lignes": [ (ARRAY de STRUCTs)
      { "produit_id": "A10", "quantite": 2, "prix": 10.00 },
      { "produit_id": "B20", "quantite": 1, "prix": 50.00 }
    ]
  },
  ...
]

Avantage : Évite les JOIN coûteux (les données sont pré-jointes).
Inconvénient : Nécessite UNNEST (6.2) pour requêter.

L'architecture de BigQuery (comme Snowflake) sépare totalement le Stockage (Données) et le Calcul (Compute).

[ COUCHE 1 : STOCKAGE (Colossus) ]
  (Facturé au Go/mois)
  (Stocke les tables (format Capacitor))
  
  (Séparation physique/réseau)
  
[ COUCHE 2 : COMPUTE (Dremel) ]
  (Facturé au To scanné (On-Demand)
   OU au Slot/heure (Reservations))

Avantages (Similaire à Snowflake 1.2)

• Scalabilité Indépendante : Vous pouvez stocker 10 Pétaoctets (Stockage) sans payer pour le Calcul (Compute), ou lancer une requête massive (Compute) sur 10 Ko (Stockage).
• Isolation : (Contrairement à Snowflake) Le modèle "On-Demand" (4.2) est "multi-tenant" (partagé). Pour une isolation (Workload) totale, il faut utiliser des Reservations (4.3).

BigQuery (comme tous les DWH analytiques) utilise un stockage colonnaire.

Capacitor : C'est le format de stockage (successeur de Parquet/Dremel) utilisé par BigQuery (sur Colossus).

Ligne (OLTP) vs Colonne (OLAP)

(Ligne 1) [ID_A | Nom_A | Ville_A]
(Ligne 2) [ID_B | Nom_B | Ville_B]
(Ligne 3) [ID_C | Nom_C | Ville_C]

(Fichier Col_ID)   [ID_A | ID_B | ID_C]
(Fichier Col_Nom)  [Nom_A | Nom_B | Nom_C]
(Fichier Col_Ville) [Ville_A | Ville_B | Ville_C]
(Fichier Col_Age)  [Age_A | Age_B | Age_C]

Le Time Travel (Voyage Temporel) (similaire à Snowflake) permet de requêter l'état d'une table tel qu'il était dans le passé.

Fonctionnement : BigQuery (stockage immuable) conserve un historique (jusqu'à 7 jours par défaut) des données (avant UPDATE/DELETE).

Syntaxe (FOR SYSTEM_TIME AS OF)

-- (Scénario : Un 'DELETE FROM users' a été lancé
--  par erreur à 14:00:00)

-- 1. Restaurer la table (complète)
-- (Créer une copie 'backup' de la table
--  telle qu'elle était à 13:59:00)
CREATE TABLE users_backup AS
SELECT *
FROM users
FOR SYSTEM_TIME AS OF '2025-11-10 13:59:00'

-- 2. Requêter les données (il y a 1h)
SELECT *
FROM users
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

Snapshots (Copies)

Si 7 jours ne suffisent pas, vous pouvez créer un SNAPSHOT (instantané) (zéro-copie, métadonnées) d'une table pour la conserver (coût de stockage).

Un Slot (Emplacement) est l'unité de calcul (Compute) de BigQuery (Dremel).

C'est une unité virtuelle (abstraite) de CPU, RAM, et Réseau (IO) nécessaire pour exécuter une "étape" (stage) d'une requête SQL.

Fonctionnement (Exécution Parallèle)

(Requête: "SELECT AVG(col) FROM table GROUP BY ...")
   │
   ▼
[ Dremel (Optimizer) ]
   │ (Découpe en "stages")
   │
   ├─► [ Stage 1 (Read) ] -> (Slot 1, Slot 2, ...)
   ├─► [ Stage 2 (Shuffle)] -> (Slot 500, Slot 501, ...)
   └─► [ Stage 3 (Agg)  ] -> (Slot 1000)

BigQuery (Dremel) parallélise massivement la requête en la distribuant à des milliers de Slots.

C'est le modèle de facturation (Compute) par défaut de BigQuery.

Fonctionnement (Pool Partagé)

Vous ne payez pas pour les "Slots" (4.1). Vous payez par Téraoctet (To) scanné (lu) (ex: ~$6.00 / To).

Votre requête est exécutée sur un pool "partagé (shared)" massif de Slots (ex: 2000-3000 Slots) géré par Google (Fair Use).

• Avantage : Simple (pay-as-you-go), rapide (accès à un pool immense pour les requêtes "spiky").
• Inconvénient : Coût imprévisible (une mauvaise requête SELECT * peut coûter 100$).
• Inconvénient : Performance non garantie ("Fair Use" - si les voisins sont bruyants, vos 2000 slots peuvent être lents).

C'est le modèle de facturation (Compute) alternatif. Vous ne payez plus au "To scanné". Vous achetez (réservez) une capacité de Slots (dédiés), facturée au temps (heure/seconde).

Types de Réservations

• Flat-Rate (Fixe) : (Engagement) Vous achetez N Slots (ex: 500) pour une longue durée (ex: 1 an). (Coût prévisible, le moins cher).
• Flex Slots : (Élastique) Vous achetez N Slots (ex: 1000) pour une courte durée (ex: 60 secondes minimum). (Idéal pour les pics ETL de nuit).

• Avantage : Coût prévisible (le prix est fixe, que vous scanniez 1 To ou 1 Pétaoctet).
• Avantage : Performance garantie (vous avez vos 500 Slots dédiés, pas de "voisins").
• Inconvénient : Coûteux si inutilisé.

L'ingestion "Batch" (par lots) est la méthode standard (et la moins chère) pour charger des données.

Coût (Gratuit)

Le chargement (Load) de données (depuis GCS ou local) dans BigQuery est GRATUIT. (Contrairement à l'ingestion "Streaming" (5.2)).

Formats Recommandés

BigQuery peut charger du CSV, JSON... mais les formats colonnaires (auto-descriptifs) sont fortement recommandés :

• Parquet (Standard Spark/Hadoop)
• Avro (Gère l'évolution de schéma)

Exemple (CLI bq)

# Charger un fichier Parquet (depuis GCS)
# (Auto-détecte le schéma)
bq load --source_format=PARQUET \
    mon_dataset.ma_table \
    gs://mon-bucket/data/fichier.parquet
    
# Charger un CSV (en spécifiant le schéma)
bq load --source_format=CSV --skip_leading_rows=1 \
    mon_dataset.ma_table \
    gs://mon-bucket/data/fichier.csv \
    nom:STRING,age:INT64

L'ingestion "Streaming" est la méthode temps réel. Elle est utilisée pour insérer des données (ligne par ligne) immédiatement (ex: logs d'application, événements IoT).

API (tabledata.insertAll)

Le streaming se fait via un appel API (HTTP POST) (généralement via les SDKs GCP).

Coût : Le streaming est PAYANT (facturé au $/Go inséré).

// (Exemple (pseudo-code) Python SDK)
client = bigquery.Client()
table_ref = client.dataset("mon_dataset").table("ma_table")

rows_to_insert = [
    {"nom": "Alice", "age": 30},
    {"nom": "Bob", "age": 31},
]

# (Appel API HTTP)
errors = client.insert_rows_json(table_ref, rows_to_insert)

if errors == []:
    print("OK")

Une Table Externe (Fédérée) est une "vue" (Read-Only) qui permet à BigQuery de requêter des données qui se trouvent à l'extérieur de BigQuery (ex: dans GCS, Google Drive), sans les ingérer.

-- (Création d'une table externe pointant vers GCS)
CREATE EXTERNAL TABLE mon_dataset.logs_externes
OPTIONS (
  format = 'CSV',
  uris = ['gs://mon-bucket/logs/*.csv']
);

-- (Requête)
SELECT * FROM mon_dataset.logs_externes LIMIT 10;

• Avantage : Permet de "tester" (explorer) des données (Data Lake) sans payer le coût de stockage BQ.
• Inconvénient : Performance très faible (BigQuery doit lire les fichiers GCS à chaque requête, sans l'optimisation "Capacitor").

BigQuery supporte deux dialectes SQL :

• Legacy SQL (SQL Historique) : (Obsolète) L'ancien dialecte (syntaxe [projet:dataset.table]). À ne plus utiliser.
• Standard SQL (Google SQL) : (Défaut) Le dialecte moderne, conforme à la norme SQL 2011.

(Pour activer : #standardSQL en haut de la requête, ou (défaut) décocher "Use Legacy SQL" dans l'UI).

Syntaxe (Standard SQL)

# (Utilise les backticks ` ` (pas ' ou "))
SELECT
    t1.colonneA,
    SUM(t1.colonneB) AS total
FROM
    `mon-projet.mon_dataset.ma_table` AS t1
JOIN
    `mon-projet.mon_dataset.autre_table` AS t2
    ON t1.id = t2.id
WHERE
    t1.date > '2025-01-01'
GROUP BY
    1  -- (Group by 'colonneA')
ORDER BY
    total DESC
LIMIT 100

CTE (Common Table Expressions) - WITH

L'utilisation de WITH (CTEs) est la meilleure pratique pour organiser des requêtes complexes.

WITH
  etape_1 AS (
    SELECT ... FROM `table_a`
  ),
  etape_2 AS (
    SELECT ... FROM `table_b`
  )
SELECT
  e1.col, e2.col
FROM etape_1 AS e1
JOIN etape_2 AS e2 ON e1.id = e2.id

Problème : Comment requêter une colonne ARRAY (2.3) (une liste) ?

La Fonction UNNEST() (Aplatir)

UNNEST() est l'opérateur (similaire à FLATTEN) qui prend un ARRAY et le transforme en lignes (Rows).

Table "Commandes" (1 Ligne) :
{
  "commande_id": "1001",
  "lignes": [
    { "produit": "A", "qte": 2 },
    { "produit": "B", "qte": 1 }
  ]
}

Requête (UNNEST)

SELECT
    commande_id,
    
    # (Accéder à la ligne 'unnestée')
    ligne.produit,
    ligne.qte
    
FROM
    `ma_table.commandes`
    
# (Le "JOIN" spécial pour "dénormaliser" (aplatir) l'ARRAY)
CROSS JOIN
    UNNEST(lignes) AS ligne

Résultat (2 Lignes)

commande_id | produit | qte
----------------------------
1001        | A       | 2
1001        | B       | 1

BigQuery permet de créer des fonctions personnalisées (UDFs) (réutilisables) directement en SQL ou en JavaScript.

-- 1. Définir la fonction (temporaire)
CREATE TEMP FUNCTION multiplier_par_deux(x INT64) AS (
  x * 2
);

-- 2. L'utiliser
SELECT
  valeur,
  multiplier_par_deux(valeur) AS double
FROM
  ma_table

CREATE TEMP FUNCTION extraire_email(str STRING)
RETURNS STRING
LANGUAGE js -- (Langage = JavaScript)
AS r"""
  // (Code JS)
  try {
    let email = str.match(/[\w\.-]+@[\w\.-]+/);
    return email[0];
  } catch (e) {
    return null;
  }
""";

SELECT
  texte_log,
  extraire_email(texte_log) AS email
FROM
  mes_logs

C'est la principale technique d'optimisation (Coût & Vitesse) de BigQuery.

Le Partitionnement (Partitioning) est une "division" (physique) de la table en "sous-tables" (partitions) basée sur une colonne (généralement la Date).

Problème (Table 5 To, 3 ans d'historique)

-- (Requête pour hier : 10 Go)
SELECT * FROM ma_table_5To WHERE date = '2025-11-09'
-- (BQ (On-Demand) scanne les 5 To ! (Coût $$$))

Solution (Partitionnement par Temps)

-- (Créer la table partitionnée par jour)
CREATE TABLE ma_table_5To ( ... )
PARTITION BY DATE(timestamp_col)

Flux (Requête sur table partitionnée) :

SELECT * FROM ma_table_5To WHERE date(timestamp_col) = '2025-11-09'

Résultat : BigQuery (Dremel) ne scanne que la partition "2025-11-09" (10 Go). (Coût réduit de 99%).

Types

• Par Temps (Défaut) : (Jour/Heure/Mois/An) (_PARTITIONTIME).
• Par Colonne (Entier) : (ex: customer_id).

Le Clustering (similaire à Snowflake 3.3 ou Z-Order 3.3) est une optimisation "secondaire" (utilisée après le Partitionnement).

Le Clustering trie (sort) physiquement les données à l'intérieur d'une partition, en se basant sur une (ou plusieurs) colonne(s) (ex: region, user_id).

Exemple (Partition + Cluster)

CREATE TABLE ma_table ( ... )
PARTITION BY DATE(date_commande)
CLUSTER BY region

Flux (Requête)

SELECT * FROM ma_table
WHERE date_commande = '2025-11-09'
  AND region = 'France'

1. Étape 1 (Partitioning) : BQ ne lit que la partition "2025-11-09".
2. Étape 2 (Clustering) : À l'intérieur de cette partition, BQ (grâce au tri) ne lit que les blocs (micro-partitions) où region = 'France'.

Résultat : Optimisation (Coût/Vitesse) encore meilleure.