ETL pour 15 millions de pings GPS : pile et architecture

Traiter 15 millions de pings GPS par jour : Architecture ETL pour l'analyse des transports

Le système traite 15 millions de coordonnées GPS provenant de 1 800 bus répartis sur 170 lignes à Tachkent. Les pings arrivent toutes les 10 secondes via l'API 3TM. Le défi principal consiste à transformer des coordonnées isolées en données structurées pour l'analyse : identifier les arrêts, les embouteillages, les trajets et les écarts d'itinéraire.

Un chargeur utilisant asyncio + aiohttp interroge l'API via des minuteries systemd. Les données sont écrites dans PostgreSQL avec PostGIS. L'orchestration ETL via Dagster garantit la séquence : lignes → bus → arrêts → mises à jour GPS.

Choix de la pile technique et éviter le surdimensionnement

Initialement, Kafka et ClickHouse ont été testés mais se sont avérés inadaptés :

• Kafka n'est pas idéal pour un modèle d'interrogation d'API basé sur le pull — il ajoute de la complexité sans bénéfice.
• ClickHouse est prématuré compte tenu des volumes de requêtes actuels.

Pile finale :

• Ingestion : Chargeur Python asynchrone, ~360 insertions/sec sur 2 vCPU / 4 Go de RAM.
• Stockage : PostgreSQL + PostGIS pour les lignes, arrêts, séries temporelles GPS.
• Orchestration : Dagster pour les dépendances et la traçabilité.
• Analyse spatiale : PySpark pour les tâches lourdes — alignement GPS sur les lignes, calcul de la charge.

La couche Staging stocke le JSON brut pendant 1 jour, puis l'ODS structure les données en mémoire sans relecture depuis la base de données.

Modèle de données multi-couches

Les données sont organisées en trois couches pour l'évolutivité et l'auditabilité :

• ODS : Données opérationnelles — lignes, arrêts, positions actuelles des bus.
• DDS (Data Vault 2.0) : Hubs (entités), satellites (attributs), links (relations). Le GPS n'est pas stocké ; l'accent est mis sur les lignes, bus, arrêts.
• CDM : Entrepôts analytiques. La table fct_bus_trips — LineString PostGIS par trajet avec métadonnées : horodatages, kilométrage, vitesse, % de correspondance à la ligne.

Data Vault permet d'ajouter des attributs (télémétrie) sans refondre le modèle.

Algorithmes de segmentation des trajets

La segmentation détermine le début et la fin d'un trajet en fonction de trois signaux :

• Vitesse : Immobilisation aux arrêts de départ/d'arrivée.
• Intervalle de temps >5 min : Interruption du flux.
• Proximité du point d'arrivée de la ligne.

— Editorial Team