一、DDL操作
1.1 create操作
create table t_order_mt2(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime,
INDEX a total_amount TYPE minmax GRANULARITY 5
) engine =MergeTree
partition by toYYYYMMDD(create_time)
order by (id, sku_id)
primary key (id)
index granularity=8192
;
primary key主鍵(可選)
clickhouse中的主鍵，和其他數(shù)據(jù)庫不太一樣，它只提供了數(shù)據(jù)的一級(jí)索引，但是卻不是唯一約束。這就意味著是可以存在相同primary key的數(shù)據(jù)的。
主鍵的設(shè)定主要依據(jù)是查詢語句中的 where 條件。根據(jù)條件通過對(duì)主鍵進(jìn)行某種形式的二分查找，能夠定位到對(duì)應(yīng)的index granularity,避免了全表掃描。
index granularity稀疏索引(可選)
直接翻譯的話就是索引粒度，指在稀疏索引中兩個(gè)相鄰索引對(duì)應(yīng)數(shù)據(jù)的間隔。clickhouse中的MergeTree默認(rèn)是8192。官方不建議修改這個(gè)值，除非該列存在大量重復(fù)值，比如在一個(gè)分區(qū)中幾萬行才有一個(gè)不同數(shù)據(jù)。
稀疏索引的好處就是可以用很少的索引數(shù)據(jù)，定位更多的數(shù)據(jù)，代價(jià)就是只能定位到索引粒度的第一行，然后再進(jìn)行進(jìn)行一點(diǎn)掃描。
order by(必選)
order by 設(shè)定了分區(qū)內(nèi)的數(shù)據(jù)按照哪些字段順序進(jìn)行有序保存。order by是MergeTree中唯一一個(gè)必填項(xiàng)，甚至比primary key 還重要，因?yàn)楫?dāng)用戶不設(shè)置主鍵的情況，很多處理會(huì)依照order by的字段進(jìn)行處理（比如去重和匯總）。
多個(gè)排序字段的順序，可以遵循兩個(gè)原則：
- WHERE 子句中出現(xiàn)頻次高的字段放到頻率低字段的前面，增加查詢命中索引的概率；
- 維度基數(shù)大的字段放到維度基數(shù)小字段的后面，降低查詢掃描范圍。
什么是字段基數(shù)
以性別為例，gender 有兩種情況，即 gender=0 或 gender=1，那么 gender 的基數(shù)為 2。
注意：主鍵必須是order by字段的前綴字段。如order by 字段是 (id,sku_id) 那么主鍵必須是id 或者(id,sku_id)
二級(jí)索引（可選）
在clickhouse的官網(wǎng)上二級(jí)索引的功能是被標(biāo)注為實(shí)驗(yàn)性的。所以使用二級(jí)索引前需要增加設(shè)置。
set allow_experimental_data_skipping_indices=1;
其中GRANULARITY N 是設(shè)定二級(jí)索引對(duì)于一級(jí)索引粒度的粒度。在一級(jí)索引上加個(gè)二級(jí)索引。
數(shù)據(jù)TTL（可選）
TTL即Time To Live，MergeTree提供了可以管理數(shù)據(jù)或者列的生命周期的功能。
1、列級(jí)別TTL
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id)
2、表級(jí)TTL
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id)
TTL ct + INTERVAL 1 MINUTE DELETE ; -- 超過時(shí)間一分鐘后會(huì)將數(shù)據(jù)刪除
注意：當(dāng)列中的值過期時(shí), ClickHouse會(huì)將它們替換成該列數(shù)據(jù)類型的默認(rèn)值。如果數(shù)據(jù)片段中列的所有值均已過期，則ClickHouse 會(huì)從文件系統(tǒng)中的數(shù)據(jù)片段中此列。
1.2 alter操作
--新增字段
alter table tableName add column newcolname String after col1;
--修改字段類型
alter table tableName modify column newcolname String；
--刪除字段
alter table tableName drop column newcolname;
二、DML操作
2.1 insert操作
插入幾行數(shù)據(jù)到表中
insert into [table_name] values(…),(….)
從表到表的插入
insert into [table_name] select a,b,c from [table_name_2]
2.2 update和delete
ClickHouse提供了Delete 和Update的能力，這類操作被稱為Mutation查詢，它可以看做Alter 的一種。雖然可以實(shí)現(xiàn)修改和刪除，但是和一般的OLTP數(shù)據(jù)庫不一樣，Mutation語句是一種很“重”的操作，而且不支持事務(wù)。“重”的原因主要是每次修改或者刪除都會(huì)導(dǎo)致放棄目標(biāo)數(shù)據(jù)的原有分區(qū)，重建新分區(qū)。所以盡量做批量的變更，不要進(jìn)行頻繁小數(shù)據(jù)的操作。
刪除操作
alter table t_order_smt delete where sku_id ='sku_001';
修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;
由于操作比較“重”，所以 Mutation語句分兩步執(zhí)行，同步執(zhí)行的部分其實(shí)只是進(jìn)行新增數(shù)據(jù)新增分區(qū)和并把舊分區(qū)打上邏輯上的失效標(biāo)記。直到觸發(fā)分區(qū)合并的時(shí)候，才會(huì)刪除舊數(shù)據(jù)釋放磁盤空間，一般不會(huì)開放這樣的功能給用戶，由管理員完成。
總結(jié)：1、不支持事務(wù)，2、同過alert 間接實(shí)現(xiàn)了delete/update 功能， 這是一個(gè)比較重的操作，會(huì)生成新的臨時(shí)分區(qū)，合并后才會(huì)清空過期數(shù)據(jù)，相當(dāng)于重新寫了一份數(shù)據(jù)，3、設(shè)計(jì)一些標(biāo)記字段，定期清除老的數(shù)據(jù)。
刪除所有數(shù)據(jù)
alter table t_order_mt delete where 1=1;
2.3 查詢操作
ClickHouse基本上與標(biāo)準(zhǔn)SQL 差別不大
- 支持子查詢
- 支持CTE(Common Table Expression 公用表表達(dá)式 with 子句)
- 支持各種JOIN， 但是JOIN操作無法使用緩存，所以即使是兩次相同的JOIN語句，ClickHouse也會(huì)視為兩條新SQL
- 窗口函數(shù)處于實(shí)驗(yàn)階段
- 不支持自定義函數(shù)
- GROUP BY 操作增加了 with rollup\with cube\with total 用來計(jì)算小計(jì)和總計(jì)。
演示
- 插入數(shù)據(jù)
insert into t_order_mt values
(101,'sku_001',1000.00,'2022-05-18 12:00:00'),
(102,'sku_002',2000.00,'2022-05-18 12:00:00'),
(103,'sku_004',2500.00,'2022-05-18 12:00:00'),
(104,'sku_002',2000.00,'2022-05-18 12:00:00'),
(105,'sku_003',600.00,'2022-05-19 12:00:00'),
(106,'sku_001',1000.00,'2022-05-22 12:00:00'),
(107,'sku_002',2000.00,'2022-05-22 12:00:00'),
(108,'sku_004',2500.00,'2022-05-22 12:00:00'),
(109,'sku_002',2000.00,'2022-05-22 12:00:00'),
(110,'sku_003',600.00,'2022-05-18 12:00:00');
- with rollup：從右至左去掉維度進(jìn)行小計(jì)
select id, sku_id, sum(total_amount)
from t_order_mt
group by id, sku_id
with rollup;
# 結(jié)果
102 sku_004 5000.00
102 sku_002 33200.00
101 sku_001 2000.00
102 "" 38200.00
101 "" 2000.00
0 "" 40200.00
- with cube：從右至左去掉維度進(jìn)行小計(jì)，再從左至右去掉維度進(jìn)行小計(jì)
select id, sku_id, sum(total_amount)
from t_order_mt
group by id, sku_id
with cube;
102 sku_004 5000.00
102 sku_002 33200.00
101 sku_001 2000.00
102 "" 38200.00
101 "" 2000.00
0 sku_004 5000.00
0 sku_001 2000.00
0 sku_002 33200.00
0 "" 40200.00
- with totals：只計(jì)算合計(jì)
select id, sku_id, sum(total_amount)
from t_order_mt
group by id, sku_id
with totals;
102 sku_004 5000.00
102 sku_002 33200.00
101 sku_001 2000.00
查看分區(qū)狀態(tài)
SELECT partition,name,part_type,active
FROM system.parts
WHERE table = 'partition_directory_merge'
- partition 列存儲(chǔ)分區(qū)的名稱。此示例中有1個(gè)分區(qū)：1
- name 列為分區(qū)目錄的名稱。
- part_type 數(shù)據(jù)存儲(chǔ)格式。Wide: 每一列都單獨(dú)存儲(chǔ)一個(gè)數(shù)據(jù)文件；Compact: 所有列都存儲(chǔ)一個(gè)數(shù)據(jù)文件。
- active 列為片段狀態(tài)。1 代表激活狀態(tài)；0 代表非激活狀態(tài)。非激活片段是那些在合并到較大片段之后剩余的源數(shù)據(jù)片段。損壞的數(shù)據(jù)片段也表示為非活動(dòng)狀態(tài)。
導(dǎo)出數(shù)據(jù)
clickhouse-client --query "select * from t_order_mt where create_time='2022-05-18 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv
更多支持格式參照：https://clickhouse.com/docs/en/interfaces/formats/
參考：
https://clickhouse.com/docs/zh/sql-reference/data-types/
