ClickHouse是俄羅斯的重要網(wǎng)絡(luò)服務(wù)門(mén)戶(hù)之一Yandex所開(kāi)源的一套針對(duì)數(shù)據(jù)倉(cāng)庫(kù)場(chǎng)景的多維數(shù)據(jù)存儲(chǔ)與檢索工具，一個(gè)用于聯(lián)機(jī)分析(OLAP)的列式數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS),它通過(guò)針對(duì)性的設(shè)計(jì)力圖解決海量多維度數(shù)據(jù)的查詢(xún)性能問(wèn)題。

下面，enjoy：

一、數(shù)據(jù)庫(kù)的行存與列存

在傳統(tǒng)的行式數(shù)據(jù)庫(kù)系統(tǒng)中，數(shù)據(jù)按順序存儲(chǔ)：處于同一行中的數(shù)據(jù)總是被物理的存儲(chǔ)在一起，常見(jiàn)的行式數(shù)據(jù)庫(kù)系統(tǒng)有：MySQL、Postgres和MS SQL Server。

行存需要逐行讀取而后進(jìn)行整合，速度較慢

在列式數(shù)據(jù)庫(kù)系統(tǒng)中，來(lái)自不同列的值被單獨(dú)存儲(chǔ)，來(lái)自同一列的數(shù)據(jù)被存儲(chǔ)在一起。列式數(shù)據(jù)庫(kù)更適合于OLAP場(chǎng)景(對(duì)于大多數(shù)查詢(xún)而言，處理速度至少提高了100倍)。新興的 Hbase、HP Vertica、EMC Greenplum 等分布式數(shù)據(jù)庫(kù)均采用列式存儲(chǔ)。

列存能夠快速讀取同類(lèi)型數(shù)據(jù)，速度較行存有著顯著提升

ClickHouse采取的就是列示存儲(chǔ)的方式。

二、ClickHouse安裝及常用命令參數(shù)

1.ClickHouse支持的操作系統(tǒng)和硬件環(huán)境

只要是Linux，64位都可以。優(yōu)先支持Ubuntu，Ubuntu有官方編譯好的安裝包可以使用。其次是CentOS和RedHat，有第三方組織編譯好的rpm包可以使用。

如果是其他Linux系統(tǒng)，需要自己編譯源碼。

而且，機(jī)器的CPU必須支持SSE 4.2指令集。

[root@localhost ~]# grep -q sse4_2 /proc/cpuinfo && echo “SSE 4.2 supported” || echo “SSE 4.2 not supported”

2.ClickHouse的安裝方法

(1)RPM安裝包

推薦使用CentOS、RedHat和所有其他基于rpm的Linux發(fā)行版的官方預(yù)編譯rpm包。

首先，您需要添加官方存儲(chǔ)庫(kù)：

sudo yum install yum-utils

sudo rpm --import?https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG

sudo yum-config-manager --add-repo?https://repo.clickhouse.tech/rpm/stable/x86_64

然后運(yùn)行命令安裝：

sudo yum install clickhouse-server clickhouse-client

(2)設(shè)置系統(tǒng)參數(shù)

CentOS取消打開(kāi)文件數(shù)限制

在/etc/security/limits.conf、/etc/security/limits.d/90-nproc.conf這2個(gè)文件的末尾加入以下內(nèi)容：

* soft nofile 65536

* hard nofile 65536

* soft nproc 131072

* hard nproc 131072

CentOS取消SELINUX

修改/etc/selinux/config中的SELINUX=disabled后重啟

關(guān)閉防火墻

Centos 6.X：

service iptables stop

service ip6tables stop

Centos 7.X：

chkconfig iptables off

chkconfig ip6tables off

(3)啟動(dòng)連接

啟動(dòng)服務(wù)：service clickhouse-server start

連接客戶(hù)端：clickhouse-client

3.ClickHouse常用命令參數(shù)

(1)進(jìn)入交互式客戶(hù)端

用clickhouse-client連接本機(jī)clickhouse-server服務(wù)器：

clickhouse-client -m

用本機(jī)clickhouse-client連接遠(yuǎn)程clickhouse-server服務(wù)器：

clickhouse-client –host?http://192.168.x.xxx?–port 9000 –database default–user default –password ””

啟動(dòng)失敗可以查看日志，日志的目錄默認(rèn)為/var/log/clickhouse-server/下。

(2)服務(wù)

停止：

service clickhouse-server stop

啟動(dòng)：

service clickhouse-server start

重啟：

service clickhouse-server restart

(3) 設(shè)置數(shù)據(jù)目錄

vi /etc/clickhouse-server/config.xml

(4) 放開(kāi)遠(yuǎn)程訪問(wèn)

vi /etc/clickhouse-server/config.xml

修改服務(wù)器的配置文件/etc/clickhouse-server/config.xml，第65行，放開(kāi)注釋即可。

(5) 內(nèi)存限制設(shè)置

vi /etc/clickhouse-server/users.xml

三、ClickHouse引擎介紹

ClickHouse提供了大量的數(shù)據(jù)引擎，分為數(shù)據(jù)庫(kù)引擎、表引擎，根據(jù)數(shù)據(jù)特點(diǎn)及使用場(chǎng)景選擇合適的引擎至關(guān)重要。

1.ClickHouse引擎分類(lèi)

在以下幾種情況下，ClickHouse使用自己的數(shù)據(jù)庫(kù)引擎：

●決定表存儲(chǔ)在哪里以及以何種方式存儲(chǔ)

●支持哪些查詢(xún)以及如何支持

●并發(fā)數(shù)據(jù)訪問(wèn)

●索引的使用

●是否可以執(zhí)行多線程請(qǐng)求

●數(shù)據(jù)復(fù)制參數(shù)

在所有的表引擎中，最為核心的當(dāng)屬M(fèi)ergeTree系列表引擎，這些表引擎擁有最為強(qiáng)大的性能和最廣泛的使用場(chǎng)合。對(duì)于非MergeTree系列的其他引擎而言，主要用于特殊用途，場(chǎng)景相對(duì)有限。而MergeTree系列表引擎是官方主推的存儲(chǔ)引擎，支持幾乎所有ClickHouse核心功能。

MergeTree作為家族系列最基礎(chǔ)的表引擎，主要有以下特點(diǎn)：

●存儲(chǔ)的數(shù)據(jù)按照主鍵排序：允許創(chuàng)建稀疏索引，從而加快數(shù)據(jù)查詢(xún)速度

●支持分區(qū)，可以通過(guò)PRIMARY KEY語(yǔ)句指定分區(qū)字段

●支持?jǐn)?shù)據(jù)副本

●支持?jǐn)?shù)據(jù)采樣

2.建表引擎參數(shù)

ENGINE：ENGINE = MergeTree()，MergeTree引擎沒(méi)有參數(shù)。

ORDER BY：order by 設(shè)定了分區(qū)內(nèi)的數(shù)據(jù)按照哪些字段順序進(jìn)行有序保存。

order by是MergeTree中唯一一個(gè)必填項(xiàng)，甚至比primary key 還重要，因?yàn)楫?dāng)用戶(hù)不設(shè)置主鍵的情況，很多處理會(huì)依照order by的字段進(jìn)行處理。

要求：主鍵必須是order by字段的前綴字段。

如果ORDER BY與PRIMARY KEY不同，PRIMARY KEY必須是ORDER BY的前綴(為了保證分區(qū)內(nèi)數(shù)據(jù)和主鍵的有序性)。

ORDER BY 決定了每個(gè)分區(qū)中數(shù)據(jù)的排序規(guī)則;

PRIMARY KEY 決定了一級(jí)索引(primary.idx);

ORDER BY 可以指代PRIMARY KEY, 通常只用聲明ORDER BY 即可。

PARTITION BY：分區(qū)字段，可選。如果不填：只會(huì)使用一個(gè)分區(qū)。

分區(qū)目錄：MergeTree 是以列文件+索引文件+表定義文件組成的，但是如果設(shè)定了分區(qū)那么這些文件就會(huì)保存到不同的分區(qū)目錄中。

PRIMARY KEY：指定主鍵，如果排序字段與主鍵不一致，可以單獨(dú)指定主鍵字段。否則默認(rèn)主鍵是排序字段?？蛇x。

SAMPLE BY：采樣字段，如果指定了該字段，那么主鍵中也必須包含該字段。比如SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))?？蛇x。

TTL：數(shù)據(jù)的存活時(shí)間。在MergeTree中，可以為某個(gè)列字段或整張表設(shè)置TTL。當(dāng)時(shí)間到達(dá)時(shí)，如果是列字段級(jí)別的TTL，則會(huì)刪除這一列的數(shù)據(jù)；如果是表級(jí)別的TTL，則會(huì)刪除整張表的數(shù)據(jù)?？蛇x。

SETTINGS：額外的參數(shù)配置?？蛇x。

3.建表示例

4.數(shù)據(jù)導(dǎo)入

clickhouse-client --query "INSERT INTO default.emp_mgetree FORMAT CSV" --max_insert_block_size=100000 < test_data.csv

默認(rèn)情況下間隔符是 ,

四、ClickHouse的優(yōu)劣勢(shì)總結(jié)

1.優(yōu)勢(shì)

為了高效的使用CPU，數(shù)據(jù)不僅僅按列存儲(chǔ)，同時(shí)還按向量進(jìn)行處理

數(shù)據(jù)壓縮空間大，減少I(mǎi)O。處理單查詢(xún)高吞吐量每臺(tái)服務(wù)器每秒最多數(shù)十億行

索引非B樹(shù)結(jié)構(gòu)，不需要滿(mǎn)足最左原則，只要過(guò)濾條件在索引列中包含即可。即使在使用的數(shù)據(jù)不在索引中，由于各種并行處理機(jī)制ClickHouse全表掃描的速度也很快

寫(xiě)入速度非?？欤?0-200M/s，對(duì)于大量的數(shù)據(jù)更新非常適用

2.劣勢(shì)

不支持事務(wù)，不支持真正的刪除/更新

不支持高并發(fā)，官方建議qps為100，在服務(wù)器足夠好的情況下可以通過(guò)修改配置文件增加連接數(shù)

SQL滿(mǎn)足日常使用80%以上的語(yǔ)法，join寫(xiě)法比較特殊。最新版已支持類(lèi)似SQL的join，但性能不好

盡量做1000條以上批量的寫(xiě)入，避免逐行insert或小批量的insert，update，delete操作，因?yàn)镃lickHouse底層會(huì)不斷的做異步的數(shù)據(jù)合并，會(huì)影響查詢(xún)性能，這個(gè)在做實(shí)時(shí)數(shù)據(jù)寫(xiě)入的時(shí)候要盡量避開(kāi)

Clickhouse快是因?yàn)椴捎昧瞬⑿刑幚頇C(jī)制，即使一個(gè)查詢(xún)，也會(huì)用服務(wù)器一半的CPU去執(zhí)行，所以ClickHouse不能支持高并發(fā)的使用場(chǎng)景，默認(rèn)單查詢(xún)使用CPU核數(shù)為服務(wù)器核數(shù)的一半，安裝時(shí)會(huì)自動(dòng)識(shí)別服務(wù)器核數(shù)，可以通過(guò)配置文件修改該參數(shù)

全量數(shù)據(jù)導(dǎo)入：數(shù)據(jù)導(dǎo)入臨時(shí)表 -> 導(dǎo)入完成后，將原表改名為tmp1 -> 將臨時(shí)表改名為正式表 -> 刪除原表

增量數(shù)據(jù)導(dǎo)入：增量數(shù)據(jù)導(dǎo)入臨時(shí)表 -> 將原數(shù)據(jù)除增量外的也導(dǎo)入臨時(shí)表 -> 導(dǎo)入完成后，將原表改名為tmp1-> 將臨時(shí)表改成正式表-> 刪除原數(shù)據(jù)表

五、ClickHouse使用優(yōu)化總結(jié)

1.數(shù)據(jù)類(lèi)型

建表時(shí)能用數(shù)值型或日期時(shí)間型表示的字段，就不要用字符串——全String類(lèi)型在以Hive為中心的數(shù)倉(cāng)建設(shè)中常見(jiàn)，但CK環(huán)境不應(yīng)受此影響。

雖然clickhouse底層將DateTime存儲(chǔ)為時(shí)間戳Long類(lèi)型，但不建議直接存儲(chǔ)Long類(lèi)型，因?yàn)镈ateTime不需要經(jīng)過(guò)函數(shù)轉(zhuǎn)換處理，執(zhí)行效率高、可讀性好。

官方已經(jīng)指出Nullable類(lèi)型幾乎總是會(huì)拖累性能，因?yàn)榇鎯?chǔ)Nullable列時(shí)需要?jiǎng)?chuàng)建一個(gè)額外的文件來(lái)存儲(chǔ)NULL的標(biāo)記，并且Nullable列無(wú)法被索引。因此除非極特殊情況，應(yīng)直接使用字段默認(rèn)值表示空，或者自行指定一個(gè)在業(yè)務(wù)中無(wú)意義的值（例如用-1表示沒(méi)有商品ID）。

2.分區(qū)和索引

分區(qū)粒度根據(jù)業(yè)務(wù)特點(diǎn)決定，不宜過(guò)粗或過(guò)細(xì)。一般選擇按天分區(qū)，也可指定為tuple()；以單表1億數(shù)據(jù)為例，分區(qū)大小控制在10-30個(gè)為最佳。

必須指定索引列，clickhouse中的索引列即排序列，通過(guò)order by指定，一般在查詢(xún)條件中經(jīng)常被用來(lái)充當(dāng)篩選條件的屬性被納入進(jìn)來(lái)；可以是單一維度，也可以是組合維度的索引；通常需要滿(mǎn)足高級(jí)列在前、查詢(xún)頻率大的在前原則；還有基數(shù)特別大的不適合做索引列，如用戶(hù)表的userid字段；通常篩選后的數(shù)據(jù)滿(mǎn)足在百萬(wàn)以?xún)?nèi)為最佳。

3.表參數(shù)

index_granularity 是用來(lái)控制索引粒度的 默認(rèn)是8192，如非必須不建議調(diào)整。

如果表中不是必須保留全量歷史數(shù)據(jù)，建議指定TTL，可以免去手動(dòng)過(guò)期歷史數(shù)據(jù)的麻煩。TTL也可以通過(guò)ALTER TABLE語(yǔ)句隨時(shí)修改。

4.單表查詢(xún)

使用prewhere替代where關(guān)鍵字；當(dāng)查詢(xún)列明顯多于篩選列時(shí)使用prewhere可十倍提升查詢(xún)性能。

5.數(shù)據(jù)采樣策略

通過(guò)采用運(yùn)算可極大提升數(shù)據(jù)分析的性能。

數(shù)據(jù)量太大時(shí)應(yīng)避免使用select * 操作，查詢(xún)的性能會(huì)與查詢(xún)的字段大小和數(shù)量成線性變換；字段越少，消耗的io資源就越少，性能就會(huì)越高。

千萬(wàn)以上數(shù)據(jù)集進(jìn)行order by查詢(xún)時(shí)需要搭配where條件和limit語(yǔ)句一起使用。

如非必須不要在結(jié)果集上構(gòu)建虛擬列，虛擬列非常消耗資源浪費(fèi)性能，可以考慮在前端進(jìn)行處理，或者在表中構(gòu)造實(shí)際字段進(jìn)行額外存儲(chǔ)。

不建議在高基列上執(zhí)行distinct去重查詢(xún)，改為近似去重 uniqCombined。

多表Join時(shí)要滿(mǎn)足小表在右的原則，右表關(guān)聯(lián)時(shí)被加載到內(nèi)存中與左表進(jìn)行比較。

6.存儲(chǔ)

ClickHouse不支持設(shè)置多數(shù)據(jù)目錄，為了提升數(shù)據(jù)io性能，可以掛載虛擬券組，一個(gè)券組綁定多塊物理磁盤(pán)提升讀寫(xiě)性能；多數(shù)查詢(xún)場(chǎng)景SSD盤(pán)會(huì)比普通機(jī)械硬盤(pán)快2-3倍。

END

更多干貨內(nèi)容請(qǐng)關(guān)注微信公眾號(hào)“錄信數(shù)軟”~