ClickHouse大数据准实时更新

一、问题背景

最近有一个项目需求，需要对日活跃的3万辆车的定位数据进行分析，并支持查询和统计分析结果。每辆车每天产生1条分析结果数据，要求能够查询过去一年内的所有分析结果。因此，每月需要处理约90万条记录，一年大约有1000万条记录。由于数据量庞大，同时还需要考虑数据权限关联，若采用传统关系型数据库进行查询，查询时间可能过长，甚至出现超时的情况。

二、解决方案

针对上述问题，我们选择使用Clickhouse数据库存储分析结果数据。Clickhouse以其高性能的查询能力，可以快速生成BI报表，并支持多维度、多指标的数据分析。然而，Clickhouse的更新操作非常耗资源，频繁的更新可能会导致系统崩溃。由于每辆车平均每20秒会产生1条定位数据，3万辆车每小时将生成540万条数据，因此对这些数据的分析需要频繁更新数据库。

为了解决这一问题，我们将频繁更新的操作放入关系型数据库进行处理，并通过定时同步的方式将数据传输到Clickhouse。这样可以大幅降低Clickhouse的更新频率。尽管如此，由于Clickhouse的设计理念并不鼓励频繁更新，更新操作仍然是不可避免的。那么，如何在Clickhouse中进行数据更新，并实现准实时更新呢？

三、Clickhouse更新

ClickHouse的更新操作本身是低效的，因为它的MergeTree存储引擎一旦生成一个数据分区（Data Part），该分区无法直接修改。任何更新操作都需要删除旧的数据分区并重新写入新的数据分区。因此，从MergeTree的存储引擎设计上看，ClickHouse并不擅长进行数据的更新和删除。

更新方案：Insert + xxxMergeTree + Optimize

1、Insert + xxxMergeTree

通过结合 Insert 操作和特定的MergeTree引擎（如 ReplacingMergeTree 或 CollapsingMergeTree），可以实现数据更新的效果。此方法适用于那些需要基于某些字段替换或折叠数据的场景，但需要注意的是，更新操作是异步的，刚插入的数据不能马上看到最新的结果，因此无法做到准实时。

例如，使用 ReplacingMergeTree 创建表：

create table gps_result_vehicle_day
(`belong_time` String comment '数据归属时间，格式yyyy-MM-dd',`belong_partition` String comment '数据归属分区',`vehicle_plate` String comment '车牌，车牌号+车牌颜色',`vehicle_plate_no` String comment '车辆(挂车)号牌',`vehicle_plate_color` String comment '车牌颜色',`vehicle_plate_color_code` String comment '车牌颜色代码',`enterprise_id` Nullable(String) COMMENT '所属企业id',`enterprise_name` Nullable(String) COMMENT '所属企业名称',`online_time` Int64 DEFAULT 0 comment '上线时长，单位分钟',`online_day` Int64 DEFAULT 0 comment '上线天数',`run_time` Int64 DEFAULT 0 comment '行驶时长，单位分钟',`total_point_num` Int64 DEFAULT 0 comment '总点数',`qualified_point_num` Int64 DEFAULT 0 comment '合格点数',`qualified_rate` Decimal(10, 2) DEFAULT 0 comment '数据合格率，单位%',`total_mileage` Decimal(10, 3) DEFAULT 0 comment '总里程，单位km',`complete_mileage` Decimal(10, 3) DEFAULT 0 comment '完整里程（或连续里程），单位km',`abnormal_mileage` Decimal(10, 3) DEFAULT 0 comment '异常里程，单位km',`track_complete_rate` Decimal(10, 2) DEFAULT 0 comment '轨迹完整率，单位%',`drift_num` Int64 DEFAULT 0 comment '漂移次数',`in_net_date` Nullable(Date) comment '入网时间',`create_time` DateTime comment '创建时间',`update_time` DateTime comment '更新时间',index idx_arvd_enterprise_name enterprise_name type minmax granularity 1
)
engine = ReplacingMergeTree(update_time)
partition by belong_partition
primary key (belong_time, vehicle_plate)
order by (belong_time, vehicle_plate)
settings index_granularity = 8192

使用 ReplacingMergeTree，因为它相比 CollapsingMergeTree 更加简单。CollapsingMergeTree 对数据的要求比较严格，不仅需要反位标记，而且需要保证正负标记号的个数对应。上述表采用belong_partition字段进行分区，该字段存储数据归属的月份，表示数据是按月进行分区。采用ReplacingMergeTree引擎，可以针对同分区内相同主键的数据进行去重，它能够在合并分区时删除重复的数据。 

2、optimize final

为了确保数据合并的及时性，可以使用 optimize final 强制触发数据合并。ClickHouse的 MergeTree 引擎会自动合并数据，但合并过程的执行时间不确定，可能导致数据更新不完全，甚至可能延迟一天以上。为了解决这个问题，可以在写入数据后，使用 optimize final 强制进行数据合并。

OPTIMIZE TABLE {tableName} PARTITION {partitionName} FINAL;

需要注意的是，optimize 操作会消耗较多资源，执行速度较慢，因此不宜频繁使用。

四、总结

这种更新方法通过巧妙的设计，能够在ClickHouse中实现准实时的数据更新，虽然更新并非即时完成，但能够有效地平衡性能与数据一致性的需求。

1. 异步更新：通过 ReplacingMergeTree 或 CollapsingMergeTree 实现数据的异步更新，虽然更新不是实时的，但可以保证数据一致性。
2. 数据合并：optimize final 命令可以强制触发数据合并，确保数据及时一致。
3. 性能考虑：由于 optimize 操作代价较高，必须谨慎使用，避免频繁执行。