clickhouse使用的一點總結

等你歸去來發表於2021-12-03

　　clickhouse據說是用在大資料量的olap場景列式儲存資料庫，也有幸能夠用到它在實際場景中落地。本篇就來說說簡單的使用心得吧。

 

1. 整體說明

　　架構啥的，就不多說了，列式儲存、大資料量、高效能。參見官方文件地址: https://clickhouse.com/docs/en/

　　對於使用者而言，除了泛泛而談的架構之外，更多的是如何使用的問題。

　　從整體而言，clickhouse的使用方法基本遵守普通的sql規範，所以基本上只要你會寫sql，對於普通的增刪改查就問題不大了。也就是說應對業務而言，問題並不大了。

　　比如: create table...; select xx from t; insert into table xx... ; alter table xx update x=xx...;(當然了，這個用法差異有點大); alter table xx delete where x=xx...(同理);

 

2. 儲存引擎簡說

　　一個資料庫的最大特點，應該就是其儲存引擎或者說儲存方式。而這在clickhouse體現得更加明顯，其擁有超級多的儲存引擎，不管你用不用得上，反正可選範圍很大。

　　其中，我們最常用或者最簡單可使用的是 MergeTree 系列，簡單來說是歸併樹的儲存結構，查詢肯定是很快的，另外，它也適用於大資料量的儲存。所以，一般就選擇這玩意就行了。當然，它下面有很多的子類，需要根據作出相應的改變。

　　比如: ReplicatedMergeTree 代表有多節點儲存資料，這對於高可用查詢是必須的（針對任意節點的查詢也是必須的）。

　　AggregatingMergeTree 代表當前節點是一種按主鍵聚合的資料分片方式。

　　單就MergeTree引擎而言，如果想要有比較優化的應用或者比較特殊的需求，則必須要親自再去細細翻閱clickhouse的官方文件了，太多選擇是真苦惱啊。

　　其他儲存引擎，比如 Log系列，則更少場景會使用到，一般當作臨時表用時，可以考慮。其他的如 File, 則可以算是被當作解析器來使用。。。

　　總之，要全面理解ck的儲存引擎，實非易事，除非深度使用它。

 

3. clickhouse中的主鍵

　　clickhouse中，其實並沒有明確說一定要有主鍵之類的話，只是在建立表時，會預設以排序欄位作為主鍵。

　　它的主鍵的作用，一定程度上相當於普通索引，這可能也是為什麼它沒有明確叫主鍵的原因，因為不需要唯一但有利於查詢。

　　但它還是有  Primary Key 的定義。

 

4. curd sql

　　我們只說最簡單的方式，但其實clickhouse中，有一個非常大的特點就是，它的sql非常之多樣，靈活，不管你用不用得上，反正就是功能很多。而且文件也是呵呵的。

-- 建立表, 值得說明的是，它可以非常複雜的過期策略
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|ALIAS expr2] [compression_codec] [TTL expr2],
    PRIMARY KEY(expr1[, expr2,...])],
    ...
) ENGINE = MergeTree comment 'xxx'
PARTITION BY toYYYYMM(d)
TTL d + INTERVAL 1 MONTH [DELETE],
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';
-- 更新和刪除，這語法夠獨特的，據說是為了讓大家少用這種功能而設計的，厲害了
ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr
ALTER TABLE [db.]table [ON CLUSTER cluster] DELETE WHERE filter_expr
-- 查詢，有很多獨特的用法，如WITH
[WITH expr_list|(subquery)]
SELECT [DISTINCT [ON (column1, column2, ...)]] expr_list
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE sample_coeff]
[ARRAY JOIN ...]
[GLOBAL] [ANY|ALL|ASOF] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI] JOIN (subquery)|table (ON <expr_list>)|(USING <column_list>)
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr_list] [WITH ROLLUP|WITH CUBE] [WITH TOTALS]
[HAVING expr]
[ORDER BY expr_list] [WITH FILL] [FROM expr] [TO expr] [STEP expr]
[LIMIT [offset_value, ]n BY columns]
[LIMIT [n, ]m] [WITH TIES]
[SETTINGS ...]
[UNION  ...]
[INTO OUTFILE filename [COMPRESSION type] ]
[FORMAT format]
-- 資料插入，可以作排除性插入語法
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
INSERT INTO [db.]table [(c1, c2, c3)] SELECT ...
INSERT INTO insert_select_testtable (* EXCEPT(b)) Values (2, 2);
-- 執行計劃查詢，這對於瞭解其內部機制很有幫助，它的執行計劃非常詳細，不過看起來也有點嚇人
EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]


5. 本地與叢集

　　雖然clickhouse號稱是大資料量實時分析資料庫，但它不絕對的使用分散式儲存。而是構造了兩個名詞供選擇，即本地表與分散式表。

　　本地表顧名思義就是，只是儲存在本地機器上的表。這種本地表，只應對某些場景，比如你連線的節點永遠是同一個機器時，可以使用，此時它和mysql之類的資料庫是一樣的，儲存容量和效能都是單機的，但有點值得注意的是當它與分佈表進行關聯查詢時，可能會有你意想不到的結果：報錯。

　　分散式表，就是說它可以每個節點上都能查詢到。這是我們理解的真正的大資料量的分散式儲存，我也不關心資料儲存在哪裡。只要能給到正確的結果就行。實際上，分佈表的背後，是一個個的本地表。不過，它一般會要求有副本儲存機制。

　　但是很無賴的是，我們無法直接建立分散式表，而是要先建立本地表，然後再以此為基礎建立對應的分散式表。即一個建表工作，我們需要做兩遍才能完成。

　　而且，對於資料的寫入，你可以往本地表寫，也可以往分散式表寫。雖然入口的確變多了，但也給了大家很迷惑的感覺。

-- 建立人群原始表
create table loc_tab1 ON CLUSTER ck_cluster (
    xx String comment 'xx',
    version_no String comment 'version, for update'
) ENGINE = ReplicatedMergeTree
partition by xx
order by xx
;
-- 建立分散式表
CREATE TABLE distributed_tab2 AS loc_tab1 ENGINE = Distributed(ck_cluster, currentDatabase(), loc_tab1, rand());

　　

6. bitmap資料操作參考

　　bitmap資料由於其有著超高效能的交差運算能力，以及節省儲存空間的能力，被我們某些場景應用，如碼值資料表。但是為構建bitmap資料，則往往要做比較多的前置工作，而且由於bitmap的資料壓縮，可能會無法應對複雜場景，這些都需要提前評估。

-- 建立原始bitmap表
create table loc_tab1_bitmap ON CLUSTER ck_cluster (
    xx String comment 'xx',
    uv AggregateFunction(groupBitmap, UInt64),
    version_no String comment 'version, for update'
) ENGINE = ReplicatedMergeTree
partition by xx
order by xx
;
-- 建立分散式表
CREATE TABLE distributed_tab2_bitmap AS loc_tab1_bitmap ENGINE = Distributed(ck_cluster, currentDatabase(), loc_tab1_bitmap, rand());
-- 插入人群bitmap表資料, 往本地表插資料，往分散式表讀資料
-- 讀取的資料來源表，一般也會要求是一個分佈寬表，而且其作用如果只是為了構建bitmap資料，則會有一個用後即刪的動作
insert into loc_tab1_bitmap
    select xx, groupBitmapState(toUInt64OrZero(uid)) as uv,version_no
    from dist_data_raw
    group by xx,version_no;
-- 讀取參考，求兩個bitmap資料的交集，併到另一個表中做group by 
with intersect_tab as ( select arrayJoin(bitmapToArray(bitmapAnd(user1, user2))) as uid  from (select uv as user1, 1 as join_id from distributed_tab2_bitmap   where xx = '1') t1  inner join (select uv as user2, 1 as join_id from distributed_tab2_bitmap   where  xx = '2') t2  on t1.join_id = t2.join_id ),a1 as (select x2, toUInt64(xx) as uid from distributed_tb3)  select x2,count(1) as cnt from a1 right join intersect_tab a2 on a1.uid = a2.uid group by x2 order by cnt desc

 

相關文章