最近公司搭建了Clickhouse的集群，作為一款久負盛名的高性能OLAP查詢引擎，我們也針對自己的使用場景的進行了一下體驗，對Clickhouse的使用和性能有了一定的體會。下面我們將從Clickhouse的建表，數(shù)據(jù)導入，查詢語法和性能情況進行簡要的總結：

1. Clickhouse的建表

首先貼一下我們Clickhouse的集群情況：集群由三臺機器組成，其中一個為集群節(jié)點，三個為分片節(jié)點，每個分片節(jié)點的磁盤為12T。

Clickhouse集群配置

這次我們想導入的數(shù)據(jù)，來源是離線計算產(chǎn)生的Hive表，因此首先現(xiàn)在Clickhouse上創(chuàng)建對應的表，在建表時有以下幾點需要注意：

• 由于搭建的是Clickhouse集群環(huán)境，建表時需要在集群節(jié)點上創(chuàng)建一個Distributed的表，在每個分片節(jié)點創(chuàng)建MergeTree的表。在數(shù)據(jù)導入和查詢時直接操作Distributed表，Distributed表會自動路由到相應的MergeTree表。
• Hive中的數(shù)據(jù)類型，在Clickhouse中都有對應的類型名稱：比如bigint -> Int64, int -> Int32, float -> Float32，需要按照Clickhouse的類型定義各個字段。
• Clickhouse的字段默認是不允許為NULL的，如果數(shù)據(jù)有可能為NULL，需要將字段定義為類似Nullable(Int64)的類型。
• 創(chuàng)建MergeTree表，需要設置分區(qū)字段和排序字段，排序字段一般會選擇將經(jīng)常聚合的維度排在前面，如果不清楚常用查詢場景的話，和分區(qū)字段一致就可以了。
• 創(chuàng)建Distributed表，不需要分區(qū)字段和排序字段，但要注意在Clickhouse的集群節(jié)點創(chuàng)建，不要在分片節(jié)點創(chuàng)建。

因此這次我們的建表語句如下所示，執(zhí)行后顯示OK。

• 創(chuàng)建Distributed表，在10.128.184.59:8000集群節(jié)點：

CREATE TABLE t
(
    platform_id Nullable(Int32),
    channel_id Nullable(Int64),
    ...
    bidding_strategy Nullable(Int32),
    landing_page_type Nullable(Int32),
    region_id Nullable(Int16),
    dt String
)
ENGINE = Distributed(ad_test_cluster, ad_test, t, rand())

• 創(chuàng)建MergeTree表，在10.128.184.55:9000, 10.128.184.59:9000和10.128.184.59:9000三個分片節(jié)點：

CREATE TABLE t
(
    platform_id Nullable(Int32),
    channel_id Nullable(Int64),
    ...
    bidding_strategy Nullable(Int32),
    landing_page_type Nullable(Int32),
    region_id Nullable(Int16),
    dt String
)
ENGINE = MergeTree
PARTITION BY dt
ORDER BY dt
SETTINGS index_granularity = 8192

2. Clickhouse的數(shù)據(jù)導入

建表之后開始向表中導入數(shù)據(jù)，這里我們采用的是將csv文件直接導入的方式，這里有一些值得注意的細節(jié)：

• 如果表的字段是Nullable的話，在csv文件中，對應列的值應該為\N，否則將無法導入。
• 由于將csv文件導入，執(zhí)行的是INSERT語句，因此在導入前需要先Drop相應的分區(qū)，保證數(shù)據(jù)不會重復導入。但是Drop的操作需要直接在分片節(jié)點操作，因此需要找到分片節(jié)點。可以在每個分片節(jié)點的system.parts表中，查看該分片上包含哪些分區(qū)，如果存在的話則可以進行Drop操作。

以下是數(shù)據(jù)導入的過程執(zhí)行的命令：

# 將csv中的NULL替換為\N
sed -i "s/NULL/\\\N/g" data.csv
# drop分區(qū)已有的數(shù)據(jù)（需要找到對應的分片節(jié)點）
clickhouse-client -h 10.128.184.59 --port 9000 -d ad_test -u ad_test --password adxxx --query="alter table t drop partition('2019-10-01')"
# 導入數(shù)據(jù)到Clickhouse中
cat data.csv | clickhouse-client -h 10.128.184.59 --port 8000 -d ad_test -u ad_test --password adxxx --format_csv_delimiter="|" --query="insert into t format CSV"

3. Clickhouse的查詢語法

Clickhouse支持標準的SQL語法，在實測中沒有遇到太多的問題。

目前只有一種情況是需要注意的：

• 聚合指標不能同時出現(xiàn)在兩個select字段中：

SELECT
    sum(charged_fees) AS charged_fees,
    sum(conversion_count) AS conversion_count,
    (sum(charged_fees) / sum(conversion_count)) / 100000 AS conversion_cost
FROM t
WHERE dt = '2019-07-01'

Received exception from server (version 19.1.9):
Code: 184. DB::Exception: Received from 10.128.184.59:9000. DB::Exception: Aggregate function sum(charged_fees) is found inside another aggregate function in query.

0 rows in set. Elapsed: 0.041 sec.

針對這種情況，把SQL改寫為以下形式即可：

SELECT
    sum(charged_fees) AS charged_fees,
    sum(conversion_count) AS conversion_count,
    (charged_fees / conversion_count) / 100000 AS conversion_cost
FROM t
WHERE dt = '2019-07-01'

┌──charged_fees─┬─conversion_count─┬────conversion_cost─┐
│ 3143142724482 │           250537 │ 150.37090954370022 │
└───────────────┴──────────────────┴────────────────────┘
(虛假數(shù)據(jù)，可能不準確)

1 rows in set. Elapsed: 0.155 sec. Processed 32.56 million rows, 1.20 GB (210.00 million rows/s., 7.77 GB/s.)

4. Clickhouse的性能測試

這里橫向比較了Clickhouse和Impala的性能，針對線上的1219個查詢語句，數(shù)據(jù)量基本在Billion級別，分別統(tǒng)計了兩者的查詢時間的指標。

• Clickhouse的查詢平均性能要優(yōu)于Impala，提升大概在2-4倍。
• Clickhouse的查詢時間分布更加穩(wěn)定，Impala會偶爾出現(xiàn)查詢時間不穩(wěn)定的情況。

以下是詳細的測試數(shù)據(jù)，單位為毫秒（ms）：

• 全部查詢語句：

• Impala查詢小于60s的語句：

Impala查詢小于60s的語句查詢時間分布

• Impala查詢小于10s的語句：

Impala查詢小于10s的語句查詢時間分布

可以看到，在去掉Impala大部分的慢查詢后，Clickhouse仍然有一定的性能優(yōu)勢，在整體上的表現(xiàn)是優(yōu)于Impala的。測試的SQL中沒有覆蓋到join的場景，但從原理上來看，Clickhouse的join性能表現(xiàn)應該也會比較穩(wěn)定。

5. 小結

以上是在初次接觸Clickhouse的一些體會，后續(xù)會在Clickhouse的使用和優(yōu)化，以及數(shù)據(jù)同步工具Waterdrop的調研上繼續(xù)。