clickhouse分区表操作

分区表操作

只有MergeTree引擎支持数据分区，区内排序，合并，去重

create table tb_partition(
id UInt8,
name String,
birthday DateTime
)engine=MergeTree()
partition by toDate(birthday)
order by id;

insert into tb_partition values
(1,'xl',now()),
(2,'ly','2021-08-27 17:44:30'),
(3,'xf','2021-08-27 17:44:33'),
(4,'xc','1999-09-27 10:00:00');

select * from tb_partition;
┌─id─┬─name─┬────────────birthday─┐
│  2 │ ly   │ 2021-08-27 17:44:30 │
│  3 │ xf   │ 2021-08-27 17:44:33 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────birthday─┐
│  4 │ xc   │ 1999-09-27 10:00:00 │
└────┴──────┴─────────────────────┘
┌─id─┬─name─┬────────────birthday─┐
│  1 │ xl   │ 2021-08-30 09:59:33 │
└────┴──────┴─────────────────────┘


create table tb_city(
id UInt8,
name String,
birthday DateTime,
city String
)engine=MergeTree()
partition by (city)
order by id;

select * from tb_city;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  3 │ c    │ 2001-09-27 10:00:00 │ SH   │
└────┴──────┴─────────────────────┴──────┘
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  1 │ a    │ 1999-09-27 10:00:00 │ HZ   │
│  2 │ b    │ 2000-09-27 10:00:00 │ HZ   │
└────┴──────┴─────────────────────┴──────┘


查看分区数据
select table,name,partition from system.parts where table='tb_partition'     
┌─table───┬─name───────────────────────────────────┬─partition─┐
│ tb_city │ aa2c8afe45b9cefa4743ec6ea0cd0cf7_1_1_0 │ HZ        │
│ tb_city │ d5c94d4e3fd2c16ed94e970edcfe56a3_2_2_0 │ SH        │
└─────────┴────────────────────────────────────────┴───────────┘


删除分区
alter table tb_city drop partition 'HZ';
select * from tb_city;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  3 │ c    │ 2001-09-27 10:00:00 │ SH   │
└────┴──────┴─────────────────────┴──────┘

insert into table tb_city values(2,'ly','2021-08-27 17:44:30','SH');
insert into table tb_city values(1,'xl',now(),'HZ');
select * from tb_city;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  2 │ ly   │ 2021-08-27 17:44:30 │ SH   │
└────┴──────┴─────────────────────┴──────┘
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  1 │ xl   │ 2021-08-30 10:34:13 │ HZ   │
└────┴──────┴─────────────────────┴──────┘
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  3 │ c    │ 2001-09-27 10:00:00 │ SH   │
└────┴──────┴─────────────────────┴──────┘

合并分区
optimize table tb_city;   
select * from tb_city;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  1 │ xl   │ 2021-08-30 10:34:13 │ HZ   │
└────┴──────┴─────────────────────┴──────┘
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  2 │ ly   │ 2021-08-27 17:44:30 │ SH   │
│  3 │ c    │ 2001-09-27 10:00:00 │ SH   │
└────┴──────┴─────────────────────┴──────┘

复制分区
create table tb_city_back as tb_city;

show create table tb_city_back;
CREATE TABLE system.tb_city_back(    
`id` UInt8,    
`name` String,    
`birthday` DateTime,    
`city` String
)ENGINE = MergeTree
PARTITION BY city
ORDER BY id
SETTINGS index_granularity = 8192


复制分区
alter table tb_city_back replace partition 'SH' from tb_city;    
select * from tb_city_back;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  2 │ ly   │ 2021-08-27 17:44:30 │ SH   │
│  3 │ c    │ 2001-09-27 10:00:00 │ SH   │
└────┴──────┴─────────────────────┴──────┘

分区的装载和卸载
分区的卸载
alter table tb_city detach partition 'SH';    

select * from tb_city;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  1 │ xl   │ 2021-08-30 10:34:13 │ HZ   │
└────┴──────┴─────────────────────┴──────┘

被卸载掉的分区会移动到detach目录下记住，
一旦分区被移动到了detached子目录，就代表它已经脱离了clickhouse的管理，
clickhouse并不会主动清理这些文件，这些分区文件会一直存在，除非我们主动删除或者使用attach语句重新装载


分区的装载
alter table tb_city attach partition 'SH';    
select * from tb_city;
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  2 │ ly   │ 2021-08-27 17:44:30 │ SH   │
│  3 │ c    │ 2001-09-27 10:00:00 │ SH   │
└────┴──────┴─────────────────────┴──────┘
┌─id─┬─name─┬────────────birthday─┬─city─┐
│  1 │ xl   │ 2021-08-30 10:34:13 │ HZ   │
└────┴──────┴─────────────────────┴──────┘