作者：俊达 引言MergeTree是Clickhouse中最核心的存储引擎。上一篇文章中，我们介绍了MergeTree的基本结构。1、MergeTree由分区（partiton）和part组成。2、Part是MergeTree可操作的基本数据单元。 当插入数据时，会生成新的part。同时，可以对分区和part进行脱机和挂载操作，还可以对分区进行备份、恢复。此外，还可以移动分区，即将part从一个实例中脱机，然后在另一个实例中挂载，也可以将分区从一个表挂载到另外一个表。 本文将介绍clickhouse MergeTree表的基本运维操作。包括： detach / attachfreezeoptimize1 detach和attachclickhouse中detach相关的命令有： detach table / attach tablealter table detach partition / alter table attach partitionalter table detach part / alter table attach part其中detach table和attach table对整个表生效。detach patition / attach partiton 和 detach part / attach part分别对表的分区和part进行操作。 1.1 detach table / attach tabledetach table后，无法从该表查询数据： ck01 :) select count(*) from metrics;SELECT count(*)FROM metricsQuery id: eb5f8146-3dfc-4fd5-a25f-e7b2d69a377f┌─count()─┐│ 3 │└─────────┘1 row in set. Elapsed: 0.002 sec.ck01 :)ck01 :)ck01 :) detach table metrics;DETACH TABLE metricsQuery id: 39b42e6f-fb70-402f-81a4-248b8f677a9aOk.0 rows in set. Elapsed: 0.001 sec.## detach后，无法查询数据ck01 :) select count(*) from metrics;SELECT count(*)FROM metricsQuery id: 245b8032-b41a-442c-a265-150d7555ae990 rows in set. Elapsed: 0.001 sec.Received exception from server (version 22.6.3):Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table local.metrics doesn't exist. (UNKNOWN_TABLE)detach table后，数据文件还在原先的路径 detach table并不会删除数据文件和metadata文件。通过attach table命令可以恢复表 root@ck01:/data/clickhouse/clickhouse/data/local/metrics# ls -ltotal 12drwxr-x--- 2 root root 4096 Dec 6 03:07 20221129_8_8_2drwxr-x--- 5 root root 4096 Dec 6 03:07 detached-rw-r----- 1 root root 1 Nov 29 05:43 format_version.txt可以通过attach table命令恢复表 ck01 :) attach table metrics;ATTACH TABLE metricsQuery id: b7d80ed1-4588-4884-903b-999523c7bff6Ok.0 rows in set. Elapsed: 0.002 sec.ck01 :) select count(*) from metrics;SELECT count(*)FROM metricsQuery id: 929c04dd-0474-47cd-a3d5-ea02eba0e910┌─count()─┐│ 3 │└─────────┘1 row in set. Elapsed: 0.002 sec.clickhouse重启时，会自动attach table 如果detach table时没有指定PERMANENTLY，则重启clickhouse时，会自动attach table。 DETACH TABLE table [PERMANENTLY]1.2 alter table detach/attach partitiondetach partition将某个分区脱机，分区脱机后，分区内的所有part都会被移动到detach目录下。可以通过attach parition命令将数据加载回来。 1.2.1 detach partition测试数据 ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: 8c35e7ef-af1c-4248-bfae-3c02de5b6b78┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 ││ 20221130 │ 20221130_13_13_0 │ 1 │ 2 ││ 20221130 │ 20221130_14_14_0 │ 1 │ 1 ││ 20221130 │ 20221130_15_15_0 │ 1 │ 1 │└───────────┴──────────────────┴────────┴──────┘使用detach partiton命令将20221130分区detach: ck01 :) alter table metrics detach partition 20221130;ALTER TABLE metrics DETACH PARTITION 20221130Query id: 88999a92-2e2c-4e4a-845d-487101f3af33Ok.0 rows in set. Elapsed: 0.002 sec.ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: ab66d880-6c7d-4312-99c7-3a00c256882f┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 │└───────────┴──────────────────┴────────┴──────┘对应的part被移动到了detached目录： root@ck01:/data/clickhouse/clickhouse/data/local/metrics# ls -l detached/total 12drwxr-x--- 2 root root 4096 Dec 6 05:39 20221130_13_13_0drwxr-x--- 2 root root 4096 Dec 6 05:39 20221130_14_14_0drwxr-x--- 2 root root 4096 Dec 6 05:39 20221130_15_15_01.2.2 attach partition使用attach partition命令将20221130分区attach回来。可以观察到part的名称发生了变化。 ck01 :) alter table metrics attach partition 20221130;ALTER TABLE metrics ATTACH PARTITION 20221130Query id: 24a4784f-653c-4874-8dea-60cb4981abe0Ok.0 rows in set. Elapsed: 0.002 sec.ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: d64868c0-513f-4e36-be57-65abe3cf6065┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 ││ 20221130 │ 20221130_16_16_0 │ 1 │ 1 ││ 20221130 │ 20221130_17_17_0 │ 1 │ 1 ││ 20221130 │ 20221130_18_18_0 │ 1 │ 2 │└───────────┴──────────────────┴────────┴──────┘4 rows in set. Elapsed: 0.002 sec.1.3 alter table detach/attach partdetach part和attach part的操作对象是分区中的某一个具体的part，操作粒度比分区更细。 1.3.1使用detach part命令detach某一个具体的part：ck01 :) alter table metrics detach part '20221130_17_17_0';ALTER TABLE metrics DETACH PART '20221130_17_17_0'Query id: 51fcc860-b8b1-4410-8e89-ffcbd845b085Ok.0 rows in set. Elapsed: 0.002 sec.ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: cec0db47-2c43-460a-b121-f2d2329b1e62┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 ││ 20221130 │ 20221130_16_16_0 │ 1 │ 1 ││ 20221130 │ 20221130_17_17_0 │ 0 │ 1 ││ 20221130 │ 20221130_18_18_0 │ 1 │ 2 │└───────────┴──────────────────┴────────┴──────┘4 rows in set. Elapsed: 0.003 sec.detach的part被移动到detached目录 root@ck01:/data/clickhouse/clickhouse/data/local/metrics# ls -l detached/total 4drwxr-x--- 2 root root 4096 Dec 6 05:52 20221130_17_17_01.3.2使用attach part命令attach某一个part：ck01 :) alter table metrics attach part '20221130_17_17_0';ALTER TABLE metrics ATTACH PART '20221130_17_17_0'Query id: a1dfdda0-bc1e-4abe-b14f-aaa7b306a0f4Ok.0 rows in set. Elapsed: 0.001 sec.ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: 5baad864-3700-4993-9a09-2f52a9477459┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 ││ 20221130 │ 20221130_16_16_0 │ 1 │ 1 ││ 20221130 │ 20221130_18_18_0 │ 1 │ 2 ││ 20221130 │ 20221130_19_19_0 │ 1 │ 1 │└───────────┴──────────────────┴────────┴──────┘由于part数据一旦写入之后就不会变化，我们可以使用detach part/attach part命令来移动数据。 2 freeze和unfreezeALTER TABLE table_name [ON CLUSTER cluster] FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name']使用alter table freeze命令给表创建一个备份。如不不指定分区，则会对整个表的所有分区进行备份。 freeze命令通过创建硬连接的方式备份数据，所以不会占用额外的空间。 ck01 :) alter table metrics freeze with name 'backup20221206';ALTER TABLE metrics FREEZE WITH NAME 'backup20221206'Query id: 48279b8e-4e2a-41ed-b0e2-f2a8e2db4652Ok.0 rows in set. Elapsed: 0.010 sec.执行freeze命令之后，可以在clickhouse数据目录的shadow目录下看到对应的文件： root@ck01:/data/clickhouse/clickhouse/shadow# ls -ltotal 12drwxr-x--- 3 root root 4096 Dec 6 06:20 469drwxr-x--- 3 root root 4096 Dec 6 06:21 backup20221206-rw-r----- 1 root root 4 Dec 6 06:21 increment.txtroot@ck01:/data/clickhouse/clickhouse/shadow# tree backup20221206backup20221206└── store └── def └── def88518-fd7b-418d-a7dd-6564e38bba39 ├── 20221129_12_12_0 │ ├── checksums.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.mrk3 │ ├── default_compression_codec.txt │ ├── minmax_tt.idx │ ├── partition.dat │ └── primary.idx ├── 20221130_16_16_0 │ ├── checksums.txt │ ├── columns.txt │ ├── count.txt │ ├── data.bin │ ├── data.mrk3 │ ├── default_compression_codec.txt │ ├── minmax_tt.idx │ ├── partition.dat │ └── primary.idx......使用unfreeze命令删除备份 ck01 :) alter table metrics unfreeze with name '469';ALTER TABLE metrics UNFREEZE WITH NAME '469'Query id: bba89afe-54f7-457b-ab3d-3db76bf3f2f3Ok.0 rows in set. Elapsed: 0.001 sec.由于clickhouse的part文件一旦生成之后就不会修改。可以使用freeze生成的备份文件来恢复数据。 3 optimize使用optimize命令发起表的分区合并操作。 ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: 2917d850-fd9d-4e2c-b02e-9fd129565dc7┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 ││ 20221130 │ 20221130_16_16_0 │ 1 │ 1 ││ 20221130 │ 20221130_18_18_0 │ 1 │ 2 ││ 20221130 │ 20221130_19_19_0 │ 1 │ 1 │└───────────┴──────────────────┴────────┴──────┘4 rows in set. Elapsed: 0.002 sec.ck01 :) optimize table metrics;OPTIMIZE TABLE metricsQuery id: 4f53e875-21bc-4c00-85b3-88c3ef156b1bOk.0 rows in set. Elapsed: 0.002 sec.ck01 :) select partition, name, active,rows from system.parts where table='metrics';SELECT partition, name, active, rowsFROM system.partsWHERE table = 'metrics'Query id: 3a32a138-26f5-4b43-874b-af2e58d18419┌─partition─┬─name─────────────┬─active─┬─rows─┐│ 20221129 │ 20221129_12_12_0 │ 1 │ 1 ││ 20221130 │ 20221130_16_16_0 │ 0 │ 1 ││ 20221130 │ 20221130_16_19_1 │ 1 │ 4 ││ 20221130 │ 20221130_18_18_0 │ 0 │ 2 ││ 20221130 │ 20221130_19_19_0 │ 0 │ 1 │└───────────┴──────────────────┴────────┴──────┘5 rows in set. Elapsed: 0.002 sec.optimize后，创建了新的part 20221130_16_19_1，同时被合并的part active状态设置为0。inactive的part会在一定时间后被自动清理。 更多技术信息请查看云掣官网https://yunche.pro/?t=yrgw