clickhouse分区管理

这篇教程，我们创建一个分区并对分区进行删除、修改、卸载、挂载等操作

目录

创建分区表

查看分区信息

手动合并分区

分区删除

清除分区内某一列数据

分区复制

卸载分区

装载分区

---

创建分区表

create table tb_partition(
	oid Int8,
	ctime DateTime,
	money Float64
)engine=MergeTree()
partition by toYYYYMM(ctime)
order by oid;

插入一些数据

insert into tb_partition values 
(1, '2022-02-05 13:22:12', 556.21), 
(2, '2022-03-04 15:18:47', 12.94), 
(3, '2022-03-16 17:58:14', 643.58);

查看分区信息

select table, partition, engine, path from system.parts where table = 'tb_partition';

手动合并分区

optimize table tb_partition;

分区删除

alter table tb_partition drop partition '202204';

清除分区内某一列数据

alter table tb_partition clear column money in partition 202202; 

分区复制

create table tb_partition_new2 as tb_partition;

alter table tb_partition_new2 replace partition 202202 from tb_partition;

卸载分区

alter table tb_partition detach partition 202202;

装载分区

alter table tb_partition attach partition 202202;