ETL para 15 millones de pings GPS: stack y arquitectura

Procesando 15 Millones de Pings GPS Diarios: Arquitectura ETL para Análisis de Transporte

El sistema procesa 15 millones de coordenadas GPS provenientes de 1.800 autobuses en 170 rutas de Taskent. Los pings llegan cada 10 segundos a través de la API 3TM. El desafío principal es transformar coordenadas aisladas en datos estructurados para su análisis: identificar paradas, atascos, viajes y desviaciones de ruta.

Un cargador que utiliza asyncio + aiohttp consulta la API mediante temporizadores de systemd. Los datos se escriben en PostgreSQL con PostGIS. La orquestación ETL mediante Dagster garantiza la secuencia: rutas → autobuses → paradas → actualizaciones GPS.

Selección de Tecnologías y Evitar la Sobreingeniería

Inicialmente, se probaron Kafka y ClickHouse, pero resultaron inadecuados:

• Kafka no es ideal para un modelo de consulta de API basado en extracción (pull), ya que añade complejidad sin beneficio.
• ClickHouse es prematuro dado los volúmenes de consulta actuales.

Stack final:

• Ingesta: Cargador asíncrono en Python, ~360 inserciones/segundo en 2 vCPU / 4 GB de RAM.
• Almacenamiento: PostgreSQL + PostGIS para rutas, paradas y series temporales de GPS.
• Orquestación: Dagster para dependencias y trazabilidad.
• Análisis Espacial: PySpark para tareas pesadas: ajustar GPS a rutas, calcular carga.

La capa de Staging almacena JSON crudo durante 1 día, luego la ODS estructura los datos en memoria sin volver a leer desde la base de datos.

Modelo de Datos Multicapa

Los datos se organizan en tres capas para escalabilidad y auditabilidad:

• ODS: Datos operativos: rutas, paradas, posiciones actuales de autobuses.
• DDS (Data Vault 2.0): Hubs (entidades), satélites (atributos), enlaces (relaciones). El GPS no se almacena; el foco está en rutas, autobuses y paradas.
• CDM: Marts analíticos. La tabla fct_bus_trips: LineString de PostGIS por viaje con metadatos: marcas de tiempo, kilometraje, velocidad, porcentaje de coincidencia con la ruta.

Data Vault permite añadir atributos (telemetría) sin refactorizar el modelo.

Algoritmos de Segmentación de Viajes

La segmentación determina el inicio y fin de un viaje basándose en tres señales:

• Velocidad: Ralentización en paradas de inicio/fin.
• Intervalo de tiempo >5 min: Interrupción del flujo.
• Proximidad al punto final de la ruta.

— Editorial Team