创建你的第一个 MergeTree 表

通过创建 MergeTree 表、加载英国房产价格数据，并观察 parts 和 merges 对存储和查询性能的影响，了解 ClickHouse 的核心表引擎如何工作。

所有快速入门

实时分析数据仓库可观测性AI/MLCloudOss

前置条件

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

你将构建的内容

在本快速入门中，你将创建一个 MergeTree 表，用于存储可追溯到 1995 年的英国住宅房产销售记录。 你将设计一个具有合适列类型的 schema，选择合理的 ORDER BY 和 PARTITION BY，直接从 S3 加载数据，然后查询 system.parts，了解 ClickHouse 如何在磁盘上实际组织数据。 完成后，你将理解为什么 MergeTree 引擎几乎是所有 ClickHouse 表的基础，以及它的排序和分区方式如何直接影响查询性能。

1. 了解 MergeTree 的工作原理

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

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

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

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

   Clause	What it does
   ORDER BY	在每个分片内按物理顺序对数据排序。决定主键。必需。
   PARTITION BY	将数据拆分为不同分区，通常按日期范围划分。不同分区中的 parts 永远不会合并，从而实现快速分区裁剪。
   PRIMARY KEY	默认与 ORDER BY 相同，除非你显式设置了一个更短的前缀。稀疏索引基于它构建。

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

2. 预览源数据

   在创建表之前，先使用 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 等列的行。

3. 设计并创建你的 MergeTree 表

   现在创建一个具有合适 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，从而提供复制和共享存储支持。其行为和查询接口保持不变。

4. 从 S3 加载数据

   通过直接从 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 万行数据。

5. 使用 system.parts 查看数据分区片段

   这时就能看到 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。

6. 查询数据并观察主键行为

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

   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 很重要。

后续步骤

在本快速入门中，你从零开始构建了一个 MergeTree 表，从 S3 加载了 3000 万条英国房产交易记录，了解了 ClickHouse 如何将数据组织为已排序的 parts 和分区，并通过查询演示了主键索引的强大能力。

MergeTree 引擎是一切的基础——接下来，你可以探索构建在其之上的专用引擎，或了解 Materialized Views 如何进一步扩展这一模式。

接下来请查看以下快速入门：

• Materialized Views 简介

或者进一步查阅参考文档：

• MergeTree 引擎参考
• system.parts 参考
• 选择合适的列类型

Check out the ClickHouse academy for on-demand and live training

所有快速入门