Clickhouse數(shù)據(jù)表、數(shù)據(jù)分區(qū)partition的基本操作代碼

更新時(shí)間：2023年11月20日 10:22:56 作者：Bulut0907

clickhouse的分區(qū)是指將數(shù)據(jù)按照分區(qū)鍵進(jìn)行劃分,每個(gè)分區(qū)可以包含多個(gè)數(shù)據(jù)塊,這篇文章主要介紹了Clickhouse數(shù)據(jù)表、數(shù)據(jù)分區(qū)partition的基本操作代碼,需要的朋友可以參考下

1. 數(shù)據(jù)表的基本操作

只有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 修改字段數(shù)據(jù)類型、添加或修改字段默認(rèn)值

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

修改前后的字段數(shù)據(jù)類型需要兼容

1.3 添加或修改字段備注

clickhouse1 :) 
clickhouse1 :) alter table alter_table_test comment column if exists score '分?jǐn)?shù)';
clickhouse1 :)

1.4 刪除字段

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

1.5 重命名或移動(dòng)數(shù)據(jù)表

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

多個(gè)db.tb to db.tb用逗號(hào)分隔
如果源表與目標(biāo)表數(shù)據(jù)庫(kù)不一樣，則表示移動(dòng)數(shù)據(jù)表, 但數(shù)據(jù)表的移動(dòng)只能在同一服務(wù)器
支持on cluster cluster_name操作

1.6 清空數(shù)據(jù)表

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

支持on cluster cluster_name操作

1.7 insert數(shù)據(jù)

2. 數(shù)據(jù)分區(qū)partition的基本操作

測(cè)試表和測(cè)試數(shù)據(jù)的準(zhǔn)備

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 查詢數(shù)據(jù)表partition相關(guān)信息

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 :)

一個(gè)partition_id下面存在多個(gè)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，然后再通過(guò)insert插入新的數(shù)據(jù)，就間接實(shí)現(xiàn)了數(shù)據(jù)更新

2.3 復(fù)制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表的數(shù)據(jù)partition，復(fù)制到B表的條件:
兩張表字段結(jié)構(gòu)完全相同

兩張表partition by、order by一樣
會(huì)刪除目標(biāo)表partition_table_test2原來(lái)的城市Shanghai partition

2.4 將partition中某一列的數(shù)據(jù)變?yōu)槟J(rèn)值

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定義的字段
如果該字段未聲明默認(rèn)值，則以字段數(shù)據(jù)類型的默認(rèn)值為準(zhǔn)

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后，該分區(qū)目錄被移動(dòng)到數(shù)據(jù)表目錄的detached目錄下
clickhouse除了能對(duì)detached目錄下的分區(qū)目錄執(zhí)行attach命令, 不能執(zhí)行其它操作
attach則將detached目錄下的分區(qū)目錄重新移回去

到此這篇關(guān)于Clickhouse數(shù)據(jù)表、數(shù)據(jù)分區(qū)partition的基本操作的文章就介紹到這了,更多相關(guān)Clickhouse 數(shù)據(jù)分區(qū)partition內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: