ClickHouse：完整事件分析项目与解答

前置：基础SQL阅读能力。适用：单节点ClickHouse 24.8 LTS基础语法；资料核对2026-10-17。示例未执行，本轮仅静态解析和文本审阅，不连接数据库、启动服务或调用产品接口。

ClickHouse 完整项目

ClickHouse 完整项目用SQL实现事件事实表、每日派生汇总及独立版本表，CSV给出成功与失败输入，XML提供本地备份磁盘配置。示例未执行，读者在未来自行授权的独立24.8 LTS环境按README分支执行；这是可审阅的完整项目，不是性能测试或线上部署模板。

前置是SQL筛选和GROUP BY阅读能力，目标是分清存储排序、逻辑版本、输入重放和派生聚合四件事。00-schema.sql要求数据库不存在以阻止无意重复使用；主流程只导入一次，备份分支必须先于30-replay故障演示。requirements.txt没有隐藏安装步骤，SQL无SDK、账号或外部下载依赖。

验收需要留存版本与DDL、输入校验、六行60.00基线、五个日报分组、FINAL两行结果。XML只有在管理员已配置并授权磁盘后才进入备份分支；正文不把XML解析通过当作服务配置生效。所有文件全文见后方同源引用。

ClickHouse 重放练习

ClickHouse 重放练习要求先手算首条事件重复一次后的结果：原表从6行60.00变成7行70.00，租户7在1月1日的每日汇总由2笔25.50变成3笔35.50。其他日期和租户不变；event_id相同并不令普通MergeTree拒绝插入。这种失败是业务错误，不一定出现数据库异常。

题目：把events引擎改成ReplacingMergeTree，就能保证daily不重复吗？另一个题目是把count改成uniqExact(event_id)后，sum是否也正确？先说明聚合在哪个时刻处理什么输入，再回答，不能只改DDL或函数名称。

参考答案

Replacing后台合并只处理源表parts，增量视图已经处理过每次插入块，不能自动减去旧贡献。uniqExact能改变事件数量口径，却不会修正另一个原始sum。应在形成可加汇总之前确定唯一有效事实，或采用可撤回的版本/差量方案并对账；本例恢复采用清空隔离库并从原始六行重建，而非伪造“等待即恢复”。

扩展题：latest表若把version放进ORDER BY会怎样？解答：每个版本拥有不同排序键，不再互相替换。若把可能变化的country放入排序键，也会把国家更新拆成不同逻辑行。

ClickHouse 实验恢复

ClickHouse 实验恢复使用隔离库重建恢复CSV基线，备份分支只恢复events到全新events_restored表并校对6行60.00。两者验证不同目标：重建验证已知输入的推导，备份验证指定备份对象的可读取性；都不能被称作已验证的跨集群容灾。所有命令均在README列明，当前交付仅静态检查。

先保存观察结果，再确认p2_clickhouse只含本实验数据，执行90-cleanup.sql删除该库；随后重新建模并单次导入。不要只清空events再导入而保留旧daily，视图会把新输入再次累加到旧目标。若选择恢复源表而非重建CSV，需另行设计新汇总表的一次性回填，不混用旧目标。

归档和配置不会随DROP DATABASE消失；读者检查留存期后只清理本实验准确归档路径，不删除共用备份目录。遇到失败要先停止重放并查看诊断，不执行广泛删除来“恢复”。完整文件均展示在同页，方便离线审查和未来自跑。

完整源码、配置、输入与预期

以下每个文件来自examples/p2-clickhouse同一份源文件，README包含完整依赖、步骤、人工预期、失败输入、恢复与清理。代码是未来读者自跑说明，不是本轮执行证据。

requirements.txt

# Reader environment; not installed or executed in this delivery.
# ClickHouse server and client: 24.8 LTS, same patch version in reader environment.
# No Python/Node packages, SDKs or external services required.
# SQL uses only the 24.8 feature subset; current docs checked 2026-10-17.

config/backup_disk.xml

<clickhouse>
  <storage_configuration>
    <disks>
      <course_backups>
        <type>local</type>
        <path>/var/lib/clickhouse/course-backups/</path>
      </course_backups>
    </disks>
  </storage_configuration>
  <backups>
    <allowed_disk>course_backups</allowed_disk>
    <allowed_path>/var/lib/clickhouse/course-backups/</allowed_path>
  </backups>
</clickhouse>

00-schema.sql

-- Fresh, isolated database only. No IF NOT EXISTS: fail if it already exists.
CREATE DATABASE p2_clickhouse;
CREATE TABLE p2_clickhouse.events
(
    event_id UInt64,
    tenant_id UInt32,
    event_time DateTime('UTC'),
    country LowCardinality(String),
    amount Decimal(12, 2),
    CONSTRAINT non_negative CHECK amount >= 0
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_time, event_id);

CREATE TABLE p2_clickhouse.daily
(
    tenant_id UInt32,
    day Date,
    amount Decimal(18, 2),
    event_count UInt64
)
ENGINE = SummingMergeTree((amount, event_count))
ORDER BY (tenant_id, day);

-- Create before the first import: no POPULATE and no live backfill race.
CREATE MATERIALIZED VIEW p2_clickhouse.daily_mv TO p2_clickhouse.daily AS
SELECT tenant_id, toDate(event_time, 'UTC') AS day,
       CAST(sum(amount) AS Decimal(18, 2)) AS amount,
       count() AS event_count
FROM p2_clickhouse.events
GROUP BY tenant_id, day;

CREATE TABLE p2_clickhouse.latest
(
    tenant_id UInt32,
    event_id UInt64,
    version UInt64,
    country LowCardinality(String),
    amount Decimal(12, 2)
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (tenant_id, event_id);

data/events.csv

event_id,tenant_id,event_time,country,amount
1,7,2026-01-01 00:00:00,CN,10.00
2,7,2026-01-01 01:00:00,CN,15.50
3,7,2026-01-02 00:00:00,US,4.50
4,8,2026-01-01 00:00:00,US,20.00
5,7,2026-02-01 00:00:00,CN,7.00
6,8,2026-02-02 00:00:00,US,3.00

data/invalid-negative.csv

event_id,tenant_id,event_time,country,amount
90,7,2026-01-01 00:00:00,CN,-1.00

data/invalid-time.csv

event_id,tenant_id,event_time,country,amount
91,7,not-a-time,CN,1.00

10-queries.sql

-- Run only immediately after one clean import.
SELECT count() AS n, sum(amount) AS total FROM p2_clickhouse.events;
SELECT tenant_id, toDate(event_time, 'UTC') AS day,
       count() AS event_count, sum(amount) AS amount
FROM p2_clickhouse.events GROUP BY tenant_id, day ORDER BY tenant_id, day;
-- Still aggregate: target parts need not have merged.
SELECT tenant_id, day, sum(event_count) AS event_count, sum(amount) AS amount
FROM p2_clickhouse.daily GROUP BY tenant_id, day ORDER BY tenant_id, day;
SELECT country, count() AS n, sum(amount) AS amount
FROM p2_clickhouse.events
WHERE tenant_id = 7 AND event_time >= toDateTime('2026-01-01 00:00:00', 'UTC')
  AND event_time < toDateTime('2026-02-01 00:00:00', 'UTC')
GROUP BY country ORDER BY country;
EXPLAIN indexes = 1
SELECT sum(amount) FROM p2_clickhouse.events
WHERE tenant_id = 7 AND event_time >= toDateTime('2026-01-01 00:00:00', 'UTC')
  AND event_time < toDateTime('2026-02-01 00:00:00', 'UTC');

20-versions.sql

-- Independent from events/daily. Run once, not as a retry protocol.
INSERT INTO p2_clickhouse.latest VALUES
(7,1,1,'CN',10.00),(7,1,2,'CN',12.00),(7,2,1,'US',5.00);
SELECT tenant_id, event_id, version, country, amount
FROM p2_clickhouse.latest FINAL ORDER BY tenant_id, event_id;
-- A second delivery of this batch does not define end-to-end exactly-once.
-- Equal versions with different payloads must be rejected upstream.

30-replay.sql

-- DELIBERATE BUSINESS FAILURE: separate insert of the first logical event.
-- Plain MergeTree does not deduplicate logical event_id values.
INSERT INTO p2_clickhouse.events VALUES
(1,7,'2026-01-01 00:00:00','CN',10.00);
SELECT count() AS n, sum(amount) AS total FROM p2_clickhouse.events;
SELECT tenant_id, day, sum(event_count) AS n, sum(amount) AS amount
FROM p2_clickhouse.daily GROUP BY tenant_id, day ORDER BY tenant_id, day;

40-diagnostics.sql

SELECT partition, count() AS active_parts, sum(rows) AS rows,
       sum(bytes_on_disk) AS bytes
FROM system.parts
WHERE database = 'p2_clickhouse' AND table = 'events' AND active
GROUP BY partition ORDER BY partition;
SELECT query_id, query_duration_ms, read_rows, read_bytes, memory_usage, exception
FROM system.query_log
WHERE type IN ('QueryFinish','ExceptionWhileProcessing')
  AND position(query, 'p2_clickhouse.events') > 0
ORDER BY event_time DESC LIMIT 10;
SELECT mutation_id, command, is_done, parts_to_do, latest_fail_reason
FROM system.mutations WHERE database = 'p2_clickhouse' ORDER BY create_time;

50-mutation.sql

-- Optional branch AFTER baseline and replay observations.
-- This changes source only; it does not retract the MV output.
ALTER TABLE p2_clickhouse.events UPDATE country = 'CA'
WHERE event_id = 4 SETTINGS mutations_sync = 1;
SELECT event_id, country FROM p2_clickhouse.events WHERE event_id = 4;
SELECT mutation_id, is_done, parts_to_do, latest_fail_reason
FROM system.mutations WHERE database = 'p2_clickhouse';

60-backup.sql

-- Execute only on clean baseline BEFORE replay or optional mutation.
-- Requires administrator-provisioned course_backups disk and privileges.
-- Unique destination per reader run; this name must not already exist.
BACKUP TABLE p2_clickhouse.events
TO Disk('course_backups', 'p2-events-clean.zip');
RESTORE TABLE p2_clickhouse.events AS p2_clickhouse.events_restored
FROM Disk('course_backups', 'p2-events-clean.zip');
SELECT count() AS n, sum(amount) AS total FROM p2_clickhouse.events_restored;
-- Only the raw table is backed up, not daily/latest/MV/users/server config.

90-cleanup.sql

-- Destructive only inside the reader's disposable exercise database.
DROP DATABASE p2_clickhouse SYNC;
-- Backup archive remains. Remove only the exact archive after retention review.

expected/baseline.tsv

tenant_id	day	event_count	amount
7	2026-01-01	2	25.50
7	2026-01-02	1	4.50
7	2026-02-01	1	7.00
8	2026-01-01	1	20.00
8	2026-02-02	1	3.00

expected/versions.tsv

tenant_id	event_id	version	country	amount
7	1	2	CN	12.00
7	2	1	US	5.00

README.md

# ClickHouse：事件分析、版本与导入边界

本项目是理论课程的完整 SQL、配置、输入与人工预期，**示例未执行**。交付阶段没有安装依赖、启动服务/容器/模拟器、调用接口或执行 clickhouse-client。本文件命令仅供读者未来在自行授权的隔离环境使用，不是本轮验证记录。

## 前置与版本

读者已具备独立 Linux 实验机上的 ClickHouse server/client **24.8 LTS 同一补丁版本**，以及建表、建物化视图、查询 system 表权限；不提供安装或启动脚本。24.8是语法兼容目标，不代表当前维护状态或已通过该版本实测。记录实际 `SELECT version(), timezone()` 输出；SQL明确UTC，不依赖机器时区。没有SDK或额外包，完整声明见requirements.txt。

所有命令在本目录执行，示例使用POSIX shell输入重定向。读者选择本地已有client连接配置（用户、密码和TLS由自己的配置管理），不要把真实密码写进这些文件。数据库名p2_clickhouse必须不存在；已有同名库时停止，不能跳过建表后继续导入。

## 完整主流程（读者将来执行）

1. 核对所有SQL与CSV；确认这是一台可丢弃实验机。用 `clickhouse-client --query "SELECT version(), timezone()"` 留存实际版本。
2. `clickhouse-client --multiquery < 00-schema.sql` 建立三表与视图；视图先于导入创建。
3. `clickhouse-client --query "INSERT INTO p2_clickhouse.events FORMAT CSVWithNames" < data/events.csv` 只导入一次，六行数据固定，不从公网下载。CSV列名与表列顺序相同，日期均UTC，金额精确到分。不要设置跳过错误。
4. `clickhouse-client --multiquery < 10-queries.sql`。原始行数6，总额60.00；直接分组与daily重新聚合都等于expected/baseline.tsv。国家查询CN为2笔25.50、US为1笔4.50。EXPLAIN形状、part数、耗时不提供固定断言；六行不足以证明索引收益。
5. `clickhouse-client --multiquery < 20-versions.sql`。FINAL逻辑结果等于expected/versions.tsv；不带FINAL的行数随后台合并变化，不断言一定为3。相同排序键选最大version，不承担事务或分布式全局去重。

## 可选备份分支（先于失败演示）

管理员在已有实验server的config.d配置config/backup_disk.xml，并预先创建指定目录、授予ClickHouse服务用户写权限；配置加载/重启由读者自行负责。备份磁盘不是MergeTree数据盘。确认p2-events-clean.zip和events_restored都不存在，再执行 `clickhouse-client --multiquery < 60-backup.sql`。预期备份完成、恢复完成，恢复表6行60.00；同名目标非空默认拒绝，不启用allow_non_empty_tables混入数据。

仅备份events表，未备份daily、视图、latest、权限与服务配置；单机同盘副本不防磁盘损坏。真实恢复需独立介质、容量、版本和权限核对及恢复演练。此流程只到新表校对，不自动切换线上名称。备份命令返回成功也不能代替跨表业务一致性校验。

## 失败输入与人工预期

分别将主流程第3步输入换为data/invalid-negative.csv、data/invalid-time.csv。每份只有一行，前者违反non_negative约束，后者日期解析失败；预期客户端非零退出并显示约束/类型错误，events仍为6行60.00、daily基线不变。错误码/完整文本随补丁变化，不把它们硬编码为断言。输入结构通过CSV解析不意味着值可由ClickHouse接受。

`clickhouse-client --multiquery < 30-replay.sql` 是有意的业务失败：event_id=1再写一次后events为7行70.00，daily的7号租户1月1日变为3笔35.50。其他分组不变。这条INSERT在普通MergeTree成功，而不是数据库抛错；重试协议必须在上游设计。不要靠等待Replacing合并修复已累加的汇总。

`clickhouse-client --multiquery < 40-diagnostics.sql` 读parts/query_log/mutations。query_log可能未启用或尚未刷盘；空结果不能证明无查询。不要把固定part数当成功标准。

## 可选mutation分支与恢复

在所有基线观察之后执行 `clickhouse-client --multiquery < 50-mutation.sql`，event_id=4的国家变为CA。mutations_sync=1等待当前节点；没有该设置时UPDATE默认异步。daily只有金额和笔数，仍是原有汇总；如果汇总包含国家维度，它也不会自动撤回US旧贡献。中途失败先看system.mutations；取消不能撤销已改parts，不提供一键回滚承诺。

恢复本实验基线最清晰的方法：确认只含练习数据后执行 `clickhouse-client --multiquery < 90-cleanup.sql`，再重新执行00和主流程的单次CSV导入。清理会删除整个练习库。不要在旧daily上再次回填，避免重复计数；源表备份恢复后若要汇总，先建全新目标/视图并设计一次性回填边界。

## 文件与清理边界

00建模，10查询，20版本，30重复输入，40诊断，50异步变更，60备份恢复，90清理；data含成功和两个失败输入，expected含手算结果。XML是真实备份磁盘配置，不包含凭据或外部端点。SQL文件按本README顺序而非文件名全量自动执行，60必须在30之前。依赖声明与全部文件在实践网页同源展示。

数据库清理不会删除备份归档或管理员配置。保留检查完成后，由读者只删除准确的p2-events-clean.zip以及本实验专用配置；不能递归删除共用目录。若观察与预期不同，保存版本/DDL/错误/查询证据后停止，不通过重复导入掩盖差异。

## 官方依据

- [MergeTree英文](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/mergetree)与[中文](https://clickhouse.com/docs/zh/reference/engines/table-engines/mergetree-family/mergetree)：排序键、稀疏索引和分区。
- [ReplacingMergeTree](https://clickhouse.com/docs/reference/engines/table-engines/mergetree-family/replacingmergetree)、[增量物化视图](https://clickhouse.com/docs/concepts/features/materialized-views/incremental-materialized-view)、[ALTER](https://clickhouse.com/docs/reference/statements/alter)。
- [CSVWithNames](https://clickhouse.com/docs/reference/formats/CSV/CSVWithNames)、[本地备份](https://clickhouse.com/docs/concepts/features/backup-restore/local-disk)。资料核对2026-10-17；当前文档可能包含24.8之后能力，本项目只使用列明的基础语法。

官方依据与范围

• ClickHouse官方资料 1。
• ClickHouse官方资料 2。
• ClickHouse官方资料 3。

中文与英文MergeTree、ReplacingMergeTree对照核对；翻译或当前文档包含的新功能不自动适用于24.8。本课没有运行该版本，只有静态证据。旧文ClickHouse概述保留原URL与正文，旧容量描述不作为本课性能结论。