clickhouse分区设计操作以及优化

  |   0 评论   |   0 浏览

一、分区设计

1.使用类型

　　1）不指定分区键

　　如果建表时不指定分区键，则数据默认不分区，所有数据写到一个默认分区all里面。

　　2）使用整型

　　如果分区键取值属于整型且无法转换为日期类型YYYVYMMDD格式，则直接按照该整型的字符形式输出作为分区ID的取值。

　　3）使用日期类型

　　如果分区键取值属于日期类型，或者是能够转换为YYYYMMDD日期格式的整型，则按照分区表达式逻辑格式化后作为分区ID的取值。

　　4）使用其它类型

　　如果分区键取值既不属于整型，也不属于日期类型，例如String、Float等，则通过128位Hash算法取其Hash值作为分区ID的取值。

（默认情况下，不支持浮点分区键。要使用它，请启用设置allow_floating_point_partition_key。）
　　5）使用tuple元组类型

　　分区键也可以是表达式的元组（类似于主键）

ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));

2.分区目录命名规则

20220124_1_1_0_[11]

20220123_1_2_0

partition_id_ min_block_number_ max_block _number_ level [_version]

　　

**　　！！！旧类型表的部分具有以下名称：（20220117_20220123_2_2_0最小日期 - 最大日期 - 最小块数 - 最大块数 - 级别）。**

　　1）partition_id：20220124，分区生成规则见上；

　　2）min_block_number：1，最小块编号，MergeTree引|擎从1开始计数，每次+1；

　　3）max block_number：1，最大块编号，新插入的数据，最小与最大编号一致；

　　4）level：0，这个可以理解为合并的次数，新插入的数据都是0，每合并1次+1。

　　5）mutation version：11是突变版本（如果部分突变）。[并非所有都带有突变]

3.分区目录合并过程

　　如图：　　

　　

　　向表中插入新数据时，此数据存储为按主键排序的单独部分（块）。插入后10-15分钟，同一分区的部分合并为整个部分。

　　！！！合并仅适用于分区表达式具有相同值的数据部分。这意味着不应该制作过于精细的分区（超过大约一千个分区）。否则，SELECT由于文件系统中的文件数量过多以及打开的文件描述符，查询性能会很差。

　　使用system.parts表查看表部分和分区。例如，假设我们有一个visits按月分区的表。让我们对表执行SELECT查询system.parts：

复制代码

SELECT
    partition,
    name,
    active
FROM system.parts
WHERE table = 'visits' and database ='test';

复制代码

复制代码

┌─partition─┬─name──────────────┬─active─┐
│ 202201    │ 202201_1_3_1      │      0 │
│ 202201    │ 202201_1_9_2_11   │      1 │
│ 202201    │ 202201_8_8_0      │      0 │
│ 202201    │ 202201_9_9_0      │      0 │
│ 202202    │ 202202_4_6_1_11   │      1 │
│ 202202    │ 202202_10_10_0_11 │      1 │
│ 202202    │ 202202_11_11_0_11 │      1 │
└───────────┴───────────────────┴────────┘

复制代码

　　该active列显示部件的状态。1活跃; 0处于非活动状态。例如，非活动部分是合并到较大部分后剩余的源部分。损坏的数据部分也被指示为非活动的。

　　同一分区有多个独立的部分（例如，202201_1_3_1和202201_1_9_2）。这意味着这些部分尚未合并。ClickHouse 会定期合并插入的部分数据，大约在插入后 15 分钟。此外，可以使用OPTIMIZE查询执行非计划合并。

　　合并后大约 10 分钟将删除不活动的部分。

4.分区设计

　　分区选择考虑merge (写入放大)、查询(slelect part数量)、启动等方面考虑。在实际生产中选择时间作为分区键,根据表数据的大小按天或者按周进行了分区。

　　如图：

二、分区操作

分区操作

  • DETACH PARTITION - 将分区移动到detached目录并忘记它。
  • DROP PARTITION — 删除分区。
  • ATTACH PART|PARTITION — 将detached目录中的数据块或分区添加到表中。
  • ATTACH PARTITION FROM — 将数据分区从一个表复制到另一个表并添加。
  • REPLACE PARTITION — 将数据分区从一个表复制到另一个表并替换。
  • MOVE PARTITION TO TABLE — 将数据分区从一个表移动到另一个表。
  • CLEAR COLUMN IN PARTITION — 重置分区中指定列的值。
  • CLEAR INDEX IN PARTITION — 重置分区中指定的二级索引。
  • FREEZE PARTITION — 创建分区的备份。
  • UNFREEZE PARTITION — 删除分区的备份。
  • FETCH PARTITION|PART — 从另一台服务器下载数据块或分区。
  • MOVE PARTITION|PART — 将分区/数据块移动到另一个磁盘或卷。
  • UPDATE IN PARTITION — 按条件更新分区内的数据。
  • DELETE IN PARTITION — 按条件删除分区内的数据。

　　DETACH PARTITION|PART

ALTER TABLE table_name DETACH PARTITION|PART partition_expr 

　　将指定分区的所有数据移动到detached目录中。服务器忘记了分离的数据分区，就好像它不存在一样。在进行ATTACH查询之前，服务器不会知道此数据。

　　例子：

ALTER TABLE mt DETACH PARTITION '2020-11-21';
ALTER TABLE mt DETACH PART 'all_2_2_0'; 

　　执行查询后，你可以对detached目录中的数据做任何你想做的事情——从文件系统中删除它，或者直接离开它。

　　此查询被复制——它将数据移动到detached所有副本上的目录。请注意，只能在领导者副本上执行此查询。要确定副本是否是领导者，请对system.replicas表执行SELECT查询。或者，对所有副本进行查询更容易- 所有副本都抛出异常，除了领导者副本（因为允许多个领导者）。DETACH

　　DROP PARTITION|PART

ALTER TABLE table_name DROP PARTITION|PART partition_expr 

　　从表中删除指定的分区。此查询将分区标记为非活动并完全删除数据，大约在 10 分钟内。

　　查询被复制——它删除所有副本上的数据。

　　例子：

ALTER TABLE mt DROP PARTITION '2020-11-21';
ALTER TABLE mt DROP PART 'all_4_4_0'; 

　　DROP DETACHED PARTITION|PART

ALTER TABLE table_name DROP DETACHED PARTITION|PART partition_expr 

　　从 中删除指定分区的指定数据块或所有数据块detached。

　　ATTACH PARTITION|PART

ALTER TABLE table_name ATTACH PARTITION|PART partition_expr 

　　将数据从detached目录添加到表中。可以为整个分区或单独的数据块添加数据。例子：

ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0; 

　　此查询被复制。副本发起者检查目录中是否有数据detached。如果数据存在，则查询检查其完整性。如果一切正确，则查询将数据添加到表中。

　　如果接收到附加命令的非发起者副本在其自己的detached文件夹中找到具有正确校验和的数据块，它会附加数据而不从其他副本中获取数据。如果没有具有正确校验和的零件，则从具有该零件的任何副本下载数据。

　　可以将数据detached放在一个副本上的目录中，并使用ALTER ... ATTACH查询将其添加到所有副本上的表中。

　　ATTACH PARTITION FROM

ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1 

　　此查询将数据分区从 复制table1到table2。请注意，既不会从table1也不会从中删除数据table2。

　　要使查询成功运行，必须满足以下条件：

  • 两个表必须具有相同的结构。
  • 两个表必须具有相同的分区键。

　　REPLACE PARTITION

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1 

　　此查询将数据分区从 复制table1到table2并替换 中的现有分区table2。请注意，数据不会从table1.

　　要使查询成功运行，必须满足以下条件：

  • 两个表必须具有相同的结构。
  • 两个表必须具有相同的分区键。

　　MOVE PARTITION TO TABLE

ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest 

　　此查询将数据分区从 移动table_source到，并从 中table_dest删除数据table_source。

　　要使查询成功运行，必须满足以下条件：

  • 两个表必须具有相同的结构。
  • 两个表必须具有相同的分区键。
  • 两个表必须是相同的引擎系列（复制或非复制）。
  • 两个表必须具有相同的存储策略。

　　CLEAR COLUMN IN PARTITION

ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr 

　　重置分区中指定列中的所有值。如果DEFAULT在创建表时确定了该子句，则此查询将列值设置为指定的默认值。

　　例子：

ALTER TABLE visits CLEAR COLUMN hour in PARTITION 201902 

　　FREEZE PARTITION

ALTER TABLE table_name FREEZE [PARTITION partition_expr] [WITH NAME 'backup_name'] 

　　此查询创建指定分区的本地备份。如果PARTITION省略该子句，则查询会立即创建所有分区的备份。

　　！！！note "注意" 整个备份过程在不停止服务器的情况下执行。

　　对于旧式表，可以指定分区名称的前缀（例如，2019） - 然后查询会为所有相应的分区创建备份。

　　在执行时，对于数据快照，查询会创建指向表数据的硬链接。硬链接放置在目录/var/lib/clickhouse/shadow/N/...中，其中：

  • /var/lib/clickhouse/是配置中指定的工作 ClickHouse 目录。
  • N是备份的增量数。
  • 如果WITH NAME指定了'backup_name'参数，则使用参数的值而不是增量数。

**　　！！！“注意” 如果使用一组磁盘存储表中的数据，则shadow/N目录会出现在每个磁盘上，存储PARTITION表达式匹配的数据数据块** 。

　　在备份内部创建与 inside 相同的目录结构/var/lib/clickhouse/。查询chmod对所有文件执行，禁止写入它们。

　　创建备份后，可以将数据从远程服务器复制/var/lib/clickhouse/shadow/到远程服务器，然后将其从本地服务器中删除。请注意，ALTER t FREEZE PARTITION查询不会被复制。它仅在本地服务器上创建本地备份。

　　该查询几乎立即创建备份（但首先它等待对相应表的当前查询完成运行）。

　　ALTER TABLE t FREEZE PARTITION只复制数据，不复制表元数据。要备份表元数据，请复制文件/var/lib/clickhouse/metadata/database/table.sql

　　要从备份中恢复数据，请执行以下操作：

  1. 如果表不存在，则创建它。要查看查询，请使用 .sql 文件（ATTACH在其中替换为CREATE）。
  2. data/database/table/将备份中的目录中的数据复制到/var/lib/clickhouse/data/database/table/detached/目录中。
  3. 运行ALTER TABLE t ATTACH PARTITION查询以将数据添加到表中。

　　从备份恢复不需要停止服务器。

　　UNFREEZE PARTITION

ALTER TABLE 'table_name' UNFREEZE [PARTITION 'part_expr'] WITH NAME 'backup_name' 

　　从磁盘中删除freezed具有指定名称的分区。如果PARTITION省略该子句，则查询会立即删除所有分区的备份。

　　CLEAR INDEX IN PARTITION

ALTER  TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr 

　　该查询的工作方式类似于CLEAR COLUMN，但它重置索引而不是列数据。

　　FETCH PARTITION|PART

ALTER TABLE table_name FETCH PARTITION|PART partition_expr FROM 'path-in-zookeeper' 

　　从另一台服务器下载分区。此查询仅适用于复制的表。

　　查询执行以下操作：

  1. 从指定的分片下载分区|数据块。在“path-in-zookeeper”中，必须指定 ZooKeeper 中分片的路径。
  2. 然后查询把下载的数据放到表的detached目录下table_name。使用ATTACH PARTITION|PART查询将数据添加到表中。

　　例如：

  1. 获取分区
ALTER TABLE users FETCH PARTITION 201902 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PARTITION 201902; 
  1. 获取数据块
ALTER TABLE users FETCH PART 201901_2_2_0 FROM '/clickhouse/tables/01-01/visits';
ALTER TABLE users ATTACH PART 201901_2_2_0; 

　　注意：

  • 查询不会被ALTER ... FETCH PARTITION|PART复制。它仅将部件或分区detached放置到本地服务器上的目录中。
  • 查询被ALTER TABLE ... ATTACH复制。它将数据添加到所有副本。数据从detached目录添加到其中一个副本，并从相邻副本添加到其他副本。

　　在下载之前，系统会检查分区是否存在以及表结构是否匹配。从健康的副本中自动选择最合适的副本。

　　虽然调用了查询ALTER TABLE，但它不会更改表结构，也不会立即更改表中可用的数据。

　　MOVE PARTITION|PART

　　将分区或数据块移动到另一个卷或磁盘以用于MergeTree-engine 表。

ALTER TABLE table_name MOVE PARTITION|PART partition_expr TO DISK|VOLUME 'disk_name' 

　　ALTER TABLE t MOVE查询：

  • 不复制，因为不同的副本可以有不同的存储策略。
  • 如果未配置指定的磁盘或卷，则返回错误。如果存储策略中指定的数据移动条件无法应用，查询也会返回错误。
  • 在要移动的数据已被后台进程、并发ALTER TABLE t MOVE查询或后台数据合并的结果移动的情况下，可以返回错误。在这种情况下，用户不应执行任何其他操作。

　　例子：

ALTER TABLE hits MOVE PART '20190301_14343_16206_438' TO VOLUME 'slow'
ALTER TABLE hits MOVE PARTITION '2019-09-01' TO DISK 'fast_ssd' 

　　UPDATE IN PARTITION

　　操作与指定过滤表达式匹配的指定分区中的数据。作为一个突变实现。

　　句法：

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] [IN PARTITION partition_id] WHERE filter_expr　　例子

ALTER TABLE mt UPDATE x = x + 1 IN PARTITION 2 WHERE p = 2; 

　　DELETE IN PARTITION

　　删除与指定过滤表达式匹配的指定分区中的数据。作为一个突变实现。

　　句法：

ALTER TABLE [db.]table DELETE [IN PARTITION partition_id] WHERE filter_expr　　例子

ALTER TABLE mt DELETE IN PARTITION 2 WHERE p = 2; 

三、分区优化

　　1）分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为Tuple()，以单表一亿数据为例，分区大小控制在10-30个为最佳。
　　2）那些有相同分区表达式值的数据片段才会合并。这意味着 你不应该用太精细的分区方案（超过一千个分区）。否则，会因为文件系统中的文件数量过多和需要打开的文件描述符过多，导致 SELECT 查询效率不佳。
　　3）还有就是一般我们都是使用的是日期作为分区键，同一分区内有序，不同分区不能保证有序。

原文链接https://www.cnblogs.com/MrYang-11-GetKnow/p/16016400.html

标题：clickhouse分区设计操作以及优化
作者：michael
地址：https://blog.junxworks.cn/articles/2024/05/11/1715412018627.html