當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

最强OLAP分析引擎-Clickhouse快速精通-上

發布時間：2024/3/26 编程问答 31 豆豆

生活随笔收集整理的這篇文章主要介紹了最强OLAP分析引擎-Clickhouse快速精通-上小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

最強OLAP分析引擎-Clickhouse快速精通一

==樓蘭==

文章目錄

一、Clickhouse簡介
- 1、什么是Clickhouse
- 2、Clickhouse適用場景。
二、Clickhouse環境安裝
- 1、線上快速體驗
- 2、本地快速部署
- 3、遠程連接clickhouse
- - 3.1 打開遠程連接控制
  - 3.2 其他方式訪問clickhouse
三、Clickhouse使用篇
- 3.1、建庫
- - 3.1.1 Atomic 庫引擎
  - 3.1.2 MySQL庫引擎
- 3.2、建表
- - 3.2.1 數據類型
  - 3.2.2 MergeTree 表引擎
  - - partition by 分區鍵
    - order by 排序鍵
    - primary key 主鍵
    - TTL 數據存活時間
    - SAMPLE BY 數據抽樣
  - 3.2.3 ReplacingMergeTree
  - MergeTree引擎族總結
- 3.3 數據使用
- - 3.3.1、數據導入導出
  - 3.3.2、數據修改
  - 3.3.3、數據查詢

一、Clickhouse簡介

1、什么是Clickhouse

? Clickhouse是由俄羅斯yandex公司開源的一個用于聯機分析OLAP的列式數據庫管理系統。他是使用C++語言編寫的，支持SQL實時查詢的大型數據管理系統。由于Clickhouse在大型數據集查詢處理的高效表現，從2016年開源以來，就吸引了全球的目光，甚至一度登上github的關注度頭把交易。

? 這一段介紹中標出了Clickhouse的幾個顯著特點。

OLAP

Clickhouse的設計定位就是用于OLAP離線數據處理。相比于OLTP在線事務處理，Clickhouse更關注于對海量數據的計算分析，關注的是數據吞吐、查詢速度、計算性能等指標。而對于數據頻繁的修改變更，則不太擅長。所以Clickhouse通常用來構建后端的實時數倉或者離線數倉。
列式存儲

? Clickhouse是一個真正意義上的列式存儲數據庫。傳統數據庫存儲數據都是按照數據行進行存儲。

? 比如常用的MySQL，他的B+樹的葉子節點就會整體保留一行數據。

? 這樣的好處是，當想要查詢某一條數據時，可以通過一次磁盤查找加順序讀取獲得這一條完整的數據。

? 而Clickhouse存儲數據的方式則是按照列來存儲，將來自不同列的數據進行單獨存儲。實際上后面會介紹到，Clickhouse存儲一個表數據時，就是以一列為一個文件進行存儲的。

? 列式存儲的數據庫產品其實也有很多，像Druid數據庫，InfiniDB等等很多。只是在國內其實用的還是比較少的。列式存儲相比于行式存儲在很多數據計算方面會體現出很多優勢。例如通常一個計算過程都只會用到少數幾個列的數據，這時行式存儲就需要讀取到相關行的所有列數據再進行過濾。而列式存儲就可以直接讀取這幾個列的相關數據，而不用查找其他不關心的數據。

2、Clickhouse適用場景。

? 一個典型的OLAP場景主要是對海量數據進行更新，相比于我們常用的mysql等OLTP數據庫，有一些很明顯的特征。

絕大多數請求都是讀請求。對數據的修改比較少或者幾乎沒有。
數據量很大。這個量即包括數據的行數，也包括數據的列數。也就是通常說的寬表。大部分情況下，對分布式表結構的要求是必須的。
數據通常以大的批次進行整體更新，而不是單行更新。這需要有很高的數據吞吐量。
對事務的要求不是必須的。對于數據一致性的要求不會太高。通常只要求數據最終一致性。

? Clickhouse的數據吞吐量相當大，能夠存儲海量的數據，并能夠以水平擴展的方式進行擴容。對大表的查詢計算處理效率也非常高，甚至很多場景下都可以擁有媲美于關系型數據庫的查詢效率。官網給出的一些測試數據也大都是上千萬行*數百列的數據規模。很多大規模的數據查詢也都能輕松達到毫秒級別。

? 但是需要指出，Clickhouse高效性能的背后，肯定伴隨計算機資源的大量消耗。Clickhouse對內存和CPU的占用率都非常高，一個很普通的查詢都可能需要消耗非常多的資源。因此，Clickhouse的查詢頻率也不宜太高。過于頻繁的連續或者并發查詢甚至很容易導致服務直接崩潰。

? 綜合Clickhouse的特點，他就非常適合用于后端數倉的建設。當然，這本身也是Clickhouse的設計目標。

二、Clickhouse環境安裝

1、線上快速體驗

? Clickhouse的官方網站是https://clickhouse.com/ 。他的LOGO是一個列式存儲的示意圖。

? 點擊Online Demo按鈕，可以直接訪問線上的演示環境，直接運行Clickhouse。

? 線上環境中datasets庫里有默認的測試數據，這幾個表都是千萬級別行*上百列級別的大型測試數據，在上面可以直接執行SQL語句。并且在左側的幾個不同菜單中還包含默認的執行腳本。

? 你可以簡單體驗一下，執行select count(WatchID) from hits_v1 這樣的統計SQL非常快，幾乎都是毫秒級。但是執行select * from hits_v1 limit 100就慢很多。

2、本地快速部署

? Clickhouse的官方文檔非常詳細，是學習Clickhouse最重要的資料。其中關于安裝部署的章節地址是：https://clickhouse.com/docs/zh/getting-started/install/。

? 演示環境還是用三臺CentOS服務器hadoop01,hadoop02,hadoop03三臺機器。安裝前，建議關閉三臺服務器的防火墻以及SELinux安全組件，并打開操作系統的文件限制。

接下來這一章節將演示單機環境下的clickhouse安裝。clickhouse的單機環境已經能夠體現出非常強的性能。從單機擴展到集群是相當簡單的，在后續clickhouse的備份以及分布式表章節用到集群時再一起分享。

? clickhouse不需要依賴其他的組件，自己就能夠提供非常強悍的數據處理性能。支持的安裝環境非常多，安裝方式也有很多種。這里，我們采用最為直觀的tgz壓縮包的方式進行安裝。當然，如果是生產環境，建議的方式還是通過源碼打包編譯。

? 首先需要下載Clickhouse。下載地址：https://repo.clickhouse.com/tgz/stable/。在這個倉庫中直接包含了Clickhouse的所有發布版本，俄羅斯簡單直接的風格一覽無余。從這里也能看出，Clickhouse的版本發布非常頻繁。這里我們選取當前最新的21.9.4.35版本。這里面總共有四種tgz包是我們需要下載的，clickhouse-server-21.9.4.35.tgz, clickhouse-common-static-dbg-21.9.4.35.tgz, clickhouse-common-static-21.9.4.35.tgz, clickhouse-client-21.9.4.35.tgz。

? 然后將這四個壓縮包解壓并依次進行安裝。

tar -xzvf clickhouse-common-static-$LATEST_VERSION.tgz sudo clickhouse-common-static-$LATEST_VERSION/install/doinst.shtar -xzvf clickhouse-common-static-dbg-$LATEST_VERSION.tgz sudo clickhouse-common-static-dbg-$LATEST_VERSION/install/doinst.shtar -xzvf clickhouse-server-$LATEST_VERSION.tgz sudo clickhouse-server-$LATEST_VERSION/install/doinst.shtar -xzvf clickhouse-client-$LATEST_VERSION.tgz sudo clickhouse-client-$LATEST_VERSION/install/doinst.sh

安裝過程中，第1、第2、第4三個包的安裝都沒有輸出，因為這三個包的安裝過程只是簡單的復制文件。安裝第3個包clickhouse-server-21.9.4.35時，clickhouse會在數據庫中創建一個默認的用戶default，安裝過程中，會需要給這個default用戶輸入一個密碼。這里進行測試，就直接回車，不設置密碼即可。正常情況下，安裝完成會得到這樣的輸出日志。

Enter password for default user: Password for default user is empty string. See /etc/clickhouse-server/users.xml and /etc/clickhouse-server/users.d to change it. Setting capabilities for clickhouse binary. This is optional.ClickHouse has been successfully installed.Start clickhouse-server with:sudo clickhouse startStart clickhouse-client with:clickhouse-client

? 安裝完成后，就可以使用clickhouse start指令啟動clickhouse服務了。

服務端還有其他一些指令，可以使用clickhouse --help查看

? 然后使用clickhouse-client就可以打開客戶端。這樣就可以使用SQL進行查詢了。例如 show databases;查看已有的數據庫。show tables from system; 查看系統表。

客戶端啟動時可以設置非常多的參數，可以使用clickhouse-client --help查看。常用的clickhouse -m表示支持多行SQL查詢。

關于系統表的詳細介紹可以參看官網文檔操作->系統表章節。https://clickhouse.com/docs/zh/operations/system-tables/

? 安裝完成后，有幾個重要的目錄要記住：

執行腳本： /usr/bin/ 之前用到的clickhouse和clickhouse-client這些指令就被默認安裝在這個目錄。
配置文件： /etc/clickhouse-server/ 這個目錄下的config.xml和users.xml是最為重要的兩個配置文件。后續章節會介紹其中的重要配置。
運行日志：/var/log/clickhouse-server/ 服務運行的詳細日志。
數據目錄： /var/lib/clickhouse/ 這個目錄包含了clickhouse運行時的所有數據文件。例如metadata目錄下存放了所有表的元數據，可以看到，clickhouse就是以sql文件的方式保存表結構，啟動時加載這些sql文件就完成了數據加載。而data目錄下存放了所有的表數據。像之前看到的default和system兩個默認的數據庫就對應data目錄下的兩個文件夾。

? 另外，clickhouse在安裝時，會默認創建一個clickhouse用戶來部署這些文件。所以，如果不是使用root用戶進行操作的話，需要注意下用戶權限的問題。

3、遠程連接clickhouse

3.1 打開遠程連接控制

? 默認情況下，clickhouse服務只能在本地進行連接，遠程機器是無法連接的。這點跟mysql是很類似的。因此，還需要做一些修改，讓clickhouse可以遠程訪問。

? 配置方式直接修改clickhouse的config.xml配置文件。所在目錄 /etc/clickhouse-server。將下面這一行注釋打開。文件156行。

<listen_host>::</listen_host>

? 然后重啟clickhouse即可。

clickhouse restart

注意，重啟時不能有客戶端連接上。否則無法正常重啟。

? 重啟完成后，其他機器上就可以使用clickhouse-client命令行工具遠程連接clickhouse服務了。只不過需要通過-h參數指定服務端機器名即可。

關于clickhouse-client命令行工具的其他使用方式，可以使用clickhouse-client --help方式查看幫助。也可以查看官方文檔：https://clickhouse.com/docs/zh/interfaces/cli/

3.2 其他方式訪問clickhouse

? clickhouse除了命令行客戶端外，還提供了非常豐富的接入客戶端。例如使用瀏覽器直接訪問地址 http://hadoop01:8123/?query=show databases 就可以訪問clickhouse的http客戶端。這個8123端口可以在clickhouse的配置文件中進行定制。

? 同樣在8123端口，clickhouse還提供了JDBC驅動程序來連接。目前官網提供了一個官方的驅動包以及兩個第三方的驅動包。其中，官方JDBC驅動包的maven坐標是

<dependency><groupId>ru.yandex.clickhouse</groupId><artifactId>clickhouse-jdbc</artifactId><version>0.3.2</version> </dependency>

? 引入這個驅動包后，就可以像連接其他關系型數據庫一樣訪問clickhouse了。

Class.forName("ru.yandex.clickhouse.ClickHouseDriver"); Connection connection = DriverManager.getConnection("jdbc:clickhouse://hadoop01:8123/default"); .....

? 當然，這個客戶端工具也提供了自己封裝的客戶端，簡化數據庫訪問。具體參見官方github倉庫：https://github.com/ClickHouse/clickhouse-jdbc

? 另外還有兩個第三方開發的JDBC驅動包。分別是 ClickHouse-Native-JDBC(倉庫地址：https://github.com/housepower/ClickHouse-Native-JDBC) 和 clickhouse4j (倉庫地址：https://github.com/blynkkk/clickhouse4j)

? 如果你覺得自己下載JDBC驅動包比較麻煩，那還有更簡單的方式。 clickhouse完全兼容最常用的mysql和postgresql兩個數據庫，可以用他們對應的JDBC驅動直接連接。只需要注意 mysql服務的默認端口是9004。Postgresql的默認端口是9005。

? 接下來，也可以將對應的jar包導入到一些第三方的客戶端工具中，例如navicat, DataGrip等工具，像訪問MySQL一樣訪問clickhouse。

從這個安裝過程中可以體會到，雖然clickhouse底層的設計非常精妙，但是表現出來的實現方式卻是非常簡單直接。大部分的功能都是以一種統一有序的方式直接進行堆疊。

三、Clickhouse使用篇

? clickhouse本身作為一個數據庫，對普通增刪改查的操作都是支持的。但是，他針對數倉的使用場景，又有非常多的高級特性。對這些高級特性的掌握程度將直接影響clickhouse的使用效率。實現一個同樣的查詢邏輯，不同的SQL寫法在clickhouse上很容易體現出非常非常大的執行時長差別。所以在使用clickhouse時，需要對這些特性非常重視。

3.1、建庫

? 使用數據庫首先要建庫，clickhouse提供了多種庫引擎實現不同場景下的庫聲明。

3.1.1 Atomic 庫引擎

? 這是clickhouse默認的庫引擎。默認創建的default庫就是使用的這種引擎。可以在建庫時進行聲明。

CREATE DATABASE test[ ENGINE = Atomic];

? Atomic類型的數據庫完全由clickhouse自己管理數據。每個數據庫對應/var/lib/data/目錄下的一個子目錄。數據庫中的每個表會分配一個唯一的UUID，數據存儲在目錄 /var/lib/clickhouse/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy是該表的UUID。

3.1.2 MySQL庫引擎

? clickhouse作為一個數據倉庫，還提供了非常多與其他數據庫整合的庫引擎。最為常見的就是MySQL。

? MySQL引擎用于將遠程的MySQL服務器中的表映射到ClickHouse中，并允許您對表進行INSERT和SELECT查詢，以方便您在ClickHouse與MySQL之間進行數據交換。MySQL數據庫引擎會將對其的查詢轉換為MySQL語法并發送到MySQL服務器中，因此您可以執行諸如SHOW TABLES或SHOW CREATE TABLE之類的操作。

? 通過MySQL引擎可以省掉很多ETL的過程。例如下面的語句就可以在clickhouse中創建一個mysqldb。

CREATE DATABASE IF NOT EXISTS mysqldb ENGINE = MySQL('hadoop01:3306', 'testdb', 'root', 'root')；

? 對于mysqldb庫的操作，會轉義成mysql語法，發送到相對應的MySQL中執行。

//使用mysqldb use mysqldb; //列出庫中所有的表 --clickhouse中并沒有建表 show tables; //查詢mysql中的數據 hadoop01 :) select * from user;

? 接下來，可以像操作clickhouse自己的表一樣進行insert\delete等操作。但是不能進行 RENAME、CREATE TABLE、ALTER操作。

? 這種庫引擎，clickhouse本身并不存儲數據，只是將請求轉發到mysql。同樣，clickhouse還提供了針對PostgreSQL、SQLLite的庫引擎。

? 是不是覺得只是請求轉發還不夠爽？性能不夠高？clickhouse還提供了自己存儲數據的物化引擎，針對MySQL的MaterializedMySQL引擎和針對PostgreSQL的MaterializedPostgreSQL引擎。這兩個引擎都會將clickhouse服務器作為對應數據庫的從庫工作。通過執行日志實時將主庫中的數據同步到clickhouse中。但是目前這兩個引擎還在實驗階段。可以嘗試，但不建議在生產上使用。

具體使用方式詳見官方文檔：https://clickhouse.com/docs/zh/engines/database-engines/materialized-mysql/

? 實際上，大部分場景下，我們就使用clickhouse自己的默認引擎就夠了。而其他的引擎會通過定制的ETL過程來實現。但是clickhouse功能的樸實無華已經盡顯無疑。

3.2、建表

3.2.1 數據類型

? clickhouse的極簡化設計在基礎數據類型中體現得尤為明顯。

1、整型

? clickhouse中的整型不像其他數據庫中區分int，short，long等等這些類型，而是統一表示固定長度的整數，包括有符號整型和無符號整型。統一定義為Int，后面帶上數字表示占用的字節數。

整型范圍

Int8-[-128:127] 占用8個字節，對應java中的byte
Int16-[-32768:32767] 占用16個字節，對應java中的short
Int32-[-2147483648:2147483647] 占用32個字節，對應java中的int
Int64-[-9223372036854775808:9223372036854775807] 占用64個字節，對應java中的long

無符號整型范圍

UInt8-[0:255]
UInt16-[0:65535]
UInt32-[0:4294967295]
UInt64-[0:18446744073709551615]

2、boolean布爾型

? clickhouse中沒有定義表示true和false的布爾類型數據，通常都是直接使用UInt8

3、浮點型

Float32 - float
Float64 - double

? 官方建議盡量使用整型來存儲數據，將固定精度的數字轉換成為整數值。例如時間用毫秒為單位保存。這是因為使用浮點型有精度丟失問題。例如執行 select 1-0.9 得到的結果將是 0.09999999999999998 而不是0.1。

? 浮點型一般用于數據值比較小，不設計大量的統計計算，精度要求也不高的場景。例如保存商品的重量。但是對于精度要求比較高的金額，就極不建議使用浮點型。而應該用Decimal型。

4、Decimal型

? 有符號的浮點數，可以在加、減和乘法運算過程中保持精度。對于除法，最低有效數字將被拋棄(不進行四舍五入)。通常有三種聲明： Decimal32(s)、Decimal64(s)、Decimal128(s)。后面的s表示小數點后的數字位數。前面的32，64, 128表示浮點精度，決定可以有多少個十進制數字(包含小數位)，也就代表不同的取值范圍。

? 數據在底層會采用與自身位寬相同的有符號整數存儲。而現代CPU不支持128位的數字，因此Decimal128上的操作需要由軟件來進行模擬。所以Decimal128的運算速度會明顯慢于Decimal32\Decimal64。也就是說盡量少用Decimal128。

5、字符型

? clickhouse的字符型數據使用String進行聲明。這個字符串可以是任意長度的。他可以包含任意的字節集，包含空字節。因此，字符串類型可以代替其他數據庫中的VARCHAR、BLOB、CLOB等類型。

? clickhouse中沒有編碼的概念。字符串可以是任意的字節集，按他們原本的方式進行存儲和輸出。對于不同的編碼文本，clickhouse會有不同處理字符串的函數。比如 length函數可以計算字符串包含的字節數組的長度，而lengthUTF8函數是假設字符串以UTF-8編碼，計算的字符串包含的Unicode字符的長度。

? 還有一個固定長度的字符串類型FixedString(N)，這個N就是要聲明的字節數。如果字符串包含的字節數不足N，將會對字符串末尾進行空字節填充。如果字符串包含的字節數大于N，將會拋出異常。可以用來保存一些例如手機號碼、IP地址這一類等長的規范數據。在實際開發中使用比較少。

6、枚舉類型

? 包含Enum8和Enum16兩種類型。Enum保存’string’=integer的對應關系。在clickhouse中，盡管用戶使用的是字符串常量，但所有罕有Enum數據類型的操作都是按照韓包含整數的值來執行的。這在性能方便比使用String數據類型更有效。Enum后面的8和16也是對應的整數值integer的位寬。

? 例如：先創建一個帶枚舉類型列的表。

CREATE TABLE t_enum (x Enum8('hello' = 1, 'world' = 2) ) ENGINE = TinyLog

? 這個x列只能存儲類型定義中列出來的值，hello 或者是 world。嘗試insert插入其他值時會拋異常。

:) INSERT INTO t_enum VALUES ('hello'), ('world'), ('hello')INSERT INTO t_enum VALUESOk.3 rows in set. Elapsed: 0.002 sec.:) insert into t_enum values('a')INSERT INTO t_enum VALUESException on client: Code: 49. DB::Exception: Unknown element 'a' for type Enum8('hello' = 1, 'world' = 2)

? 從表中查詢數據時， clickhouse會返回前面的字符串值。

SELECT * FROM t_enum┌─x─────┐ │ hello │ │ world │ │ hello │ └───────┘

? 如果需要查看對應行的數值，則必須將Enum值轉換成為整數類型。

SELECT CAST(x, 'Int8') FROM t_enum┌─CAST(x, 'Int8')─┐ │ 1 │ │ 2 │ │ 1 │ └─────────────────┘

? 枚舉類型在開發中可以很方便的代替字典表，優化一些例如狀態、類型這樣的字段。但是實際使用時，如果枚舉值發生變化，就會帶來非常多的維護成本，甚至會帶來數據丟失的問題。因此，枚舉類型要謹慎使用。

7、數組類型

? 類型聲明： array(T) 。表示一個由T類型元素組成的數組。T可以是任意類型，甚至也可以是數組類型。但是不建議使用多位數組，clickhouse對多維數組的支持有限。例如在MergeTree引擎中就不能存儲多維數組。

? 示例：

:) SELECT array(1, 2) AS x, toTypeName(x)SELECT[1, 2] AS x,toTypeName(x)┌─x─────┬─toTypeName(array(1, 2))─┐ │ [1,2] │ Array(UInt8) │ └───────┴─────────────────────────┘1 rows in set. Elapsed: 0.002 sec.:) SELECT [1, 2] AS x, toTypeName(x)SELECT[1, 2] AS x,toTypeName(x)┌─x─────┬─toTypeName([1, 2])─┐ │ [1,2] │ Array(UInt8) │ └───────┴────────────────────┘1 rows in set. Elapsed: 0.002 sec.

8、時間類型

? 時間類型是每個數據庫都要處理的類型。clickhouse的時間類型聲明相對簡單很多。在clickhouse中有三種時間類型

Date 可以接受一個 年-月-日 格式的字符串。例如 ‘2021-10-13’。
Datetime 可以接受一個 年-月-日時:分:秒 格式的字符串。例如’2021-10-13 20:50:10’。
Datatime64 可以接受一個 年-月-日時:分:秒.毫秒 格式的字符串。例如 ‘2021-10-13 20:50:10.232’。

9、可為空類型

? 絕大部分的基礎類型都可以通過在前面添加一個Nullable()聲明來允許接受Null空值。例如Nullable(Int8)類型的列可以存儲Int8類型的值，沒有值的行將存儲NULL。

? Nullable類型字段不能包含在表索引中。并且使用Nullable幾乎總是對性能產生負面影響，在設計數據庫時要盡量避免使用Nullable。例如對于字符串，可以用空字符代替Null。而對于整型數據，可以用無業務意義的數字例如-1來表示Null

CREATE TABLE t_null(x Int8, y Nullable(Int8)) ENGINE TinyLog; INSERT INTO t_null VALUES (1, NULL), (2, 3); SELECT x + y FROM t_null; ┌─plus(x, y)─┐ │ ???? │ │ 5 │ └────────────┘

? clickhouse中還設計了很多非常有特色的數據類型，例如Geo,Map,Tuple,UUID等類型。具體參見官方文檔。https://clickhouse.com/docs/zh/sql-reference/data-types/

3.2.2 MergeTree 表引擎

? 就像MySQL提供了Innodb和MyISAM等等多種數據引擎來對表進行管理一樣，clickhouse也提供了非常多的引擎來對表進行管理，只是clickhouse的表引擎更多，功能更強大。表引擎也是clickhouse非常有特色的一個功能。表引擎決定了一個表的所有數據屬性，包括

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

? clickhouse中的表引擎非常豐富，有好幾十中。整體可以分為四類。

MergeTree 合并樹家族：這是適用于高負載任務的最通用同時功能最強大的表引擎。這一類引擎的共同特點是可以快速插入數據并進行后續的后臺數據處理。是clickhouse默認的也是最為重要的引擎。
Log 日志系列：具有最小功能的輕量級引擎。用于快速寫入許多小表(最多約100萬行)，并在以后整體讀取這些數據。例如常用的滾動日志。例如TinyLog引擎，以列文件的形式保存在磁盤上，不支持索引，沒有并發控制，通常只用于練習。
Integration Engines 集成引擎：用于與其他的數據存儲與處理系統集成的引擎。通常可用來簡化一些ETL的工作。例如同樣有MySQL的表引擎，將對表的查詢語句轉發到遠程MySQL數據庫中。另外，可以看到，clickhouse支持的集成表引擎比庫引擎豐富很多。
Special Engines 特別引擎：用于其他特定功能的引擎。比如使用內存表、字典表等。

? 這其中，我們最關注的當然是MergeTree合并樹家族，后續關于clickhouse表的分享也都基于MergeTree引擎。其他幾種類型的表引擎可以在用到的時候去官網查一下即可。

官方資料查詢地址：https://clickhouse.com/docs/zh/engines/table-engines/

? clickhouse中最強大的表引擎當屬MergeTree合并樹引擎以及該系列 *MergeTree中的其他引擎。其地位堪比MySQL中的innodb。

? 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, ...]

? 主要通過Engine指定表引擎，然后下面指定一些相關的參數。

例如：

create table t_stock(id UInt32,sku_id String,total_amount Decimal(16,2),create_time Datetime ) engine =MergeTree()partition by toYYYYMMDD(create_time)primary key (id)order by (id,sku_id);

? 對于MergeTree，SETTINGS中的大部分蠶食都有了默認值，可以不用設置。重點關注的就是以下幾個配置。partition by 分區鍵，primary key 主鍵以及 order by 排序鍵。下面來逐一進行分享。

partition by 分區鍵

1> 分區鍵的作用

? 分區鍵的作用主要是降低數據掃描的范圍，優化查詢速度。例如示例中，按天進行了分區，當查詢的where條件中指定了日期條件，就只需要去掃描對應日期的數據，而不用進行全表掃描了。使用分區后，涉及到跨分區的查詢操作，clickhouse將會以分區為單位進行并行處理。在clickhouse中這是一個可選項，如果不填，相當于只用一個分區。

2> 分區表的數據目錄

? MergeTree引擎默認是以列文件+索引文件+表定義文件共同描述一個表。這些文件都在clickhouse的本地數據磁盤當中(默認/var/lib/clickhouse目錄)。如果設定了分區，那么這些文件都會保存在不同的分區目錄中。

? 例如我們在示例表中插入幾條數據：

insert into t_stock values (101,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (103,'sku_002',2000.00,'2020-06-02 13:00:00'), (104,'sku_002',12000.00,'2020-06-03 13:00:00'), (105,'sku_002',600.00,'2020-06-04 12:00:00');

? 然后進入clickhouse的數據目錄來看看。

? 在default庫對應的元數據目錄 /var/lib/clickhouse/metadata/default 下，保存了t_stock.sql。這個就是clickhouse保存的元數據信息。直接就是一個簡單粗暴的SQL語句。

ATTACH TABLE _ UUID 'a6f7175a-71e0-40e5-a6f7-175a71e040e5' (`id` UInt32,`sku_id` String,`total_amount` Decimal(16, 2),`create_time` DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMMDD(create_time) PRIMARY KEY id ORDER BY (id, sku_id) SETTINGS index_granularity = 8192

? 只不過對我們寫的建表語句做了一點小小的修改。

? 然后進入t_stock表的數據目錄，/var/lib/clickhouse/data/default/t_stock。這個目錄實際上是指向store目錄下的一個數據目錄的軟連接。

[root@hadoop01 default]# ll 總用量 0 lrwxrwxrwx 1 clickhouse clickhouse 67 10月 13 15:07 t_stock -> /var/lib/clickhouse/store/a6f/a6f7175a-71e0-40e5-a6f7-175a71e040e5/

store下的目錄之前提過，就是表的UUID。

這個目錄下就保存了t_stock表的數據。可以看到里面的目錄結構：

[root@hadoop01 t_stock]# ll 總用量 4 drwxr-x--- 2 clickhouse clickhouse 203 10月 13 15:14 20200601_1_1_0 drwxr-x--- 2 clickhouse clickhouse 203 10月 13 15:14 20200602_2_2_0 drwxr-x--- 2 clickhouse clickhouse 203 10月 13 15:14 20200603_3_3_0 drwxr-x--- 2 clickhouse clickhouse 203 10月 13 15:14 20200604_4_4_0 drwxr-x--- 2 clickhouse clickhouse 6 10月 13 15:07 detached -rw-r----- 1 clickhouse clickhouse 1 10月 13 15:07 format_version.txt

? 對于20200601_1_1_0這樣的目錄，用下劃線拆分成了四個部分。20200601就表示對應的分區。這個分區下的數據就保存在這個目錄下。而后面的 1_1_0 。前面兩個1，表示這個目錄包含的分區最小塊編號和最大塊編號，最后一個0表示數據合并次數。關于分區合并，會在下面講到。

關于數據分區：如果分區鍵沒有指定，會生成一個all分區。如果分區鍵是String,Float型，會經過hash產生一個hashid作為分區值。

? 這個目錄下就記載了當前分區的詳細信息。里面有幾個關鍵的文件：

bin文件：數據文件 mrk文件：標記文件標記文件在 idx索引文件和 bin數據文件之間起到了橋梁作用。以mrk2結尾的文件，表示該表啟用了自適應索引間隔。 primary.idx文件：主鍵索引文件，用于加快查詢效率。 minmax_create_time.idx：分區鍵的最大最小值。 checksums.txt：校驗文件，用于校驗各個文件的正確性。存放各個文件的size以及hash值。 columns.txt: 表的結構信息 count.txt: 當前分區的數據條數。 --所以對于clickhouse來說，查表的行數非常非常快。

? 設置了分區鍵后，在客戶端使用select * from t_stock就能看到分區的結果。

使用第三方工具是看不到分區結果的。

3> 分區合并

? MergeTree引擎底層使用一種類似于LSM樹的結構來保存數據。任何一次對數據的修改都會臨時產生一個分區，而不會修改已有的分區。寫入后的某個時刻，clickhouse會在后臺自動執行合并操作。這個這個間隔時間是未知的，大概在10~15分鐘左右。如果等不及，也可以執行手動合并。合并指令

optimize table t_stock final;

? 下面。我們再次插入幾條20200601這一個分區內的測試數據。

insert into t_stock values (101,'sku_002',2000.00,'2020-06-01 14:00:00'), (102,'sku_004',2500.00,'2020-06-01 15:00:00'), (103,'sku_002',2000.00,'2020-06-01 16:00:00'), (104,'sku_002',12000.00,'2020-06-01 17:00:00');

? 插入完成后，再次查詢t_stock的數據，會成這樣：

? 從結果中可以看出 2020-06-01這個分區的數據沒有合并。

? 然后我們再去t_stock表的數據目錄下看一下clickhouse的數據目錄。

? 可以看到，新插入的數據進入了20200601_5_5_0這個目錄。這表示新插入的數據進入了20200601分區的5號數據塊。

? 這時，我們執行一次手動合并 optimize table t_stock final; 之后再來看下數據目錄：

? 這個結果表明原有的20200601_1_1_0和20200601_5_5_0 這兩個數據塊，被合并到了20200601_1_5_1這個數據塊中。新生成的數據塊包含了20200601分區下，從1號數據塊到5號數據塊的內容，合并次數為1。而后續對于t_stock表的查詢，都會走這個新生成的數據目錄來查。原有的兩個數據目錄會在未來clickhouse進行全局合并時刪除。

order by 排序鍵

? order by 排序鍵指定分區內的數據按照哪些字段排序進行有序保存。這是MergeTree中唯一的一個必填項。

? 數據有序保存對于clickhouse底層的數據處理是相當重要的，在海量數據場景下，實現快速檢索、去重、匯總等計算都離不開數據有序性的支持。這里需要注意的是，clickhouse的數據是分區內局部有序的。實際上這也比較好理解，因為clickhouse對于數據的處理就是以分區作為最小維度的。

? 分區鍵的設置對于主鍵也是有影響的。在clickhouse中，如果不設置表的主鍵，他就會以排序鍵來對數據進行檢索等數據處理。這里要注意的是，如果設置主鍵，主鍵必須是order by的前綴字段。例如order by 排序鍵設置為(id,sku_id)，那么主鍵只能是 id 或者是 (id,sku_id)。

primary key 主鍵

? 主鍵的作用是為了加快數據檢索的。clickhouse中的主鍵與其他數據庫有點不太一樣，他并不要求主鍵的數據具有唯一性。

? 我們之前已經看到，在clickhouse的metadata文件中保存的關于t_stock表的sql語句。而在那個sql文件當中，clickhouse在我們自定義的建表語句之后，加了一個默認的參數 index granularity，指定了值是8192。這是clickhouse中主鍵的一個重要作用。

? index granularity，直接翻譯的話是叫做索引粒度，是指在稀疏索引中兩個相鄰索引對應數據的間隔。clickhouse給出的默認值是8192。官方不建議修改這個值，但是有一種情況可能需要調整這個值，那就是數據中有非常大量的重復值，例如一個分區中幾萬行數據的主鍵列數值都是一樣的，當然很明顯，這種情況是非常少見的。

? 首先需要理解一個概念，稀疏索引。稀疏索引的概念非常類似于在Redis中經常提到的調表skiplist。也就是在構建索引數據時，并不記錄每一個主鍵的數值。而是按照一定的稀疏度，記錄幾個節點的索引數據。而這些記錄的數據，就保存在分區所在的數據目錄中。

? 稀疏索引的好處是可以減少數據的檢索次數。每次根據一個主鍵進行數據查找時，可以根據稀疏索引確定數據所在的范圍，然后再在選定的范圍內進行逐行掃描，就能快速定位到目標數據。

? clickhouse中的主鍵相當于給主鍵列的數據建立了一級索引，而實際上，在一級索引的基礎上，clickhouse還提供了二級索引的功能，相當于給一級索引再建立一個索引。二級索引的目的同樣也是為了加快數據檢索速度。例如

create table t_stock_2(id UInt32,sku_id String,total_amount Decimal(16,2),create_time Datetime, INDEX secondIndex total_amount TYPE minmax GRANULARITY 5 ) engine =MergeTreepartition by toYYYYMMDD(create_time)primary key (id)order by (id, sku_id);

? 在total_amout列上，就設定了一個類型為minmax的二級索引(還有一些其他的索引類型，例如常見的bloomfliter。具體查看官網)，名字為secondIndex。建立二級索引時，還指定了一個GRANULARITY參數，翻譯過來也是粒度的意思。這個粒度表示對一級索引進行聚合的粒度。這是什么意思呢？

? 例如按照上面的示例圖，對于一級索引，按照GRANULARITY粒度為4，就會劃分為[1,3],[3,6],[6,9],[9,12]…這樣的一些區間。而二級索引按照3的粒度，就會將三個區間聚合到一起，形成[1,9],[9,18]這樣的區間信息。當對數據進行檢索時，就可以先按二級索引先確定一個初略的范圍，再按照一級索引確定數據遍歷的范圍。

TTL 數據存活時間

? TTL即Time To Live。可以用來指定行存儲的持續時間。MergeTree可以針對表或者列聲明數據存活時間。設置TTL需要指定一個表達式來表示數據的存活時間。表達式中必須存在至少一個表示時間的Date或DateTime類型的列。比如 TTL date + INTERVAL 1 DAY 。也就是說，存活時間必須跟數據相關聯。

列級TTL

? 可以在列上直接聲明TTL規則。例如下面的語句就可以聲明total_amount字段的存活時間為create_time創建時間后的10秒鐘。

CREATE TABLE example_table (d DateTime,a Int TTL d + INTERVAL 1 MONTH,b Int TTL d + INTERVAL 1 MONTH,c String ) ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d;

? 當列中的值過期時，clickhouse會將他們替換成該列數據類型的默認值。如果某個數據塊中列的所有值都過期了，那么clickhouse會從文件系統中的數據塊中直接刪除這一列。

? 列式TTL不能用于主鍵。

表級TTL

? 設置表級TTL時，除了設置一個過期表達式之外，還可以配置一個數據移除規則。完整的聲明指令是這樣的：

TTL expr[DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'][, DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'] ...[WHERE conditions][GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ]

? 但是通常不會用得這么復雜。一般用到后面第一個中括號的可選項就差不多了。這里是定義clickhouse如何移除過期數據。

Delete - 刪除過期的行默認行為
TO DISK ‘aaa’ - 將數據塊移動到磁盤’aaa’
TO VOLUME ‘bbb’ - 將數據塊移動到卷 ‘bbb’
GROUP BY - 聚合過期的行

后面的where 可以指定哪些過期的行為會被刪除或聚合(不適用于數據移動)。

例如：
CREATE TABLE example_table (d DateTime,a Int ) ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d TTL d + INTERVAL 1 MONTH [DELETE],d + INTERVAL 1 WEEK TO VOLUME 'aaa',d + INTERVAL 2 WEEK TO DISK 'bbb';

SAMPLE BY 數據抽樣

? 數據抽樣同樣用于大數據分析，可以極大提升數據分析的性能。采樣修飾符只能用在MergeTree的表中才有效，并且抽樣表達式指定的列，必須包含在主鍵中。進行了采樣聲明后，就可以在查詢時進行采樣查詢。

? 例如官方提供的測試數據集hits_v1，在表聲明時指定了采樣規則

SAMPLE BY intHash32(UserID)

? 接下來就可以在查詢時指定采樣效率。

SELECT Title,count(*) AS PageViews FROM hits_v1 SAMPLE 0.1 #代表采樣 10%的數據,也可以是具體的條數 WHERE CounterID =57 GROUP BY Title ORDER BY PageViews DESC LIMIT 1000

? 這個采樣查詢是在滿足條件的結果集中隨機抽取10%的數據。

3.2.3 ReplacingMergeTree

? 下面我們介紹一個MergeTree家族中用得比較多的一個表引擎ReplacingMergeTree。這個表引擎與MergeTree的不同之處在于他會刪除排序值相同的重復項。這個去重的功能在實際開發中還是經常會要用到的。

? 但是要注意，ReplactingMergeTree的數據去重只會在數據合并期間進行。對應之前數據合并的示例就比較容易理解。正常情況下，數據合并是在后臺一個不確定的時間進行，這個時間是無法預先規劃的。當然如果確實需要，可以使用optimize 語句手動發起合并，但是這顯然是不建議的，因為optimize語句會引發數據的大量讀寫，會嚴重影響數據庫的性能。

? 所以，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] [SAMPLE BY expr] [SETTINGS name=value, ...]

? 基本上跟MergeTree是差不多的。最大的區別就在于ReplactingMergeTree需要指定一個參數ver。這個參數就表示版本列。類型必須是UInt*,Date或者DateTime。這是個可選的參數。

? 這個參數是用來指定數據去重的規則。可以想象，當數據集中出現了多條重復的數據，ReplacingMergeTree會在這一批重復數據中保存版本列的數據最大的那一條數據，而其他數據則標記為過期。如果沒有指定版本列，則會默認保留最后插入的那一條數據。

? 例如可以做個實驗

create table t_stock_merge(id UInt32,sku_id String,total_amount Decimal(16,2) ,create_time Datetime ) engine =ReplacingMergeTree(create_time)partition by toYYYYMMDD(create_time)primary key (id)order by (id, sku_id);

? 然后插入一批帶有重復id和sku_id的數據

insert into t_stock_merge values (101,'sku_001',1000.00,'2020-06-01 12:00:00') , (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00');

? 然后查詢t_stock_merge表中的數據：

? 可以看到， id為102，sku為sku_002的一組數據已經完成了合并。

? 對于ReplactingMergeTree需要注意的幾個重點：

ReplacingMergeTree是按照order by 指定的排序鍵作為判斷重復的標準。
他的去重只限定在一個分區中，不能跨區去重。
對于判斷為重復的數據，保留版本字段最大的一條數據，如果沒有指定版本值或者版本值也有重復的，就會保留最后插入的一條數據。
ReplacingMergeTree并不能始終保證數據是完全去重的。數據去重只會發生在 同一批插入數據 以及 后臺數據合并 這兩個時機。

同樣，我們再來試驗一下 SummingMergeTree。他在MergeTree的基礎上，在進行數據合并時，clickhouse會把所有具有相同主鍵的行合并成一行。新合并的行包含了被合并的行中具有數值數據類型的列的匯總值。

例如在建表時，通過engine參數指定SummingMergeTree，SummingMergeTree需要指定參數，表明按照哪個列進行聚合分析。

create table t_stock_sum(id UInt32,sku_id String,total_amount Decimal(16,2) ,create_time Datetime ) engine =SummingMergeTree(total_amount)partition by toYYYYMMDD(create_time)primary key (id)order by (id,sku_id );

插入一些測試數據：

insert into t_stock_sum values (101,'sku_001',1000.00,'2020-06-01 12:00:00') , (102,'sku_002',2000.00,'2020-06-01 11:00:00'), (102,'sku_004',2500.00,'2020-06-01 12:00:00'), (102,'sku_002',2000.00,'2020-06-01 13:00:00'), (102,'sku_002',12000.00,'2020-06-01 13:00:00'), (102,'sku_002',600.00,'2020-06-02 12:00:00');

接下來再次查詢數據時，會將totalamount進行聚合。

對于SummingMergeTree需要注意的幾個重點：

以 SummingMergeTree（）中指定的列作為匯總數據列。可以填寫多列必須數字列，如果不填，以所有非維度列且為數字列的字段為匯總數據列
以 order by 的列為準，作為維度列。其他的列按插入順序保留第一行
不在一個分區的數據不會被聚合
Summing并不會始終保證數據是聚合的，只有在數據合并的過程中聚合。數據合并的時機是同一批次插入或分片合并時才會進行聚合

MergeTree引擎族總結

? MergeTree是clickhouse最為常用也是最為強大的表引擎族。這一族表引擎提供了大數據下的數據快速插入、管理以及檢索的功能。

? MergeTree一系列表引擎底層都使用類似于LSM樹的方式提供數據的快速讀寫功能，這跟HBase其實是很像的。即新的數據(包括更新以及刪除的數據)并不會影響原有的數據，而是會記錄在一個新開辟的臨時數據塊中。查詢時會通過版本號查詢最新的一條結果。新開辟的數據需要等到后臺進行數據合并時，才會進入主數據中。數據合并是在后臺某個不確定的時間點進行的，當然也可以手動觸發數據合并。

? 這一系列表引擎當中，以MergeTree為核心，確定了整個數據讀寫合并的機制。而擴展出來的一系列*MergeTree則大都是在數據合并的過程中定制數據合并的邏輯。例如ReplacingMergeTree主要關注合并時去重。SummingMergeTree主要關注合并時進行簡單規則統計，生成統計報告。AggregatingMergeTree也是對合并邏輯進行定制。理解MergeTree的核心機制，這些擴展的表引擎就不難理解了。當然，具體的使用細節，還是參看官方文檔：https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/mergetree/

? 另外的幾種表引擎使用都比較簡單，這里就不再過多分享了。有興趣還是可以去參考一下官方文檔，用到的時候查一下即可。

3.3 數據使用

? clickhouse作為一個數據庫，支持標準的SQL操作。這一章只分享一些clickhouse比較與眾不同的用法。

3.3.1、數據導入導出

? 使用clickhouse首先需要有數據。我們之前也通過insert into語句造了一些測試數據，但是這種方式，在clickhouse中是非常不推薦的。一方面，insert語句插入數據，效率太低。clickhouse是面向海量數據進行查詢分析，insert語句很難用來形成海量的數據。另一方面，clickhouse最常用的MergeTree表引擎，會將新插入的數據放到一個臨時的分區當中，后續需要進行數據合并。頻繁的insert操作會產生大量的臨時分區，增加數據合并的性能消耗。所以，clickhouse中通常情況下都是通過數據文件進行大批量的導出導入操作來產生的。

? 最常用的數據導入導出方式是通過clickhouse-client客戶端寫入或讀取csv文件來完成。

? 例如導出數據到csv文件：

clickhouse-client -h 127.0.0.1 --database="defalut" --query="select * from t_stock FORMAT CSV" > t_stock.csv

? 從csv文件導入數據：

clickhouse-client -h 127.0.0.1 --database="default" --query="insert into t_stock FORMAT CSV" < ./test.csv

? 另外，官方也提供了一個clickhouse-copier工具來專門對clickhouse數據進行備份與恢復。

? 同時，官方也提供了大量高質量的數據集可供測試。因此我們需要將這些高質量的數據集導入到clickhouse中，這樣對于學習clickhouse是非常方便高效的。

官方數據集參見：https://clickhouse.com/docs/zh/getting-started/example-datasets/

? 這里面有些非常龐大的數據集，比如GitHub Events數據集，包含了31億行數據。數據包有75G，而clickhouse保存這些數據，需要硬盤空間超過200G。所以官方這些數據集對于測試，絕對是夠用的。

? 學習過程中，最常用的數據集還是線上測試數據庫中用到的數據，也就是Yandex.Metric Data數據集。數據集包含兩張表hits_v1和visits_v1。數據集可以從官方網站上下載。參見 https://clickhouse.com/docs/zh/getting-started/example-datasets/metrica/ 。

? 而這個官方文件的導入過程相當簡單粗暴，那就是直接轉移數據文件。

# 導入hits_v1表 tar -xvf hits_v1.tar -C /var/lib/clickhouse # 導入vits_v1表 tar -xvf visits_v1.tar -C /var/lib/clickhouse # 解壓出來的文件分配給clickhouse用戶 -- 可選 chown -R clickhouse:clickhouse /var/lib/clickhouse # 重啟clickhouse服務 clickhouse restart

? 重啟完成后，就可以在clickhouse中查到一個datasets數據以及hits_v1和visits_v1兩張表。hits_v1表使用的是MergeTree引擎，擁有800W+的數據。visits_v1表使用的是CollapsingMergeTree引擎，擁有160W+的數據。

? 實際上這個導入的過程也給我們演示了clickhouse底層數據的文件結構。clickhouse的底層文件結構相比其他數據庫，也是非常的簡單粗暴的。

元數據保存在metadata目錄下。
- datasets庫對應 metadata目錄下的 datasets.sql文件以及datasets目錄
- 表名對應metadata/datasets目錄下的.sql文件
數據保存在data目錄下。
- 表數據就對應data目錄下表名對應的文件夾中。
- 在表的數據目錄 data/datasts/hits_v1/201403-10-18_2目錄中，每個列對應一個.bin文件和.mrk文件。

這里需要關注下的是metadata目錄下的sql語句。例如查看hits_v1表的聲明文件：/var/lib/clickhouse/metadata/datasets/hits_v1.sql，看到他的內容如下:

ATTACH TABLE hits_v1 (WatchID UInt64, ... #省略字段名 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192

? 可以看到，就是通過一個簡單的Attach語句直接將表信息加載到數據庫中的。這個Attach指令并不在磁盤上實際產生數據，而是假設數據已經在正確的目錄下了。這樣通過執行Attach指令，clickhouse服務就將會識別對應表的存在。通過Attach語句，也可以加載默認目錄以外的目錄。通常是用在服務啟動時加載數據信息。

? 而與Attach語句對應的就是DETACH語句，是將表信息進行解綁。

3.3.2、數據修改

? 數據修改對應update和delete操作。clickhouse也提供了這兩個操作的能力，但是在clickhouse中，對數據的修改和刪除是非常"重"的操作，因為對應的目標數據需要放棄原有的分區，重建新的臨時分區，然后還要進行大量的合并。在語句執行過程中，只是將原有的數據打上邏輯上的刪除標記，然后新增數據放入新的分區。直到觸發分區合并的時候，才會刪除舊的數據。頻繁的update和delete操作會加大服務器的負擔。

? 在clickhouse中，數據變更操作被稱為Mutation查詢，他被作為alter指令的一部分，即對表進行變更。實際上，你會發現，在官方文檔上SQL部分都沒有列出UPDATE和DELETE語句。通常情況下，這類Mutation操作都要交由運維人員來完成。普通用戶更多的只需要關注數據的查詢與分析，盡量避免不必要的數據變更操作。

-- 刪除操作 alter table t_stock delete where sku_id='sku_001'; -- 修改操作 alter table t_stock update total_amount=toDecimal132(2000.00,2) where id=2;

3.3.3、數據查詢

? 查詢是clickhouse的重頭戲，clickhouse除了支持標準SQL外，還提供了非常豐富的查詢功能。

? 在標準SQL查詢這一塊：

支持子查詢
支持各種JOIN查詢。但是不建議使用。因為JOIN操作無法使用緩存。并且clickhouse執行join操作的方式是將后面的表全部加載到內存中執行，優化不是很好。表很大時性能影響非常明顯。
支持With關鍵字創建臨時表。例如使用下面的語句查詢當前數據庫中占用磁盤空間最大的10張表
WITH(SELECT sum(bytes)FROM system.partsWHERE active) AS total_disk_usage SELECT(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,table FROM system.parts GROUP BY table ORDER BY table_disk_usage DESC LIMIT 10

另外，clickhouse還提供了非常豐富的特性函數

表函數

表函數是clickhouse非常有特色的一類函數。顧名思義，就是可以像表一樣使用的函數。

例如numbers函數，可以生成一組連續的整數，在測試時非常有用。
select * from numbers(10); --產生一組0~10的整數 select * from numbers(10,20); --從10開始，產生20個整數 select toDate('2021-01-01') + number as d from numbers(365); -- 產生2021-01-01 ~ 2021-12-31的日期序列
generateRandom函數則可以產生一組隨機值。例如下面的SQL語句會產生包含a,b,c三列的一個表結果。每一列都會產生一個1到10之間的隨機值。
SELECT * FROM generateRandom('a Array(Int8), d Decimal32(4), c Tuple(DateTime64(3), UUID)', 1, 10, 2) LIMIT 3;
最后這個2是表示隨機種子。一個固定的隨機種子會產生穩定的隨機結果。

mysql函數允許對存儲在遠程MySQL服務器上的數據執行SELECT和INSERT查詢。
select * from mysql("hadoop01:3306",'testdb','user','root','root');
clickhouse中還有一系列的表函數，可以直接讀取遠程數據庫中的數據。比如file文件，hdfs文件，jdbc數據，postgresql，甚至直接訪問遠程服務器上的文件等。這樣非常方便于將遠程數據與本地數據結合使用。比如在clickhouse中存儲0或1這樣的字典值，而將字典的字面含義直接從mysql的字典表中讀取。另外，也可以很方便的將遠程數據中的數據插入到clickhouse本地，直接使用。連ETL過程都省了。
聚合函數

也就是group by 之后進行聚合。clickhouse提供了大量的聚合函數，除了count，min，max，sum等這些常見的聚合函數外，甚至還包括了corr皮爾遜相關系數，rankCorr斯皮爾曼相關系數，simpleLinearRegression一維線性回歸等機器學習中常用的聚合函數，功能相當的豐富。具體可以查看官方文檔。

rankCorr計算的是斯皮爾曼相關系數。是用來計算兩個數據列的相關性。這是一個-1到1之間的值。值為1，表示兩個數據列完全正相關，即第一個列中較大的值對應的第二個列中的值就越大。而-1表示兩個數據列負相關，即第一個列中較大的值對應第二個列中的值就越小。例如，如果一個公司在多年發展過程中，投入越多，收入也越多，也就是投入與收入的相關系數大，那就可以認為這個公司是一個非常好的公司。在統計學中，這是一個很重要的指標。

另外，在group by操作上，clickhouse還支持with rollup\with cube\with totals，來進行統計聚合。
-- 按gourp by 的順序，從右至左逐個去掉維度進行聚合。依次按照 (id,sku_id),(id),()分組，對total_amount進行求和 select id,sku_id,sum(total_amount) from t_stock group by id,sku_id with rollup; -- 按照goup by 的字段，互相組合進行聚合 select id,sku_id,sum(total_amount) from t_stock group by id,sku_id with cube; -- 只按照group by的全字段，以及所有數據一起聚合。只按照 (id,sku_id),()兩個分組進行求和 select id,sku_id,sum(total_amount) from t_stock group by id,sku_id with totals;
函數

clickhouse的聚合函數已經如此強大，針對普通字段的函數那就更不在話下了。常用的算數函數、比較函數、邏輯函數、類型轉換函數等這些就不用多說。file函數可以直接訪問操作系統的文件作為一個字段。例如
insert into table select file('a.txt') ,file('b.txt');
還有用于機器學習的線性回歸、邏輯回歸的預測函數。甚至還有NLP自然語言處理的函數。比如自動將英文中的復數轉換成為單數，將wolves轉換成為wolf，這樣的奇葩函數。當然，這個NLP函數目前還在實驗階段。

不過比較遺憾的是，clickhouse目前還不支持自定義函數。

總結

以上是生活随笔為你收集整理的最强OLAP分析引擎-Clickhouse快速精通-上的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：【论文精读】Local-Adaptive
下一篇：科蓝金融科技工具箱，为金融APP数字化运