ClickHouse 基礎 - 實踐 - 調優全視角解析

Clickhouse 是一個高性能且開源的數據庫管理系統,主要用於在線分析處理 (OLAP) 業務。它採用列式存儲結構,可使用 SQL 語句實時生成數據分析報告,另外它還支持索引,分佈式查詢以及近似計算等特性,憑藉其優異的表現,ClickHouse 在各大互聯網公司均有廣泛地應用。

Why ClickHouse

首先我們來看一下 OLAP 場景下的關鍵特徵。

  1. 大多數數據庫訪問都是讀請求。
  2. 數據總是以批量形式寫入數據庫(每次寫入大於 1000 行)。
  3. 已添加的數據一般無需修改。
  4. 每次查詢都從數據庫中讀取大量的行,但是同時又僅需少量的列。
  5. 數據表多爲寬表,即每個表均包含着大量的列。
  6. 查詢量一般較少(非高併發,通常每臺服務器每秒約有數百個查詢或更少)。
  7. 對於簡單查詢,允許的延遲大約爲 50 毫秒(響應時間要迅速)。
  8. 列中的數據相對較小,一般爲數字或短字符串。
  9. 處理單個查詢時需要高吞吐量(每個服務器每秒高達數十億行)。
  10. 事務不是必須的。
  11. 對數據一致性要求低。
  12. 查詢結果明顯小於源數據,換句話說,數據被過濾或聚合後能夠被存放在單臺服務器的內存中。

可以看到,OLAP 業務場景與其它流行的業務場景如 OLTP 等有很大的不同,使用 OLTP 數據庫或 Key-Value 數據庫去處理分析查詢業務將會獲得非常差的性能,而且沒有任何意義。

另外,相比於行式數據庫,列式數據庫則更適用於 OLAP 場景,因爲對於大多數的查詢而言,列式數據庫的處理速度要至少比行式數據庫快 100 倍。二者的性能差別很大,列式數據庫明顯佔優,可以從以下幾方面來解釋:

ClickHouse 特性

相比於其它的列式數據庫,ClickHouse 的以下特性決定了它更適用於 OLAP 業務場景。

  1. 數據壓縮:ClickHouse 會自動對插入的數據進行壓縮,這對於性能的提升起到了至關重要的作用。
  2. 磁盤存儲:ClickHouse 被設計爲工作在傳統磁盤上,這意味着數據存儲的成本較低。
  3. 多核心並行處理:ClickHouse 會利用服務器的一切必要資源,從而以最自然的方式並行化處理大規模查詢。
  4. 分佈式查詢:在 ClickHouse 中,數據可以保存在不同的分片 (shard) 上,查詢可以在所有分片上並行處理。
  5. 支持 SQL:ClickHouse 的查詢語言大部分情況下是與 SQL 標準兼容的,更容易上手。
  6. 向量引擎:ClickHouse 採用了列向量引擎技術,可以更爲高效地使用 CPU 資源。
  7. 實時數據更新:ClickHouse 使用 MergeTree 引擎對數據進行增量排序,數據可以持續不斷地寫入到表中並進行合併,而且在整個過程中不會存在任何加鎖行爲。
  8. 支持索引:ClickHouse 按照排序鍵對數據進行排序並支持主鍵索引,可以使其在幾十毫秒內完成對特定值或特定範圍的查找。
  9. 支持近似計算:ClickHouse 提供了許多在允許犧牲數據精度的情況下對查詢進行加速的方法。

下圖顯示了 ClickHouse 與其它主流列式數據庫的性能對比。可以看到,對於大多數查詢而言,ClickHouse 的響應速度更快,這也是選擇 ClickHouse 作爲 OLAP 數據處理的主要原因。

ClickHouse 配置文件

在使用 ClickHouse 之前,我們需要修改 ClickHouse 配置文件中的一些默認配置,比如數據存儲路徑,集羣信息以及用戶信息等,這樣可以更好地對 ClickHouse 進行管理控制,以滿足我們的業務需求。

01 配置說明

1.ClickHouse 支持多配置文件管理,主配置文件爲 config.xml,默認位於 /etc/clickhouse-server 目錄下,其餘的配置文件均需包含在 /etc/clickhouse-server/config.d 目錄下。
2.ClickHouse 的所有配置文件均是 XML 格式的,而且在每個配置文件中都需要有相同的根元素,通常爲。
3. 主配置文件中的一些配置可以通過 replace 或 remove 屬性被其子配置文件所覆蓋,如子配置文件中的表示將使用該配置來替換主配置文件中的 zookeeper 選項。如果兩個屬性都未指定,則會遞歸組合各配置文件的內容並替換重複子項的值。
4. 另外,配置文件中還可以定義 substitution 替換,如果一個配置包含 incl 屬性,則替換文件中相應的配置將被使用。默認情況下替換文件的路徑爲 /etc/metrika.xml,可以通過 include_from 配置項進行設置。如果待替換的配置不存在,ClickHouse 會記錄錯誤日誌,爲了避免這種情況,可以指定配置項的 optional 屬性來表示該替換是可選的,如。
5. 在啓動時,ClickHouse 會根據已有的配置文件生成相應的預處理文件,這些文件中包含了所有已完成替換和覆蓋的配置項,它們被統一放置於 preprocessed 目錄下,你可以從這些文件中查看最終的配置項是否正確。另外 ClickHouse 會跟蹤配置文件的更改,對於某些配置如集羣配置以及用戶配置等,更改後會自動生效,無需重啓 ClickHouse 服務,而對於其它配置項的更改可能需要重啓服務才能生效。
6. 對於集羣中的全部 ClickHouse 節點,除部分配置(如 macros)外,其它所有的配置最好都保持一致,以便於統一管理及使用。

02 數據路徑配置

  1. 數據路徑下既存儲數據庫和表的元數據信息(位於 metadata 目錄)也存儲表的真實數據(位於 data 目錄)。元數據是指建庫和建表的語句,亦即數據庫和表的結構信息,每次 ClickHouse 啓動時會根據元數據信息去加載相應的數據庫和表。
  2. 數據路徑的配置如下所示,其對應的 XML 標籤爲。
<path>/path/to/clickhouse/</path>
  1. 當單個物理盤無法存儲全部的數據時,可以考慮將不同的數據庫存儲在不同的物理盤上,然後在 /path/to/clickhouse/data/ 目錄下創建軟連接指向其它物理盤上的數據庫目錄。

03 日誌配置

1.ClickHouse 的日誌文件中記錄了各種類型的事件日誌,包括數據的插入和查詢的日誌以及一些配置和數據合併相關的日誌等。一般我們會通過日誌文件找出 ClickHouse 報錯的具體原因,以便解決問題。
2. 日誌的配置如下所示,其對應的 XML 標籤爲。

<logger>
    <level>trace</level>
    <log>/path/to/clickhouse-server/clickhouse-server.log</log>
    <errorlog>/path/to/clickhouse-server/clickhouse-server.err.log</errorlog>
    <size>1000M</size>
    <count>10</count>
</logger>

3.level 表示事件的日誌級別,可以配置爲 trace,debug,information,warning,error 等值。
4.log 表示主日誌文件路徑,該日誌文件中包含所有 level 級別以上的事件日誌。
5.errorlog 表示錯誤日誌文件路徑,該日誌文件僅包含錯誤日誌,便於問題排查。
6.size 表示日誌大小,當日志文件達到指定 size 後,ClickHouse 會進行日誌輪轉。
7.count 表示日誌輪轉的最大數量。
8. 需要注意,因爲事件日誌是由多線程異步寫入到日誌文件中的,所以不同事件之間的日誌會產生交錯,不利於按順序進行日誌排查。但 ClickHouse 爲每個事件都提供了唯一的 ID 來標識,我們可以根據此 ID 來跟蹤事件狀態的變化。

04 集羣配置

  1. 集羣的配置主要用於分佈式查詢,在創建分佈式表 (Distributed) 時會用到。2. 集羣配置文件的示例如下所示,其對應的 XML 標籤爲。
<yandex>
    <remote_servers>
        <cluster_name>
            <shard>
                <weight>1</weight>
                <internal_replication>false</internal_replication>
                <replica>
                    <host>hostname1/ip1</host>
                    <port>9000</port>
                </replica>
            </shard>
            <shard>
                <weight>1</weight>
                <internal_replication>false</internal_replication>
                <replica>
                    <host>hostname2/ip2</host>
                    <port>9000</port>
                </replica>
            </shard>
        </cluster_name>
    </remote_servers>
</yandex>

3.cluster_name 表示集羣名稱,shard 表示集羣的分片(即 ClickHouse 節點),集羣會有多個 shard,每個 shard 上都存有全部數據的一部分。
4.weight 表示數據寫入的權重,當有數據直接寫入集羣時會根據該權重來將數據分發給不同的 ClickHouse 節點,可以理解爲權重輪詢負載均衡。
5.replica 表示每個 shard 的副本,默認爲 1 個,可以設置多個,表示該 shard 有多個副本。正常情況下,每個副本都會存有相同的數據。
6.internal_replication 表示副本間是否爲內部複製,當通過集羣向分片插入數據時會起作用,參數的默認值爲 false,表示向該分片的所有副本中寫入相同的數據(副本間數據一致性不強,無法保證完全同步),true 表示只向其中的一個副本寫入數據(副本間通過複製表來完成同步,能保證數據的一致性)。
7. 在實際情況下,我們一般不會通過集羣進行數據寫入,而是將數據直接寫入到各 ClickHouse 節點。一來通過集羣進行分發數據會帶來二次的網絡延遲,降低了數據的寫入速度,二來當數據量較多時,由於網絡帶寬限制,數據分發節點會成爲數據傳輸的瓶頸,從而拉低了整體的數據寫入效率。
8. 可以定義多個集羣,以應對不同的查詢需要。每次添加新的集羣配置後,無需重啓 ClickHouse 服務,該配置會即時生效。

05 字典配置

  1. 字典就是一種鍵 -> 值映射關係,一般在數據查詢時使用。相比於多表 JOIN 的查詢操作,使用字典查詢會更加高效。
  2. 字典文件的位置需要由 config.xml 文件中的 dictionaries_config 配置項設置。
<dictionaries_config>dictionaries/*_dictionary.xml</dictionaries_config>

上述配置表示 ClickHouse 會從與 config.xml 文件同級的 dictionaries 目錄下加載以 _dictionary.xml 爲後綴的全部字典文件。

  1. 字典配置文件的示例如下所示,其對應的 XML 標籤爲。
<format>TabSeparated</format>
             </file>
        </source>
        <layout>
            <!-- Memory layout configuration -->
            <complex_key_hashed />
        </layout>
        <structure>
            <!-- Complex key configuration -->
            <key>
                <attribute>
                    <name>key</name>
                    <type>String</type>
                </attribute>
            </key>
            <attribute>
                <name>value</name>
                <type>String</type>
                <null_value></null_value>
                <injective>true</injective>
            </attribute>
        </structure>
        <lifetime>300</lifetime>
    </dictionary>
</yandex>

4.name 表示字典名稱。
5.source 表示字典的數據來源,數據來源有多種類型,可以是本地的文本文件,HTTP 接口或者其它各種數據庫管理系統。
6.layout 表示字典在內存中的存儲方式。一般推薦使用 flat,hashed 和 complex_key_hashed 存儲方式,因爲它們提供了最佳的查詢處理速度。
7.structure 表示字典的結構,亦即鍵值對的信息。
key 表示字典的鍵值,它可以由多個屬性組成。
attribute 表示字典的值,也可以有多個。8.lifetime 表示字典的更新頻率,單位爲秒。
9. 創建完字典後,我們就可以通過 SELECT dictGetTYPE) 語句來查詢字典中指定 key 值對應的 value 了。其中 TYPE 表示具體的數據類型,比如獲取字符串類型的值可以使用 dictGetString。
10. 除了使用配置文件來創建字典外,還可以使用 SQL 語句來生成字典。但相對而言,使用配置文件會更加直觀便捷。

06 用戶配置

1.config.xml 可以指定單獨的文件來對用戶信息進行配置,用戶配置文件的路徑通過 users_config 配置項指定,默認爲 users.xml。

<users_config>users.xml</users_config>
  1. 與 config.xml 文件類似,用戶配置也可以被切分爲不同的文件以便於管理,這些文件需要保存到 users.d 目錄下。
    3.ClickHouse 的默認用戶爲 default,密碼爲空。
  2. 用戶配置的示例如下所示,其對應的 XML 標籤爲。
<users>
    <!-- If user name was not specified, 'default' user is used. -->
    <user_name>
        <password></password>
        <!-- Or -->
        <password_sha256_hex></password_sha256_hex>
        <networks incl="networks" replace="replace">
            <ip>::/0</ip>
        </networks>
        <profile>profile_name</profile>
        <quota>default</quota>
    </user_name>
    <!-- Other users settings -->
</users>

5.user_name 表示待添加的用戶名。
6.password 表示明文密碼,不推薦使用該方式設置密碼。
7.password_sha256_hex 表示經過 sha256 hash 後的密碼,推薦使用該方式設置密碼,密碼的生成方式如下所示。

echo -n "$PASSWORD" | sha256sum | tr -d '-'

8.networks 表示允許連接到 ClickHouse 節點的網絡地址列表,可以爲 IP 地址或 Hostname。::/0 表示該用戶可以從任何網絡地址連接到 ClickHouse 節點。
9.profile 表示對用戶的一系列設置,用以控制用戶的行爲,如設置該用戶具有隻讀權限等。它是以單獨的 XML 標籤存在於 users.xml 文件中的。配置的示例如下所示。

<!-- Settings profiles -->
<profiles>
    <!-- Default settings -->
    <default>
        <!-- The maximum number of threads when running a single query. -->
        <max_threads>8</max_threads>
    </default>

    <!-- Settings for quries from the user interface -->
    <profile_name>
        <!-- Maximum memory usage for processing single query, in bytes. -->
        <max_memory_usage>30000000000</max_memory_usage>
        <!-- How to choose between replicas during distributed query processing. -->
        <load_balancing>in_order</load_balancing>
        <readonly>1</readonly>
    </profile_name>
</profiles>

profile 的名稱可以任意,不同的用戶可以配置相同的 profile。另外需要注意,default profile 必須存在,它會在 ClickHouse 啓動時作爲默認的設置使用。10.quota 表示用戶配額設置,用來限制用戶一段時間內的資源使用,如 1 小時內的查詢數不超過 1024 等。它同樣是以單獨的 XML 標籤存在於 users.xml 文件中的。配置的示例如下所示。

<!-- Quotas -->
<quotas>
    <!-- Quota name. -->
    <default>
        <!-- Restrictions for a time period. You can set many intervals with different restrictions. -->
        <interval>
            <!-- Length of the interval. -->
            <duration>3600</duration>

            <!-- Unlimited. Just collect data for the specified time interval. -->
            <queries>0</queries>
            <errors>0</errors>
            <result_rows>0</result_rows>
            <read_rows>0</read_rows>
            <execution_time>0</execution_time>
        </interval>
    </default>
</quotas>

配額限制與 profile 中限制的主要區別在於,它可以對一段時間內運行的一組查詢設置限制,而不是限制單個查詢。11. 除了使用配置文件管理用戶,還可以基於 SQL 語句來創建、修改或刪除用戶。但相對而言,使用配置文件會更加直觀便捷。

07ZooKeeper 配置

1.zookeeper 配置允許 ClickHouse 與一個 ZooKeeper 集羣進行交互。ClickHouse 主要使用 ZooKeeper 來存儲複製表的元數據,當不使用複製表時,該配置可以忽略。
2.ZooKeeper 配置文件的示例如下所示,其對應的 XML 標籤爲。

<yandex>
    <zookeeper replace="true">
        <node index="1">
            <host>hostname1/ip1</host>
            <port>2181</port>
        </node>
        <node index="2">
            <host>hostname2/ip2</host>
            <port>2181</port>
        </node>
        <node index="3">
            <host>hostname3/ip3</host>
            <port>2181</port>
        </node>
    </zookeeper>
</yandex>

3.node 表示一個 ZooKeeper 節點,可以設置多個。當嘗試連接到 ZooKeeper 集羣時,index 屬性指定了各節點的連接順序。

08Macros 配置

1.macros 配置主要用來替換複製表的參數,在創建複製表時需要用到,當不使用複製表時,該配置可以忽略。
2.Macros 配置文件的示例如下所示,其對應的 XML 標籤爲。

<yandex>
    <macros replace="true">
        <shard>01</shard>
        <replica>hostname/ip</replica>
    </macros>
</yandex>

09Prometheus 配置

  1. 該配置用來供 Prometheus 獲取 ClickHouse 的指標信息。
    2.Prometheus 配置的示例如下所示,其對應的 XML 標籤爲。
<prometheus>
    <endpoint>/metrics</endpoint>
    <port>9363</port>
    <metrics>true</metrics>
    <events>true</events>
    <asynchronous_metrics>true</asynchronous_metrics>
</prometheus>

3.endpoint 表示指標接口的 URI。
4.port 表示指標服務所使用的端口。
5.metrics,events 和 asynchronous_metrics 都是標誌項,代表是否暴露相應的指標信息。
6. 配置完成後,即可訪問 http://ip:port/metrics 來查看所有的 ClickHouse 指標信息了。

10MergeTree 配置

  1. 該配置用來對使用 MergeTree 系列引擎的表進行微調。需要注意,除非你對該配置有充分的瞭解,否則不建議修改。
    2.MergeTree 配置的示例如下所示,其對應的 XML 標籤爲。
<merge_tree>
    <!-- If more than this number active parts in single partition, throw 'Too many parts ...' exception. -->
    <parts_to_throw_insert>300</parts_to_throw_insert>
</merge_tree>
  1. 更多 MergeTree 相關配置可以參見源碼中的 MergeTreeSettings.h 頭文件。

11 其他常用配置

  1. 時區配置。
<timezone>Asia/Shanghai</timezone>
  1. 最大連接數配置。
<max_connections>4096</max_connections>
  1. 併發查詢數配置。
<max_concurrent_queries>200</max_concurrent_queries>

4.ClickHouse 最大內存使用量配置。

<max_server_memory_usage>0</max_server_memory_usage>
  1. 可刪除表的最大數據量配置。
<max_table_size_to_drop>0</max_table_size_to_drop>

單位爲字節,默認值爲 50 G,當表中數據大小超過該限制時,不能使用 DROP 語句去刪除該表(防止誤操作)。如果設置爲 0,表示沒有任何限制。如果你仍然想刪除某個數據量超限的表而不想修改上述配置並重啓 ClickHouse 時,可以在 ClickHouse 的數據目錄下創建一個標誌文件 /path/to/clickhouse/flags/force_drop_table 表示可以強制刪除該表,然後執行 DROP 語句即可刪表成功。

需要注意上述標誌文件在執行完一次 DROP 語句後會被自動刪除以防止再次執行意外的 DROP 操作,因此執行創建標誌文件和執行 DROP 語句的系統用戶(非 ClickHouse 用戶)應該保持一致,以避免在執行完 DROP 語句後,用戶沒有權限刪除標誌文件,從而導致後續操作失誤並造成數據損失。

  1. 更多的配置可參見其官方文檔,然後再按需調整。

ClickHouse 表引擎

ClickHouse 的表引擎是 ClickHouse 服務的核心,它們決定了 ClickHouse 的以下行爲:

  1. 數據的存儲方式和位置。
  2. 支持哪些查詢操作以及如何支持。
  3. 數據的併發訪問。
  4. 數據索引的使用。
  5. 是否可以支持多線程請求。
  6. 是否可以支持數據複製。

ClickHouse 包含以下幾種常用的引擎類型:

MergeTree 系列引擎

在所有的表引擎中,最爲核心的當屬 MergeTree 系列表引擎,這些表引擎擁有最爲強大的性能和最廣泛的使用場合。對於非 MergeTree 系列的其他引擎而言,主要用於特殊用途,場景相對有限。而 MergeTree 系列表引擎是官方主推的存儲引擎,支持幾乎所有 ClickHouse 核心功能。

MergeTree 表引擎

MergeTree 在寫入一批數據時,數據總會以數據片段的形式寫入磁盤,且數據片段不可修改。爲了避免片段過多,ClickHouse 會通過後臺線程,定期合併這些數據片段,屬於相同分區的數據片段會被合成一個新的片段。這種數據片段往復合併的特點,也正是合併樹名稱的由來。

MergeTree 作爲家族系列最基礎的表引擎,主要有以下特點:

建表語法

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

建表示例

CREATE TABLE emp_mergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=MergeTree()
  ORDER BY emp_id
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_mergetree 
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_mergetree 
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000); 

-- 查詢數據
-- 按work_place進行分區
cdh04 :) select * from emp_mergetree;

SELECT *
FROM emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

查看一下數據存儲格式,可以看出,存在三個分區文件夾,每一個分區文件夾內存儲了對應分區的數據。

[root@cdh04 emp_mergetree]# pwd
/var/lib/clickhouse/data/default/emp_mergetree
[root@cdh04 emp_mergetree]# ll
總用量 16
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:45 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:44 40d45822dbd7fa81583d715338929da9_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:45 a6155dcc1997eda1a348cd98b17a93e9_2_2_0
drwxr-x--- 2 clickhouse clickhouse    6 9月  17 17:43 detached
-rw-r----- 1 clickhouse clickhouse    1 9月  17 17:43 format_version.txt

進入一個分區目錄查看

注意點

-- 新插入兩條數據
cdh04 :) INSERT INTO emp_mergetree
VALUES (5,'robin','北京',35,'財務部',50000),(6,'lilei','北京',38,'銷售事部',50000);

-- 查詢結果
cdh04 :) select * from emp_mergetree;
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      5 │ robin │ 北京       │  35 │ 財務部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 銷售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

可以看出,新插入的數據新生成了一個數據塊,並沒有與原來的分區數據在一起,我們可以執行 optimize 命令,執行合併操作

-- 執行合併操作
cdh04 :) OPTIMIZE TABLE emp_mergetree PARTITION '北京';
-- 再次執行查詢
cdh04 :) select * from emp_mergetree;                  
SELECT *
FROM emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob   │ 北京       │  33 │ 財務部   │ 50000.00 │
│      5 │ robin │ 北京       │  35 │ 財務部   │ 50000.00 │
│      6 │ lilei │ 北京       │  38 │ 銷售事部 │ 50000.00 │
└────────┴───────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

執行上面的合併操作之後,會新生成一個該分區的文件夾,原理的分區文件夾不變。

-- 插入一條相同主鍵的數據
 INSERT INTO emp_mergetree
VALUES (1,'sam','杭州',35,'財務部',50000);
-- 會發現該條數據可以插入,由此可知,並不會對主鍵進行去重

ReplacingMergeTree 表引擎

上文提到 MergeTree 表引擎無法對相同主鍵的數據進行去重,ClickHouse 提供了 ReplacingMergeTree 引擎,可以針對相同主鍵的數據進行去重,它能夠在合併分區時刪除重複的數據。值得注意的是,ReplacingMergeTree 只是在一定程度上解決了數據重複問題,但是並不能完全保障數據不重複。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_replacingmergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=ReplacingMergeTree()
  ORDER BY emp_id
  PRIMARY KEY emp_id
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_replacingmergetree
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);

注意點

當我們再次向該表插入具有相同主鍵的數據時,觀察查詢數據的變化

INSERT INTO emp_replacingmergetree
VALUES (1,'tom','上海',25,'技術部',50000);
-- 查詢數據,由於沒有進行合併,所以存在主鍵重複的數據
cdh04 :) select * from emp_replacingmergetree;                                        

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 執行合併操作
optimize table emp_replacingmergetree final;
-- 再次查詢,相同主鍵的數據,保留最近插入的數據,舊的數據被清除
cdh04 :) select * from emp_replacingmergetree;       

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

從上面的示例中可以看出,ReplacingMergeTree 是支持對數據去重的,那麼是根據什麼進行去重呢?答案是:ReplacingMergeTree 在去除重複數據時,是以 ORDERBY 排序鍵爲基準的,而不是 PRIMARY KEY。我們在看一個示例:

CREATE TABLE emp_replacingmergetree1 (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=ReplacingMergeTree()
  ORDER BY (emp_id,name) -- 注意排序key是兩個字段
  PRIMARY KEY emp_id     -- 主鍵是一個字段
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree1
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);

再次向該表中插入相同 emp_id 和 name 的數據,並執行合併操作,再觀察數據

-- 插入數據
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','上海',25,'技術部',50000),(1,'sam','上海',25,'技術部',20000);
-- 執行合併操作
optimize table emp_replacingmergetree1 final;
-- 再次查詢,可見相同的emp_id和name數據被去重,而形同的主鍵emp_id不會去重
-- ReplacingMergeTree在去除重複數據時,是以ORDERBY排序鍵爲基準的,而不是PRIMARY KEY
cdh04 :) select * from emp_replacingmergetree1;                                                                           
SELECT *
FROM emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

至此,我們知道了 ReplacingMergeTree 是支持去重的,並且是按照 ORDERBY 排序鍵爲基準進行去重的。細心的你會發現,上面的重複數據是在一個分區內的,那麼如果重複的數據不在一個分區內,會發生什麼現象呢?我們再次向上面的 emp_replacingmergetree1 表插入不同分區的重複數據

-- 插入數據
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','北京',26,'技術部',10000);
-- 執行合併操作
optimize table emp_replacingmergetree1 final;
-- 再次查詢
-- 發現  1 │ tom  │ 北京       │  26 │ 技術部 │ 10000.00
-- 與    1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00
-- 數據重複,因爲這兩行數據不在同一個分區內
-- 這是因爲ReplacingMergeTree是以分區爲單位刪除重複數據的。
-- 只有在相同的數據分區內重複的數據纔可以被刪除,而不同數據分區之間的重複數據依然不能被剔除
cdh04 :) select * from emp_replacingmergetree1;       

SELECT *
FROM emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 技術部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

總結

ReplacingMergeTree 在去除重複數據時,是以 ORDERBY 排序鍵爲基準的,而不是 PRIMARY KEY。

在執行分區合併時,會觸發刪除重複數據。optimize 的合併操作是在後臺執行的,無法預測具體執行時間點,除非是手動執行。

ReplacingMergeTree 是以分區爲單位刪除重複數據的。只有在相同的數據分區內重複的數據纔可以被刪除,而不同數據分區之間的重複數據依然不能被剔除。

如果沒有設置 [ver] 版本號,則保留同一組重複數據中的最新插入的數據;如果設置了 [ver] 版本號,則保留同一組重複數據中 ver 字段取值最大的那一行。

一般在數據量比較大的情況,儘量不要使用該命令。因爲在海量數據場景下,執行 optimize 要消耗大量時間

SummingMergeTree 表引擎

該引擎繼承了 MergeTree 引擎,當合並 SummingMergeTree 表的數據片段時,ClickHouse 會把所有具有相同主鍵的行合併爲一行,該行包含了被合併的行中具有數值數據類型的列的彙總值,即如果存在重複的數據,會對對這些重複的數據進行合併成一條數據,類似於 group by 的效果。

推薦將該引擎和 MergeTree 一起使用。例如,將完整的數據存儲在 MergeTree 表中,並且使用 SummingMergeTree 來存儲聚合數據。這種方法可以避免因爲使用不正確的主鍵組合方式而丟失數據。

如果用戶只需要查詢數據的彙總結果,不關心明細數據,並且數據的彙總條件是預先明確的,即 GROUP BY 的分組字段是確定的,可以使用該表引擎。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns]) -- 指定合併彙總字段
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_summingmergetree (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=SummingMergeTree(salary)
  ORDER BY (emp_id,name) -- 注意排序key是兩個字段
  PRIMARY KEY emp_id     -- 主鍵是一個字段
  PARTITION BY work_place
  ;
 -- 插入數據 
INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_summingmergetree
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);

當我們再次插入具有相同 emp_id,name 的數據時,觀察結果

INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'信息部',10000),(1,'tom','北京',26,'人事部',10000);
cdh04 :) select * from emp_summingmergetree;
-- 查詢
SELECT *
FROM emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 執行合併操作
optimize table emp_summingmergetree final;
cdh04 :) select * from emp_summingmergetree;       
-- 再次查詢,新插入的數據 1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 原來的數據 :1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00
-- 這兩行數據合併成:1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00
SELECT *
FROM emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 財務部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

注意點

要保證 PRIMARY KEY expr 指定的主鍵是 ORDER BY expr 指定字段的前綴,比如

-- 允許
ORDER BY (A,B,C) 
PRIMARY KEY A  
-- 會報錯
--  DB::Exception: Primary key must be a prefix of the sorting key
ORDER BY (A,B,C) 
PRIMARY KEY B

這種強制約束保障了即便在兩者定義不同的情況下,主鍵仍然是排序鍵的前綴,不會出現索引與數據順序混亂的問題。

總結

-- 新插入的數據:        1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 最初的數據 :1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00

-- 聚合合併的結果:1 │ tom  │ 上海       │  25 │ 技術部 │ 30000.00

Aggregatingmergetree 表引擎

該表引擎繼承自 MergeTree,可以使用 AggregatingMergeTree 表來做增量數據統計聚合。如果要按一組規則來合併減少行數,則使用 AggregatingMergeTree 是合適的。

AggregatingMergeTree 是通過預先定義的聚合函數計算數據並通過二進制的格式存入表內。與 SummingMergeTree 的區別在於:SummingMergeTree 對非主鍵列進行 sum 聚合,而 AggregatingMergeTree 則可以指定各種聚合函數。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

CollapsingMergeTree 表引擎

CollapsingMergeTree 就是一種通過以增代刪的思路,支持行級數據修改和刪除的表引擎。它通過定義一個 sign 標記位字段,記錄數據行的狀態。如果 sign 標記爲 1,則表示這是一行有效的數據;如果 sign 標記爲 - 1,則表示這行數據需要被刪除。當 CollapsingMergeTree 分區合併時,同一數據分區內,sign 標記爲 1 和 - 1 的一組數據會被抵消刪除。

每次需要新增數據時,寫入一行 sign 標記爲 1 的數據;需要刪除數據時,則寫入一行 sign 標記爲 - 1 的數據。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

注意點

分數數據摺疊不是實時的,需要後臺進行 Compaction 操作,用戶也可以使用手動合併命令,但是效率會很低,一般不推薦在生產環境中使用。
當進行彙總數據操作時,可以通過改變查詢方式,來過濾掉被刪除的數據
只有相同分區內的數據纔有可能被摺疊。其實,當我們修改或刪除數據時,這些被修改的數據通常是在一個分區內的,所以不會產生影響。

值得注意的是:CollapsingMergeTree 對於寫入數據的順序有着嚴格要求,否則導致無法正常摺疊。
如果數據的寫入程序是單線程執行的,則能夠較好地控制寫入順序;如果需要處理的數據量很大,數據的寫入程序通常是多線程執行的,那麼此時就不能保障數據的寫入順序了。在這種情況下,CollapsingMergeTree 的工作機制就會出現問題。但是可以通過 VersionedCollapsingMergeTree 的表引擎得到解決。

VersionedCollapsingMergeTree 表引擎

上面提到 CollapsingMergeTree 表引擎對於數據寫入亂序的情況下,不能夠實現數據摺疊的效果。VersionedCollapsingMergeTree 表引擎的作用與 CollapsingMergeTree 完全相同,它們的不同之處在於,VersionedCollapsingMergeTree 對數據的寫入順序沒有要求,在同一個分區內,任意順序的數據都能夠完成摺疊操作。

VersionedCollapsingMergeTree 使用 version 列來實現亂序情況下的數據摺疊。

建表語法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

可以看出:該引擎除了需要指定一個 sign 標識之外,還需要指定一個 UInt8 類型的 version 版本號。

GraphiteMergeTree 表引擎

該引擎用來對 Graphite 數據進行'瘦身'及彙總。對於想使用 CH 來存儲 Graphite 數據的開發者來說可能有用。

如果不需要對 Graphite 數據做彙總,那麼可以使用任意的 CH 表引擎;但若需要,那就採用 GraphiteMergeTree 引擎。它能減少存儲空間,同時能提高 Graphite 數據的查詢效率。

Log 系列表引擎

應用場景

Log 系列表引擎功能相對簡單,主要用於快速寫入小表 (1 百萬行左右的表),然後全部讀出的場景。即一次寫入多次查詢。

Log 系列表引擎的特點

共性特點

區別

TinyLog 表引擎使用

該引擎適用於一次寫入,多次讀取的場景。對於處理小批數據的中間表可以使用該引擎。值得注意的是,使用大量的小表存儲數據,性能會很低。

CREATE TABLE emp_tinylog (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=TinyLog();

INSERT INTO emp_tinylog 
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_tinylog
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);

進入默認數據存儲目錄,查看底層數據存儲形式, 可以看出:TinyLog 引擎表每一列都對應的文件

[root@cdh04 emp_tinylog]# pwd
/var/lib/clickhouse/data/default/emp_tinylog
[root@cdh04 emp_tinylog]# ll
總用量 28
-rw-r----- 1 clickhouse clickhouse  56 9月  17 14:33 age.bin
-rw-r----- 1 clickhouse clickhouse  97 9月  17 14:33 depart.bin
-rw-r----- 1 clickhouse clickhouse  60 9月  17 14:33 emp_id.bin
-rw-r----- 1 clickhouse clickhouse  70 9月  17 14:33 name.bin
-rw-r----- 1 clickhouse clickhouse  68 9月  17 14:33 salary.bin
-rw-r----- 1 clickhouse clickhouse 185 9月  17 14:33 sizes.json
-rw-r----- 1 clickhouse clickhouse  80 9月  17 14:33 work_place.bin
## 查看sizes.json數據
## 在sizes.json文件內使用JSON格式記錄了每個.bin文件內對應的數據大小的信息
{
    "yandex":{
        "age%2Ebin":{
            "size":"56"
        },
        "depart%2Ebin":{
            "size":"97"
        },
        "emp_id%2Ebin":{
            "size":"60"
        },
        "name%2Ebin":{
            "size":"70"
        },
        "salary%2Ebin":{
            "size":"68"
        },
        "work_place%2Ebin":{
            "size":"80"
        }
    }
}

當我們執行 ALTER 操作時會報錯,說明該表引擎不支持 ALTER 操作

-- 以下操作會報錯:
-- DB::Exception: Mutations are not supported by storage TinyLog.
ALTER TABLE emp_tinylog DELETE WHERE emp_id = 5;
ALTER TABLE emp_tinylog UPDATE age = 30 WHERE emp_id = 4;

StripLog 表引擎使用

相比 TinyLog 而言,StripeLog 擁有更高的查詢性能(擁有. mrk 標記文件,支持並行查詢),同時其使用了更少的文件描述符(所有數據使用同一個文件保存)。

CREATE TABLE emp_stripelog (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=StripeLog;
-- 插入數據  
INSERT INTO emp_stripelog
VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_stripelog 
VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);
-- 查詢數據
-- 由於是分兩次插入數據,所以查詢時會有兩個數據塊
cdh04 :) select * from emp_stripelog;

SELECT *
FROM emp_stripelog

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部   │ 50000.00 │
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

進入默認數據存儲目錄,查看底層數據存儲形式

[root@cdh04 emp_stripelog]# pwd
/var/lib/clickhouse/data/default/emp_stripelog
[root@cdh04 emp_stripelog]# ll
總用量 12
-rw-r----- 1 clickhouse clickhouse 673 9月  17 15:11 data.bin
-rw-r----- 1 clickhouse clickhouse 281 9月  17 15:11 index.mrk
-rw-r----- 1 clickhouse clickhouse  69 9月  17 15:11 sizes.json

可以看出 StripeLog 表引擎對應的存儲結構包括三個文件:

提示:
StripeLog 引擎將所有數據都存儲在了一個文件中,對於每次的 INSERT 操作,ClickHouse 會將數據塊追加到表文件的末尾
StripeLog 引擎同樣不支持 ALTER UPDATE 和 ALTER DELETE 操作

Log 表引擎使用

Log 引擎表適用於臨時數據,一次性寫入、測試場景。Log 引擎結合了 TinyLog 表引擎和 StripeLog 表引擎的長處,是 Log 系列引擎中性能最高的表引擎。

CREATE TABLE emp_log (
  emp_id UInt16 COMMENT '員工id',
  name String COMMENT '員工姓名',
  work_place String COMMENT '工作地點',
  age UInt8 COMMENT '員工年齡',
  depart String COMMENT '部門',
  salary Decimal32(2) COMMENT '工資'
  )ENGINE=Log;

INSERT INTO emp_log VALUES (1,'tom','上海',25,'技術部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_log VALUES (3,'bob','北京',33,'財務部',50000),(4,'tony','杭州',28,'銷售事部',50000);
-- 查詢數據,
-- 由於是分兩次插入數據,所以查詢時會有兩個數據塊
cdh04 :) select * from emp_log;

SELECT *
FROM emp_log

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技術部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 財務部   │ 50000.00 │
│      4 │ tony │ 杭州       │  28 │ 銷售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

進入默認數據存儲目錄,查看底層數據存儲形式

[root@cdh04 emp_log]# pwd
/var/lib/clickhouse/data/default/emp_log
[root@cdh04 emp_log]# ll
總用量 32
-rw-r----- 1 clickhouse clickhouse  56 9月  17 15:55 age.bin
-rw-r----- 1 clickhouse clickhouse  97 9月  17 15:55 depart.bin
-rw-r----- 1 clickhouse clickhouse  60 9月  17 15:55 emp_id.bin
-rw-r----- 1 clickhouse clickhouse 192 9月  17 15:55 __marks.mrk
-rw-r----- 1 clickhouse clickhouse  70 9月  17 15:55 name.bin
-rw-r----- 1 clickhouse clickhouse  68 9月  17 15:55 salary.bin
-rw-r----- 1 clickhouse clickhouse 216 9月  17 15:55 sizes.json
-rw-r----- 1 clickhouse clickhouse  80 9月  17 15:55 work_place.bin

Log 引擎的存儲結構包含三部分:

提示:
Log 表引擎會將每一列都存在一個文件中,對於每一次的 INSERT 操作,都會對應一個數據塊

外部集成表引擎

ClickHouse 提供了許多與外部系統集成的方法,包括一些表引擎。這些表引擎與其他類型的表引擎類似,可以用於將外部數據導入到 ClickHouse 中,或者在 ClickHouse 中直接操作外部數據源。

例如直接讀取 HDFS 的文件或者 MySQL 數據庫的表。這些表引擎只負責元數據管理和數據查詢,而它們自身通常並不負責數據的寫入,數據文件直接由外部系統提供。目前 ClickHouse 提供了下面的外部集成表引擎:

HDFS:使用方式

ENGINE = HDFS(URI, format)

MySQL:使用方式

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

注意:對於 MySQL 表引擎,不支持 UPDATE 和 DELETE 操作,比如執行下面命令時,會報錯:

-- 執行更新
ALTER TABLE mysql_engine_table UPDATE name = 'hanmeimei' WHERE id = 1;
-- 執行刪除
ALTER TABLE mysql_engine_table DELETE WHERE id = 1;

-- 報錯
DB::Exception: Mutations are not supported by storage MySQL.

JDBC:使用方式

JDBC 表引擎不僅可以對接 MySQL 數據庫,還能夠與 PostgreSQL 等數據庫。爲了實現 JDBC 連接,ClickHouse 使用了 clickhouse-jdbc-bridge 的查詢代理服務。

首先我們需要下載 clickhouse-jdbc-bridge,然後按照 ClickHouse 的 github 中的步驟進行編譯,編譯完成之後會有一個 clickhouse-jdbc-bridge-1.0.jar 的 jar 文件,除了需要該文件之外,還需要 JDBC 的驅動文件,本文使用的是 MySQL,所以還需要下載 MySQL 驅動包。將 MySQL 的驅動包和 clickhouse-jdbc-bridge-1.0.jar 文件放在了 / opt/softwares 路徑下,執行如下命令:

[root@cdh04 softwares]# java -jar clickhouse-jdbc-bridge-1.0.jar  --driver-path .  --listen-host cdh04

其中 --driver-path 是 MySQL 驅動的 jar 所在的路徑,listen-host 是代理服務綁定的主機。默認情況下,綁定的端口是:9019。

然後我們再配置 / etc/clickhouse-server/config.xml,在文件中添加如下配置,然後重啓服務。

<jdbc_bridge>
    <host>cdh04</host>
    <port>9019</port>
</jdbc_bridge>

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]

注意點

當我們一旦查詢完畢之後,ClickHouse 會刪除表內的數據,其實 Kafka 表引擎只是一個數據管道,我們可以通過物化視圖的方式訪問 Kafka 中的數據。

其他特殊的表引擎

Memory 表引擎

Memory 表引擎直接將數據保存在內存中,數據既不會被壓縮也不會被格式轉換。當 ClickHouse 服務重啓的時候,Memory 表內的數據會全部丟失。一般在測試時使用。

CREATE TABLE table_memory (
    id UInt64,
    name String
  ) ENGINE = Memory();

Distributed 表引擎

使用方式

Distributed 表引擎是分佈式表的代名詞,它自身不存儲任何數據,數據都分散存儲在某一個分片上,能夠自動路由數據至集羣中的各個節點,所以 Distributed 表引擎需要和其他數據表引擎一起協同工作。

所以,一張分佈式表底層會對應多個本地分片數據表,由具體的分片表存儲數據,分佈式表與分片表是一對多的關係

Distributed 表引擎的定義形式如下所示

Distributed(cluster_name, database_name, table_name[, sharding_key])

各個參數的含義分別如下:

創建分佈式表是讀時檢查的機制,也就是說對創建分佈式表和本地表的順序並沒有強制要求。

同樣值得注意的是,在上面的語句中使用了 ON CLUSTER 分佈式 DDL,這意味着在集羣的每個分片節點上,都會創建一張 Distributed 表,這樣便可以從其中任意一端發起對所有分片的讀、寫請求。

ClickHouse 常見問題

  1. 重啓 ClickHouse 服務的時間會比較長:主要是由於該節點數據分片過多導致加載緩慢,耐心等待即可。

  2. 數據插入報錯 too many parts exception:主要是由於數據插入過於頻繁,導致數據分片在後臺 merge 緩慢,ClickHouse 啓動自我保護機制,拒絕數據繼續插入。此時可嘗試增大插入數據的 batch_size (10 萬) 並降低數據插入的頻率(每秒 1 次)以緩解該問題。

  3. 複製表變爲只讀:主要是由於 ClickHouse 無法連接 ZooKeeper 集羣或 ZooKeeper 上該複製表的元數據丟失導致的,此時新數據無法插入該表。若要解決該問題,首先要檢查 ZooKeeper 的連接狀況,如果連接失敗,則需進一步檢查網絡狀態以及 ZooKeeper 的狀態,連接恢復後,複製表就可以繼續插入數據了。如果連接正常而元數據丟失,此時可以將複製錶轉爲非複製表然後再進行數據插入操作。

  4. 執行 JOIN 操作時內存超限:可能是由於 JOIN 前後的兩個子查詢中沒有添加明確的過濾條件導致的,也有可能是由於 JOIN 的數據本身就很大,無法全部加載到內存。此時可以嘗試增加過濾條件以減小數據量,或者適當修改配置文件中的內存限制,以裝載更多的數據。

ClickHouse 問題排查方法

  1. 檢查 ClickHouse 運行狀態,確保服務正常運行。
  2. 檢查 ClickHouse 錯誤日誌文件,尋找問題根源。
  3. 檢查系統日誌文件 (/var/log/messages) 中與 ClickHouse 相關的記錄,查看是否是系統操作導致 ClickHouse 異常。
  4. 對於未知問題或 BUG,可以到官方 GitHub 倉庫的 issue 下尋求幫助,需提供完整的問題描述和錯誤日誌信息。

直接寫入的風險

用戶寫入 ClickHouse 一般有兩種選擇:分佈式表(i.e. Distributed),MergeTree 表:

寫入分佈式表

數據寫入分佈式表時,它會將數據先放入本地磁盤的緩衝區,再異步分發給所有節點上的 MergeTree 表。如果數據在同步給 MergeTree 裏面之前這個節點宕機了,數據就可能會丟失;此時如果在失敗後再重試,數據就可能會寫重。因而,直接將數據寫入用分佈式表時,不太好保證數據準確性的和一致性。

當然這個分佈式表還有其他問題,一般來說一個 ClickHouse 集羣會配置多個 shard,每個 shard 都會建立 MergeTree 表和對應的分佈式表。如果直接把數據寫入分佈式表,數據就可能會分發給每個 shard。假設有 N 個節點,每個節點每秒收到一個 INSERT Query,分發 N 次之後,一共就是每秒生成 NxN 個 part 目錄。集羣 shard 數越多,分發產生的小文件也會越多,最後會導致你寫入到 MergeTree 的 Part 的數會特別多,最後會拖垮整個文件的系統。

寫入 MergeTree 表

直接寫入 MergeTree 表可以解決數據分發的問題,但是依然抗不住高頻寫入,如果業務方寫入頻次控制不好,仍然有可能導致 ClickHouse 後臺合併的速度跟不上寫入的速度,最後會使得文件系統壓力過大。

所以一段時間內,我們禁止用戶用 INSERT Query 把數據直接寫入到 ClickHouse。

典型案例 - 推薦系統

業務需求

隨着 ClickHouse 支持的業務範圍擴大,我們也決定支持一些實時的業務,第一個典型案例是推薦系統的實時數據指標:在字節跳動內部 AB 實驗 應用非常廣泛,特別用來驗證推薦算法和功能優化的效果。

最初,公司內部專門的 AB 實驗平臺已經提供了 T+1 的離線實驗指標,而推薦系統的算法工程師們希望能更快地觀察算法模型、或者某個功能的上線效果,因此需要一份能夠實時反饋的數據作爲補充。他們大致有如下需求:

  1. 研發同學有 debug 的需求,他們不僅需要看聚合指標,某些時間還需要查詢明細數據。
  2. 推薦系統產生的數據,維度和指標多達幾百列,而且未來可能還會增加。
  3. 每一條數據都命中了若干個實驗,使用 Array 存儲,需要高效地按實驗 ID 過濾數據。
  4. 需要支持一些機器學習和統計相關的指標計算(比如 AUC)。

當時公司也有維護其他的分析型引擎,比如 Druid 和 ES。ES 不適合大批量數據的查詢,Druid 則不滿足明細數據查詢的需求。而 ClickHouse 則剛好適合這個場景。

  1. 對於明細數據這個需求:ClickHouse > Druid。
  2. 對於維度、指標多的問題,可能經常變動,我們可以用 Map 列的功能,很方便支持動態變更的維度和指標。
  3. 按實驗 ID 過濾的需求,則可以用 Bloom filter 索引。
    4.AUC 之前則已經實現過。

這些需求我們當時剛好都能滿足。

方案設計和比較

Kafka Engine 方案

第二個方案,則是使用 ClickHouse 內置的 Kafka Engine。我們可以在 ClickHouse 服務內部建一張引擎類型爲 Kafka 的表,該表會內置一個消費線程,它會直接請求 Kafka 服務,直接將 Kafka partition 的數據拉過來,然後解析並完成數據構建。對於一個 ClickHouse 集羣而言,可以在每個節點上都建一張 Kafka 表,在每個節點內部啓動一個消費者,這些消費者會分配到若干個 Kafka Partition,然後將數據直接消費到對應。

這樣的架構相對於使用了 Flink 的方案來說更簡單一些,由於少了一次數據傳輸,整體而言開銷會相對小一些,對我們來說也算是補齊了 ClickHouse 的一部分功能(比如 Druid 也支持直接消費 Kafka topic)缺點就是未來可擴展性會更差一些,也略微增加了引擎維護負擔。

Kafka engine 原理

這裏簡單介紹一下如何使用 kafka 引擎,爲了能讓 ClickHouse 消費 Kafka 數據,我們需要三張表:首先需要一張存數據的表也就是 MergeTree;然後需要一張 Kafka 表,它負責描述 Topic、消費數據和解析數據;最後需要一個物化視圖去把兩張表關聯起來,它也描述了數據的流向,某些時候我們可以裏面內置一個 SELECT 語句去完成一些 ETL 的工作。只有當三張表湊齊的時候我們纔會真正啓動一個消費任務。

這是一個簡單的例子:最後呈現的效果,就是通過表和 SQL 的形式,描述了一個 kafka -> ClickHouse 的任務。

最終效果

由於外部寫入並不可控、技術棧上的原因,我們最終採用了 Kafka Engine 的方案,也就是 ClickHouse 內置消費者去消費 Kafka。整體的架構如圖:

  1. 數據由推薦系統直接產生,寫入 Kafka。這裏推薦系統做了相應配合,修改 Kafka Topic 的消息格式適配 ClickHouse 表的 schema。
  2. 敏捷 BI 平臺也適配了一下實時的場景,可以支持交互式的查詢分析。
  3. 如果實時數據有問題,也可以從 Hive 把數據導入至 ClickHouse 中,不過這種情況不多。除此之外,業務方還會將 1% 抽樣的離線數據導入過來做一些簡單驗證,1% 抽樣的數據一般會保存更久的時間。

我們在支持推薦系統的實時數據時遇到過不少問題,其中最大的問題隨着推薦系統產生的數據量越來越大,單個節點的消費能力也要求越來越大:

改進一:異步構建索引

第一做的改進是將輔助索引的構建異步化了:在社區實現中,構建一個 Part 分爲三步:(1)解析輸入數據生成內存中數據結構的 Block;(2)然後切分 Block,並按照表的 schema 構建 columns 數據文件;(3) 最後掃描根據 skip index schema 去構建 skip index 文件。三個步驟完成之後纔會算 Part 文件構建完畢。

目前字節內部的 ClickHouse 並沒有使用社區版本的 skip index,不過也有類似的輔助索引(e.g. Bloom Filter Index, Bitmap Index)。構建 part 的前兩步和社區一致,我們構建完 columns 數據之後用戶即可正常查詢,不過此時的 part 不能啓用索引。此時,再將剛構建好數據的 part 放入到一個異步索引構建隊列中,由後臺線程構建索引文件。這個改進雖然整體的性能開銷沒有變化,但是由於隱藏了索引構建的時間開銷,整體的寫入吞吐量大概能提升 20%

改進二:支持多線程消費

第二個改進是在 Kafka 表內部支持了多線程的消費:

目前實現的 Kafka 表,內部默認只會有一個消費者,這樣會比較浪費資源並且性能達不到性能要求。一開始我們可以通過增大消費者的個數來增大消費能力,但社區的實現一開始是由一個線程去管理多個的消費者,多個的消費者各自解析輸入數據並生成的 Input Stream 之後,會由一個 Union Stream 將多個 Input Stream 組合起來。這裏的 Union Stream 會有潛在的性能瓶頸,多個消費者消費到的數據最後僅能由一個輸出線程完成數據構建,所以這裏沒能完全利用上多線程和磁盤的潛力。

一開始的解決方法,是建了多張 Kafka Table 和 Materialized View 寫入同一張表,這樣就有點近似於多個 INSERT Query 寫入了同一個 MergeTree 表。當然這樣運維起來會比較麻煩,最後我們決定通過改造 Kafka Engine 在其內部支持多個消費線程,簡單來說就是每一個線程它持有一個消費者,然後每一個消費者負責各自的數據解析、數據寫入,這樣的話就相當於一張表內部同時執行多個的 INSERT Query,最後的性能也接近於線性的提升。

改進三:增強容錯處理

對於一個配置了主備節點的集羣,我們一般來說只會寫入一個主備其中一個節點。

爲什麼呢?因爲一旦節點故障,會帶來一系列不好處理的問題。(1)首先當出現故障節點的時候,一般會替換一個新的節點上來,新替換的節點爲了恢復數據,同步會佔用非常大的網絡和磁盤 IO,這種情況,如果原來主備有兩個消費者就剩一個,此時消費性能會下降很大(超過一倍),這對於我們來說是不太能接受的。(2)早先 ClickHouse Kafka engine 對 Kafka partition 的動態分配支持不算好,很有可能觸發重複消費,同時也無法支持數據分片。因此我們默認使用靜態分配,而靜態分配不太方便主備節點同時消費。(3)最重要的一點,ClickHouse 通過分佈式表查詢 ReplicatedMergeTree 時,會基於 log delay 來計算 Query 到底要路由到哪個節點。一旦在主備同時攝入數據的情況下替換了某個節點,往往會導致查詢結果不準。

這裏簡單解釋一下查詢不準的場景。一開始我們有兩副本,Replica #1 某時刻出現故障,於是替換了一個新的節點上來,新節點會開始同步數據,白框部分是已經同步過的,虛線黃框是正在恢復的數據,新寫入的白色框部分就是新寫入的數據。如果此時兩個機器的數據同步壓力比較大或查詢壓力比較大,就會出現 Replica #1 新寫入的數據沒有及時同步到 Replica #2 ,也就是這個綠框部分,大量歷史數據也沒有及時同步到對應的黃框部分,這個情況下兩個副本都是缺少數據的。因此無論是查 Replica #1 還是 Replica #2 得到的數據都是不準的。

對於替換節點導致查詢不准問題,我們先嚐試解決只有一個節點消費的問題。爲了避免兩個節點消費這個數據,改進版的 Kafka engine 參考了 ReplicatedMergeTree 基於 ZooKeeper 的選主邏輯。對於每一對副本的一對消費者,(如上圖 A1 A2),它們會嘗試在 ZooKeeper 上完成選主邏輯,只有選舉稱爲主節點的消費者才能消費,另一個節點則會處於一個待機狀態。一旦 Replica #1 宕機,(如上圖 B1 B2 ),B1 已經宕機連不上 ZooKeeper 了,那 B2 會執行選主邏輯拿到 Leader 的角色,從而接替 B1 去消費數據。

當有了前面的單節點消費機制,就可以解決查詢的問題了。假設 Replica #1 是一個剛換上來的節點,它需要同步黃框部分的數據,這時候消費者會與 ReplicatedMergeTree 做一個聯動,它會檢測其對應的 ReplicatedMergeTree 表數據是否完整,如果數據不完整則代表不能正常服務,此時消費者會主動出讓 Leader,讓副本節點上的消費者也就是 Replica #2 上的 C2 去消費數據。

也就是說,我們新寫入的數據並不會寫入到缺少數據的節點,對於查詢而言,由於查詢路由機制的原因也不會把 Query 路由到缺少數據的節點上,所以一直能查詢到最新的數據。這個機制設計其實和分佈式表的查詢寫入是類似的,但由於分佈表性能和穩定原因不好在線上使用,所以我們用這個方式解決了數據完整性的問題。

小結一下上面說的主備只有一個節點消費的問題

配置兩副本情況下的 Kafka engine,主備僅有一個節點消費,另一個節點待機。

典型案例 - 廣告投放實時數據

業務背景

第二個典型案例是關於廣告的投放數據,一般是運營同學需要查看廣告投放的實時效果。由於業務的特點,當天產生的數據往往會涉及到多天的數據。這套系統原來基於 Druid + Superset 實現的,Druid 在這個場景會有一些難點:

難點一:產生的實時數據由於涉及到較多的時間分區,對於 Druid 來說可能會產生很多 segment,如果寫入今天之前的數據它需要執行一些 MR 的任務去把數據合併在一起,然後才能查歷史的數據,這個情況下可能會導致今天之前的數據查詢並不及時。

難點二:業務數據的維度也非常多,這種場景下使用 Druid 預聚合的效率並不高。

對比 Druid 和 ClickHouse 的特點和性能後,我們決定將該系統遷移到 ClickHouse + 自研敏捷 BI。最後由於維度比較多,並沒有採用預聚合的方式,而是直接消費明細數據。

因爲業務產生的數據由 (1) 大量的當天數據和 (2) 少量的歷史數據 組成。歷史數據一般涉及在 3 個月內,3 個月外的可以過濾掉,但是即便是 3 個月內的數據,在按天分區的情況下,也會因爲單批次生成的 parts 太多導致寫入性能有一定下降。所以我們一開始是把消費的 block_size 調的非常大,當然這樣也有缺點,雖然整個數據吞吐量會變大,但是由於數據落盤之前是沒法查到數據的,會導致整體延時更大。

改進一:Buffer Engine 增強

單次寫入生成過多 parts 的問題其實也有方案解決。社區提供了 Buffer Engine,可以在內存中緩存新寫入的數據,從而緩解 parts 高頻生成的問題。不過社區文檔也介紹了,Buffer Engine 的缺點是不太能配合 ReplicatedMergeTree 一起工作。如果數據寫入到了一對副本(如上圖),那麼 Buffer #1 和 Buffer #2 緩存的數據其實是不一樣的,兩個 Buffer 僅緩存了各自節點上新寫入的數據。對於某個查詢而言,如果查詢路由到 Replica #1,那查詢到的數據是 MergeTree 部分的數據加上 Buffer #1,這部分的數據其實是和 Replica #2 的 MergeTree 加上 Buffer2 的數據並不等價,即便 MergeTree 的數據是相同的。

針對社區版 Buffer Table 存在的問題,我們也做了相應改進。

(1) 我們選擇將 Kafka/Buffer/MergeTree 三張表結合起來,提供的接口更加易用;
(2) 把 Buffer 內置到 Kafka engine 內部, 作爲 Kafka engine 的選項可以開啓 / 關閉;
(3) 最重要的是支持了 ReplicatedMergeTree 情況下的查詢;
(4) Buffer table 內部類似 pipeline 模式處理多個 Block。

這裏解釋一下我們如何解決查詢一致性的問題。前面提到,目前一對副本僅有一個節點在消費,所以一對副本的兩個 Buffer 表,只有一個節點有數據。比如 Consumer #1 在消費時,Buffer #1 就是有緩存數據,而 Buffer #2 則是空的。

對於任何發送到 Replica #1 的查詢,數據肯定是完整的;而對於發送到 Replica #2 的查詢則會額外構建一個特殊的查詢邏輯,從另一個副本的 Buffer #1 讀取數據。這樣發送到 Replica #2 的查詢,獲取到數據就是綠框部分也就是 Replica #2 的 MergeTree 再加上 Replica #1 的 Buffer,它的執行效果是等價於發送到 Replica #1 的查詢。

改進二:消費穩定性增強

由於業務數據的分區比較分散,某個批次的寫入往往生成多個 parts。以上圖爲例,如果某個批次消費到 6 條數據,假設可以分爲 3 個 part(比如涉及到昨天、今天、大前天三天數據),第一條和第四條寫入到第一個 part,第二第五條數據寫入到第二個 part,這時候服務宕機了,沒有及時寫入第三第六條數據。

由於 ClickHouse 沒有事務的支持,所以重啓服務後再消費時,要麼會丟失數據 {3, 6},要麼會重複消費 {1, 4, 2, 5}。對於這個問題我們參考了 Druid 的 KIS 方案自己管理 Kafka Offset, 實現單批次消費 / 寫入的原子語義:實現上選擇將 Offset 和 Parts 數據綁定在一起,增強了消費的穩定性。

每次消費時,會默認創建一個事務,由事務負責把 Part 數據和 Offset 一同寫入磁盤中:如果消費的途中寫入 part #1 part #2 失敗了,事務回滾的時候會把 Offset 和 part #1 part #2 一併回滾,然後從 Part #1 的位置重新消費並重試提交 offset 1-3。

性能奧祕

如上述表格所示,傳統的 MySQL 數據庫的每一行數據都是物理的存儲在一起的。如果我要取 id 等於 10000 這一條數據的 name 列,那我就必須要把這一行數據讀取出來,然後取 name 列。

再比如,下面的 SQL:

在衆多的數據中,我只取一列,但我需要把每條數據都讀取出來。

基於上面傳統數據庫的一些特點,ClickHouse 另闢蹊徑,推出了列式存儲。

看上圖的列式存儲示例,完全和 MySQL 等數據庫不同。當我執行下面的 SQL 時,查詢效率非常的高!

由於 name 列的數據都存儲在一起,因此效率大大的超過了傳統的數據庫。

除了邏輯上的不同,磁盤上的組織結構也大不一樣。

除了列式存儲上的不同,ClickHouse 還有高效的數據壓縮,默認使用 LZ4 算法,總體壓縮比可達 8:1。ClickHouse 還採用了分佈式多主架構提高併發性能,ClickHouse 使讀請求可以隨機打到任意節點,均衡讀壓力,寫請求也無需轉發到 master 節點,不會產生單點壓力。

ClickHouse 還有向量引擎,利用 SIMD 指令實現並行計算。對多個數據塊來說,一次 SIMD 指令會同時操作多個塊,大大減少了命令執行次數,縮短了計算時間。向量引擎在結合多核後會將 ClickHouse 的性能淋漓盡致的發揮出來。

ClickHouse 在索引上也有不同,採用了稀疏索引及跳數索引。同時還有很多 MergeTree,提供海量業務場景支持。

基於以上特點,ClickHouse 在包含 count、sum、group by、order by 等情況的查詢對比,同等條件下,ClickHouse 的查詢性能異常強悍,官網上的數據顯示,是同等條件下 MySQL 的 831 倍。

本文由 Readfog 進行 AMP 轉碼,版權歸原作者所有。
來源https://mp.weixin.qq.com/s/aJ7vdaG8LpBpBB5fCN0ncQ