如何在ClickHouse中插入数据时自动更新最新实时数据？

可以完全通过ClickHouse的原生特性实现自动同步，无需应用层额外操作，最优方案是物化视图配合ReplacingMergeTree引擎，以下是具体实现步骤：

1. 修正历史时序表结构 #

MergeTree系列引擎必须定义排序键，先补充memory_usage的必要配置：

CREATE TABLE memory_usage (
  timestamp DateTime,
  app_id uint64,
  usage uint64
) ENGINE = MergeTree()
ORDER BY (app_id, timestamp); -- 按应用ID分组、时间排序，方便后续数据同步

2. 创建实时数据存储表 #

使用ReplacingMergeTree引擎，它会自动根据指定字段替换同主键的旧记录，保留最新数据：

CREATE TABLE real_time_memory_usage (
  app_id uint64,
  usage uint64,
  timestamp DateTime -- 用于判断记录新旧，保留时间戳最大的条目
) ENGINE = ReplacingMergeTree(timestamp) -- 指定用timestamp作为新旧判断依据
ORDER BY (app_id); -- 主键设为app_id，确保每个应用仅存一条记录

3. 创建物化视图实现自动同步 #

物化视图会监听memory_usage的插入操作，自动将数据同步到实时表，由ReplacingMergeTree自动完成去重逻辑：

CREATE MATERIALIZED VIEW memory_usage_sync_realtime
TO real_time_memory_usage
AS
SELECT
  app_id,
  usage,
  timestamp
FROM memory_usage;

使用说明 #

• 只需正常向memory_usage插入时序数据，物化视图会自动同步到实时表
• ReplacingMergeTree会在后台合并分区时自动清理旧数据，保留每个app_id的最新记录
• 如果需要立即获取最新结果（不等待分区合并），查询时可添加FINAL关键字：

SELECT app_id, usage FROM real_time_memory_usage FINAL;

方案优势 #

• 完全由数据库内部处理同步逻辑，无需应用层编写额外代码
• ReplacingMergeTree的去重逻辑高效稳定，适配单键最新值的业务场景
• 物化视图同步为异步低延迟操作，不会阻塞主表的插入性能
• 相比ClickHouse的触发器功能，物化视图是更成熟、性能更优的同步方案

备选高实时性方案（针对极致实时需求） #

如果需要无需等待分区合并即可获取最新数据，可使用AggregatingMergeTree配合聚合函数：

创建聚合实时表 #

CREATE TABLE real_time_memory_usage_agg (
  app_id uint64,
  latest_usage AggregateFunction(maxIf, uint64, DateTime)
) ENGINE = AggregatingMergeTree()
ORDER BY (app_id);

创建聚合物化视图 #

CREATE MATERIALIZED VIEW memory_usage_sync_agg
TO real_time_memory_usage_agg
AS
SELECT
  app_id,
  maxStateIf(usage, timestamp) AS latest_usage
FROM memory_usage
GROUP BY app_id;

查询最新数据 #

SELECT
  app_id,
  maxMerge(latest_usage) AS usage
FROM real_time_memory_usage_agg
GROUP BY app_id;

内容的提问来源于stack exchange，提问作者LTW