Collecter chaque heure la disponibilité des Vélib' en SQL

Objectif

Ce guide vous explique comment collecter toutes les heures la disponibilité de chaque station Vélib' de Paris depuis une API publique, en construire un historique propre et le modéliser avec des vues SQL prêtes pour Apache Superset. Vous connectez l'API avec une source HTTP REST, créez la table d'historique dans le Lakehouse Manager, datez et dédoublonnez chaque collecte avec le Data Processing Engine, puis modélisez l'historique avec des vues. Tout se passe dans l'interface de la Data Platform et en SQL : pas une ligne de Python.

L'API Vélib' ne donne que l'état actuel de chaque station. En l'interrogeant régulièrement, vous construisez l'historique qu'elle ne conserve pas, et c'est cet historique que vous analysez.

À la fin, vous disposerez de la table velib_raw, qui s'enrichit seule toutes les heures, et de cinq vues SQL construites dessus, prêtes à être interrogées depuis Apache Superset. La deuxième partie du guide, « Construire un dashboard de disponibilité des Vélib' dans Superset », les transforme en dashboard.

Prérequis

Pour suivre ce guide, vous devez :

• disposer d'un projet Data Platform. Ce guide utilise un projet nommé velib-availability.
• prévoir, pour l'étape 8, une application Apache Superset, facturée pour le temps où elle reste déployée : voir la tarification de la Data Platform. La collecte des données ne nécessite rien d'autre : l'API est publique et ne demande ni compte ni clé.

Avant de commencer, nous vous recommandons de suivre les guides de démarrage. Ce guide suppose que vous êtes à l'aise avec les principaux composants de la Data Platform.

Ce que vous allez construire

En pratique

Étape 1 : connecter l'API

Le jeu de données dispose d'un endpoint d'export qui renvoie toutes les stations en un seul appel :

https://opendata.paris.fr/api/explore/v2.1/catalog/datasets/velib-disponibilite-en-temps-reel/exports/json

L'endpoint records présenté dans l'onglet API de Paris Data renvoie un nombre limité de résultats par appel. L'endpoint d'export renvoie toutes les stations d'un coup : environ 1 500 (1 519 le 22 octobre 2026).

1. Ouvrez Connectors > Sources et créez une nouvelle source de type HTTP REST.

2. Nommez-la velib.

3. Dans Base URL, saisissez https://opendata.paris.fr/api/explore/v2.1/. Laissez SSL Verify activé et Authentication mode sur None, puis cliquez sur Connect.

   

4. Dans Endpoints to connect, cliquez sur Add an endpoint et renseignez l'endpoint :

   Paramètre	Valeur
   Method	GET
   Endpoint	/catalog/datasets/velib-disponibilite-en-temps-reel/exports/json
   Source object technical name, dans l'onglet Options	stations
   Pagination, dans l'onglet Options	None

5. Cliquez sur Confirm.

   

6. Cliquez sur Create.

   La source liste un endpoint, stations.json.

   

Chaque station est livrée avec sa disponibilité et sa description :

{
  "stationcode": "40001",
  "name": "Hôpital Mondor",
  "is_installed": "OUI",
  "capacity": 28,
  "numdocksavailable": 20,
  "numbikesavailable": 7,
  "mechanical": 3,
  "ebike": 4,
  "is_renting": "OUI",
  "is_returning": "OUI",
  "duedate": "2026-10-07T06:27:24+00:00",
  "coordonnees_geo": { "lon": 2.4537451531298, "lat": 48.798922410229 },
  "nom_arrondissement_communes": "Créteil",
  "code_insee_commune": "94028",
  "station_opening_hours": null
}

Étape 2 : vérifier les attributs

1. Ouvrez Connectors > Analyzer.
2. Sélectionnez la source velib, puis stations.json.
3. Cliquez sur Extract metadata.

L'Analyzer détecte les types sur un échantillon de 1 000 lignes. Vérifiez-les dès maintenant : une fois la table créée, vous ne pouvez plus changer le type de ses attributs.

Étape 3 : créer la table d'historique

Créer la table

1. Ouvrez Lakehouse Manager > Tables et cliquez sur New table > Create from a Connectors source.

2. Sélectionnez stations.json dans la source velib, puis cliquez sur Next.

   

3. Nommez la table velib_raw, conservez le dataset default_dataset et laissez les trois options activées : Automatically build the table, Automatically load the table once et Automatically generate Load action for later.

4. Cliquez sur Create.

   

   La table contient une ligne par station, soit environ 1 500 lignes. Les coordonnées imbriquées deviennent coordonnees_geo_lat et coordonnees_geo_lon.

   

Ajouter les colonnes de collecte

L'API n'indique pas quand vous avez collecté une station. Deux colonnes l'enregistrent :

1. Ouvrez la table velib_raw, puis son onglet Schema.

2. Cliquez sur New attribute, nommez-le collected_at, sélectionnez le type de données Datetime et cliquez sur Save. Faites de même pour collected_hour.

   

3. Cliquez sur Build : ajouter des attributs modifie la structure de la table.

   

Vider la table

Le premier chargement n'a pas d'heure de collecte. Videz la table avec une action Delete avant la première vraie collecte :

1. Ouvrez Data Processing Engine > Actions, cliquez sur New action et sélectionnez l'action Delete.

2. Dans Table list, déplacez velib_raw vers Table to empty.

3. Cliquez sur Create, puis exécutez l'action.

   

Dans l'Explorer, SELECT count(*) FROM velib_raw renvoie 0.

Étape 4 : dater chaque collecte

Créez une action SQL qui date les lignes tout juste chargées.

1. Dans Data Processing Engine > Actions, cliquez sur New action et sélectionnez l'action SQL.

2. Nommez-la Stamp collection time.

3. Dans le panneau Tables, cliquez sur l'icône de copie de velib_raw : une action SQL a besoin du nom complet catalog.schema.table, et le nom du catalogue est propre à votre projet.

4. Collez la requête ci-dessous, avec ce nom complet à la place de <catalog>.default_dataset.velib_raw :

   UPDATE <catalog>.default_dataset.velib_raw
   SET collected_at   = CAST(current_timestamp AT TIME ZONE 'UTC' AS timestamp(6)),
       collected_hour = date_trunc('hour', CAST(current_timestamp AT TIME ZONE 'UTC' AS timestamp(6)))
   WHERE collected_at IS NULL

5. Cliquez sur Validate. La validation est une exécution à blanc : elle vérifie la requête sans modifier la table.

6. Cliquez sur Create.

   

WHERE collected_at IS NULL ne date que les lignes de la collecte en cours : les collectes précédentes gardent leur heure. Les heures sont stockées en UTC, comme duedate.

Vérifier une première collecte

1. Exécutez l'action Load de velib_raw, générée à la création de la table.

2. Exécutez Stamp collection time.

3. Dans l'Explorer, exécutez :

   SELECT count(*) AS rows, count(collected_at) AS stamped, min(collected_at) AS first_collection, max(collected_at) AS last_collection
   FROM velib_raw

   Vous obtenez une ligne par station, toutes datées, avec une seule heure de collecte en UTC.

Étape 5 : garder une ligne par station et par heure

Quand les deux exécutions d'une heure réussissent, chaque station a deux lignes pour la même heure, avec des chiffres parfois différents. L'action Deduplicate garde une ligne par station et par heure : celle dont collected_at est le plus récent.

Si une exécution échoue entre le chargement et la datation, l'exécution suivante charge à nouveau les stations, et Stamp collection time date les deux chargements d'un coup, avec le même collected_at. L'action garde toujours une ligne par station et par heure, mais sans garantie sur laquelle des deux.

1. Dans Data Processing Engine > Actions, cliquez sur New action et sélectionnez l'action Deduplicate.

2. Renseignez l'action :

   Paramètre	Valeur
   Table	velib_raw de default_dataset
   Key column(s)	stationcode, collected_hour
   Row to keep	Most recent / oldest entry, puis Most recent, triée par la colonne collected_at
   Keep a backup of this table	Désactivé : le Time Travel conserve déjà l'état précédent

3. Cliquez sur Scan : il estime, sans rien modifier, combien de lignes la clé supprimerait. Sur une seule collecte, il ne trouve aucun doublon.

4. Cliquez sur Create. L'action s'appelle Deduplicate - velib_raw.

   

L'action Deduplicate relit toute la table à chaque exécution, et la table grossit d'environ 36 000 lignes par jour. Sa durée d'exécution, facturée en heures de DPU, augmente avec l'historique.

Étape 6 : planifier la collecte toutes les heures

Créer le workflow

1. Ouvrez Data Processing Engine > Workflows et cliquez sur New workflow.

2. Nommez-le Vélib collection.

3. Ajoutez une action par stage, dans cet ordre :

   Stage	Action
   1	velib_raw, l'action Load de la table
   2	Stamp collection time
   3	Deduplicate - velib_raw

4. Cliquez sur Create.

   

Chaque action doit être dans son propre stage : les actions d'un même stage s'exécutent en même temps, et chaque étape a besoin que la précédente soit terminée.

Le planifier

Dans l'onglet Preferences du workflow, dans la section Triggers, cliquez sur Add et renseignez le trigger basé sur le temps :

Cliquez sur Confirm.

L'objectif est une collecte par heure. Le workflow s'exécute deux fois, aux minutes 7 et 37 de chaque heure, en UTC : la deuxième exécution est un filet de sécurité, si la première échoue. Les deux exécutions d'une heure reçoivent le même collected_hour, et l'action Deduplicate garde la plus récente : chaque heure contient une ligne par station dans tous les cas. L'expression évite volontairement la minute 0, car de nombreux jobs planifiés démarrent à ce moment-là.

Vérifier la collecte

Après quelques heures, chaque heure contient exactement une ligne par station :

SELECT collected_hour, count(*) AS stations
FROM velib_raw
GROUP BY collected_hour
ORDER BY collected_hour DESC

Chaque ligne affiche le même nombre de stations, environ 1 500. Une heure manquante signale des exécutions en échec ; un nombre supérieur, une déduplication qui ne s'est pas exécutée. L'étape 7 donne une requête qui liste les heures manquantes.

Étape 7 : modéliser les données avec des vues

Cinq vues transforment l'historique brut en données prêtes pour l'analyse. Créez chacune d'elles dans Lakehouse Manager > Views > New view, dans le dataset default_dataset, dans l'ordre ci-dessous : chaque vue lit les précédentes.

station_hourly

Convertit les types, renomme les colonnes et ajoute l'heure de collecte à l'heure de Paris.

SELECT
    CAST(CAST(stationcode AS BIGINT) AS VARCHAR)                         AS station_code,
    name                                                                 AS station_name,
    nom_arrondissement_communes                                          AS commune,
    CAST(coordonnees_geo_lat AS DOUBLE)                                  AS lat,
    CAST(coordonnees_geo_lon AS DOUBLE)                                  AS lon,
    capacity,
    numbikesavailable                                                    AS bikes,
    mechanical,
    ebike,
    numdocksavailable                                                    AS free_docks,
    capacity - numbikesavailable - numdocksavailable                     AS out_of_service_docks,
    is_installed = 'OUI' AND is_renting = 'OUI'                          AS is_open,
    collected_hour,
    CAST(with_timezone(collected_hour, 'UTC') AT TIME ZONE 'Europe/Paris' AS timestamp(6)) AS collected_hour_paris
FROM velib_raw

capacity - numbikesavailable - numdocksavailable donne les bornes qui ne sont ni libres ni occupées par un vélo : les bornes hors service.

collected_hour reste en UTC, et tout regroupement par heure se fait sur cette colonne : une heure UTC n'est jamais répétée ni sautée. collected_hour_paris donne la même heure à l'heure de Paris, pour l'affichage, pour l'heure de la journée et pour le jour dans velib_daily. Ne regroupez pas les heures sur cette colonne : au passage à l'heure d'hiver, en octobre, deux heures UTC ont la même heure de Paris, et au passage à l'heure d'été, en mars, une heure de Paris n'existe pas. Le CAST conserve l'heure de Paris sans fuseau horaire, pour que Superset la traite comme une date ordinaire.

network_hourly

SELECT
    collected_hour,
    collected_hour_paris,
    hour(collected_hour_paris)                                            AS hour_of_day,
    day_of_week(collected_hour_paris)                                     AS day_of_week,
    CASE WHEN day_of_week(collected_hour_paris) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type,
    count(*)                                                              AS stations,
    sum(bikes)                                                            AS bikes,
    sum(mechanical)                                                       AS mechanical,
    sum(ebike)                                                            AS ebike,
    sum(free_docks)                                                       AS free_docks,
    sum(out_of_service_docks)                                             AS out_of_service_docks,
    count_if(bikes = 0)                                                   AS empty_stations,
    count_if(free_docks = 0)                                              AS full_stations,
    round(100.0 * sum(bikes) / sum(capacity), 1)                          AS fill_rate_pct
FROM <catalog>.default_dataset.station_hourly
WHERE is_open
GROUP BY collected_hour, collected_hour_paris

Seules les stations ouvertes comptent : pour un usager, une station fermée n'est ni vide ni pleine. L'heure de la journée et le jour de la semaine viennent de l'heure de Paris, pour que 8 h corresponde à 8 h à Paris toute l'année.

station_profile

SELECT
    station_code,
    max(station_name)                                                  AS station_name,
    max(commune)                                                       AS commune,
    max(lat)                                                           AS lat,
    max(lon)                                                           AS lon,
    max(capacity)                                                      AS capacity,
    count(*)                                                           AS observed_hours,
    round(100e0 * count_if(bikes = 0) / count(*), 1)                   AS empty_hours_pct,
    round(100e0 * count_if(free_docks = 0) / count(*), 1)              AS full_hours_pct,
    round(100e0 * sum(bikes) / nullif(sum(capacity), 0), 1)            AS avg_fill_rate_pct,
    round(avg(out_of_service_docks), 1)                                AS avg_out_of_service_docks
FROM <catalog>.default_dataset.station_hourly
WHERE is_open
GROUP BY station_code

velib_daily

SELECT
    CAST(collected_hour_paris AS date)                                    AS day,
    CASE WHEN day_of_week(collected_hour_paris) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END AS day_type,
    count(*)                                                              AS observed_hours,
    min(bikes)                                                            AS min_bikes,
    max(bikes)                                                            AS max_bikes,
    max(bikes) - min(bikes)                                               AS daily_swing,
    round(avg(bikes))                                                     AS avg_bikes,
    round(avg(fill_rate_pct), 1)                                          AS avg_fill_rate_pct,
    sum(empty_stations)                                                   AS empty_station_hours,
    max(empty_stations)                                                   AS peak_empty_stations
FROM <catalog>.default_dataset.network_hourly
GROUP BY CAST(collected_hour_paris AS date), CASE WHEN day_of_week(collected_hour_paris) IN (6, 7) THEN 'Weekend' ELSE 'Weekday' END

station_current

SELECT
    station_code,
    station_name,
    commune,
    lat,
    lon,
    capacity,
    bikes,
    mechanical,
    ebike,
    free_docks,
    out_of_service_docks,
    is_open,
    round(100e0 * bikes / nullif(capacity, 0), 1)                     AS fill_rate_pct,
    CASE
        WHEN NOT is_open      THEN 'Closed'
        WHEN bikes = 0        THEN 'Empty'
        WHEN free_docks = 0   THEN 'Full'
        ELSE 'Available'
    END                                                                AS status,
    collected_hour,
    collected_hour_paris
FROM <catalog>.default_dataset.station_hourly
WHERE collected_hour = (
    SELECT max(collected_hour)
    FROM <catalog>.default_dataset.station_hourly
)

Vérifier les vues

Dans l'Explorer, préfixez chaque vue par son dataset :

SELECT count(*) AS stations, max(collected_hour_paris) AS last_collection, sum(bikes) AS bikes
FROM default_dataset.station_current

Vous obtenez le nombre de stations, environ 1 500, et l'heure de la dernière collecte, à l'heure de Paris.

Pour lister les heures sans collecte, générez toutes les heures de la période et gardez celles qui manquent dans station_hourly. La requête travaille sur l'heure UTC : les changements d'heure ne créent donc aucun faux trou. Dans un WITH, la vue a besoin de son nom complet :

WITH bounds AS (
    SELECT min(collected_hour) AS first_hour, max(collected_hour) AS last_hour
    FROM <catalog>.default_dataset.station_hourly
),
hours AS (
    SELECT h AS collected_hour
    FROM bounds
    CROSS JOIN UNNEST(sequence(first_hour, last_hour, INTERVAL '1' HOUR)) AS t(h)
)
SELECT h.collected_hour
FROM hours h
LEFT JOIN (
    SELECT DISTINCT collected_hour
    FROM <catalog>.default_dataset.station_hourly
) s
  ON s.collected_hour = h.collected_hour
WHERE s.collected_hour IS NULL
ORDER BY h.collected_hour

Une exécution peut échouer pour une raison passagère : la deuxième exécution de chaque heure comble le trou.

La fonction sequence de Trino renvoie au plus 10 000 entrées, soit environ 416 jours de valeurs horaires : si l'historique est plus long, restreignez la période dans bounds avec un WHERE sur collected_hour.

Étape 8 : connecter Superset

Toutes les données sont maintenant prêtes dans le Lakehouse. Pour les analyser, créez une application Apache Superset :

1. Créez une connexion Trino en suivant le guide « Générer une chaîne de connexion Trino pour des outils externes ». Copiez la chaîne de connexion avant de fermer la fenêtre : elle n'est plus affichée ensuite.

   

2. Créez l'application et connectez-la à Trino en suivant les étapes 1 à 3 du guide « Déployer Apache Superset sur la Data Platform OVHcloud ».

   

Créer les datasets dans Superset

1. Dans Superset, ouvrez Datasets et cliquez sur + Dataset.
2. Sélectionnez la base de données Trino, puis le schéma default_dataset et la vue station_current.
3. Cliquez sur Create and explore dataset.
4. Faites de même pour network_hourly, station_profile et velib_daily.

station_hourly ne sert qu'à alimenter les autres vues : elle n'a pas besoin de dataset.

Bon à savoir

• L'API date la station, pas le changement. duedate peut rester identique pendant que des vélos arrivent et partent : il ne distingue donc pas deux états. Reportez-vous à la section « Pourquoi duedate n'est pas la clé de déduplication ».
• Les planifications sont en UTC. Paris a une ou deux heures d'avance, selon la saison.
• Load ajoute des lignes. Chaque exécution de l'action Load ajoute l'état actuel de chaque station : l'historique s'enrichit sans aucun réglage.

Pourquoi duedate n'est pas la clé de déduplication

La clé évidente pour un historique de stations est la station et son heure de mise à jour, stationcode et duedate. Elle semble juste : deux collectes d'une station qui ne s'est pas mise à jour renvoient le même duedate.

Le Time Travel montre qu'elle est fausse. Sur la table telle qu'elle était avant une première déduplication, 676 des 1 463 clés en double avaient des chiffres différents. Par exemple, la station Turbigo - Française avait le même duedate dans deux collectes, 06:28:26 UTC, avec des chiffres différents :

Quelqu'un a rendu un vélo électrique entre les deux collectes, et duedate n'a pas changé. Dédoublonner sur duedate aurait gardé l'une des deux lignes au hasard, et perdu un vrai changement. L'heure de collecte, que vous maîtrisez, est une clé fiable.

La vérification qui l'a révélé :

SELECT count(*) AS keys_with_different_values
FROM (
    SELECT stationcode, duedate
    FROM velib_raw FOR VERSION AS OF <ID du snapshot avant la déduplication>
    GROUP BY stationcode, duedate
    HAVING count(*) > 1
       AND (count(DISTINCT numbikesavailable) > 1 OR count(DISTINCT numdocksavailable) > 1)
)

Vérifiez qu'une clé de déduplication identifie des lignes réellement identiques avant de vous y fier : le Scan de l'action Deduplicate compte les doublons, mais ne dit pas s'ils sont identiques.

Ce que vous avez construit

Vous disposez d'un historique de chaque station Vélib', collecté toutes les heures depuis une API publique en temps réel, avec une deuxième exécution en filet de sécurité, débarrassé des doublons et modélisé par cinq vues SQL, sans une ligne de Python. Quatre datasets Superset sont construits dessus.

La partie suivante, « Construire un dashboard de disponibilité des Vélib' dans Superset », transforme ces datasets en dashboard : vélos disponibles maintenant, rythme de la journée, stations à problèmes et tendances jour après jour.

Aller plus loin

Pour une formation ou une assistance technique sur la mise en œuvre de nos solutions, contactez votre commercial ou consultez la page Professional Services pour obtenir un devis et faire analyser votre projet par nos experts.

Posez vos questions, faites-nous part de vos commentaires et interagissez directement avec l’équipe qui développe la Data Platform sur le canal Discord dédié.

Si vous avez besoin d'une assistance concernant vos services OVHcloud, créez une demande depuis notre centre d'aide.

Rejoignez notre communauté d'utilisateurs.