Module 8 : Bonnes Pratiques et Optimisation

Architecture Medallion (Bronze/Silver/Gold)

L'architecture en couches est la référence pour organiser les données dans un Data Lake :

+------------------------------------------------------------------+
|                    ARCHITECTURE MEDALLION                         |
+------------------------------------------------------------------+
|                                                                   |
|   BRONZE (Raw)          SILVER (Cleaned)       GOLD (Business)   |
|   +-------------+       +-------------+        +-------------+    |
|   | Données     |  -->  | Données     |  -->   | Agrégations |    |
|   | brutes      |       | nettoyées   |        | métriques   |    |
|   | (as-is)     |       | validees    |        | KPIs        |    |
|   +-------------+       +-------------+        +-------------+    |
|                                                                   |
|   Format: JSON/CSV      Format: Parquet       Format: Delta/      |
|   Partitions: date      Partitions: optimisé  SQL Pool            |
|                                                                   |
+------------------------------------------------------------------+
            

Exemple de structure

/datalake/
    /bronze/
        /erp/
            /orders/2024/01/01/orders_20240101.json
            /customers/2024/01/01/customers_20240101.csv
        /crm/
            /contacts/...
    /silver/
        /sales/
            /orders/year=2024/month=01/part-00000.parquet
            /customers/part-00000.parquet
    /gold/
        /analytics/
            /daily_sales/
            /customer_segments/
            /product_performance/

Optimisation SQL Pool Dédié

1. Choix de la distribution

-- Analyser la distribution actuelle
SELECT
    t.name AS table_name,
    d.distribution_policy_desc,
    d.total_space_MB,
    d.rows_per_distribution_MIN,
    d.rows_per_distribution_MAX,
    d.skew_percentage
FROM
(
    SELECT
        object_id,
        distribution_policy_desc,
        SUM(reserved_space_MB) AS total_space_MB,
        MIN(row_count) AS rows_per_distribution_MIN,
        MAX(row_count) AS rows_per_distribution_MAX,
        100.0 * (MAX(row_count) - MIN(row_count)) / NULLIF(MAX(row_count), 0) AS skew_percentage
    FROM sys.dm_pdw_nodes_db_partition_stats
    GROUP BY object_id, distribution_policy_desc
) d
JOIN sys.tables t ON d.object_id = t.object_id
ORDER BY skew_percentage DESC;

2. Index et statistiques

-- Clustered Columnstore Index (par défaut, optimal pour analytique)
CREATE TABLE dbo.FactVentes (...)
WITH (CLUSTERED COLUMNSTORE INDEX);

-- Heap pour le staging rapide
CREATE TABLE dbo.StagingVentes (...)
WITH (HEAP);

-- Créer des statistiques
CREATE STATISTICS stats_client ON dbo.FactVentes(ClientID);
CREATE STATISTICS stats_date ON dbo.FactVentes(DateVente);
CREATE STATISTICS stats_multi ON dbo.FactVentes(ClientID, DateVente);

-- Mettre à jour les statistiques
UPDATE STATISTICS dbo.FactVentes;

3. Gestion des ressources

-- Voir les DWU actuels
SELECT
    db.name,
    ds.edition,
    ds.service_objective
FROM sys.database_service_objectives ds
JOIN sys.databases db ON ds.database_id = db.database_id;

-- Classes de ressources
-- smallrc: 3% des ressources (défaut)
-- mediumrc: 10%
-- largerc: 22%
-- xlargerc: 70%

-- Assigner un utilisateur a une classe
EXEC sp_addrolemember 'largerc', 'ETLUser';

-- Vérifier l'allocation
SELECT * FROM sys.dm_pdw_exec_sessions
WHERE status = 'Active';

4. Optimisation des requêtes

-- Analyser le plan d'exécution
EXPLAIN
SELECT c.Nom, SUM(v.Montant)
FROM FactVentes v
JOIN DimClient c ON v.ClientID = c.ClientID
GROUP BY c.Nom;

-- Identifier les requêtes lentes
SELECT TOP 20
    request_id,
    submit_time,
    total_elapsed_time / 1000.0 AS duration_sec,
    [label],
    command
FROM sys.dm_pdw_exec_requests
WHERE status = 'Completed'
ORDER BY total_elapsed_time DESC;

Optimisation SQL Serverless

1. Format des fichiers

Format	Performance	Coût	Recommandation
Parquet	Excellent	Bas	Préféré
Delta	Excellent	Bas	Si mises à jour
CSV	Faible	Élevé	Éviter si possible
JSON	Faible	Élevé	Bronze layer uniquement

2. Partitionnement

-- Structure de fichiers partitionnés
/data/ventes/year=2024/month=01/day=01/*.parquet
/data/ventes/year=2024/month=01/day=02/*.parquet

-- Requête avec pruning automatique
SELECT *
FROM OPENROWSET(
    BULK 'https://storage.dfs.core.windows.net/data/ventes/year=*/month=*/day=*/*.parquet',
    FORMAT = 'PARQUET'
) AS ventes
WHERE ventes.filepath(1) = '2024'  -- Filtre sur l'année
  AND ventes.filepath(2) = '01';   -- Filtre sur le mois

3. Spécification du schéma

-- Définir le schéma explicitement (plus performant)
SELECT *
FROM OPENROWSET(
    BULK 'https://storage.dfs.core.windows.net/data/*.parquet',
    FORMAT = 'PARQUET'
)
WITH (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(18,2)
) AS orders;

Optimisation Spark

1. Configuration du cluster

# Configuration Spark recommandée
spark.conf.set("spark.sql.adaptive.enabled", "true")
spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", "true")
spark.conf.set("spark.sql.shuffle.partitions", "auto")

# Pour les jointures
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")

2. Bonnes pratiques PySpark

# BIEN: Operations colonnes natives
from pyspark.sql.functions import col, when, sum

df = df.withColumn("category",
    when(col("amount") > 1000, "High")
    .when(col("amount") > 100, "Medium")
    .otherwise("Low")
)

# MAUVAIS: UDF Python (lent)
@udf
def categorize(amount):
    if amount > 1000: return "High"
    elif amount > 100: return "Medium"
    else: return "Low"

# BIEN: Filtrer tôt
df_filtered = df.filter(col("date") >= "2024-01-01").select("id", "amount")

# MAUVAIS: Filtrer tard
df_all = df.select("*")  # Charge tout
df_filtered = df_all.filter(col("date") >= "2024-01-01")

# BIEN: Broadcast pour petites tables
from pyspark.sql.functions import broadcast
result = big_df.join(broadcast(small_df), "key")

# Cache si réutilisation
df.cache()  # ou df.persist(StorageLevel.MEMORY_AND_DISK)

3. Delta Lake

# Optimiser les fichiers Delta
spark.sql("OPTIMIZE delta.`/path/to/table`")

# Compacter les petits fichiers
spark.sql("OPTIMIZE delta.`/path/to/table` ZORDER BY (customer_id)")

# Nettoyer les anciennes versions
spark.sql("VACUUM delta.`/path/to/table` RETAIN 168 HOURS")

Gestion des coûts

SQL Pool Dédié

-- Script pour pause automatique (Azure Automation)
# PowerShell
$pool = Get-AzSynapseSqlPool -WorkspaceName "myworkspace" -Name "mypool"
if ($pool.Status -eq "Online") {
    Suspend-AzSynapseSqlPool -WorkspaceName "myworkspace" -Name "mypool"
}

SQL Serverless

Spark Pools

Monitoring et alertes

-- Requêtes en cours d'exécution
SELECT * FROM sys.dm_pdw_exec_requests
WHERE status NOT IN ('Completed', 'Failed', 'Cancelled');

-- Utilisation des ressources
SELECT * FROM sys.dm_pdw_nodes_resource_governor_resource_pools;

-- Attentes
SELECT * FROM sys.dm_pdw_waits WHERE state = 'Granted';

-- Configurer des alertes Azure Monitor
-- 1. Metric: DWU utilisation > 80%
-- 2. Metric: Nombre de connexions actives
-- 3. Log alert: Requêtes > 10 minutes

Checklist de performance