Clickhouse数据表、数据分区partition的基本操作

1. 数据表的基本操作

只有MergeTree系列、Merge、Distributed表引擎支持alter操作

1.1 添加字段

clickhouse1 :)
clickhouse1 :) create table alter_table_test(
:-] id UInt32,
:-] name String,
:-] city String
:-] ) engine = MergeTree()
:-] order by id;
clickhouse1 :) 
clickhouse1 :) alter table alter_table_test add column if not exists score Float32 default 8.8 after city;
clickhouse1 :) 

1.2 修改字段数据类型、添加或修改字段默认值

clickhouse1 :) 
clickhouse1 :) alter table alter_table_test modify column if exists score Float64 default 0.0;
clickhouse1 :) 

• 修改前后的字段数据类型需要兼容

1.3 添加或修改字段备注

clickhouse1 :) 
clickhouse1 :) alter table alter_table_test comment column if exists score '分数';
clickhouse1 :)

1.4 删除字段

clickhouse1 :) 
clickhouse1 :) alter table alter_table_test drop column if exists score;
clickhouse1 :) 

1.5 重命名或移动数据表

clickhouse1 :) 
clickhouse1 :) rename table default.alter_table_test to default.alter_table_rename_test;
clickhouse1 :) 

• 多个db.tb to db.tb用逗号分隔
• 如果源表与目标表数据库不一样，则表示移动数据表, 但数据表的移动只能在同一服务器
• 支持on cluster cluster_name操作

1.6 清空数据表

clickhouse1 :) 
clickhouse1 :) truncate table if exists default.alter_table_rename_test;
clickhouse1 :) 

• 支持on cluster cluster_name操作

1.7 insert数据

2. 数据分区partition的基本操作

测试表和测试数据的准备

clickhouse1 :) 
clickhouse1 :) create table partition_table_test(
:-] id UInt32,
:-] name String,
:-] city String
:-] ) engine = MergeTree()
:-] order by id
:-] partition by city;
clickhouse1 :) 
clickhouse1 :) insert into partition_table_test(id, name, city) values(1, 'name1', 'Beijing');
clickhouse1 :) insert into partition_table_test(id, name, city) values(2, 'name2', 'Shanghai');
clickhouse1 :) 
clickhouse1 :) create table partition_table_test2(
:-] id UInt32,
:-] name String,
:-] city String
:-] ) engine = ReplacingMergeTree()
:-] order by id
:-] partition by city;
clickhouse1 :) 

2.1 查询数据表partition相关信息

clickhouse1 :) 
clickhouse1 :) select database, table, partition, partition_id, name, path from system.parts where database = 'default' and table = 'partition_table_test';
┌─database─┬─table────────────────┬─partition─┬─partition_id─────────────────────┬─name───────────────────────────────────┬─path────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ default  │ partition_table_test │ Shanghai  │ 6a9748c898bf80cb661db240706867aa │ 6a9748c898bf80cb661db240706867aa_2_2_0 │ /root/clickhouse/store/9eb/9ebd4336-b065-48ac-9ebd-4336b06588ac/6a9748c898bf80cb661db240706867aa_2_2_0/ │
│ default  │ partition_table_test │ Beijing   │ 8d2db6c332407299b732139fd8a261c0 │ 8d2db6c332407299b732139fd8a261c0_1_1_0 │ /root/clickhouse/store/9eb/9ebd4336-b065-48ac-9ebd-4336b06588ac/8d2db6c332407299b732139fd8a261c0_1_1_0/ │
└──────────┴──────────────────────┴───────────┴──────────────────────────────────┴────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────┘
clickhouse1 :) 

• 一个partition_id下面存在多个name

2.2 删除partition

clickhouse1 :) 
clickhouse1 :) alter table partition_table_test drop partition 'Beijing'
:-] ;
clickhouse1 :) 
clickhouse1 :) select * from partition_table_test;
┌─id─┬─name──┬─city─────┐
│  2 │ name2 │ Shanghai │
└────┴───────┴──────────┘
clickhouse1 :)

• 上面我们删除了城市为Beijing的partition，然后再通过insert插入新的数据，就间接实现了数据更新

2.3 复制partition

clickhouse1 :) 
clickhouse1 :) alter table partition_table_test2 replace partition 'Shanghai' from partition_table_test;
clickhouse1 :) 
clickhouse1 :) select * from partition_table_test2;
┌─id─┬─name──┬─city─────┐
│  2 │ name2 │ Shanghai │
└────┴───────┴──────────┘
clickhouse1 :) 

• 将A表的数据partition，复制到B表的条件:
  1. 两张表字段结构完全相同
  2. 两张表partition by、order by一样
• 会删除目标表partition_table_test2原来的城市Shanghai partition

2.4 将partition中某一列的数据变为默认值

clickhouse1 :)
clickhouse1 :) alter table partition_table_test clear column name in partition 'Shanghai';
clickhouse1 :)
clickhouse1 :) select * from partition_table_test;
┌─id─┬─name─┬─city─────┐
│  2 │      │ Shanghai │
└────┴──────┴──────────┘
clickhouse1 :) 

• 变更字段不能为primary key、order by、partition by定义的字段
• 如果该字段未声明默认值，则以字段数据类型的默认值为准

2.5 partition的卸载和装载

clickhouse1 :)
clickhouse1 :) alter table partition_table_test detach partition 'Shanghai';
clickhouse1 :) 
clickhouse1 :) select * from partition_table_test;

SELECT *
FROM partition_table_test

Query id: 45460933-7b2e-4389-a056-85d3d75184a8

Ok.

0 rows in set. Elapsed: 0.005 sec. 
clickhouse1 :) 
clickhouse1 :) alter table partition_table_test attach partition 'Shanghai';
clickhouse1 :) 
clickhouse1 :) select * from partition_table_test;
┌─id─┬─name─┬─city─────┐
│  2 │      │ Shanghai │
└────┴──────┴──────────┘
clickhouse1 :) 

• detach后，该分区目录被移动到数据表目录的detached目录下
• clickhouse除了能对detached目录下的分区目录执行attach命令, 不能执行其它操作
• attach则将detached目录下的分区目录重新移回去