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表的条件:
两张表字段结构完全相同

两张表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 🙂