Superset sur Snowflake

Construire les trois tableaux de bord opérationnels côté source : connexion, jeux de données, graphiques et assemblage.

Ce guide explique comment créer les trois tableaux de bord dans Apache Superset sur http://localhost:8088 (admin / admin).

Ordre des opérations :

Démarrer Superset et enregistrer la connexion Snowflake
Créer tous les jeux de données, sous forme de requêtes SQL enregistrées et nommées
Construire les graphiques et assembler les tableaux de bord

Étape 1 : démarrer Superset et se connecter à Snowflake

Démarrer Superset

L’image Superset est personnalisée pour inclure les pilotes Snowflake et ClickHouse. Lors de la première exécution, utilisez --build afin que Docker la construise :

cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake/superset"
source ../.env
docker compose up -d --build

Enregistrer automatiquement la connexion à la base

Depuis le répertoire superset/, exécutez :

source ../.env && ./init_superset.sh

Le script attend que Superset soit prêt, puis enregistre automatiquement NYC Taxi — Snowflake (Source). Résultat attendu :

>>> Superset is up.
>>> Authenticated.
>>> CSRF token obtained.
>>> Registering: NYC Taxi — Snowflake (Source)
    Registered successfully.

Enregistrer manuellement la connexion à la base

Si vous préférez utiliser l’interface :

Ouvrez Settings → Database Connections → + Database.
Sélectionnez Snowflake.
Renseignez l’URI SQLAlchemy. Encodez dans l’URL tous les caractères spéciaux du mot de passe (# → %23, ! → %21, @ → %40, etc.) :

snowflake://<USER>:<URL_ENCODED_PASSWORD>@<SNOWFLAKE_ORG>-<SNOWFLAKE_ACCOUNT>/NYC_TAXI_DB/ANALYTICS?warehouse=ANALYTICS_WH&role=ANALYST_ROLE

Définissez Display Name sur NYC Taxi — Snowflake (Source).
Sous Advanced → SQL Lab, activez Allow this database to be explored et Allow DML.
Cliquez sur Test Connection : le message "Connection looks good!" doit apparaître.
Cliquez sur Connect.

Étape 2 : créer tous les jeux de données

Tous les graphiques utilisent des jeux de données virtuels : des requêtes SQL enregistrées sous un nom.

Pour créer chaque jeu de données :

Ouvrez Datasets → + Dataset.
Sélectionnez la base NYC Taxi — Snowflake (Source).
Cliquez sur Create dataset from SQL query, puis collez le SQL ci-dessous.
Enregistrez-le sous le nom indiqué.
Après l’enregistrement, ouvrez Datasets → icône en forme de crayon → onglet Columns → "Sync columns from source" → Save. Sans cette étape, le générateur de graphiques affiche zéro colonne.

Jeux de données du tableau de bord 1

`ops_hourly_revenue` — Chiffre d’affaires horaire par arrondissement

Schéma : ANALYTICS

SELECT
    DATE_TRUNC('hour', pickup_at)                      AS hour_bucket,
    pickup_borough,
    COUNT(*)                                           AS trip_count,
    SUM(total_amount_usd)                              AS total_revenue,
    AVG(tip_amount_usd / NULLIF(fare_amount_usd, 0))  AS avg_tip_rate,
    AVG(trip_distance_miles)                           AS avg_distance_miles
FROM ANALYTICS.FACT_TRIPS
WHERE pickup_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
  AND pickup_borough IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, total_revenue DESC

`ops_zone_agg` — Agrégats par zone

Schéma : ANALYTICS

SELECT
    hour_bucket,
    zone_id,
    trips,
    revenue,
    avg_distance
FROM ANALYTICS.AGG_HOURLY_ZONE_TRIPS
WHERE hour_bucket >= DATEADD('day', -7, CURRENT_TIMESTAMP())

`ops_payment_split` — Répartition par type de paiement

Schéma : ANALYTICS

SELECT
    payment_type,
    COUNT(*)              AS trip_count,
    SUM(total_amount_usd) AS total_revenue
FROM ANALYTICS.FACT_TRIPS
WHERE pickup_at >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP BY 1

Jeux de données du tableau de bord 2

`exec_rolling_avg` — Moyennes mobiles sur 7 jours

Schéma : ANALYTICS

SELECT
    pickup_at::DATE                                          AS trip_date,
    COUNT(*)                                                 AS daily_trip_count,
    AVG(trip_distance_miles)                                 AS daily_avg_distance,
    AVG(AVG(trip_distance_miles)) OVER (
        ORDER BY pickup_at::DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                                                        AS rolling_7d_avg_distance,
    SUM(total_amount_usd)                                    AS daily_revenue,
    SUM(SUM(total_amount_usd)) OVER (
        ORDER BY pickup_at::DATE
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                                                        AS rolling_7d_revenue
FROM ANALYTICS.FACT_TRIPS
GROUP BY 1
ORDER BY 1 DESC
LIMIT 365

`exec_top_trips` — Les 10 trajets les plus chers par arrondissement

Schéma : ANALYTICS

Utilise le QUALIFY propre à Snowflake, un défi essentiel de la migration. La réécriture ClickHouse exige une sous-requête.

SELECT
    trip_id,
    pickup_at,
    pickup_borough,
    total_amount_usd,
    tip_amount_usd,
    trip_distance_miles,
    ROW_NUMBER() OVER (
        PARTITION BY pickup_borough
        ORDER BY total_amount_usd DESC
    ) AS rank_in_borough
FROM ANALYTICS.FACT_TRIPS
WHERE pickup_at::DATE = CURRENT_DATE() - 1
QUALIFY rank_in_borough <= 10
ORDER BY pickup_borough, rank_in_borough

`exec_surge` — Incidence de la tarification dynamique

Schéma : ANALYTICS

SELECT
    CASE
        WHEN surge_multiplier >= 2.0 THEN 'High Surge (2x+)'
        WHEN surge_multiplier >= 1.5 THEN 'Medium Surge (1.5–2x)'
        WHEN surge_multiplier > 1.0  THEN 'Low Surge (1–1.5x)'
        ELSE 'No Surge (1x)'
    END                                    AS surge_category,
    COUNT(*)                               AS trip_count,
    ROUND(AVG(total_amount_usd), 2)        AS avg_total_fare,
    ROUND(AVG(fare_amount_usd), 2)         AS avg_base_fare,
    ROUND(AVG(surge_multiplier), 2)        AS avg_surge
FROM ANALYTICS.FACT_TRIPS
WHERE surge_multiplier IS NOT NULL
GROUP BY 1
ORDER BY avg_surge DESC

Jeux de données du tableau de bord 3

`dqa_rating_dist` — Répartition des notes des conducteurs

Schéma : RAW ← modifiez cette valeur lors de la création du jeu de données

Interroge directement RAW.TRIPS_RAW à l’aide de la syntaxe de chemin avec deux-points de VARIANT. Cette requête est volontairement lente et sert de cible au benchmark ClickHouse.

SELECT
    ROUND(TRIP_METADATA:driver.rating::FLOAT, 1)                           AS rating_bucket,
    COUNT(*)                                                               AS trip_count,
    ROUND(AVG(TOTAL_AMOUNT), 2)                                            AS avg_fare,
    ROUND(AVG(DATEDIFF('minute', PICKUP_DATETIME, DROPOFF_DATETIME)), 1)   AS avg_duration_minutes
FROM RAW.TRIPS_RAW
WHERE TRIP_METADATA:driver IS NOT NULL
  AND TRIP_METADATA:driver.rating IS NOT NULL
GROUP BY 1
ORDER BY 1

`dqa_vehicle` — Chiffre d’affaires par type de véhicule

Schéma : ANALYTICS

SELECT
    vehicle_type,
    COUNT(*)                 AS trip_count,
    SUM(total_amount_usd)    AS total_revenue,
    AVG(total_amount_usd)    AS avg_fare,
    AVG(trip_distance_miles) AS avg_distance
FROM ANALYTICS.FACT_TRIPS
WHERE vehicle_type IS NOT NULL
GROUP BY 1
ORDER BY total_revenue DESC

`dqa_traffic` — Niveau de circulation et durée du trajet

Schéma : ANALYTICS

SELECT
    traffic_level,
    COUNT(*)                   AS trip_count,
    AVG(duration_minutes)      AS avg_duration_minutes,
    AVG(trip_distance_miles)   AS avg_distance_miles,
    AVG(total_amount_usd)      AS avg_fare
FROM ANALYTICS.FACT_TRIPS
WHERE traffic_level IS NOT NULL
GROUP BY 1
ORDER BY avg_duration_minutes DESC

`dqa_platform` — Tendances par plateforme d’application

Schéma : ANALYTICS

SELECT
    pickup_at::DATE       AS trip_date,
    app_platform,
    COUNT(*)              AS trip_count,
    AVG(surge_multiplier) AS avg_surge
FROM ANALYTICS.FACT_TRIPS
WHERE app_platform IS NOT NULL
  AND pickup_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1 DESC

Étape 3 : construire les tableaux de bord

Les dix jeux de données sont prêts. Créez les graphiques et ajoutez-les aux tableaux de bord.

Tableau de bord 1 : Operations Command Center

Objectif : fournir une vue opérationnelle en temps réel des sept derniers jours. Dans la deuxième partie, c’est le premier tableau de bord que les partenaires redirigent vers ClickHouse.

Créer le tableau de bord :

Dashboards → + Dashboard
Titre : Operations Command Center
Actualisation automatique toutes les 15 minutes avec ··· → Edit dashboard → Auto-refresh

Graphique 1 : Trips per Hour (courbe)

Chart type : Line Chart
Dataset : ops_hourly_revenue
X-axis : hour_bucket
Metrics : SUM(trip_count)
Series : pickup_borough
Title : Trips per Hour — Last 7 Days

Graphique 2 : Revenue by Borough (histogramme)

Chart type : Bar Chart
Dataset : ops_hourly_revenue
X-axis : pickup_borough
Metrics : SUM(total_revenue)
Sort : décroissant selon la métrique
Title : Total Revenue by Borough — Last 7 Days

Graphique 3 : Payment Type Split (secteurs)

Chart type : Pie Chart
Dataset : ops_payment_split
Dimension : payment_type
Metric : SUM(trip_count)
Show labels : activé
Title : Trip Count by Payment Type

Graphique 4 : Total Trips (grand nombre)

Chart type : Big Number with Trendline
Dataset : ops_hourly_revenue
Metric : SUM(trip_count)
Title : Total Trips (Last 7 Days)

Graphique 5 : Borough Performance Summary (tableau)

Chart type : Table
Dataset : ops_hourly_revenue
Columns : pickup_borough, SUM(trip_count), SUM(total_revenue), AVG(avg_tip_rate)
Row limit : 10
Sort : SUM(total_revenue) décroissant
Title : Borough Performance Summary

Disposition :

[ Total Trips — Big Number ]  [ Total Revenue — Big Number (add 2nd)  ]
[ Trips per Hour — Line chart (full width)                            ]
[ Revenue by Borough — Bar ]  [ Payment Type Split — Pie             ]
[ Borough Performance — Table (full width)                            ]

Tableau de bord 2 : Executive Weekly Report

Objectif : fournir une vue stratégique pour la revue hebdomadaire de l’activité. Il met en évidence les fonctions de fenêtrage et la syntaxe propre à Snowflake, QUALIFY, qu’il faut réécrire dans ClickHouse.

Créer le tableau de bord :

Dashboards → + Dashboard
Titre : Executive Weekly Report
Actualisation automatique : 1 heure

Graphique 6 : Rolling 7-Day Revenue Trend (courbe)

Chart type : Line Chart
Dataset : exec_rolling_avg
X-axis : trip_date
Metrics : MAX(daily_revenue), MAX(rolling_7d_revenue)
Title : Daily Revenue with 7-Day Rolling Average

Graphique 7a : Daily Trip Volume (grand nombre)

Chart type : Big Number with Trendline
Dataset : exec_rolling_avg
Metric : MAX(daily_trip_count)
Title : Daily Trip Volume (Last Year)

Graphique 7b : Rolling 7-Day Average Distance (courbe)

Chart type : Line Chart
Dataset : exec_rolling_avg
X-axis : trip_date
Metrics : MAX(rolling_7d_avg_distance)
Title : Rolling 7-Day Average Distance (miles)

Graphique 8 : Top 10 Trips per Borough (tableau)

Chart type : Table
Dataset : exec_top_trips
Query Mode : RAW RECORDS ← important : le jeu de données utilise QUALIFY, Superset ne doit donc pas l’agréger à nouveau
Columns : pickup_borough, rank_in_borough, total_amount_usd, tip_amount_usd, trip_distance_miles, pickup_at
Sort By : total_amount_usd décroissant
Row limit : 60
Title : Top 10 Trips per Borough — Yesterday
Note : utilise QUALIFY, propre à Snowflake, et doit être réécrit sous forme de sous-requête pour ClickHouse

Graphique 9 : Surge Pricing Breakdown (graphique combiné)

Chart type : Mixed Chart ← utilisez ce type et non Bar Chart, qui ne prend pas en charge un axe secondaire
Dataset : exec_surge
X-axis : surge_category
Query A — Bar : métrique SUM(trip_count), libellé Trip Count
Query B — Line : métrique MAX(avg_total_fare), libellé Avg Total Fare, Y-axis : Right
Sort : SUM(trip_count) décroissant
Title : Trip Volume and Average Fare by Surge Category

Graphique 10 : Surge Distribution (secteurs)

Chart type : Pie Chart
Dataset : exec_surge
Dimension : surge_category
Metric : SUM(trip_count)
Title : Surge Pricing Distribution

Disposition :

[ Rolling Revenue — Line chart (full width)                              ]
[ Daily Trip Volume — Big Number (50%) ]  [ Avg Distance — Line (50%)   ]
[ Top 10 Trips — Table (60%) ]  [ Surge Distribution — Pie (40%)        ]
[ Surge Breakdown — Bar chart (full width)                               ]

Tableau de bord 3 : Driver & Quality Analytics

Objectif : analyser en profondeur les performances des conducteurs et la qualité des trajets. Il s’agit volontairement du tableau de bord le plus lent : il interroge directement RAW.TRIPS_RAW avec un accès VARIANT. Consignez ici la durée de la requête afin d’établir la valeur de référence du benchmark de performances ClickHouse de la deuxième partie.

Créer le tableau de bord :

Dashboards → + Dashboard
Titre : Driver & Quality Analytics
Actualisation automatique : 1 heure

Graphique 11 : Trip Count by Driver Rating (histogramme)

Chart type : Bar Chart
Dataset : dqa_rating_dist
X-axis : rating_bucket
Metrics : SUM(trip_count)
Title : Trip Count by Driver Rating
Note : parcourt RAW.TRIPS_RAW avec un accès VARIANT ; comparez la durée de la requête à celle de ClickHouse

Graphique 12 : Average Fare by Rating (courbe)

Chart type : Line Chart
Dataset : dqa_rating_dist
X-axis : rating_bucket
Metrics : MAX(avg_fare)
Title : Average Fare by Driver Rating

Graphique 13 : Revenue by Vehicle Type (histogramme horizontal)

Chart type : Bar Chart (horizontal)
Dataset : dqa_vehicle
X-axis : vehicle_type
Metrics : SUM(total_revenue), SUM(trip_count) sur l’axe secondaire
Title : Revenue and Trip Count by Vehicle Type

Graphique 14 : Traffic Level Impact (histogramme)

Chart type : Bar Chart
Dataset : dqa_traffic
X-axis : traffic_level
Metrics : MAX(avg_duration_minutes), MAX(avg_distance_miles) sur l’axe secondaire
Title : Average Trip Duration and Distance by Traffic Level

Graphique 15 : Daily Trips by App Platform (courbe)

Chart type : Line Chart
Dataset : dqa_platform
X-axis : trip_date
Metrics : SUM(trip_count)
Series : app_platform
Title : Daily Trips by App Platform — Last 30 Days

Graphique 16 : Surge by Platform (tableau)

Chart type : Table
Dataset : dqa_platform
Columns : app_platform, SUM(trip_count), AVG(avg_surge)
Row limit : 10
Title : Surge by Platform

Disposition :

[ Trip Count by Rating — Bar ]  [ Avg Fare by Rating — Line            ]
[ Revenue by Vehicle Type — Horizontal bar (full width)                ]
[ Traffic Level Impact — Bar (50%) ]  [ Surge by Platform — Table (50%)]
[ Daily Trips by Platform — Line chart (full width)                    ]

Étape 4 : vérifier

Ouvrez chaque tableau de bord et vérifiez que tous les graphiques se chargent sans erreur.
Pour le troisième tableau de bord, relevez la durée d’exécution de la requête dqa_rating_dist dans Snowflake UI → Activity → Query History, puis conservez-la comme référence pour le benchmark de migration.

Étape 5 : exporter pour réutilisation

Une fois les tableaux de bord terminés, exportez-les afin de pouvoir les importer automatiquement lors des prochaines exécutions :

Ouvrez chaque tableau de bord → ··· → Export, ce qui enregistre un fichier .zip.
Placez les fichiers dans superset/dashboards/ :
- 01_operations_command_center.zip
- 02_executive_weekly_report.zip
- 03_driver_quality_analytics.zip
Relancez ./init_superset.sh : le script les importera automatiquement lors des prochaines préparations.

Important — identifiants fictifs dans les fichiers ZIP enregistrés.

Pour chaque fichier *.zip enregistré, les valeurs de connexion à la base sont masquées dans databases/*.yaml :
sqlalchemy_uri: snowflake://LAB_USER:XXXXXXXXXX@MYORG-MYACCOUNT/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH
Importation automatique (./init_superset.sh) — fonctionne sans modification. Le script enregistre la véritable connexion Snowflake à partir de .env avant l’importation, puis réapplique l’URI correcte après chaque importation, dans _update_db de init_superset.sh. Les valeurs fictives sont donc remplacées par vos véritables identifiants.

Importation manuelle dans l’interface Superset — la base importée est créée avec l’URI fictive et ne peut pas se connecter. Après l’importation, ouvrez Settings → Database Connections → Edit, puis remplacez sqlalchemy_uri par votre véritable URI Snowflake, par exemple snowflake://<USER>:<PASSWORD>@<ORG>-<ACCOUNT>/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH.

Nouvelle exportation de vos propres tableaux de bord — Superset inscrit votre localisateur de compte et votre nom d’utilisateur dans databases/*.yaml. Avant d’ajouter les fichiers ZIP réexportés au dépôt, remplacez ces valeurs par MYORG-MYACCOUNT / LAB_USER afin de ne pas divulguer vos identifiants de compte dans l’historique git.

Sur cette page