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  │└────┴──────┴─────┴─────┘

---

👨‍💻如需博文中的资料请私信博主。

---