ClickHouse概念

clickhouse是一個用于聯(lián)機分析(OLAP)的列式數(shù)據(jù)庫管理系統(tǒng)(DBMS)，由俄羅斯最大的搜索公司Yandex開發(fā)，于2016年開源，采用c++開發(fā)。

OLAP 和 OLTP 這兩個概念

• OLAP（On-Line Analytical Processing）：聯(lián)機分析處理OLAP（On-Line Analytical Processing),倉庫型數(shù)據(jù)庫，主要是讀取數(shù)據(jù)，做復(fù)雜數(shù)據(jù)分析（多維），側(cè)重技術(shù)決策支持，提供直觀簡單的結(jié)果,開源OLAP引擎包含Hive、SparkSQL、Presto、HAWQ、Druid、ClickHouse、Impala、Kylin、Greeplum等。
• OLTP（On-Line transaction processing）：傳統(tǒng)的關(guān)系型數(shù)據(jù)庫，主要操作增刪改查，強調(diào)事務(wù)一致性，比如銀行系統(tǒng)、電商系統(tǒng)；

OLAP介紹

OLAP的前世今生

OLAP(Online analytical processing)，即聯(lián)機分析處理，主要用于支持企業(yè)決策管理分析。數(shù)據(jù)庫概念最初源于1962年Kenneth Iverson發(fā)表的名為“A Programming Language” (APL)的著作，它第一次提出了處理操作和多維變量的的數(shù)學(xué)表達式，后來APL語言由IBM實現(xiàn)。

OLAP委員會對聯(lián)機分析處理的定義為:使分析人員、管理人員或執(zhí)行人員能夠從多種角度對從原始數(shù)據(jù)中轉(zhuǎn)化出來 的、能夠真正為用戶所理解的、并真實反映企業(yè)維特性的信息進行快速、一致、交互的存取，從而獲得對數(shù)據(jù)更深 入了解的一類軟件技術(shù).

第一款OLAP產(chǎn)品Express于1975年問世，隨著被Oracle收購后繁榮發(fā)展了30余年，最后由繼任者Oracle 9i替代。這么多年過去，基本的OLAP理念和數(shù)據(jù)模型仍然未變。

OLAP這個名詞是數(shù)據(jù)庫之父Edgar F. Codd于1993年在文章《Providing OLAP (On-Line Analytical Processing) to User-Analysts: An IT Mandate》提出，他總結(jié)了OLAP產(chǎn)品的12個原則，隨后OLAP產(chǎn)品相繼問世并逐漸形成今天的格局。

OLAP產(chǎn)品分類

OLAP按存儲器的數(shù)據(jù)存儲格式分為基于多維數(shù)據(jù)庫的MOLAP和交互分析的的ROLAP。

• MOLAP（Multi-dimensional OLAP） 以多維數(shù)組（Multi-dimensional Array）存儲模型的OLAP，是OLAP發(fā)源最初的形態(tài)，某些方面也等同于OLAP。它的特點是數(shù)據(jù)需要預(yù)計算（pre-computaion），然后把預(yù)計算之后的結(jié)果（cube）存在多維數(shù)組里。
• 優(yōu)點：cube包含所有維度的聚合結(jié)果，所以查詢速度非常快，計算結(jié)果數(shù)據(jù)占用的磁盤空間相對關(guān)系型數(shù)據(jù)庫更小、高性能、高并發(fā)。
• 缺點：空間和時間開銷大，隨著維度增加計算時間大幅增加、查詢靈活度比較低，需要提前設(shè)計維度模型，查詢分析的內(nèi)容僅限于這些指定維度，增加維度需要重新計算、不支持明細數(shù)據(jù)查詢。
• 代表：Kylin / Druid，適合對性能非常高的OLAP場景。
• ROLAP（Relational OLAP）
• 基于關(guān)系模型存放數(shù)據(jù)，一般要求事實表（fact table）和維度表（dimensition table）按一定關(guān)系設(shè)計，它不需要預(yù)計算，使用標(biāo)準(zhǔn)SQL就可以根據(jù)需要即時查詢不同維度數(shù)據(jù)。
• 優(yōu)點：擴展性強，適用于維度數(shù)量多的模型，MOLAP對于維度多的模型預(yù)計算慢，空間占用大、支持任意SQL表達、無數(shù)據(jù)冗余與預(yù)處理。
• 缺點：因為是即時計算，查詢響應(yīng)時間一般比預(yù)計算的MOLAP長、大數(shù)據(jù)量下分鐘級響應(yīng)，不支持實時數(shù)據(jù)。
• 代表：Presto / Impala / SparkSQL / Drill

OLAP場景的關(guān)鍵特征

• 大多數(shù)是讀請求
• 數(shù)據(jù)總是以相當(dāng)大批地寫入(>1000rows)
• 不修改已添加的數(shù)據(jù)
• 每次查詢都從數(shù)據(jù)中讀取大量的行，但同時僅需要少量的列
• 寬表，即每個表包含大量的列
• 較少的查詢(通常每臺服務(wù)器每秒數(shù)百個查詢或更少)
• 對于簡單查詢，允許延遲大約50ms
• 列中的數(shù)據(jù)相對較小，如數(shù)字和短字符串
• 處理單個查詢時需要高吞吐量(每個服務(wù)器每秒高達數(shù)十億行)
• 事務(wù)不是必須的
• 對于數(shù)據(jù)一致性要求低
• 每個查詢除了一個大表外，其余都很小
• 查詢結(jié)果明顯小于源數(shù)據(jù)，或者說，數(shù)據(jù)被過濾或聚合后能夠被盛放在內(nèi)存中

列式存儲更適合OLAP的原因

列式數(shù)據(jù)對于大多數(shù)查詢而言，處理速度至少提高了100倍

行式

列式

ClickHouse核心特性

ClickHouse為什么這么快

行存儲和列存儲

分析場景中，我們一般會讀大量的行而取少量的列，在列式存儲結(jié)構(gòu)下，我們只需要取對應(yīng)的列數(shù)據(jù)就可以，不參與計算的列完全不會被掃描到，這會極大的降低磁盤 IO 的消耗。

數(shù)據(jù)壓縮的本質(zhì)

基于列式存儲的結(jié)構(gòu)，同一列中的數(shù)據(jù)屬于同一類型，壓縮效果會更加顯著。列存儲往有著高達十倍甚至更高的壓縮比，節(jié)省了大量的存儲空間，降低了存儲成本。

向量化執(zhí)行引擎

SIMD（Single Instruction Multiple Data）即單條指令操作多條數(shù)據(jù)，它是通過數(shù)據(jù)并行以提高性能的一種方式，可以簡單理解為在寄存器層面對程序中的數(shù)據(jù)做并行處理，Clickhouse 在能夠提升計算效率的地方大量使用了 SIMD，通過使用 SIMD，基本上能帶來幾倍的性能提升，像阿里云的 PolarDB-X 也引入了向量化執(zhí)行引擎，為表達式計算帶來了幾十倍的性能提升。

多線程與分布式

分布式領(lǐng)域存在一條定律，計算移動比數(shù)據(jù)移動更加劃算，這也是其核心所在，將數(shù)據(jù)的計算直接發(fā)放到數(shù)據(jù)所在的服務(wù)器，多機并行處理，再把最終的結(jié)果匯集在一起；另外 Clickhouse 也通過線程級別并行的方式為效率進一步提速，極致去利用服務(wù)器的資源。

多樣化的表引擎

ClickHouse的安裝與部署

ClickHouse單安裝過程

ClickHouse可以在任何具有x86_64，AArch64或PowerPC64LE CPU架構(gòu)的Linux，F(xiàn)reeBSD或Mac OS X上運行。

官方預(yù)構(gòu)建的二進制文件通常針對x86_64進行編譯，并利用SSE 4.2指令集，因此，除非另有說明，支持它的CPU使用將成為額外的系統(tǒng)需求。下面是檢查當(dāng)前CPU是否支持SSE 4.2的命令:

$ grep -q sse4_2 /proc/cpuinfo && echo “SSE 4.2 supported” || echo “SSE 4.2 not supported”

Ubuntu的官方預(yù)編譯deb軟件包。運行以下命令來安裝包:

sudo apt-get install apt-transport-https ca-certificates dirmngrsudo apt-key adv –keyserver hkp://keyserver.ubuntu.com:80 –recv E0C56BD4echo “deb https://repo.clickhouse.com/deb/stable/ main/” | sudo tee /etc/apt/sources.list.d/clickhouse.listsudo apt-get updatesudo apt-get install -y clickhouse-server clickhouse-clientsudo service clickhouse-server startclickhouse-client

Mac下單機安裝部署Clickhouse、使用Docker容器部署安裝

1、安裝docker

2、安裝ClickHouse

客戶端：docker pull yandex/clickhouse-client 服務(wù)端：docker pull yandex/clickhouse-server

3、命令啟動鏡像或者docker控制臺啟動

docker run -d –name ch-server –ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 -p 9009:9009 yandex/clickhouse-server

4、啟動命令

clickhouse-client

方法二：y

sudo yum install yum-utilssudo rpm –import https://repo.clickhouse.com/CLICKHOUSE-KEY.GPGsudo yum-config-manager –add-repo https://repo.clickhouse.com/rpm/stable/x86_64

服務(wù)端啟動命令：clickhouse-server

客戶端啟動命令：clickhouse-client

ClickMergeTree原理解析

MergeTree的創(chuàng)建方式與存儲結(jié)構(gòu)

創(chuàng)建的時候?qū)NGINE參數(shù)聲明為MergeTree()

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],省略…) ENGINE = MergeTree()[PARTITION BY expr][ORDER BY expr][PRIMARY KEY expr][SAMPLE BY expr][SETTINGS name=value, 省略…]

PARTITION BY [選填]：分區(qū)鍵，用于指定表數(shù)據(jù)以何種標(biāo)準(zhǔn)進行分區(qū)。

ORDER BY[選填]：排序鍵

PRIMARY KEY [選填] ： 主鍵

SAMPLE BY [選填]：抽樣表達式，用于聲明數(shù)據(jù)以何種標(biāo) 準(zhǔn)進行采樣

SETTINGS：index_granularity [選填]：它表示索引的粒度，默認(rèn)值為 8192。也就是說，MergeTree的索引在默認(rèn)情況下，每間隔8192行數(shù)據(jù) 才生成一條索引，稀疏索引。

MergeTree的存儲結(jié)構(gòu)

結(jié)構(gòu)圖

partition：分區(qū)目錄，下面存放這各類數(shù)據(jù)文件，相同分區(qū)的數(shù)據(jù)，會被合并到同一個分區(qū)目錄，不同的分區(qū)，數(shù)據(jù)永遠不會被合并到一起。

• checksums.txt：校驗文件，保存了分區(qū)各類文件的size大小以及hash值，主要用于快速校驗文件的完整性和正確性。
• columns.txt： 列信息文件，使用明文格式存儲，用于保存數(shù)據(jù)分區(qū)下的列字段信息。
• count.txt：計數(shù)文件，用于記錄當(dāng)前數(shù)據(jù)分區(qū)目錄下數(shù)據(jù)的總行數(shù)。
• primary.idx：一級索引文件，使用二進制格式存儲，存放稀疏索引。
• [Column].bin：數(shù)據(jù)文件，存儲某一列的數(shù)據(jù)，由于MergeTree采用列式存儲，所以 每一個列字段都擁有獨立的.bin數(shù)據(jù)文件，并以列字段名稱命名。
• [Column].mrk：列字段標(biāo)記文件，標(biāo)記文件中保存了.bin文件中數(shù)據(jù)的偏移量信息，標(biāo)記文件與稀疏索引對 齊，又與.bin文件一一對應(yīng)，所以MergeTree通過標(biāo)記文件建立了 primary.idx稀疏索引與.bin數(shù)據(jù)文件之間的映射關(guān)系。
• [Column].mrk2：如果使用了自適應(yīng)大小的索引間隔，則標(biāo)記 文件會以.mrk2命名。它的工作原理和作用與.mrk標(biāo)記文件相同。
• partition.dat與minmax_[Column].idx：如果使用了分區(qū)鍵，例 如PARTITION BY EventTime，則會額外生成partition.dat與minmax索引 文件，它們均使用二進制格式存儲，minmax記錄當(dāng)前分區(qū)下分區(qū)字段 對應(yīng)原始數(shù)據(jù)的最小和最大值，如2019-05-012019-05-05。
• ）skpidx[Column].idx與skpidx[Column].mrk：二級索引與標(biāo)記文件，這些索引的 最終目標(biāo)與一級稀疏索引相同，都是為了進一步減少所需掃描的數(shù)據(jù) 范圍，以加速整個查詢過程。

數(shù)據(jù)的分區(qū)規(guī)則

• 不指定分區(qū)鍵：即不使用PARTITION BY聲明任何分區(qū)表達式，則分區(qū)ID默認(rèn)取名為all，所有的數(shù)據(jù)都會被 寫入這個all分區(qū)
• 使用整型：如果分區(qū)鍵取值屬于整型且無法轉(zhuǎn)換為日期類型YYYYMMDD格 式，則直接按照該整型的字符形式輸出，作為分區(qū)ID的取值。
• 使用日期類型：如果分區(qū)鍵取值屬于日期類型，或者是能夠 轉(zhuǎn)換為YYYYMMDD格式的整型，則使用按照YYYYMMDD進行格式化 后的字符形式輸出，并作為分區(qū)ID的取值。
• 使用其他類型：如果分區(qū)鍵取值既不屬于整型，也不屬于日 期類型，例如String、Float等，則通過128位Hash算法取其Hash值作為 分區(qū)ID的取值

分區(qū)目錄的命名規(guī)則

MergeTree分區(qū)目錄的完整 物理名稱并不是只有ID而已，在ID之后還跟著一串奇怪的數(shù)字，例如 20190511_0，分區(qū)ID如何生成的。

命名規(guī)則

PartitionID_MinBlockNum_MaxBlockNum_Level

• PartitionID： 分區(qū)ID, 201905 表示分區(qū)目錄的ID
• MinBlockNum和MaxBlockNum：顧名思義，最小數(shù)據(jù)塊編號 與最大數(shù)據(jù)塊編號,1_1 表示最小的數(shù)據(jù)塊編碼和最大的數(shù)據(jù)塊編碼.
• Level :合并的層級，可以理解為某個分區(qū)被合并過的次數(shù)，或者這個分區(qū)的年齡。

分區(qū)目錄的合并過程

屬于同一個分區(qū)的多個目錄，在合并之后會生成一個全新的目 錄，目錄中的索引和數(shù)據(jù)文件也會相應(yīng)地進行合并。新目錄名稱的合并方式遵循以下規(guī)則，其中：

• MinBlockNum：取同一分區(qū)內(nèi)所有目錄中最小的MinBlockNum 值。
• MaxBlockNum：取同一分區(qū)內(nèi)所有目錄中最大的MaxBlockNum 值。
• ·Level：取同一分區(qū)內(nèi)最大Level值并加1。

分區(qū)目錄從創(chuàng)建、合并到刪除的整個過程

分區(qū)目錄在發(fā)生合并之后，舊的分區(qū)目 錄并沒有被立即刪除，而是會存留一段時間。但是舊的分區(qū)目錄已不 再是激活狀態(tài)（active=0），所以在數(shù)據(jù)查詢時，它們會被自動過濾 掉。

一級索引

MergeTree的主鍵使用PRIMARY KEY定義，待主鍵定義之后， MergeTree會依據(jù)index_granularity間隔（默認(rèn)8192行），為數(shù)據(jù)表生成 一級索引并保存至primary.idx文件內(nèi)，索引數(shù)據(jù)按照PRIMARY KEY排 序。相比使用PRIMARY KEY定義，更為常見的簡化形式是通過 ORDER BY指代主鍵。

稀疏索引

primary.idx文件內(nèi)的一級索引采用稀疏索引實現(xiàn)。

稀疏索引的優(yōu)勢是顯而易見的，它僅需使用少量的索引標(biāo)記就能 夠記錄大量數(shù)據(jù)的區(qū)間位置信息，且數(shù)據(jù)量越大優(yōu)勢越為明顯。以默 認(rèn)的索引粒度（8192）為例，MergeTree只需要12208行索引標(biāo)記就能為 1億行數(shù)據(jù)記錄提供索引。由于稀疏索引占用空間小，所以primary.idx 內(nèi)的索引數(shù)據(jù)常駐內(nèi)存，取用速度自然極快。

索引粒度

索引粒度 通過index_granularity這個參數(shù)設(shè)置，默認(rèn)是8092。

數(shù)據(jù)以indexgranularity的粒度（默認(rèn)8192）被標(biāo)記成多個小的區(qū) 間，其中每個區(qū)間最多8192行數(shù)據(jù)。MergeTree使用MarkRange表示一 個具體的區(qū)間，并通過start和end表示其具體的范圍。indexgranularity 的命名雖然取了索引二字，但它不單只作用于一級索引（.idx），同時 也會影響數(shù)據(jù)標(biāo)記（.mrk）和數(shù)據(jù)文件（.bin）。

索引數(shù)據(jù)的生成規(guī)則

如果使用CounterID作為主 鍵（ORDER BY CounterID），則每間隔8192行數(shù)據(jù)就會取一次 CounterID的值作為索引值，索引數(shù)據(jù)最終會被寫入primary.idx文件進行 保存

如果使用多個主鍵，例如ORDER BY(CounterID,EventDate)，則每 間隔8192行可以同時取CounterID與EventDate兩列的值作為索引值

索引的查詢過程

MarkRange： 一個具體的數(shù)據(jù)段，MarkRange與索引編號對應(yīng)，使用start和end兩個屬性表示其區(qū)間范圍。

索引查詢其實就是兩個數(shù)值區(qū)間的交集判斷。其中，一個區(qū)間是由基于主鍵的查詢條件轉(zhuǎn)換而來的條件區(qū)間；而另一個區(qū)間是MarkRange對應(yīng)的數(shù)值區(qū)間。

假如現(xiàn)在有一份測試數(shù)據(jù)，共192行記 錄。其中，主鍵ID為String類型，ID的取值從A000開始，后面依次為 A001、A002……直至A192為止。MergeTree的索引粒度 index_granularity=3，根據(jù)索引的生成規(guī)則，primary.idx文件內(nèi)的索引數(shù)據(jù)會如下圖所示。

根據(jù)索引數(shù)據(jù)，MergeTree會將此數(shù)據(jù)片段劃分成192/3=64個小的 MarkRange，兩個相鄰MarkRange相距的步長為1。其中，所有 MarkRange（整個數(shù)據(jù)片段）的最大數(shù)值區(qū)間為[A000,+inf)，如下圖所示。

如圖是64個MarkRange與其數(shù)值區(qū)間范圍的示意圖。

查詢過程

• 生成查詢條件區(qū)間，將查詢條件轉(zhuǎn)換為區(qū)間的形勢查詢。
• 遞歸交集判斷：以遞歸的形式，依次對MarkRange的數(shù)值區(qū) 間與條件區(qū)間做交集判斷
• 不存在交集，則直接通過剪枝算法優(yōu)化此整段MarkRange
• 如果存在交集，且MarkRange步長大于8(end-start)，則將此區(qū)間進 一步拆分成8個子區(qū)間，并重復(fù)此規(guī)則，繼續(xù)做遞歸交集判斷。
• 如果存在交集，且MarkRange不可再分解（步長小于8），則記錄 MarkRange并返回。
• 合并MarkRange區(qū)間：將最終匹配的MarkRange聚在一起，合 并它們的范圍

二級索引

granularity與index_granularity的關(guān)系

indexgranularity定 義了數(shù)據(jù)的粒度，而granularity定義了聚合信息匯總的粒度。換言之， granularity定義了一行跳數(shù)索引能夠跳過多少個indexgranularity區(qū)間的 數(shù)據(jù)。

二級索引類型

• minmax：minmax索引記錄了一段數(shù)據(jù)內(nèi)的最小和最大極 值，其索引的作用類似分區(qū)目錄的minmax索引，能夠快速跳過無用的 數(shù)據(jù)區(qū)間。
• set：set索引直接記錄了聲明字段或表達式的取值
• ngrambfv1：ngrambfv1索引記錄的是數(shù)據(jù)短語的布隆表過 濾器，只支持String和FixedString數(shù)據(jù)類型
• tokenbfv1：tokenbfv1索引是ngrambf_v1的變種，同樣也是 一種布隆過濾器索引

數(shù)據(jù)存儲

壓縮數(shù)據(jù)塊

MergeTree在數(shù)據(jù)具體的寫入過程中，會依照索引粒度，按批次獲取數(shù)據(jù)并進行處理。如果把一批數(shù)據(jù) 的未壓縮大小設(shè)為size，壓縮前數(shù)據(jù)字節(jié)大小，嚴(yán)格控制在64kb-1MB之間。

寫入過程：

• 單個批次數(shù)據(jù)size=64KB時，生成下一個壓縮數(shù) 據(jù)塊。
• 單個批次數(shù)據(jù)64KB<=size<=1MB ：如果單個批次數(shù)據(jù)大小恰 好在64KB與1MB之間，則直接生成下一個壓縮數(shù)據(jù)塊。
• 單個批次數(shù)據(jù)size>1MB ：如果單個批次數(shù)據(jù)直接超過1MB， 則首先按照1MB大小截斷并生成下一個壓縮數(shù)據(jù)塊。

數(shù)據(jù)標(biāo)記的生成規(guī)則

分區(qū)、索引、標(biāo)記和壓縮數(shù)據(jù)， 現(xiàn)在將它們聚在一塊進行一番總結(jié)。接下來，就分別從寫入過程、查 詢過程，以及數(shù)據(jù)標(biāo)記與壓縮數(shù)據(jù)塊的三種對應(yīng)關(guān)系的角度展開介紹。

寫入過程

• 生成一個新的分區(qū)目錄
• 分區(qū)目錄合并
• 按照索引粒度、生成一級索引
• 生成列字段的.mrk數(shù)據(jù)標(biāo)記和.bin壓縮數(shù)據(jù)文件

數(shù)據(jù)標(biāo)記與壓縮數(shù)據(jù)塊的對應(yīng)關(guān)系：一對一、多對一、一對多。

查詢過程

ClickMergeTree系列表引擎

表引擎可以分為6個系列，分別是合并樹、外部存儲、內(nèi)存、文件、接口和其他，每一個系列的 表引擎都有著獨自的特點與使用場景。

除了基礎(chǔ)表引擎MergeTree之 外，常用的表引擎還有ReplacingMergeTree、SummingMergeTree、 AggregatingMergeTree、CollapsingMergeTree和 VersionedCollapsingMergeTree

多路徑存儲策略

有三類存儲策略

• 默認(rèn)策略：MergeTree原本的存儲策略，無須任何配置，所有分 區(qū)會自動保存到config.xml配置中path指定的路徑下
• JBOD策略：這種策略適合服務(wù)器掛載了多塊磁盤，但沒有做 RAID的場景，它是一種輪詢策略，每執(zhí)行一次INSERT或者MERGE，所產(chǎn)生的新分區(qū)會輪詢寫入各 個磁盤。
• HOT/COLD策略：這種策略適合服務(wù)器掛載了不同類型磁盤的場 景。將存儲磁盤分為HOT與COLD兩類區(qū)域。HOT區(qū)域使用SSD這類高 性能存儲媒介，注重存取性能。

ReplacingMergeTree

MergeTree擁有主鍵，但是它的主鍵卻沒有唯一鍵的約束，ReplacingMergeTree為了數(shù)據(jù)去重而設(shè)計的，它能夠在合并分區(qū)時刪除重復(fù)的數(shù)據(jù)。

創(chuàng)建ReplacingMergeTree表的方法，替換Engine即可

ENGINE = ReplacingMergeTree(ver)//ver是選填參數(shù)，會指定一個UInt*、Date或者DateTime類型的字段作為版本號

創(chuàng)建ReplacingMergeTree數(shù)據(jù)表方法

CREATE TABLE replace_table( id String, code String, create_time DateTime)ENGINE = ReplacingMergeTree()PARTITION BY toYYYYMM(create_time)ORDER BY (id,code) //根據(jù)id與code去重PRIMARY KEY id

只有在相同的數(shù)據(jù)分區(qū)內(nèi)重復(fù)的數(shù)據(jù)才可以被刪除，而不同數(shù) 據(jù)分區(qū)之間的重復(fù)數(shù)據(jù)依然不能被剔除

• 使用ORBER BY排序鍵作為判斷重復(fù)數(shù)據(jù)的唯一鍵。(
• 只有在合并分區(qū)的時候才會觸發(fā)刪除重復(fù)數(shù)據(jù)的邏輯。
• 以數(shù)據(jù)分區(qū)為單位刪除重復(fù)數(shù)據(jù)。當(dāng)分區(qū)合并時，同一分區(qū) 內(nèi)的重復(fù)數(shù)據(jù)會被刪除；不同分區(qū)之間的重復(fù)數(shù)據(jù)不會被刪除。
• 在進行數(shù)據(jù)去重時，因為分區(qū)內(nèi)的數(shù)據(jù)已經(jīng)基于ORBER BY 進行了排序，所以能夠找到那些相鄰的重復(fù)數(shù)據(jù)。
• 數(shù)據(jù)去重策略有兩種：
• 如果沒有設(shè)置ver版本號，則保留同一組重復(fù)數(shù)據(jù)中的最后一 行。
• 如果設(shè)置了ver版本號，則保留同一組重復(fù)數(shù)據(jù)中ver字段取值最 大的那一行。

總結(jié)：ReplacingMergeTree在去除重復(fù)數(shù)據(jù)時，確實是以O(shè)RDER BY排序鍵為基準(zhǔn)的，而不是PRIMARY KEY。

SummingMergeTree

終端用戶只需要查詢數(shù)據(jù)的匯總結(jié)果，不關(guān)心明細數(shù)據(jù),則使用SummingMergeTree 引擎

SummingMergeTree能夠在合并分區(qū)的時候按照預(yù)先定義的條件聚合匯總數(shù)據(jù)，將同一分組下的多行數(shù)據(jù)匯總合并成一行，這樣既減少了數(shù)據(jù)行，又降低了后續(xù)匯總查詢的開銷。

CREATE TABLE summing_table( id String, city String, v1 UInt32, v2 Float64,create_time DateTime)ENGINE = SummingMergeTree()PARTITION BY toYYYYMM(create_time)ORDER BY (id, city)PRIMARY KEY id

執(zhí)行optimize強制進行觸發(fā)和合并操作：

optimize TABLE summing_table FINAL

• 用ORBER BY排序鍵作為聚合數(shù)據(jù)的條件Key。
• 只有在合并分區(qū)的時候才會觸發(fā)匯總的邏輯。
• 以數(shù)據(jù)分區(qū)為單位來聚合數(shù)據(jù)。當(dāng)分區(qū)合并時，同一數(shù)據(jù)分 區(qū)內(nèi)聚合Key相同的數(shù)據(jù)會被合并匯總，而不同分區(qū)之間的數(shù)據(jù)則不 會被匯總。
• 如果在定義引擎時指定了columns匯總列（非主鍵的數(shù)值類 型字段），則SUM匯總這些列字段；如果未指定，則聚合所有非主鍵 的數(shù)值類型字段。
• 在進行數(shù)據(jù)匯總時，因為分區(qū)內(nèi)的數(shù)據(jù)已經(jīng)基于ORBER BY 排序，所以能夠找到相鄰且擁有相同聚合Key的數(shù)據(jù)。
• 在匯總數(shù)據(jù)時，同一分區(qū)內(nèi)，相同聚合Key的多行數(shù)據(jù)會合 并成一行。其中，匯總字段會進行SUM計算；對于那些非匯總字段， 則會使用第一行數(shù)據(jù)的取值。
• 支持嵌套結(jié)構(gòu)，但列字段名稱必須以Map后綴結(jié)尾。嵌套類 型中，默認(rèn)以第一個字段作為聚合Key。除第一個字段以外，任何名 稱以Key、Id或Type為后綴結(jié)尾的字段，都將和第一個字段一起組成復(fù) 合Key。

AggregatingMergeTree

AggregatingMergeTree能夠在合并分區(qū)的時候，按照預(yù)先定義的條件聚合數(shù)據(jù)。同時，根據(jù)預(yù)先定義的 聚合函數(shù)計算數(shù)據(jù)并通過二進制的格式存入表內(nèi)。將同一分組下的多 行數(shù)據(jù)聚合成一行，既減少了數(shù)據(jù)行，又降低了后續(xù)聚合查詢的開銷。

AggregatingMergeTree更為常見的應(yīng)用方式是結(jié)合物化視圖使用， 將它作為物化視圖的表引擎。而這里的物化視圖是作為其他數(shù)據(jù)表上 層的一種查詢視圖。

定義

ENGINE = AggregatingMergeTree()

例子：

CREATE TABLE agg_table(id String,city String,code AggregateFunction(uniq,String),value AggregateFunction(sum,UInt32),create_time DateTime)ENGINE = AggregatingMergeTree()PARTITION BY toYYYYMM(create_time)ORDER BY (id,city)PRIMARY KEY id

上例中列字段id和city是聚合條件，等同于下面的語義GROUP BY id，city,

• 用ORBER BY排序鍵作為聚合數(shù)據(jù)的條件Key。
• 使用AggregateFunction字段類型定義聚合函數(shù)的類型以及聚 合的字段。
• 只有在合并分區(qū)的時候才會觸發(fā)聚合計算的邏輯。
• 以數(shù)據(jù)分區(qū)為單位來聚合數(shù)據(jù)。當(dāng)分區(qū)合并時，同一數(shù)據(jù)分 區(qū)內(nèi)聚合Key相同的數(shù)據(jù)會被合并計算，而不同分區(qū)之間的數(shù)據(jù)則不會 被計算。
• 在進行數(shù)據(jù)計算時，因為分區(qū)內(nèi)的數(shù)據(jù)已經(jīng)基于ORBER BY 排序，所以能夠找到那些相鄰且擁有相同聚合Key的數(shù)據(jù)。
• AggregateFunction類型的字段使用二進制存儲，在寫入數(shù)據(jù) 時，需要調(diào)用State函數(shù)；而在查詢數(shù)據(jù)時，則需要調(diào)用相應(yīng)的Merge 函數(shù)。其中，*表示定義時使用的聚合函數(shù)。
• AggregatingMergeTree通常作為物化視圖的表引擎，與普通 MergeTree搭配使用。

CollapsingMergeTree

CollapsingMergeTree就是一種通過以增代刪的思路，支持行級數(shù)據(jù) 修改和刪除的表引擎，通過定義一個sign標(biāo)記位字段，記錄數(shù)據(jù)行的 狀態(tài)。如果sign標(biāo)記為1，則表示這是一行有效的數(shù)據(jù)；如果sign標(biāo)記 為-1，則表示這行數(shù)據(jù)需要被刪除，相互抵消。

聲明CollapsingMergeTree的方式

ENGINE = CollapsingMergeTree(sign)

例子：

CREATE TABLE collpase_table( id String, code Int32, create_time DateTime, sign Int8)ENGINE = CollapsingMergeTree(sign)PARTITION BY toYYYYMM(create_time)ORDER BY id

修改數(shù)據(jù)

–修改前的源數(shù)據(jù), 它需要被修改INSERT INTO TABLE collpase_table VALUES(‘A000′,100,’2019-02-20 00:00:00’,1)–鏡像數(shù)據(jù), ORDER BY字段與源數(shù)據(jù)相同(其他字段可以不同),sign取反為-1,它會和源數(shù)據(jù)折疊INSERT INTO TABLE collpase_table VALUES(‘A000′,100,’2019-02-20 00:00:00’,-1)–修改后的數(shù)據(jù) ,sign為1INSERT INTO TABLE collpase_table VALUES(‘A000′,120,’2019-02-20 00:00:00’,1)

刪除數(shù)據(jù)

–修改前的源數(shù)據(jù), 它需要被刪除INSERT INTO TABLE collpase_table VALUES(‘A000′,100,’2019-02-20 00:00:00’,1)–鏡像數(shù)據(jù), ORDER BY字段與源數(shù)據(jù)相同, sign取反為-1, 它會和源數(shù)據(jù)折疊INSERT INTO TABLE collpase_table VALUES(‘A000′,100,’2019-02-20 00:00:00’,-1)

VersionedCollapsingMergeTree

VersionedCollapsingMergeTree對數(shù) 據(jù)的寫入順序沒有要求，在同一個分區(qū)內(nèi)，任意順序的數(shù)據(jù)都能夠完 成折疊操作.

定義

ENGINE = VersionedCollapsingMergeTree(sign,ver)

例子

CREATE TABLE ver_collpase_table( id String, code Int32, create_time DateTime, sign Int8, ver UInt8)ENGINE = VersionedCollapsingMergeTree(sign,ver)PARTITION BY toYYYYMM(create_time)ORDER BY id

其他常見類型表引擎

HDFS

Docker安裝HDFS

拉取hadoop鏡像

docker pull singularities/hadoop

創(chuàng)建docker-compose.yml文件

version: “2”services: namenode: image: singularities/hadoop command: start-hadoop namenode hostname: namenode environment: HDFS_USER: hdfsuser ports: – “8020:8020” – “14000:14000” – “50070:50070” – “50075:50075” – “10020:10020” – “13562:13562” – “19888:19888” datanode: image: singularities/hadoop command: start-hadoop datanode namenode environment: HDFS_USER: hdfsuser links: – namenode

執(zhí)行

[root@localhost hadoop]# docker-compose up -dCreating network “hadoop_default” with the default driverCreating hadoop_namenode_1 … doneCreating hadoop_datanode_1 … done

生成3個datanode

[root@localhost hadoop]# docker-compose scale datanode=3WARNING: The scale command is deprecated. Use the up command with the –scale flag instead.Starting hadoop_datanode_1 … doneCreating hadoop_datanode_2 … doneCreating hadoop_datanode_3 … done

列出容器查看

[root@localhost hadoop]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES19f9685e286f singularities/hadoop “start-hadoop data…” 48 seconds ago Up 46 seconds 8020/tcp, 9000/tcp, 10020/tcp, 13562/tcp, 14000/tcp, 19888/tcp, 50010/tcp, 50020/tcp, 50070/tcp, 50075/tcp, 50090/tcp, 50470/tcp, 50475/tcp hadoop_datanode_3e96b395f56e3 singularities/hadoop “start-hadoop data…” 48 seconds ago Up 46 seconds 8020/tcp, 9000/tcp, 10020/tcp, 13562/tcp, 14000/tcp, 19888/tcp, 50010/tcp, 50020/tcp, 50070/tcp, 50075/tcp, 50090/tcp, 50470/tcp, 50475/tcp hadoop_datanode_25a26b1069dbb singularities/hadoop “start-hadoop data…” 8 minutes ago Up 8 minutes 8020/tcp, 9000/tcp, 10020/tcp, 13562/tcp, 14000/tcp, 19888/tcp, 50010/tcp, 50020/tcp, 50070/tcp, 50075/tcp, 50090/tcp, 50470/tcp, 50475/tcp hadoop_datanode_1a8656de09ecc singularities/hadoop “start-hadoop name…” 8 minutes ago Up 8 minutes 0.0.0.0:8020->8020/tcp, 0.0.0.0:10020->10020/tcp, 0.0.0.0:13562->13562/tcp, 0.0.0.0:14000->14000/tcp, 9000/tcp, 50010/tcp, 0.0.0.0:19888->19888/tcp, 0.0.0.0:50070->50070/tcp, 50020/tcp, 50090/tcp, 50470/tcp, 0.0.0.0:50075->50075/tcp, 50475/tcp hadoop_namenode_1

打開瀏覽器，查看效果圖

http://localhost:50070/dfshealth.html#tab-overview

進入某臺docker容器

//拿到container iddocker ps 執(zhí)行命令進入容器docker exec -it e96b395f56e3 bash

執(zhí)行HDFS命令

# 查看所有命令hadoop fs# 創(chuàng)建目錄hadoop fs -mkdir /hdfs #在根目錄下創(chuàng)建hdfs文件夾# 查看目錄hadoop fs -ls / #列出根目錄下的文件列表# 創(chuàng)建多級目錄hadoop fs -mkdir -p /hdfs/d1/d2# 上傳文件到HDFSecho “hello world” >> local.txt #創(chuàng)建文件hadoop fs -put local.txt /hdfs/ #上傳文件到hdfs# 下載hdfs文件hadoop fs -get /hdfs/local.txt# 刪除hdfs中的文件hadoop fs -rm /hdfs/local.txt# 刪除hdfs中的目錄hadoop fs -rmdir /hdfs/d1/d2

docker 容器里安裝一下clickhouse，進行通信

sudo apt-get install apt-transport-https ca-certificates dirmngrsudo apt-key adv –keyserver hkp://keyserver.ubuntu.com:80 –recv E0C56BD4echo “deb https://repo.clickhouse.com/deb/stable/ main/” | sudo tee /etc/apt/sources.list.d/clickhouse.listsudo apt-get updatesudo apt-get install -y clickhouse-server clickhouse-clientsudo service clickhouse-server startclickhouse-client

HDFS授權(quán)

hadoop fs -mkdir /clickhousehadoop fs -chown -R clickhouse:clickhouse /clickhouse

創(chuàng)建HDFS數(shù)據(jù)表

CREATE TABLE hdfs_table10(id UInt32,code String,name String)ENGINE = HDFS(‘hdfs://namenode:8020/clickhouse/hdfs_table10′,’CSV’)

寫入數(shù)據(jù)

INSERT INTO hdfs_table10 SELECT number,concat(‘code’,toString(number)),concat(‘n’,toString(number)) FROM numbers(5)

— 再次插入就會失敗，報文件已存在。一般是csv文件已經(jīng)在hdfs中存在了，我們直接建表直接去讀

查詢數(shù)據(jù)

select * from hdfs_table10

這種方式與使用Hive類似，我們直接可以將HDFS對應(yīng)的文件映射成ClickHouse中的一張表，這樣就可以使用SQL操作HDFS上的文件了。注意：ClickHouse并不能夠刪除HDFS上的數(shù)據(jù)，當(dāng)我們在ClickHouse客戶端中刪除了對應(yīng)的表，只是刪除了表結(jié)構(gòu)，HDFS上的文件并沒有被刪除，這一點跟Hive的外部表十分相似。

MySQL

服務(wù)器安裝mysql

apt-get install mysql-server//啟動服務(wù)service mysql start//進入服務(wù)mysql -uroot -p

MySQL表引擎可以與MySQL數(shù)據(jù)庫中的數(shù)據(jù)表建立映射，并通過 SQL向其發(fā)起遠程查詢，包括SELECT和INSERT，它的聲明方式如 下：

ENGINE = MySQL(‘host:port’, ‘database’, ‘table’, ‘user’, ‘password'[,replace_query, ‘on_duplicate_clause’])

·replacequery默認(rèn)為0，對應(yīng)MySQL的REPLACE INTO語法。如果 將它設(shè)置為1，則會用REPLACE INTO代替INSERT INTO。 ·onduplicateclause默認(rèn)為0，對應(yīng)MySQL的ON DUPLICATE KEY 語法。如果需要使用該設(shè)置，則必須將replacequery設(shè)置成0。

clickhouse創(chuàng)建映射表

CREATE TABLE dolphin_scheduler_table00(id UInt32,name String)ENGINE = MySQL(‘127.0.0.1:3306’, ‘test’,’dolphin_scheduler_table’, ‘root’, ”)

插入數(shù)據(jù)

INSERT INTO TABLE dolphin_scheduler_table00 VALUES (1,’流程1′)

查詢Mysql 表 dolphinschedulertable ，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)被遠程寫入了。

Kafka

kafka表引擎的聲明方式

ENGINE = Kafka()SETTINGS kafka_broker_list = ‘host:port,… ‘, //表示Broker服務(wù)的地址列表、多個地址之間使用逗號分隔，如broker_1,broker_2 kafka_topic_list = ‘topic1,topic2,…’, //表示訂閱消息主題的名稱列表 kafka_group_name = ‘group_name’, //表示消費組的名稱， kafka_format = ‘data_format'[,] //表示用于解析消息的數(shù)據(jù)格式 [kafka_row_delimiter = ‘delimiter_symbol’] //表示判定一行數(shù)據(jù)的結(jié)束符，默認(rèn)值為” [kafka_schema = ”] //對應(yīng)Kafka的schema參數(shù) [kafka_num_consumers = N] //表示消費者的數(shù)量，默認(rèn)值為1 [kafka_skip_broken_messages = N] [kafka_commit_every_batch = N //表示執(zhí)行Kafka commit的頻率 注意：帶方括號的為選填項

創(chuàng)建數(shù)據(jù)表方式

CREATE TABLE kafka_test( id UInt32, code String, name String) ENGINE = Kafka()SETTINGS kafka_broker_list = ‘hdp1.nauu.com:6667’, kafka_topic_list = ‘sales-queue’, kafka_group_name = ‘chgroup’, kafka_format = ‘JSONEachRow’, kafka_skip_broken_messages = 100

ClickHouse數(shù)據(jù)查詢實操

1、在生產(chǎn)環(huán)境中、或者在實際應(yīng)用場景中、應(yīng)當(dāng)避免使用SELECT * 形式來查詢數(shù)據(jù)，因為通配符*對于采用列式存儲的ClickHouse而言沒有任何好處。假如面對一張擁有數(shù)百個列字段的數(shù)據(jù)表，下面這兩條 SELECT語句的性能可能會相差100倍之多,因為 * 會查詢所有列字段。

–使用通配符*與按列按需查詢相比，性能可能相差100倍SELECT * FROM datasets.hits_v1;SELECT WatchID FROM datasets.hits_v1;

各OLAP引擎對比