clickhouse 分区表

分区表底层怎么存储

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


insert into tb_partition
values (1, 'zs', now()),
       (2, 'ls', '1999-11-11 11:12:13'),
       (3, 'ww', '1999-11-11 12:12:13');

查询数据

1）先从系统表中，查询分区存储情况

select table,
       name,
       partition,
       rows
from system.parts
where table = 'tb_partition';

2）证实分区存储

复制分区

-- 创建相同表结构
create table new_tb_partition as tb_partition;
-- 复制分区 1999-11-11 的数据到表 new_tb_partition
alter table new_tb_partition replace partition '1999-11-11' from tb_partition;
-- 查询数据
select * from new_tb_partition;

删除分区

alter table new_tb_partition drop partition '1999-11-11';

分区合并

insert into tb_partition
values(4, 'lw', '1999-11-11 22:17:13');


select * from tb_partition;

手动触发分区合并

optimize table tb_partition final;

分区装载和卸载

分区卸载：将 2024-03-09 分区数据给卸载掉

alter table tb_partition detach partition '2024-03-09';
select * from tb_partition;

分区装载：将 2024-03-09 分区数据在给装载上

alter table tb_partition attach partition '2024-03-09';

select * from tb_partition;

分区合并规则

create table tb_merge
(
    uid      UInt32,
    name     String,
    birthday Datetime,
    city     String
) engine = MergeTree
      partition by (toDate(birthday))
      order by uid;

1）第1次插入数据

insert into tb_merge
values (1, 'zs', '1999-08-08 11:12:13','BJ'),
       (1, 'zss', '1999-08-08 11:12:13','BJ'),
       (1, 'ls', '1999-11-11 11:12:13','BJ'),
       (1, 'lss', '1999-11-11 12:12:13','BJ');

2）第2次插入数据

insert into tb_merge
values (1, 'zs', '1999-08-08 11:12:13','BJ'),
       (1, 'zss', '1999-08-08 11:12:13','BJ'),
       (1, 'ls', '1999-11-11 11:12:13','BJ'),
       (1, 'lss', '1999-11-11 12:12:13','BJ');

3）手动触发分区合并

-- 如果不加final 可能会合并部分分区
optimize table tb_merge final ;