clickhouse分区相关操作小记

废话不多说直接上操作：

创建分区表

示例一：函数式分区

Clickhouse>  create table visits(userid int,visitdate date,website String) engine=MergeTree() PARTITION BY toYYYYMM(visitdate) order by userid;
 
CREATE TABLE visits
(
    `userid` int,
    `visitdate` date,
    `website` String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(visitdate)
ORDER BY userid
Ok.

示例二：字段直接分区

create table test.user_action_log (
   `event_time` DateTime,
   `user_id` String,
   `user_name` String,
   `school_id` String,
   `school_name` String,
   `stay_time` Float64,
   `create_time` DateTime,
   `student_id` String,
   `grade` String,
   `date_key` String
)
ENGINE = MergeTree()
PARTITION BY (date_key)
ORDER BY (user_id,student_id,school_id)
PRIMARY KEY (user_id)
SETTINGS index_granularity = 8192;

注：在创建表时一定要指定排序键，这一点非常重要，在批量插入数据时也可以对数据提前进行排序

插入数据:

下面我们向示例一中表插入数据：

Clickhouse> insert into visits(userid,website,visitdate) values(100,'www.baidu.com','2020-06-01'),(100,'www.google.com','2020-07-02'),(100,'www.toutiao.com','2020-08-03');
 
INSERT INTO visits (userid, website, visitdate) VALUES
Ok.
3 rows in set. Elapsed: 0.004 sec. 

查询下插入的数据，可以发现以上操作没啥子问题：

Clickhouse> select * from visits where visitdate='2020-07-02';
 
SELECT *
FROM visits
WHERE visitdate = '2020-07-02'
 
┌─userid─┬──visitdate─┬─website────────┐
│    100 │ 2020-07-02 │ www.google.com │
└────────┴────────────┴────────────────┘
 
1 rows in set. Elapsed: 0.013 sec. 

clickhouse想对于hive没有show partitions xxx来展示分区的操作我们可以通过查询数据库中的system.parts表来查看想要的信息。

Clickhouse> select database,table,partition,partition_id,name
,path from system.parts where table='visits';
 
SELECT 
    database,
    table,
    partition,
    partition_id,
    name,
    path
FROM system.parts
WHERE table = 'visits'
 
┌─database─┬─table──┬─partition─┬─partition_id─┬─name─────────┬─path───────────────────────────────────────────────────┐
│ datasets │ visits │ 202006    │ 202006       │ 202006_1_1_0 │ /var/lib/clickhouse/data/datasets/visits/202006_1_1_0/ │
│ datasets │ visits │ 202007    │ 202007       │ 202007_2_2_0 │ /var/lib/clickhouse/data/datasets/visits/202007_2_2_0/ │
│ datasets │ visits │ 202008    │ 202008       │ 202008_3_3_0 │ /var/lib/clickhouse/data/datasets/visits/202008_3_3_0/ │
└──────────┴────────┴───────────┴──────────────┴──────────────┴────────────────────────────────────────────────────────┘
 
3 rows in set. Elapsed: 0.002 sec. 

删除分区：

alter table visits drop partition 202007 ；

通过删除分区在重新插入数据：

insert into visits(userid,website,visitdate) values(100,'www.baidu.com','2020-06-02')；

可以实现数据更新，先删除分区再插入新的分区数据。

复制分区数据

Clickhouse> alter table visits replace partition 201908 from visits ;

ALTER TABLE visits REPLACE PARTITION 201908 FROM visits

Ok.

0 rows in set. Elapsed: 0.009 sec. 

重置分区：

Clickhouse> alter table visits CLEAR column website in partition 202007;
 
ALTER TABLE visits CLEAR COLUMN website IN PARTITION 202007

查询结果：

Clickhouse> select * from visits FORMAT PrettyCompactMonoBlock;
 
SELECT *
FROM visits
FORMAT PrettyCompactMonoBlock
 
┌─userid─┬──visitdate─┬─website─────────┐
│    100 │ 2020-06-01 │ www.baidu.com   │
│    100 │ 2020-07-02 │                 │
│    100 │ 2020-08-03 │ www.toutiao.com │
└────────┴────────────┴─────────────────┘
 
3 rows in set. Elapsed: 0.002 sec. 

卸载和装载分区|数据块：

alter table visits DETACH  partition 202007; --将分区数据移动到 detached ，并且忘记它
 
alter table visits ATTACH  partition 202007; --将detached 目录中的分区重新添加到表中.
ALTER TABLE visits ATTACH PART 201901_2_2_0;--将detached 目录中单独的数据块重新添加到表中

分区备份 (同分区复制)

ALTER TABLE table_name FREEZE [PARTITION partition_expr]

示例一：备份指定分区

ALTER TABLE test.user_action_log  FREEZE PARTITION '2020-12-04';

示例二：备份所有分区

ALTER TABLE test.user_action_log  FREEZE ;

该操作为指定分区创建一个本地备份。如果 PARTITION 语句省略，该操作会一次性为所有分区创建备份。

分区还原：

跨表分区关联：

ALTER TABLE table2 ATTACH PARTITION partition_expr FROM table1

该操作将 table1 表的数据分区复制到 table2 表的已有分区。注意table1表的数据不会被删除。
为保证该操作能成功运行，下列条件必须满足：

• 2张表必须有相同的结构
• 2张表必须有相同的分区键

跨表分区移动

ALTER TABLE table_source MOVE PARTITION partition_expr TO TABLE table_dest

该操作将 table_source表的数据分区移动到 table_dest表，并删除table_source表的数据。
为保证该操作能成功运行，下列条件必须满足：

• 2张表必须有相同的结构
• 2张表必须有相同的分区键
• 2张表必须属于相同的引擎系列（可复制表或不可复制表）
• 2张表必须有相同的存储方式

跨表分区替换：

ALTER TABLE table2 REPLACE PARTITION partition_expr FROM table1

该操作将 table1 表的数据分区复制到 table2表，并替换 table2表的已有分区。注意table1表的数据不会被删除。
为保证该操作能成功运行，下列条件必须满足：

• 2张表必须有相同的结构
• 2张表必须有相同的分区键

分区索引：

删除分区的索引

ALTER TABLE table_name CLEAR INDEX index_name IN PARTITION partition_expr

该操作和 CLEAR COLUMN类似，但是它重置的是索引而不是列的数据。

更新分区索引名

ALTER TABLE [db.]table MATERIALIZE INDEX name IN PARTITION partition_name

该操作更新 partition_name分区中的二级索引 name.单次操作可以包含多个逗号分隔的命令。