ClickHouse（二十）：Clickhouse SQL DDL操作-2-分区表DDL操作
进入正文前，感谢宝子们订阅专题、点赞、评论、收藏！关注IT贫道，获取高质量博客内容！
🏡个人主页：含各种IT体系技术，IT贫道_Apache Doris,大数据OLAP体系技术栈,Kerberos安全认证-CSDN博客
📌订阅：拥抱独家专题，你的订阅将点燃我的创作热情！
👍点赞：赞同优秀创作，你的点赞是对我创作最大的认可！
⭐️ 收藏：收藏原创博文，让我们一起打造IT界的荣耀与辉煌！
✏️评论：留下心声墨迹，你的评论将是我努力改进的方向！
目录
1. 查看分区信息
2. 卸载分区
3. 装载分区
4. 删除分区
5. 替换分区
6. 移动分区
7. 重置分区列
ClickHouse中只有MergeTree家族引擎下的表才能分区。这里说的分区表就是MergeTree家族表引擎对应的分区表。
1. 查看分区信息
- 示例：
#在newdb中创建分区表 t_partition ,使用MergeTree引擎node1 :) create table t_partition (id UInt8,name String,age UInt8,loc String) engine = MergeTree() order by id partition by loc;#向表 t_partition 中插入以下数据：node1 :) insert into t_partition values (1,'张三',18,'BJ'),(2,'李四',19,'GZ'),(3,'王五',20,'BJ'),(4,'马六',21,'GZ');#查询表 t_partition 的分区信息node1 :) select database,table,name,partition from system.parts where table = 't_partition';也可以在ClickHouse节点上查看分区信息，路径为：/var/lib/clickhouse/data/newdb/t_partition/,信息如下：
2. 卸载分区
将指定分区的数据移动到 detached 目录。服务器会忽略被分离的数据分区。只有当你使用 ATTACH 时，服务器才会知晓这部分数据。当执行操作以后，可以对 detached 目录的数据进行任意操作，例如删除文件，或者放着不管。
- 卸载分区语法：
ALTER TABLE table_name DETACH PARTITION partition_expr- 示例：
#卸载 表 t_partition 中 ‘BJ’分区数据node1 :) alter table t_partition detach partition 'BJ'#查看表 t_partition中的数据node1 :) select * from t_partition;┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└────┴──────┴─────┴─────┘#查看表 t_partition 中的分区信息node1 :) select database,table,name,partition from system.parts where table = 't_partition';┌─database─┬─table───────┬─name───────────────────────────────────┬─partition─┐│ newdb │ t_partition │ 8700fff36a8bf87b6ea3eedb16d04038_2_2_0 │ GZ │└──────────┴─────────────┴────────────────────────────────────────┴───────────┘#查看路径/var/lib/clickhouse/data/newdb/t_partition/detached中数据，发现卸载的对应分区移动到此目录中
3. 装载分区
我们可以将已经卸载的分区重新装载到对应的表分区中。这里就是将detached目录中的数据重新移动到对应的表数据目录下。
也可以将卸载的分区数据加载到其他表中，但是这个表需要与原来的表具有相同的表结构及相同的分区字段。
- 装载分区数据语法：
ALTER TABLE table_name ATTACH PARTITION partition_expr- 示例：
#将表 t_partition 对应的 ‘BJ’分区装载回来node1 :) alter table t_partition attach partition 'BJ';#查看表 t_partition 中的数据node1 :) select * from t_partition;┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└────┴──────┴─────┴─────┘#查看表 t_partition 分区信息node1 :) select database,table,name,partition from system.parts where table = 't_partition';
4. 删除分区
ClickHouse中的分区表，可以针对分区表删除某个分区，之后再导入当前分区的数据，以达到数据更新的目的。
执行删除分区命名是直接将对应分区数据删除，不会放入detached目录。该操作会将分区标记为不活跃的，然后在大约10分钟内删除全部数据。
- 删除分区语法：
ALTER TABLE table_name DROP PARTITION partition_expr- 示例：
#删除表 t_partition 中的 'BJ' 分区：node1 :) alter table t_partition drop partition 'BJ';#查询 t_partition 中的分区信息：node1 :) select database,table,name,partition from system.parts where table = 't_partition';┌─database─┬─table───────┬─name───────────────────────────────────┬─partition─┐│ newdb │ t_partition │ 8700fff36a8bf87b6ea3eedb16d04038_2_2_0 │ GZ │└──────────┴─────────────┴────────────────────────────────────────┴───────────┘5. 替换分区
替换分区支持将table1表的分区数据复制到table2表，并替换table2表的已有分区。table1表中分区数据不会被删除，table1和table2表必须要有相同的表结构且分区字段相同。这个操作经常用作数据备份、表数据同步操作。
- 替换分区语法：
ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1- 示例：
#创建表 table1 和table2 ，使用MergeTree表引擎，并且两表结构相同node1 :) create table table1 (id UInt8,name String,age UInt8,loc String) engine = MergeTree() order by id partition by loc;node1 :) create table table2 (id UInt8,name String,age UInt8,loc String) engine = MergeTree() order by id partition by loc;#向table1中插入以下数据node1 :) insert into table1 values (1,'张三',18,'BJ'),(2,'李四',19,'GZ'),(3,'王五',20,'BJ'),(4,'马六',21,'GZ');┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└───┴────┴────┴────┘┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└───┴────┴────┴────┘#向table2中插入以下数据node1 :) insert into table2 values (5,'田七',22,'BJ'),(6,'赵八',23,'GZ'),(7,'李九',24,'BJ'),(8,'郑十',25,'GZ');┌─id─┬─name─┬─age─┬─loc─┐│ 5 │ 田七 │ 22 │ BJ ││ 7 │ 李九 │ 24 │ BJ │└───┴────┴────┴────┘┌─id─┬─name─┬─age─┬─loc─┐│ 6 │ 赵八 │ 23 │ GZ ││ 8 │ 郑十 │ 25 │ GZ │└───┴────┴────┴────┘#将table1表中’BJ’分区内的数据替换到table2中node1 :) alter table table2 replace partition 'BJ' from table1;#查看表 table2中的数据node1 :) select * from table2;┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 6 │ 赵八 │ 23 │ GZ ││ 8 │ 郑十 │ 25 │ GZ │└────┴──────┴─────┴─────┘#查看表 table1中的数据，没有变化，不会删除 ‘BJ’ 分区的数据node1 :) select * from table1;┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└────┴──────┴─────┴─────┘6. 移动分区
该操作将 table_source表的数据分区移动到 table_dest表，并删除table_source表的数据。
- 移动分区语法：
ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest- 示例：
#创建表 table_source ,table_dest, 两表结构相同，都是MergeTree引擎表node1 :) create table table_source (id UInt8,name String,age UInt8,loc String) engine = MergeTree() order by id partition by loc;node1 :) create table table_dest (id UInt8,name String,age UInt8,loc String) engine = MergeTree() order by id partition by loc;#向table_source 表中插入以下数据node1 :) insert into table_source values (1,'张三',18,'BJ'),(2,'李四',19,'GZ'),(3,'王五',20,'BJ'),(4,'马六',21,'GZ');┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└───┴────┴────┴────┘┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└───┴────┴────┴────┘#向table_dest 表中插入以下数据：node1 :) insert into table_dest values (5,'田七',22,'BJ'),(6,'赵八',23,'GZ'),(7,'李九',24,'BJ'),(8,'郑十',25,'GZ');┌─id─┬─name─┬─age─┬─loc─┐│ 5 │ 田七 │ 22 │ BJ ││ 7 │ 李九 │ 24 │ BJ │└───┴────┴────┴────┘┌─id─┬─name─┬─age─┬─loc─┐│ 6 │ 赵八 │ 23 │ GZ ││ 8 │ 郑十 │ 25 │ GZ │└───┴────┴────┴────┘#将表 table_source 中的分区‘BJ’的数据移动到 table_dest表中node1 :) alter table table_source move partition 'BJ' to table table_dest;#查看表 table_source中的数据node1 :) select * from table_source;┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└────┴──────┴─────┴─────┘#查看表 table_dest中的数据node1 :) select * from table_dest;┌─id─┬─name─┬─age─┬─loc─┐│ 6 │ 赵八 │ 23 │ GZ ││ 8 │ 郑十 │ 25 │ GZ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 5 │ 田七 │ 22 │ BJ ││ 7 │ 李九 │ 24 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ │└────┴──────┴─────┴─────┘#手动执行 optimize 命令，合并table_dest相同分区数据node1 :) optimize table table_dest;#查询表 table_dest中的数据node1 :) select * from table_dest;┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ 张三 │ 18 │ BJ ││ 3 │ 王五 │ 20 │ BJ ││ 5 │ 田七 │ 22 │ BJ ││ 7 │ 李九 │ 24 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 6 │ 赵八 │ 23 │ GZ ││ 8 │ 郑十 │ 25 │ GZ │└────┴──────┴─────┴─────┘#查看 table_source 表中的数据，分区‘BJ’被删除。node1 :) select * from table_source;┌─id─┬─name─┬─age─┬─loc─┐│ 2 │ 李四 │ 19 │ GZ ││ 4 │ 马六 │ 21 │ GZ │└────┴──────┴─────┴─────┘7. 重置分区列
重置指定分区的特定列的值，就是将指定分区下某列的数据清空，如果建表时使用了 DEFAULT 语句，该操作会将列的值重置为该默认值。
- 重置分区列语法：
ALTER TABLE table_name CLEAR COLUMN column_name IN PARTITION partition_expr- 示例：
#针对之前的表 table_dest中的数据进行操作，清空当前表中 ‘BJ’分区中name列node1 :) alter table table_dest clear column name in partition 'BJ';#查看表 table_dest中的数据node1 :) select * from table_dest;┌─id─┬─name─┬─age─┬─loc─┐│ 1 │ │ 18 │ BJ ││ 3 │ │ 20 │ BJ ││ 5 │ │ 22 │ BJ ││ 7 │ │ 24 │ BJ │└────┴──────┴─────┴─────┘┌─id─┬─name─┬─age─┬─loc─┐│ 6 │ 赵八 │ 23 │ GZ ││ 8 │ 郑十 │ 25 │ GZ │└────┴──────┴─────┴─────┘👨💻如需博文中的资料请私信博主。