1500万 GPS pings 的 ETL：技术栈和架构

每日处理1500万GPS信号：交通分析ETL架构详解

该系统处理塔什干170条线路、1800辆公交车产生的1500万个GPS坐标点。信号通过3TM API每10秒传输一次。核心挑战在于将孤立坐标转化为结构化数据进行分析：识别站点、交通拥堵、行程及路线偏离。

采用asyncio + aiohttp的加载器通过systemd定时器轮询API。数据写入支持PostGIS的PostgreSQL数据库。通过Dagster进行ETL编排，确保执行顺序：路线→公交车→站点→GPS更新。

技术栈选择与避免过度设计

初期测试了Kafka和ClickHouse，但均不适用：

• Kafka不适合基于拉取的API轮询模型——只会增加开销而无实际收益。
• 鉴于当前查询量，ClickHouse为时过早。

最终技术栈：

• 数据接入： 异步Python加载器，在2 vCPU / 4 GB RAM配置下约每秒360次插入。
• 存储： PostgreSQL + PostGIS，用于存储路线、站点、GPS时间序列。
• 编排： Dagster，用于管理依赖关系和可追溯性。
• 空间分析： PySpark处理繁重任务——将GPS点匹配到路线、计算负载。

暂存层将原始JSON数据存储1天，随后ODS层在内存中结构化数据，无需从数据库重新读取。

多层数据模型

数据组织为三层，以实现可扩展性和可审计性：

• ODS： 运营数据——路线、站点、公交车实时位置。
• DDS（Data Vault 2.0）： 中心（实体）、卫星（属性）、链接（关系）。不存储GPS数据；重点关注路线、公交车、站点。
• CDM： 分析集市。fct_bus_trips表——每条行程对应一个PostGIS LineString，包含元数据：时间戳、里程、速度、与路线匹配百分比。

Data Vault模型允许添加属性（如遥测数据）而无需重构模型。

行程分段算法

分段基于三个信号确定行程起止点：

• 速度： 在起止站点怠速。
• 时间间隔 >5分钟： 数据流中断。
• 接近路线端点。

— Editorial Team