创建你的第一个 MergeTree 表

在开始编写 SQL 之前，先了解 MergeTree 与传统数据库表的不同之处会很有帮助。

当你向 MergeTree 表插入数据时，ClickHouse 不会按行逐条写入。相反，它会将一个数据分区片段——一小块已经排序并压缩好的行数据——直接写入磁盘。随后，ClickHouse 会随着时间推移在后台将这些 parts 合并起来。这也正是这个名字的由来：merge + tree。

每个数据分区片段都会按照表的 ORDER BY 表达式排序。这个排序顺序会成为主键索引，从而让 ClickHouse 在查询时跳过大量无需读取的数据块 (这称为数据裁剪) 。对于最常见的查询来说，ORDER BY 列的选择性越高，ClickHouse 需要读取的数据就越少。

有三个子句决定 MergeTree 如何组织数据：

现在，你应该已经能够解释 MergeTree 表中数据分区片段、主键与查询性能之间的关系。

在创建表之前，先使用 s3 表函数查看源文件。这样你就可以直接查询 S3，而无需先将任何数据写入 ClickHouse。

在 SQL 控制台中运行以下内容：

DESCRIBE s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

请注意，几乎每一列都会被推断为 Nullable(String)。ClickHouse 读取的是原始 CSV，因此无法识别真实的数据类型——这需要你在下一步设计表 schema 时进行修正。

预览几行数据：

SELECT *
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
)
LIMIT 5;

该数据集包含在 HM Land Registry 登记的英格兰和威尔士住宅房产交易数据，包括交易 id、成交 price、date、房产 type、地址字段以及地理标识符。你还会注意到末尾有两列 (column15、column16) 为空——可以忽略。

要验证这一点，请确认你能看到包含 id、price、date、postcode、type、town 和 county 等列的行。

现在创建一个具有合适 schema 的永久表。下面的列类型都是有意这样选择的：

• LowCardinality(String) 用于唯一值较少的列 (邮编、城镇名称、郡名称) 。它在内部使用字典编码，可显著减少存储占用，并提升基于这些列进行分组和过滤时的性能。
• Enum8 会将 type 和 duration 列在磁盘上编码为较小的整数，同时在查询中保留便于阅读的字符串标签。源 CSV 使用单字母代码，因此我们会在 insert 时完成映射。
• PARTITION BY toYYYYMM(date) 会按日历月创建分区，这样当 WHERE 子句按 date 过滤时，ClickHouse 就能跳过整个月份的数据。
• ORDER BY (postcode, addr1, addr2) 会对数据进行排序，以支持按房产地址快速查找——这是该数据集最自然的访问模式。

CREATE TABLE uk_price_paid
(
price      UInt32,
date       Date,
postcode   LowCardinality(String),
type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
is_new     UInt8,
duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
addr1      String,
addr2      String,
street     LowCardinality(String),
locality   LowCardinality(String),
town       LowCardinality(String),
district   LowCardinality(String),
county     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (postcode, addr1, addr2);

运行以下命令，确认该表已创建：

SHOW CREATE TABLE uk_price_paid;

双击结果单元格以查看完整输出。请注意，虽然你指定了 ENGINE = MergeTree，但 ClickHouse Cloud 创建该表时实际使用的是 SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')。这是预期行为——Cloud 会自动将 MergeTree 转换为 SharedMergeTree，从而提供复制和共享存储支持。其行为和查询接口保持不变。

通过直接从 s3() 表函数查询，即可插入完整数据集。ClickHouse 会从 S3 流式读取压缩文件，并将其按排序后的 parts 写入表中。

INSERT INTO uk_price_paid
SELECT
    toUInt32(price),
    date,
    postcode,
    transform(type, ['T', 'S', 'D', 'F', 'O'],
        ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
    if(is_new = 'Y', 1, 0) AS is_new,
    transform(duration, ['F', 'L', 'U'],
        ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

由于源 CSV 将所有内容都存储为带有单字母代码的字符串 (例如，T 表示联排住宅，F 表示永久产权，Y/N 表示新建房屋) ，因此我们使用 transform 将它们映射为易读的标签，并使用 toUInt32/if 将数值列转换为相应类型。由于不需要 id、column15 和 column16 这几列，因此将它们排除。

这一步通常需要一到两分钟，具体取决于你的服务规模。完成后，确认行数：

SELECT formatReadableQuantity(count())
FROM uk_price_paid;

你应该能看到已加载约 3000 万行数据。

这时就能看到 MergeTree 的内部机制。system.parts 表会记录你的服务中每个 MergeTree 表在磁盘上的每个数据分区片段。

SELECT
partition,
name,
rows,
bytes_on_disk,
marks
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true
ORDER BY partition
LIMIT 20;

每一行代表一个活动中的数据分区片段。请注意：

• partition - 从 PARTITION BY 表达式派生出的 YYYYMM 值。每个月的数据彼此隔离。
• name - 分区片段名称编码了分区、块编号范围以及合并层级 (例如，199501_1_4_2 表示分区 199501、块 1–4，且已合并两次) 。
• marks - 索引粒度的数量。默认情况下，每个粒度覆盖 8,192 行，主键索引则为每个粒度存储一个条目。这个稀疏索引会常驻内存，从而实现快速的数据跳过。
• bytes_on_disk - 默认情况下，ClickHouse 使用 LZ4 按列压缩每个分区片段。将其与原始大小进行比较，可以直观看出压缩率。

要查看表中 parts 的总数以及整体的压缩后大小，请运行：

SELECT
count()          AS parts,
sum(rows)        AS total_rows,
formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true;

如果你过一段时间再次运行此查询，可能会发现 parts 数量减少了。这正是 MergeTree 中的 merge 在起作用——ClickHouse 会在后台持续将较小的 parts 合并为较大的 parts，从而减少 parts 的数量。active = true 过滤器可确保你只看到当前已合并的 parts，而不是那些仍在等待清理的旧 parts。

现在运行一些实际的分析查询。首先，找出有记录以来金额最高的一笔销售：

SELECT
addr1,
addr2,
town,
county,
price,
date
FROM uk_price_paid
ORDER BY price DESC
LIMIT 5;

在 SQL 控制台中查看查询统计信息——注意，30,033,199 行已全部读取。由于 price 不在 ORDER BY 键中，ClickHouse 无法利用主索引跳过数据，因此必须执行全表扫描。

接下来，按县计算平均销售价格：

SELECT
county,
round(avg(price)) AS avg_price,
count()           AS sales
FROM uk_price_paid
GROUP BY county
ORDER BY avg_price DESC;

再次会读取全部 30,033,199 行——county 不在 ORDER BY 或 PARTITION BY 中，因此 ClickHouse 会扫描整张表。

现在运行一条将聚合与 ORDER BY 结合起来的查询。由于数据按 (postcode, addr1, addr2) 排序，按邮政编码前缀过滤后，ClickHouse 就能跳过表中的大部分数据。这里我们来查看 SW1A 邮政编码区域内房产按年份统计的平均售价：

SELECT
toYear(date) AS year,
round(avg(price)) AS avg_price,
count() AS sales,
min(price) AS cheapest,
max(price) AS most_expensive
FROM uk_price_paid
WHERE postcode LIKE 'SW1A%'
GROUP BY year
ORDER BY year DESC;

每次查询后，都要在 SQL 控制台中查看查询统计信息。对 postcode 进行过滤后的聚合应只读取表中一小部分行，这表明主键索引正在发挥作用。将其与前面扫描范围更广的查询作比较——这种差异说明了为什么选择合适的 ORDER BY 很重要。