Clickhouse表引擎介紹

Clickhouse表引擎

表引擎是Clickhouse的一大特色，表引擎決定了如何存儲數(shù)據(jù)，包括：

  • 數(shù)據(jù)的存儲方式和位置，寫到哪里以及從哪里讀取數(shù)據(jù)
  • 支持哪些查詢以及如何支持
  • 并發(fā)數(shù)據(jù)訪問
  • 索引的使用（如果存在）
  • 是否可以執(zhí)行多線程請求
  • 數(shù)據(jù)復(fù)制參數(shù)

特別注意：引擎的名稱大小寫敏感

一、TinyLog

專門存放小數(shù)據(jù)，什么參數(shù)不用設(shè)置，以列文件形式存儲在磁盤中，不支持索引,沒有并發(fā)控制，會出現(xiàn)覆蓋數(shù)據(jù)風(fēng)險。生產(chǎn)環(huán)境上作用有限，可以用于平時練習(xí)測試用。

二、Memory

內(nèi)存引擎，數(shù)據(jù)保存在內(nèi)存中，Clickhouse的服務(wù)不能停，服務(wù)一停，數(shù)據(jù)就會沒有；查詢速度快，沒有并發(fā)控制，生產(chǎn)環(huán)境上作用有限，可以用于平時練習(xí)測試用。

三、MergeTree

Clickhouse最強(qiáng)大的表引擎當(dāng)屬M(fèi)ergeTree（合并樹）引擎及該系列（*MergeTree）中的其他引擎；地位相當(dāng)于innodb之于Mysql; 而且基于MergeTree，還衍生出了很多非常有特色的引擎。
特點(diǎn)：有索引，可分區(qū)
建表語句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr] --toYYYYMMDD(datetime)
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]


intHash32()和toYYYYDD()兩個函數(shù)在建表時常用

order by是必要的，partition by和primary key等不是必須的

CREATE TABLE tutorial.hits_v1(
    `WatchID` UInt64,
    `JavaEnable` UInt8,
    `Title` String,
    `GoodEvent` Int16,
    `EventTime` DateTime,
    `EventDate` Date,
    `CounterID` UInt32,
    `ClientIP` UInt32,
    `ClientIP6` FixedString(16),
    `RegionID` UInt32,
    `UserID` UInt64,
    `CounterClass` Int8,
    `OS` UInt8,
    `UserAgent` UInt8,
    `URL` String,
    `Referer` String,
    `URLDomain` String,
    `RefererDomain` String,
    `Refresh` UInt8,
    `IsRobot` UInt8,
    `RefererCategories` Array(UInt16),
    `URLCategories` Array(UInt16),
    `URLRegions` Array(UInt32),
    `RefererRegions` Array(UInt32),
    `ResolutionWidth` UInt16,
    `ResolutionHeight` UInt16,
    `ResolutionDepth` UInt8,
    `FlashMajor` UInt8,
    `Params` Array(String),
    `Goals` Nested(
        ID UInt32,
        Serial UInt32,
        EventTime DateTime,
        Price Int64,
        OrderID String,
        CurrencyID UInt32)
 )
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192

Import Data：由文件(tsv,csv)導(dǎo)入數(shù)據(jù)進(jìn)指定表，在Clickhouse-client命令中Shell語句執(zhí)行，代碼如下：

clickhouse-client --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv
clickhouse-client --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

SAMPLE BY — An expression for sampling. Optional.
If a sampling expression is used, the primary key must contain it. Example: SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)).

The partition key can also be a tuple of expressions (similar to the primary key). For example:

ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign)
PARTITION BY (toMonday(StartDate), EventType)
ORDER BY (CounterID, StartDate, intHash32(UserID));

MergerTree表對新插入的數(shù)據(jù)，往往會在15鐘內(nèi)進(jìn)行按分區(qū)自動合同，如果想快點(diǎn)，也可以使用OPTIMIZE語句，加快速度,一般不要做，生產(chǎn)環(huán)境還是靠后臺自己合并；如下例子：

OPTIMIZE TABLE visits [final] PARTITION 201902;

  • Primary Key:
    索引：只是一個一級索引，不是唯一主鍵，主鍵可以設(shè)置常用維度設(shè)為主鍵，不用在乎唯一，稀疏索引； index granulartiy: 索引顆粒度：兩個索引之間的間隔
    索引 + 掃描：先用索引確定要查詢的值的區(qū)間，然后再去這個區(qū)間中掃描。
    好處：索引量小了，可以把索引放在內(nèi)存中，mysql就做不到把索引加載到內(nèi)存中

  • Order By（必填）：
    主鍵默認(rèn)值是Order by, 如果要自定義主鍵，必須是Order by的前綴
    比如order by 字段是（id,sku_id）,那么主鍵必須是id 或者（id,sku_id）
    Order by設(shè)定了分區(qū)內(nèi)的數(shù)據(jù)按照哪些字段順序進(jìn)行有序保存。

  • 二級索引
    目前在Clickhouse的官網(wǎng)上二級索引的功能是被標(biāo)注為實(shí)驗(yàn)性的。例子如下：

create table t_order_mt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime,
    INDEX idx_amount total_amount TYPE minmax GRANULARITY 5
) engine=MergeTree
partition by toYYYYMMDD(create_time)
primary key(id)
order by (id,sku_id)

其中INDEX idx_mount total_mount TYPE minmax GRANULARITY 5是定義二級索引的
GRANULARITY: 是設(shè)定二級索引對于一級索引粒度的粒度。

  • 數(shù)據(jù)TTL
    TTL即Time To Live，MergeTree提供了可以管理數(shù)據(jù)或者列的生命周期功能
    列級別TTL,如下： 
create table t_order_mt(
    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)

表級別TTL，更加常用，如下：

alter table t_order_mt modify TTL create_time + INTERVAL 10 SECOND;

涉及判斷的字段必須是Date或者Datetime類型，推薦使用分區(qū)的日期字段；
能夠使用的時間周期
-SECOND
-MINUTE
-HOUR
-DAY
-WEEK
-MONTH
-QUARTER
-YEAR

加快效果可以使用OPTIMIZE

四、HDFS

ENGINE = HDFS(URI, format)

The URI parameter is the whole file URI in HDFS.
The format parameter specifies one of the available file formats.
To performSELECT queries, the format must be supported for input, and to performINSERT queries – for output.
The available formats are listed in the Formats section.

五、Kafka

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster](
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'host:port',
    kafka_topic_list = 'topic1,topic2,...',
    kafka_group_name = 'group_name',
    kafka_format = 'data_format'[,]
    [kafka_row_delimiter = 'delimiter_symbol',]
    [kafka_schema = '',]
    [kafka_num_consumers = N,]
    [kafka_max_block_size = 0,]
    [kafka_skip_broken_messages = N,]
    [kafka_commit_every_batch = 0,]
    [kafka_thread_per_consumer = 0]

kafka_format：表示kafka消息中數(shù)據(jù)格式，這邊一般推薦使用JSONEachRow，如下圖所示是Clickhouse能接受的數(shù)據(jù)格式
[圖片上傳失敗...(image-7878a9-1600845006241)]

建表例子如下：

CREATE TABLE queue2 (
    timestamp UInt64,
    level String,
    message String
  ) ENGINE = Kafka SETTINGS kafka_broker_list = 'localhost:9092',
                            kafka_topic_list = 'topic',
                            kafka_group_name = 'group1',
                            kafka_format = 'JSONEachRow',
                            kafka_num_consumers = 4;

字節(jié)跳動目前使用Clickhouse實(shí)時接kafka消息的，就是使用kafka engine處理的，不過字節(jié)跳動對Clickhouse進(jìn)行了二次開發(fā)，進(jìn)行了優(yōu)化

六、ReplacingMergeTree

ReplacingMergeTree是MergeTree的一個變種，它存儲特性完全繼承MergeTree，多了一個去重功能
去重時機(jī)：數(shù)據(jù)的去重只會在合并的過程中出現(xiàn)；合并會在未知的時間在后臺進(jìn)行，所以你無法預(yù)先作出計劃。有一些數(shù)據(jù)可能仍未被處理。

去重范圍：如果表經(jīng)過了分區(qū)，去重只會在分區(qū)內(nèi)部進(jìn)行去重，不能執(zhí)行跨分區(qū)去重。

所以 ReplacingMergeTree能力有限，ReplacingMergeTree適用在后臺清除重復(fù)的數(shù)據(jù)以節(jié)省空間，但是不保證沒有重復(fù)的數(shù)據(jù)出現(xiàn)。

create table t_order_rmt(
    id UInt32,
    sku_id  String,
    total_amount  Decimal64(2),
    create_time Datetime
)engine = ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

ReplacingMergeTree()填入的參數(shù)為版本字段，重復(fù)數(shù)據(jù)保留版本字段值最大的，如果不填版本字段，默認(rèn)保留最后一條，是使用order by字段去重

總結(jié)：

  • 實(shí)際上是使用order by字段作為唯一建
  • 去重不能跨分區(qū)
  • 只有合并分區(qū)才會去重
  • 認(rèn)定重復(fù)的數(shù)據(jù)保留，版本字段值最大的
  • 如果版本字段相同則保留最后一筆

七、SummingMergeTree

對于不查詢明細(xì)，只關(guān)心以維度進(jìn)行匯總聚合結(jié)果的場景，如果只使用普通的MergeTree的話，無論是存儲空間的開銷，還是查詢時臨時聚合的開銷都比較大。
Clickhouse為了這種場景，提供了一種能夠“預(yù)聚合”引擎，SummingMergeTree
例子：

create table t_order_smt(
    id UInt32,
    sku_id  String,
    total_amount  Decimal64(2),
    create_time Datetime
)engine = SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

SummingMergeTree()中填的是需要sum的字段，以order by為維度列進(jìn)行聚合，不能跨分區(qū)聚合
既不是維度，又不是度量的其他列：保留第一行
設(shè)計聚合表的話，主鍵id（唯一健值可以去掉），所有字段全部是維度，度量或者時間戳
統(tǒng)計有延遲

FAQ： 能不能直接select 獲取聚合好的值呢：
答： 不行，數(shù)據(jù)可能會有兩部分，一部分是歷史聚合好的值，還有一部分還沒來得及聚合的臨時明細(xì),還需要進(jìn)行sum聚合，使用SummingMergeTree性能會快很多

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容