天天看點
傳回

ClickHouseClickHouse

ClickHouse

這裡目錄标題

  • ClickHouse
    • 1、軟體介紹
      • 1.1. 簡介
      • 1.2. 特點
      • 1.3. 優點
      • 1.4. 缺點
    • 2、系統架構
      • 2.1. Column與Field
      • 2.2. 資料類型DataType
      • 2.3. 塊Block
      • 2.4. 塊流BlockStreams
      • 2.5. Formats格式
      • 2.6. 資料讀寫I/O
      • 2.7. 資料表Table
      • 2.8. 解析器Parser
      • 2.9. 解釋器Interpreter
      • 2.10. 函數Functions
        • 2.10.1. 普通函數Functions
        • 2.10.2. 聚合函數Aggregate Functions
      • 2.11. Cluster與Replication
    • 3、環境搭建
    • 4、資料定義
      • 4.1. 資料類型
        • 4.1.1. 基本資料類型
        • 4.1.2. 字元串
        • 4.1.3. 日期時間
        • 4.1.4. 複合類型
        • 4.1.5. 其他類型
      • 4.2. 資料庫操作
      • 4.3. 資料表
        • 4.3.1. 建立表
        • 4.3.2. 删除表
        • 4.3.3. 臨時表
        • 4.3.4. 分區表
        • 4.3.5. 資料表操作
      • 4.4. 視圖
      • 4.5. 資料的CRUD
        • 4.5.1. 資料的寫入
        • 4.5.2. 資料的删除和修改
    • 5、MergeTree
      • 5.1. 建立與存儲
        • 5.1.2. 存儲格式
      • 5.2. 資料分區
        • 5.2.1. 資料分區規則
        • 5.2.2. 分區目錄命名
        • 5.2.3. 分區目錄合并
      • 5.3. 一級索引
        • 5.3.1. 稀疏索引
        • 5.3.2. 索引粒度
        • 5.3.3. 索引規則
        • 5.3.4. 索引查詢過程
      • 5.4. 二級索引
        • 5.4.1. 粒度
        • 5.4.2. 分類
      • 5.5. 資料存儲
        • 5.5.1. 列式存儲
        • 5.5.2. 資料壓縮
      • 5.6. 資料标記
        • 5.6.1. 生成規則
        • 5.6.2. 工作方式
      • 5.7. 資料标記與資料壓縮
        • 5.7.1. 多對一
        • 5.7.2. 一對一
        • 5.7.3. 一對多
      • 5.8. 資料讀寫流程
        • 5.8.1. 寫入資料
        • 5.8.2. 查詢資料
      • 5.9. 資料TTL
      • 5.10. 多路徑存儲政策
    • 6、 MergeTree Family
      • 6.1. MergeTree
      • 6.2. ReplacingMergeTree
      • 6.3. SummingMergeTree
      • 6.4. AggregatingMergeTree
      • 6.5. CollapsingMergeTree
      • 6.6. VersionedCollapsingMergeTree
    • 7、常見類型表引擎
      • 7.1. 外部存儲
        • 7.1.1. HDFS
        • 7.1.2. Mysql
        • 7.1.3. JDBC
        • 7.1.4. Kafka
        • 7.1.5. File
      • 7.2. 記憶體類型
        • 7.2.1. Memory
        • 7.2.2. Set
        • 7.2.3. Join
      • 7.3. 日志類型
        • 7.3.1. TinyLog
        • 7.3.2. StripeLog
        • 7.3.3. Log
      • 7.4. 接口類型
        • 7.4.1. Merge
    • 8、 資料查詢方式
      • 8.1. With子句
      • 8.2. From子句
      • 8.3. Sample子句
      • 8.4. Array Join子句
      • 8.5. Join 子句
      • 8.6. WHERE與PREWHERE子句
      • 8.7. GROUP BY子句
        • 8.7.1. WITH ROLLUP
        • 8.7.2. WITH CUBE
        • 8.7.3. WITH TOTALS
      • 8.8. Having子句
      • 8.9. ORDER BY子句
      • 8.10. LIMIT BY子句
      • 8.11. LIMIT子句
    • 5、 副本與分片

1、軟體介紹

1.1. 簡介

  ClickHouse 是開源的一個極具 " 戰鬥力 " 的實時資料分析資料庫，開發語言為C++，是一個用于聯機分析 (OLAP:Online Analytical Processing) 的列式資料庫管理系統(DBMS:Database Management System)，簡稱 CK。

  ClickHouse 的性能超過了目前市場上可比的面向列的DBMS。 每秒鐘每台伺服器每秒處理數 億至十億多行和數十千兆位元組的資料。

目前國内社群火熱，各個大廠紛紛跟進大規模使用:

  • 今日頭條 内部用ClickHouse來做使用者行為分析;
  • 騰訊内部用ClickHouse做遊戲資料分析，并且為之建立了一整套監控運維體系。
  • 攜程内部從18年7月份開始接入試用，目前80%的業務都跑在ClickHouse上。
  • 快手内部也在使用ClickHouse，存儲總量大約10PB， 每天新增200TB， 90%查詢小于3S

1.2. 特點

  • 開源的列存儲資料庫管理系統，支援線性擴充，簡單友善，高可靠性，
  • 容錯跑分快：比Vertica快5倍，比Hive快279倍，比MySQL快800倍,其可處理的資料級别已達到10 億級别
    • https://clickhouse.tech/benchmark/dbms/
    • 功能多：支援資料統計分析各種場景，支援類SQL查詢，異地複制部署

1.3. 優點

  • 真正的面向列的DBMS（ClickHouse是一個DBMS,而不是一個單一的資料庫。它允許在運作時建立表和資料庫、加載資料和運作查詢，而無需重新配置和重新啟動伺服器）
  • 資料壓縮（一些面向列的DBMS（INFINIDB CE 和 MonetDB）不使用資料壓縮。但是，資料壓縮确實是提高了性能）
  • 磁盤存儲的資料（許多面向列的DBMS(SPA HANA和GooglePowerDrill)）隻能在記憶體中工作。但即使在數千台伺服器上，記憶體也太小了。）
  • 多核并行處理(多核多節點并行化大型查詢)
  • 在多個伺服器上分布式處理(在clickhouse中，資料可以駐留在不同的分片上。每個分片都可以用于容錯的一組副本，查詢會在所有分片上并行處理)
  • SQL支援(ClickHouse sql 跟真正的sql有不一樣的函數名稱。不過文法基本跟SQL文法相容，支援 JOIN/FROM/IN 和JOIN子句及标量子查詢支援子查詢)
  • 向量化引擎(資料不僅按列式存儲，而且由矢量-列的部分進行處理，這使得開發者能夠實作高CPU 性能)
  • 實時資料更新(ClickHouse支援主鍵表。為了快速執行對主鍵範圍的查詢，資料使用合并樹 (MergeTree)進行遞增排序。由于這個原因，資料可以不斷地添加到表中)
  • 支援近似計算(統計全國到底有多少人?143456754 14.3E)
  • 資料複制和對資料完整性的支援(ClickHouse使用異步多主複制。寫入任何可用的複本後，資料将分發到所有剩餘的副本。系統在不同的副本上保持相同的資料。資料在失敗後自動恢複)

1.4. 缺點

  • 沒有完整的事務支援，不支援Transaction想快就别Transaction
  • 缺少完整Update/Delete操作，缺少高頻率、低延遲的修改或删除已存在資料的能力，僅用于批量 删除或修改資料。
  • 聚合結果必須小于一台機器的記憶體大小
  • 支援有限作業系統，正在慢慢完善
  • 不适合Key-value存儲，不支援Blob等文檔型資料庫

2、系統架構

  • ClickHouse 是一個真正的列式資料庫管理系統（DBMS)。在 ClickHouse 中，資料始終是按列存儲的，包括矢量（向量或列塊）執行的過程。
ClickHouseClickHouse
ClickHouseClickHouse

2.1. Column與Field

  • Column

     和

    Field

     是

    ClickHouse

     資料最基礎的映射單元。
  • 記憶體中的一列資料由一個Column對象表示。
  • Field對象代表一個單值；操作單個具體的數值 ( 也就是單列中的一行資料 )。
  • Column對象分為接口和實作兩個部分，在IColumn接口對象中，定義了對資料進行各種關系運算的方法。
    • 幾乎所有的操作都是不可變的：這些操作不會更改原始列，但是會建立一個新的修改後的 列。
  • Field對象使用了聚合的設計模式。在Field對象内部聚合了 Null、UInt64、String和Array等13種資料類型及相應的處理邏輯。

2.2. 資料類型DataType

  • DataType 描述了列的資料類型；
  • DataType 僅存儲中繼資料。
  • 負責序列化和反序列化：讀寫二進制或文本形式的列或單個值構成的塊。
  • DataType雖然負責序列化相關工作，但它并不直接負責資料的讀取，而是轉由從Column或Field對 象擷取。

2.3. 塊Block

  • Block 是表示記憶體中表的子集（chunk）的容器，由三元組 （cloumn，datatype，列名）構成的集合；
  • 表操作的對象是Block；Block對象可以看作資料表的子集。
  • Block并沒有直接聚合Column和DataType對象，而是通過ColumnWithTypeAndName對象進行間接引用。

2.4. 塊流BlockStreams

  • 塊流用于處理資料，負責塊資料的讀取和寫出。
  • Block流操作有兩組頂層接口：
    • IBlockInputStream負責資料的讀取和關系運算，IBlockInputStream 具有read方法，其能夠在資料可用時擷取下一個塊。
    • IBlockOutputStream負責将資料輸出到下一環節。IBlockOutputStream 具有 write 方法，其能夠将塊寫到某處。

2.5. Formats格式

  • 資料格式同塊流一起實作。用于向用戶端展示資料的方式；
  • 如塊流 IBlockOutputStream 提供的 Pretty 格式，也有其它輸入輸出格式，比如 TabSeparated 或 JSONEachRow 。
  • 如行流： IRowInputStream 和 IRowOutputStream 。它們允許你按行 pull/push 資料，而不是按 塊。

2.6. 資料讀寫I/O

  • 有一個緩沖區負責資料的讀寫；
  • 面向位元組的輸入輸出，有 ReadBuffer 和 WriteBuffer 這兩個抽象類。

2.7. 資料表Table

  • 多個列的一個集合體。
  • 讀取資料的時候以表為機關進行操作，操作的時候以BlockStream操作Block；
  • 在資料表的底層設計中并沒有所謂的Table對象；
  • 表由 IStorage 接口表示。該接口的不同實作對應不同的表引擎。

2.8. 解析器Parser

  • 查詢由一個手寫遞歸下降解析器解析。
  • 對Sql語句進行解析為AST文法樹。

2.9. 解釋器Interpreter

  • 解釋AST文法樹。
  • 解釋器負責從 AST 建立查詢執行流水線。
    • 簡單的解釋器，如 InterpreterExistsQuery 和 InterpreterDropQuery
    • 複雜的解釋器，如 InterpreterSelectQuery 。
  • 查詢執行流水線由塊輸入或輸出流組成。

2.10. 函數Functions

  • 單行函數（Functions）
  • 組函數（Aggregate Functions）

2.10.1. 普通函數Functions

  • 普通函數不會改變行數 - 它們的執行看起來就像是獨立地處理每一行資料。
  • 無狀态的普通函數，
  • 是作用在以 Block 為機關的資料上，以實作向量查詢執行。

2.10.2. 聚合函數Aggregate Functions

  • 聚合函數是狀态函數。它們将傳入的值激活到某個狀态，并允許你從該狀态擷取結果。
  • 聚合函數是有狀态的
  • 聚合狀态可以被序列化和反序列化，以在分布式查詢執行期間通過網絡傳遞或者在記憶體不夠的時候 将其寫到硬碟。

2.11. Cluster與Replication

  • ClickHouse的叢集由分片 ( Shard ) 組成，而每個分片又通過副本 ( Replica ) 組成。
  • 這種分層的概念，在一些流行的分布式系統中十分普遍。
    • ClickHouse的1個節點隻能擁有1個分片，也就是說如果要實作1分片、1副本，則至少需要部 署2個服務節點。
    • 分片隻是一個邏輯概念，其實體承載還是由副本承擔的。

3、環境搭建

  • ClickHouse 21.6.8.62 環境搭建
  • https://blog.csdn.net/weixin_43660536/article/details/120090681.

4、資料定義

4.1. 資料類型

4.1.1. 基本資料類型

  • 整數Int8->1位元組、Int16->2位元組、Int32->4位元組 和 Int64->8位元組
  • 浮點數 Float32 和 Float64
  • 定點數 Decimal32、Decimal64 和Decimal128
  • 布爾 UInt8 限制值為0或1

4.1.2. 字元串

  • String、FixedString 和 UUID
    • String 不限制長度，相當于Varchar、Text、Clob 和 Blob 等字元類型
    • FixedString(N)相當于Char，長度固定，資料長度不夠時，添加空位元組（null）；長度過長傳回錯誤消息
    • UUID：32位，格式8-4-4-4-12，如果未被指派，則用0填充

4.1.3. 日期時間

  • Date、DateTime、DateTime64
    • Date: 2020-02-02 精确到天
    • DateTime: 2020-02-02 20:20:20 精确到秒
    • DateTime64: 2020-02-02 20:20:20.335 精确到亞秒，可以設定精度

4.1.4. 複合類型

  1. 數組
    • 建立資料：array(T)或[]，類型必須相同
  2. 元組
    • 由多個元素組成，允許不同類型
    • 建立資料：(T1, T2, …)，Tuple(T1, T2, …)
  3. 枚舉類型
    • ClickHouse提供了Enum8和Enum16兩種枚舉類型，它們除了取值範圍不同之外，别無二 緻。
    • 枚舉固定使用(String:Int)Key/Value鍵值對的形式定義資料，是以Enum8和Enum16分别會對 應(String:Int8)和(String:Int16)
    • 用(String:Int) Key/Value鍵值對的形式定義資料,鍵值對不能同時為空，不允許重複，key允許 為空字元串，需要看到對應的值進行轉換
  4. 嵌套類型
    • Nested（Name1 Type1，Name2 Type2，…）
    • 相當于表中嵌套一張表，插入時相當于一個多元數組的格式，一個字段對應一個數組。

4.1.5. 其他類型

  • Nullable（TypeName）
    • 隻能與基礎資料類型搭配使用，表示某個類型的值可以為NULL；Nullable(Int8)表示可以存儲 Int8類型的值，沒有值時存NULL
    • 注意：
      • 不能與複合類型資料一起使用
      • 不能作為索引字段
      • 盡量避免使用，字段被Nullable修飾後會額外生成[Column].null.bin 檔案儲存Null值， 增加開銷
  • Domain
    • Pv4 使用 UInt32 存儲。如 116.253.40.133
    • IPv6 使用 FixedString(16) 存儲。如 2a02:aa08:e000:3100::2

4.2. 資料庫操作

  • 資料庫起到了命名空間的作用，可以有效規避命名沖突的問題，也為後續的資料隔離提供了支撐。 任何一張資料表，都必須歸屬在某個資料庫之下。 
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
SHOW DATABASES
DROP DATABASE [IF EXISTS] db_name
  • 資料庫引擎
    1. Ordinary：預設引擎
      • 在絕大多數情況下我們都會使用預設引擎，使用時無須刻意聲明。在此資料庫下可以使 用任意類型的表引擎。
    2. Dictionary：字典引擎
      • 此類資料庫會自動為所有資料字典建立它們的資料表，關于資料字典的詳細介紹會在第5 章展開。
    3. Memory：記憶體引擎
      • 用于存放臨時資料。此類資料庫下的資料表隻會停留在記憶體中，不會涉及任何磁盤操 作，當服務重新開機後資料會被清除。
    4. Lazy：日志引擎
      • 此類資料庫下隻能使用Log系列的表引擎，關于Log表引擎的詳細介紹會在第8章展開。
    5. MySQL：MySQL引擎
      • 此類資料庫下會自動拉取遠端MySQL中的資料，并為它們建立MySQL表引擎的資料表，

4.3. 資料表

4.3.1. 建立表

  • ClickHouse目前提供了三種最基本的建表方法
  • 第一種是正常定義方法 
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
省略…
) ENGINE = engine
CREATE TABLE hits_v1 (
Title String,
URL String ,
EventTime DateTime
) ENGINE = Memory;
  • 使用[db_name.]參數可以為資料表指定資料庫，如果不指定此參數，則預設會使用default數 據庫。

第二種定義方法是複制其他表的結構

CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.]
table_name2 [ENGINE = engine]
--建立新的資料庫
CREATE DATABASE IF NOT EXISTS new_db
--将default.hits_v1的結構複制到new_db.hits_v1
CREATE TABLE IF NOT EXISTS new_db.hits_v1 AS default.hits_v1 ENGINE =
TinyLog
  • 支援在不同的資料庫之間複制表結構

第三種定義方法是通過SELECT子句的形式建立

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS
SELECT …
CREATE TABLE IF NOT EXISTS hits_v1_1 ENGINE = Memory AS SELECT * FROM
hits_v1
  • 根據SELECT子句建立相應的表結構，同時還會将SELECT子句查詢的資料順帶寫入

4.3.2. 删除表

  • ClickHouse和大多數資料庫一樣，使用DESC查詢可以傳回資料表的定義結構。
  • 如果想删除一張資料表，則可以使用下面的DROP語句： 
DROP TABLE [IF EXISTS] [db_name.]table_name

4.3.3. 臨時表

  • ClickHouse也有臨時表的概念，建立臨時表的方法是在普通表的基礎之上添加

    TEMPORARY

     關鍵字。
  • 特點
    • 生命周期是會話綁定的，是以它隻支援Memory表引擎，如果會話結束，資料表就會被銷毀；
    • 臨時表不屬于任何資料庫，是以在它的建表語句中，既沒有資料庫參數也沒有表引擎參數。
    • 臨時表的優先級是大于普通表的。當兩張資料表名稱相同的時候，會優先讀取臨時表的資料

4.3.4. 分區表

  • 資料分區（partition）和資料分片（shard）是完全不同的兩個概念。
  • 資料分區是資料的一種縱向切分。而資料分片是資料的一種橫向切分；

4.3.5. 資料表操作

  • 追加新字段 
    • ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT 'mac'ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID
  • 修改字段類型 
    • ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr]
ALTER TABLE testcol_v1 MODIFY COLUMN IP IPv4
  • 修改備注 
    • ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment'
ALTER TABLE testcol_v1 COMMENT COLUMN ID '主鍵ID'
DESC testcol_v1
  • 删除已有字段 
    • ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
ALTER TABLE testcol_v1 DROP COLUMN URL
  • 清空資料表 
    • TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name
TRUNCATE TABLE db_test.testcol_v2

4.4. 視圖

  • ClickHouse擁有普通和物化兩種視圖，其中物化視圖擁有獨立的存儲，而普通視圖隻是一層簡單的查詢代理。
  • 普通視圖
    • 普通視圖不會存儲任何資料，它隻是一層單純的SELECT查詢映射，起着簡化查詢、明晰語義 的作用，對查詢性能不會有任何增強。
  • 物化視圖 
    • CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name]
[ENGINE = engine] [POPULATE] AS SELECT .
    • 物化視圖支援表引擎，資料儲存形式由它的表引擎決定 ；
    • 物化視圖建立好之後，如果源表被寫入新資料，那麼物化視圖也會同步更新。
      • POPULATE修飾符決定了物化視圖的初始化政策：
      • 如果使用了POPULATE修飾符，那麼在建立視圖的過程中，會連帶将源表中已存在的資料一并導入，如同執行了SELECT INTO一般；
      • 反之，如果不使用POPULATE修飾符，那麼物化視圖在建立之後是沒有資料的，它隻會 同步在此之後被寫入源表的資料。
      • 物化視圖目前并不支援同步删除，如果在源表中删除了資料，物化視圖的資料仍會保 留。

4.5. 資料的CRUD

4.5.1. 資料的寫入

  • INSERT語句支援三種文法範式，三種範式各有不同，可以根據寫入的需求靈活運用。
  • 第一種是使用VALUES格式的正常文法：
  • 第二種是使用指定格式的文法：
  • 第三種是使用SELECT子句形式的文法

4.5.2. 資料的删除和修改

  • ClickHouse提供了DELETE和UPDATE的能力，這類操作被稱為Mutation查詢，它可以看作ALTER 語句的變種。
  • 雖然Mutation能最終實作修改和删除，但不能完全以通常意義上的UPDATE和DELETE來了解，我 們必須清醒地認識到它的不同：
    • 首先，Mutation語句是一種“很重”的操作，更适用于批量資料的修改和删除；
    • 其次，它不支援事務，一旦語句被送出執行，就會立刻對現有資料産生影響，無法復原；
    • 最後，Mutation語句的執行是一個異步的背景過程，語句被送出之後就會立即傳回。
  • DELETE語句的完整文法
  • UPDATE語句的完整文法

5、MergeTree

  • 表引擎是ClickHouse設計實作中的一大特色
  • ClickHouse擁有非常龐大的表引擎體系，其共擁有

    合并樹

     、

    外部存儲

     、

    記憶體

     、

    檔案

     、

    接口

     和其他6大類20多種表引擎。
  • 合并樹家族自身也擁有多種表引擎的變種。其中MergeTree作為家族中最基礎的表引擎；
  • 提供了

    主鍵索引

     、

    資料分區

     、

    資料副本

     和

    資料采樣

     等基本能力；

5.1. 建立與存儲

  • MergeTree在寫入一批資料時，資料總會以資料片段的形式寫入磁盤，且資料片段不可修改。
  • 為了避免片段過多，ClickHouse會通過背景線程，定期合并這些資料片段，屬于相同分區的資料片段會被合成一個新的片段。
  • 這種資料片段往複合并的特點，也正是合并樹名稱的由來。
  • 配置選項
    1. PARTITION BY [選填]：分區鍵，用于指定表資料以何種标準進行分區。
    2. ORDER BY [必填]：排序鍵，用于指定在一個資料片段内，資料以何種标準排序。
      • 預設情況下主鍵（PRIMARY KEY）與排序鍵相同。
    3. PRIMARY KEY [選填]：主鍵，顧名思義，聲明後會依照主鍵字段生成一級索引，用于加速表 查詢。
    4. SAMPLE BY [選填]：抽樣表達式，用于聲明資料以何種标準進行采樣。
    5. SETTINGS：
      • index_granularity [選填]：索引粒度 預設8192
      • index_granularity_bytes [選填]：自适應間隔大小（粒度），預設10M；
      • enable_mixed_granularity_parts [選填] 是否開啟自适應間隔，預設開啟；
      • merge_with_ttl_timeout [選填] TTL的功能；
      • storage_policy [選填]：提供了多路徑的存儲政策；

5.1.2. 存儲格式

  • MergeTree表引擎中的資料是擁有實體存儲的，資料會按照分區目錄的形式儲存到磁盤之上。
  • ClickHouseClickHouse
  • 一張資料表的完整實體結構分為3個層級，依次是資料表目錄、分區目錄及各分區下具體的資料檔案；
  • partition：分區目錄，餘下各類資料檔案（primary.idx、[Column].mrk、[Column].bin等）
    • 以分區目錄的形式被組織存放的，屬于相同分區的資料，最終會被合并到同一個分區目錄。
    • checksums.txt：校驗檔案，使用二進制格式存儲。它儲存了餘下各類檔案(primary.idx、 count.txt等)的size大小及size的哈希值，用于快速校驗檔案的完整性和正确性。
    • columns.txt：列資訊檔案，使用明文格式存儲。用于儲存此資料分區下的列字段資訊
    • count.txt：計數檔案，使用明文格式存儲。用于記錄目前資料分區目錄下資料的總行數，
    • primary.idx：一級索引檔案，使用二進制格式存儲。用于存放稀疏索引，一張MergeTree表 隻能聲明一次一級索引（通過ORDERBY或者PRIMARY KEY）。
    • [Column].bin：資料檔案，使用壓縮格式存儲，預設為LZ4壓縮格式，用于存儲某一列的資料。
    • [Column].mrk：列字段标記檔案，使用二進制格式存儲。标記檔案中儲存了.bin檔案中資料 的偏移量資訊。
    • [Column].mrk2：如果使用了自适應大小的索引間隔，則标記檔案會以.mrk2命名。它的工作 原理和作用與.mrk标記檔案相同。
    • partition.dat與minmax_[Column].idx：如果使用了分區鍵，例如PARTITION BY EventTime，則會額外生成partition.dat與minmax索引檔案，它們均使用二進制格式存儲。
    • skp_idx[Column].idx與skp_idx[Column].mrk：如果在建表語句中聲明了二級索引，則會額外 生成相應的二級索引與标記檔案，它們同樣也使用二進制存儲。二級索引在ClickHouse中又 稱跳數索引，

5.2. 資料分區

5.2.1. 資料分區規則

  • MergeTree資料分區的規則由分區ID決定，而具體到每個資料分區所對應的ID，則是由分區鍵的取值決定的。
  • 針對取值資料類型的不同，分區ID的生成邏輯目前擁有四種規則：
    • ClickHouseClickHouse

5.2.2. 分區目錄命名

  • 一個完整分區目錄的命名公式
  • ClickHouseClickHouse
    • 201905表示分區目錄的ID；
    • 1_1分别表示最小的資料塊編号與最大的資料塊編号；
    • 而最後的_0則表示目前合并的層級。
  • PartitionID_MinBlockNum_MaxBlockNum_Level

5.2.3. 分區目錄合并

  • MergeTree的分區目錄并不是在資料表被建立之後就存在的，而是在資料寫入過程中被建立的。
    • 也就是說如果一張資料表沒有任何資料，那麼也不會有任何分區目錄存在。
  • MergeTree伴随着每一批資料的寫入（一次INSERT語句），MergeTree都會生成一批新的分區目錄。
  • 即便不同批次寫入的資料屬于相同分區，也會生成不同的分區目錄。
    1. 寫入後的10～15分鐘，也可以手動執行optimize查詢語句）
    2. ClickHouse會通過背景任務再将屬于相同分區的多個目錄合并成一個新的目錄。
    3. 已經存在的舊分區目錄并不會立即被删除，而是在之後的某個時刻通過背景任務被删除（預設 8分鐘）。
  • 新目錄名稱的合并方式遵循規則：
    • MinBlockNum：取同一分區内所有目錄中最小的MinBlockNum值。
    • MaxBlockNum：取同一分區内所有目錄中最大的MaxBlockNum值。
    • Level：取同一分區内最大Level值并加1。

5.3. 一級索引

  • MergeTree的主鍵使用PRIMARY KEY定義，
  • 待主鍵定義之後，MergeTree會依據 index_granularity間隔（預設8192行），為資料表生成一級索引并儲存至primary.idx檔案内，索引資料按照PRIMARYKEY排序。

5.3.1. 稀疏索引

  • primary.idx檔案内的一級索引采用稀疏索引實作。
    • 稠密索引中每一行索引标記都會對應到一行具體的資料記錄。
    • 稀疏索引中每一行索引标記對應的是一段資料，而不是一行。
  • 由于稀疏索引占用空間小，是以primary.idx内的索引資料常駐記憶體，取用速度自然極快。

5.3.2. 索引粒度

  • 索引粒度就如同标尺一般，會丈量整個資料的長度，并依照刻度對資料進行标注，最終将資料标記成多個間隔的小段。
ClickHouseClickHouse

5.3.3. 索引規則

  • 由于是稀疏索引，是以MergeTree需要間隔index_granularity行資料才會生成一條索引記錄，其索 引值會依據聲明的主鍵字段擷取。
  • 單主鍵
    • ClickHouseClickHouse
    • 第0(81920)行CounterID取值57，第8192(81921)行CounterID取值1635，而第 16384(8192*2)行CounterID取值3266
    • 最終索引資料将會是5716353266。
  • 多主鍵
    • 聯合主鍵或多個字段列充當主鍵，多個ID放在一起。

5.3.4. 索引查詢過程

  • MarkRange
    • MarkRange在ClickHouse中是用于定義标記區間的對象。
    • 按照index_granularity的間隔粒度，将一段完整的資料劃分成了多個小的間隔資料段，一個具體的資料段即是一個MarkRange。
    • MarkRange與索引編号對應，使用start和end兩個屬性表示其區間範圍。
    • 通過與start及end對應的索引編号的取值，即能夠得到它所對應的數值區間。而數值區間表示 了此MarkRange包含的資料範圍。
  • 索引查詢其實就是兩個數值區間的交集判斷。
    • 一個區間是由基于主鍵的查詢條件轉換而來的條件區間；
    • 一個區間是剛才所講述的與MarkRange對應的數值區間。
  • 查詢步驟
    • ClickHouseClickHouse
    • 生成查詢條件區間：首先，将查詢條件轉換為條件區間。 
      WHERE ID = 'A003'
['A003', 'A003']
    • 遞歸交集判斷：以遞歸的形式，依次對MarkRange的數值區間與條件區間做交集判斷。從最 大的區間[A000,+inf)開始：
      • 如果不存在交集，則直接通過剪枝算法優化此整段MarkRange。
      • 如果存在交集，且MarkRange步長大于8(end-start)，則将此區間進一步拆分成8個子區 間,并重複此規則，繼續做遞歸交集判斷。
      • 如果存在交集，且MarkRange不可再分解（步長小于8），則記錄MarkRange并傳回。
    • 合并MarkRange區間：将最終比對的MarkRange聚在一起，合并它們的範圍。

5.4. 二級索引

  • 二級索引又稱跳數索引，由資料的聚合資訊建構而成。
  • 根據索引類型的不同，其聚合資訊的内容也不同。跳數索引的目的與一級索引一樣，也是幫助查詢時減少資料掃描的範圍。
  • 跳數索引在預設情況下是關閉的，需要設定allow_experimental_data_skipping_indices
    • SET allow_experimental_data_skipping_indices = 1
  • 跳數索引需要在CREATE語句内定義，它支援使用元組和表達式的形式聲明，其完整的定義文法
    • INDEX index_name expr TYPE index_type(…) GRANULARITY granularity

5.4.1. 粒度

  • 定義了一行跳數索引能夠跳過多少個index_granularity區間的資料(預設8192行)。
    • 按照index_granularity粒度間隔将資料劃分成n段，總共有[0,n-1]個區（n=total_rows/index_granularity，向上取整）
    • 當移動index_granularity次區間時，則彙總并生成一行跳數索引資料。
    • ClickHouseClickHouse

5.4.2. 分類

  • MergeTree共支援4種跳數索引，分别是minmax、set、ngrambf_v1和tokenbf_v1。一張資料表支援同時聲明多個跳數索引。 
  • CREATE TABLE skip_test (
	ID String,
	URL String,
	Code String,
	EventTime Date,
	INDEX a ID TYPE minmax GRANULARITY 5,
	INDEX b（length(ID) * 8） TYPE set(2) GRANULARITY 5,
	INDEX c（ID，Code） TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5,
	INDEX d ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5
) ENGINE = MergeTree()
  • minmax
    • minmax索引記錄了一段資料内的最小和最大極值，其索引的作用類似分區目錄的minmax索 引，能夠快速跳過無用的資料區間.
  • set
    • set索引直接記錄了聲明字段或表達式的取值（唯一值，無重複），其完整形式為 set(max_rows)，其中max_rows是一個阈 值，表示在一個index_granularity内，索引最多記錄的資料行數。
  • ngrambf_v1：
    • ngrambf_v1索引記錄的是資料短語的布隆表過濾器，隻支援String和FixedString資料類型。
    • ngrambf_v1隻能夠提升in、notIn、like、equals和notEquals查詢的性能.
  • tokenbf_v1：
    • tokenbf_v1索引是ngrambf_v1的變種，同樣也是一種布隆過濾器索引。
    • tokenbf_v1除了短語token的處理方法外，其他與ngrambf_v1是完全一樣的。
    • tokenbf_v1會自動按照非字元的、數字的字元串分割token。

5.5. 資料存儲

5.5.1. 列式存儲

  • 在MergeTree中，資料按列存儲。而具體到每個列字段，資料也是獨立存儲的，每個列字段都擁有 一個與之對應的.bin資料檔案。也正是這些.bin檔案，最終承載着資料的實體存儲。
  • 資料檔案以分區目錄的形式被組織存放，是以在.bin檔案中隻會儲存目前分區片段内的這一部分資料
  • 優勢：
    1. 一是可以更好地進行資料壓縮
    2. 二是能夠最小化資料掃描的範圍
  • 存儲方式
    1. 首先，資料是經過壓縮的，目前支援LZ4、ZSTD、Multiple和Delta幾種算法，預設使用LZ4算 法；
    2. 其次，資料會事先依照ORDER BY的聲明排序；
    3. 最後，資料是以壓縮資料塊的形式被組織并寫入.bin檔案中的。

5.5.2. 資料壓縮

  • 一個壓縮資料塊由頭資訊和壓縮資料兩部分組成。
    • 頭資訊固定使用9位位元組表示，具體由1個UInt8（1位元組）整型和2個UInt32（4位元組）整型組成 。
    • 分别代表使用的壓縮算法類型、壓縮後的資料大小和壓縮前的資料大小 。
    • bin壓縮檔案是由多個壓縮資料塊組成的，而每個壓縮資料塊的頭資訊則是基于CompressionMethod_CompressedSize_UncompressedSize公式生成的。
ClickHouseClickHouse
  • ​ 每個壓縮資料塊的體積，按照其壓縮前的資料位元組大小，都被嚴格控制在64KB～1MB。
    • 其上下限分别由min_compress_block_size（預設65536）與max_compress_block_size（默 認1048576）參數指定。
    • 一個壓縮資料塊最終的大小，則和一個間隔（index_granularity）内資料的實際大小相關。
  • 資料寫入過程
    • MergeTree在資料具體的寫入過程中，會依照索引粒度（預設情況下，每次取8192行），按批次擷取資料并進行處理
    • 單個批次資料size<64KB ：如果單個批次資料小于64KB，則繼續擷取下一批資料，直至累積到size>=64KB時，生成下一個壓縮資料塊。
    • 單個批次資料64KB<=size<=1MB ：如果單個批次資料大小恰好在64KB與1MB之間，則直接生成下一個壓縮資料塊。
    • 單個批次資料size>1MB ：如果單個批次資料直接超過1MB，則首先按照1MB大小截斷并生成 下一個壓縮資料塊。剩餘資料繼續依照上述規則執行。
ClickHouseClickHouse
  • 優勢：
    • 其一，雖然資料被壓縮後能夠有效減少資料大小，降低存儲空間并加速資料傳輸效率，但資料的壓縮和解壓動作，其本身也會帶來額外的性能損耗。是以需要控制被壓縮資料的大小，以求 在性能損耗和壓縮率之間尋求一種平衡。
    • 其二，在具體讀取某一列資料時（.bin檔案），首先需要将壓縮資料加載到記憶體并解壓，這樣才能進行後續的資料處理。通過壓縮資料塊，可以在不讀取整個.bin檔案的情況下将讀取粒度降低到壓縮資料塊級别，進而進一步縮小資料讀取的範圍。

5.6. 資料标記

5.6.1. 生成規則

  • 資料标記作為銜接一級索引和資料的橋梁，其像極了做過标記小抄的書簽，而且書本中每個一級章節都擁有各自的書簽。
ClickHouseClickHouse
  • 資料标記和索引區間是對齊的，均按照index_granularity的粒度間隔。
  • 為了能夠與資料銜接，資料标記檔案也與.bin檔案一一對應。
    • 每一個列字段[Column].bin檔案都有一個與之對應的[Column].mrk資料标記檔案，用于記錄 資料在.bin檔案中的偏移量資訊。
  • 一行标記資料使用一個元組表示，元組内包含兩個整型數值的偏移量資訊。
    • 它們分别表示在此段資料區間内，在對應的.bin壓縮檔案中，壓縮資料塊的起始偏移量；
    • 以及将該資料壓縮塊解壓後，其未壓縮資料的起始偏移量。
  • 每一行标記資料都表示了一個片段的資料（預設8192行）在.bin壓縮檔案中的讀取位置資訊。标記 資料與一級索引資料不同，它并不能常駐記憶體，而是使用LRU（最近最少使用）緩存政策加快其取用速度。
  • 标記檢視指令 
    • od -An -l name.mrk

5.6.2. 工作方式

  • MergeTree在讀取資料時，必須通過标記資料的位置資訊才能夠找到所需要的資料。整個查找過程大緻可以分為讀取壓縮資料塊和讀取資料兩個步驟
ClickHouseClickHouse
  • 資料了解
    • 1B*8192=8192B,64KB=65536B,65536/8192=8
    • 頭資訊固定由9個位元組組成，壓縮後大小為8個位元組
    • 12016=8+12000+8
  • 讀取壓縮資料塊：
    • 在查詢某一列資料時，MergeTree無須一次性加載整個.bin檔案，而是可以根據需要，隻加載特定的壓縮資料塊。而這項特性需要借助标記檔案中所儲存的壓縮檔案中的偏移量。
  • 讀取資料：
    • 在讀取解壓後的資料時，MergeTree并不需要一次性掃描整段解壓資料，它可以根據需要， 以index_granularity的粒度加載特定的一小段。為了實作這項特性，需要借助标記檔案中儲存的解壓資料塊中的偏移量。

5.7. 資料标記與資料壓縮

  • 由于壓縮資料塊的劃分，與一個間隔（index_granularity）内的資料大小相關，每個未壓縮資料塊的體積都被嚴格控制在64KB～1MB。
  • 而一個間隔（index_granularity）的資料，又隻會産生一行資料标記。
  • 那麼根據一個間隔内資料的實際位元組大小，資料标記和壓縮資料塊之間會産生三種不同的對應關系。

5.7.1. 多對一

  • 多個資料标記對應一個壓縮資料塊 當一個間隔（index_granularity）内的資料未壓縮大小size小于64KB時

5.7.2. 一對一

  • 一個資料标記對應一個壓縮資料塊 當一個間隔（index_granularity）内的資料未壓縮大小size大于等于64KB且小于等于1MB時

5.7.3. 一對多

  • 一個資料标記對應多個壓縮資料塊 當一個間隔（index_granularity）内的資料未壓縮大小size直接大于1MB時

5.8. 資料讀寫流程

5.8.1. 寫入資料

  • 資料寫入的第一步是生成分區目錄，伴随着每一批資料的寫入，都會生成一個新的分區目錄。在後 續的某一時刻，屬于相同分區的目錄會依照規則合并到一起；接着，按照index_granularity索引粒 度，會分别生成primary.idx一級索引、每一個列字段的.mrk資料标記和.bin壓縮資料檔案。
    • ClickHouseClickHouse
    • 從分區目錄201403_1_34_3能夠得知，該分區資料共分34批寫入，期間發生過3次合并。在數 據寫入的過程中，依據index_granularity的粒度，依次為每個區間的資料生成索引、标記和 壓縮資料塊。其中，索引和标記區間是對齊的，而标記與壓縮塊則根據區間資料大小的不同， 會生成多對一、一對一和一對多三種關系。

5.8.2. 查詢資料

  • 資料查詢的本質，可以看作一個不斷減小資料範圍的過程。在最理想的情況下，MergeTree首先可 以依次借助分區索引、一級索引和二級索引，将資料掃描範圍縮至最小。然後再借助資料标記，将 需要解壓與計算的資料範圍縮至最小。
    • ClickHouseClickHouse

5.9. 資料TTL

  • TTL即Time To Live，顧名思義，它表示資料的存活時間。在MergeTree中，可以為某個列字段或整張表設定TTL。
    • 當時間到達時，如果是列字段級别的TTL，則會删除這一列的資料；
    • 如果是表級别的TTL，則會删除整張表的資料；
    • 如果同時設定了列級别和表級别的TTL，則會以先到期的那個為主。
  • 設定TTL
    • INTERVAL完整的操作包括SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER 和YEAR。
    • ClickHouse沒有提供取消TTL的方法。
    • 列級别設定TTL 
      -- create_time是日期類型，列字段code與type均被設定了TTL,它們的存活時間是在
-- create_time的取值基礎之上向後延續10秒
CREATE TABLE ttl_table_v1(
	id String,
	create_time DateTime,
	code String TTL create_time + INTERVAL 10 SECOND,
	type UInt8 TTL create_time + INTERVAL 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id;

INSERT INTO TABLE ttl_table_v1 VALUES('A000',now(),'C0',1), ('A001',now() + INTERVAL 10 MINUTE,'C1',1);
SELECT * FROM ttl_table_v1
-- 執行optimize指令強制觸發TTL清理
-- 第一行資料滿足TTL過期條件（目前系統時間>=create_time+10秒），它們的code和
-- type列會被還原為資料類型的預設值：
optimize TABLE ttl_table_v1 FINAL
-- 修改列字段的TTL，或是為已有字段添加TTL，則可以使用ALTER語句
ALTER TABLE ttl_table_v1 MODIFY COLUMN code String TTL create_time + INTERVAL 1 DAY
    • 表級别設定TTL
    • TTL的運作機制
      • 如果一張MergeTree表被設定了TTL表達式，那麼在寫入資料時，會以資料分區為機關，在每 個分區目錄内生成一個名為ttl.txt的檔案。
      • ttl.txt檔案中通過一串JSON配置儲存了TTL的相關資訊
        • {“columns”:[{“name”:“code”,“min”:1557478860,“max”:1557651660}],“table”: {“min”:1557565200,“max”:1557738000}}
        • columns用于儲存列級别TTL資訊；
        • table用于儲存表級别TTL資訊；
        • min和max則儲存了目前資料分區内，TTL指定日期字段的最小值、最大值分别與 INTERVAL表達式計算後的時間戳。

5.10. 多路徑存儲政策

  • 19.15版本之前，MergeTree隻支援單路徑存儲，所有的資料都會被寫入config.xml配置中path指定的路徑下，即使伺服器挂載了多塊磁盤，也無法有效利用這些存儲空間。
  • 19.15版本開始，MergeTree實作了自定義存儲政策的功能，支援以資料分區為最小移動單元，将分區目錄寫入多塊磁盤目錄。
  • 存儲政策
    • 預設政策
      • MergeTree原本的存儲政策，無須任何配置，所有分區會自動儲存到config.xml配置中 path指定的路徑下.
    • JBOD政策
      • 這種政策适合伺服器挂載了多塊磁盤，但沒有做RAID的場景。
      • JBOD的全稱是Just a Bunch of Disks，它是一種輪詢政策，每執行一次INSERT或者 MERGE，所産生的新分區會輪詢寫入各個磁盤。
    • HOT/COLD政策
      • 這種政策适合伺服器挂載了不同類型磁盤的場景。
      • 将存儲磁盤分為HOT與COLD兩類區域。
        • HOT區域使用SSD這類高性能存儲媒介，注重存取性能；
        • COLD區域則使用HDD這類高容量存儲媒介，注重存取經濟性。
      • 資料在寫入MergeTree之初，首先會在HOT區域建立分區目錄用于儲存資料，當分區資料大小累積到門檻值時，資料會自行移動到COLD區域。
  • 配置方式
    • 存儲配置需要預先定義在config.xml配置檔案中，由storage_configuration标簽表示。
    • 在storage_configuration之下又分為disks和policies兩組标簽，分别表示磁盤與存儲政策。 
<storage_configuration>
	<disks>
		<disk_name_a> <!--自定義磁盤名稱 -->
			<path>/chbase/data</path><!—磁盤路徑 -->
			<keep_free_space_bytes>1073741824</keep_free_space_bytes>
		</disk_name_a>
		<disk_name_b>
			<path>… </path>
			<keep_free_space_bytes>...</keep_free_space_bytes>
		</disk_name_b>
	</disks>
	<policies>
		<default_jbod> <!--自定義政策名稱 -->
			<volumes>
				<jbod> <!—自定義名稱 磁盤組 -->
					<disk>disk_name_a</disk>
					<disk>disk_name_b</disk>
				</jbod>
			</volumes>
		</default_jbod>
	</policies>
</storage_configuration>

6、 MergeTree Family

6.1. MergeTree

  • MergeTree表引擎向下派生出6個變種表引擎。
  • ClickHouseClickHouse
ClickHouseClickHouse

6.2. ReplacingMergeTree

  • MergeTree擁有主鍵，但是它的主鍵卻沒有唯一鍵的限制。這意味着即便多行資料的主鍵相同，它 們還是能夠被正常寫入。
  • 能夠在合并分區時删除重複的資料。
  • ReplacingMergeTree是以分區為機關删除重複資料的。
  • ENGINE = ReplacingMergeTree(ver)
  • ver是選填參數，會指定一個UInt*、Date或者DateTime類型的字段作為版本号。

6.3. SummingMergeTree

  • 隻需要查詢資料的彙總結果，不關心明細資料，并且資料的彙總條件是預先明确的 。
  • SummingMergeTree就是為了應對這類聚合查詢場景而生的。
    • 隻能進行SUM計算；對于那些非彙總字段，則會使用第一行資料的取值。
    • 照預先定義的條件聚合彙總資料，将同一分組下的多行資料彙總合并成一行。
    • 這樣既減少了資料行，又降低了後續彙總查詢的開銷。
  • ENGINE = SummingMergeTree() PRIMARY KEY id

6.4. AggregatingMergeTree

  • 将需要聚合的資料,預先計算出來，并将結果儲存起來。
  • 在後續進行聚合查詢的時候，直接使用結果資料。 
  • -- GROUP BY id，city
-- UNIQ(code), SUM(value)
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
  • AggregatingMergeTree更為常見的應用方式是結合物化視圖使用，将它作為物化視圖的表引擎。

6.5. CollapsingMergeTree

  • 是一種通過以增代删的思路，支援行級資料修改和删除的表引擎。它通過定義一個sign标記位字段，記錄資料行的狀态。
  • 如果sign标記為1，則表示這是一行有效的資料；如果sign标記為-1，則表示這行資料需要被删除。
  • 分區合并時，同一資料分區内，sign标記為1和-1的一組資料會被抵消删除。
  • 這種1和-1互相抵消的操作，猶如将一張瓦楞紙折疊了一般。
  • ENGINE = CollapsingMergeTree(sign)
  • 隻有相同分區内的資料才有可能被折疊。
  • 折疊規則
    • 如果1比-1的資料多一行，則保留最後一行sign=1的資料。
    • 如果**-1比sign=1的資料多一行，則保留第一行sign=-1的資料**。
    • 如果1和-1的資料行一樣多，并且最後一行是sign=1，則保留第一行sign=-1和最後一行sign=1的資料。
    • 如果1和-1的資料行一樣多，并且最後一行是sign=-1，則什麼也不保留。
    • 其餘情況，ClickHouse會列印警告日志，但不會報錯，在這種情形下，查詢結果不可預知。
  • 特點
    • 折疊資料并不是實時觸發的，和所有其他的MergeTree變種表引擎一樣，這項特性也隻有在分區合并的時候才會展現。
    • 是以在分區合并之前，使用者還是會看到舊的資料。解決這個問題的方式有兩種。
      • 用optimize TABLE table_name FINAL指令強制分區合并。
  • CollapsingMergeTree對于寫入資料的順序有着嚴格要求。
    • 先寫入sign=1，再寫入sign=-1，則能夠正常折疊
    • 先寫入sign=-1，再寫入sign=1，則不能夠折疊

6.6. VersionedCollapsingMergeTree

  • 作用與CollapsingMergeTree完全相同.
  • VersionedCollapsingMergeTree對資料的寫入順序沒有要求，在同一個分 區内，任意順序的資料都能夠完成折疊操作。
  • ENGINE = VersionedCollapsingMergeTree(sign,ver)

7、常見類型表引擎

7.1. 外部存儲

  • 外部存儲表引擎直接從其他的存儲系統讀取資料 .
  • 例如直接讀取HDFS的檔案或者MySQL資料庫的表。
  • 這些表引擎隻負責中繼資料管理和資料查詢，而它們自身通常并不負責資料的寫入，資料檔案直接由外部系統提供。

7.1.1. HDFS

7.1.2. Mysql

  • MySQL表引擎可以與MySQL資料庫中的資料表建立映射，并通過SQL向其發起遠端查詢，包括 SELECT和INSERT
    • host:port表示MySQL的位址和端口。
    • database表示資料庫的名稱。
    • table表示需要映射的表名稱。
    • user表示MySQL的使用者名。
    • password表示MySQL的密碼。
    • replace_query預設為0，對應MySQL的REPLACE INTO文法。如果将它設定為1，則會用 REPLACEINTO代替INSERT INTO。
    • on_duplicate_clause預設為0，對應MySQL的ON DUPLICATE KEY文法。如果需要使用該設 置，則必須将replace_query設定成0。 
CREATE TABLE mysql_dept(
	deptno UInt32,
	dname String,
	loc String
)ENGINE = MySQL('192.168.88.101:3306', 'scott', 'dept', 'root','123456');
SELECT * FROM mysql_dept INSERT INTO TABLE mysql_dept VALUES (50,'幹飯部','207')
-- 目前MySQL表引擎不支援任何UPDATE和DELETE操作

7.1.3. JDBC

  • JDBC表引擎不僅可以對接MySQL資料庫，還能夠與PostgreSQL、SQLite和H2資料庫對接。
  • JDBC表引擎無法單獨完成所有的工作，它需要依賴名為clickhouse-jdbc-bridge的查詢代理服務。
  • clickhouse-jdbc-bridge是一款基于Java語言實作的SQL代理服務，它的項目位址為https://github. com/ClickHouse/clickhouse-jdbc-bridge 。
  • clickhouse-jdbc-bridge可以為ClickHouse代理通路其他的資料庫，并自動轉換資料類型。

7.1.4. Kafka

  • 目前ClickHouse還不支援恰好一次（Exactly once）的語義，因為這需要應用端與Kafka深度配合才能實作。 
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_skip_broken_messages = N]
[kafka_commit_every_batch = N]
  • 必填參數：
    • kafka_broker_list：表示Broker服務的位址清單，多個位址之間使用逗号分隔。
    • kafka_topic_list：表示訂閱消息主題的名稱清單，多個主題之間使用逗号分隔。
    • kafka_group_name：表示消費組的名稱，表引擎會依據此名稱建立Kafka的消費組。
    • kafka_format：表示用于解析消息的資料格式，在消息的發送端，必須按照此格式發送 消息。資料格式必須是ClickHouse提供的格式之一
      • 例如TSV、JSONEachRow和CSV等。
  • 再次執行SELECT查詢會發現kafka_test資料表空空如也,這是因為Kafka表引擎在執行查詢之後就會删除表内的資料。

7.1.5. File

  • File表引擎能夠直接讀取本地檔案的資料，通常被作為一種擴充手段來使用。
  • File表引擎的定義參數中，并沒有包含檔案路徑這一項。是以，File表引擎的資料檔案隻能儲存在 config.xml配置中由path指定的路徑下。
  • 每張File資料表均由目錄和檔案組成，其中目錄以表的名稱命名，而資料檔案則固定以data.format 命名。

7.2. 記憶體類型

  • 将資料全量放在記憶體中，對于表引擎來說是一把雙刃劍：
    • 一方面，這意味着擁有較好的查詢性能；
    • 另一方面，如果表内裝載的資料量過大，可能會帶來極大的記憶體消耗和負擔。

7.2.1. Memory

  • Memory表引擎直接将資料儲存在記憶體中，資料既不會被壓縮也不會被格式轉換，資料在記憶體中儲存的形态與查詢時看到的如出一轍。
  • 當ClickHouse服務重新開機的時候，Memory表内的資料會全部丢失。
  • 當資料被寫入之後，磁盤上不會建立任何資料檔案。 操作方式。

7.2.2. Set

  • Set表引擎是擁有實體存儲的，資料首先會被寫至記憶體，然後被同步到磁盤檔案中。
  • 是以當服務重新開機時，它的資料不會丢失，當資料表被重新裝載時，檔案資料會再次被全量加載至記憶體。
  • Set表引擎具有去重的能力，在資料寫入的過程中，重複的資料會被自動忽略。
  • Set表引擎的存儲結構由兩部分組成，它們分别是：
    • [num].bin資料檔案：儲存了所有列字段的資料。其中，num是一個自增id，從1開始。伴随 着每一批資料的寫入（每一次INSERT），都會生成一個新的.bin檔案，num也會随之加1。
    • tmp臨時目錄：資料檔案首先會被寫到這個目錄，當一批資料寫入完畢之後，資料檔案會被移出此目錄。 
CREATE TABLE set_1 (
	id UInt8
)ENGINE = Set()
INSERT INTO TABLE set_1 SELECT number FROM numbers(10)
SELECT arrayJoin([1, 2, 3]) AS a WHERE a IN set_1

7.2.3. Join

  • Join表引擎可以說是為JOIN查詢而生的，它等同于将JOIN查詢進行了一層簡單封裝。在Join表引擎的底層實作中，它與Set表引擎共用了大部分的處理邏輯，是以Join和Set表引擎擁有許多相似之處。
  • join_strictness：連接配接精度，它決定了JOIN查詢在連接配接資料時所使用的政策，目前支援ALL、 ANY和ASOF三種類型。
  • join_type：連接配接類型，它決定了JOIN查詢組合左右兩個資料集合的政策，它們所形成的結果 是交集、并集、笛卡兒積或其他形式，目前支援INNER、OUTER和CROSS三種類型。當 join_type被設定為ANY時，在資料寫入時，join_key重複的資料會被自動忽略。
  • join_key：連接配接鍵，它決定了使用哪個列字段進行關聯。

7.3. 日志類型

7.3.1. TinyLog

  • TinyLog是日志家族系列中性能最低的表引擎，它的存儲結構由資料檔案和中繼資料兩部分組成。
  • 資料檔案是按列獨立存儲的，也就是說每一個列字段都擁有一個與之對應的.bin檔案。
  • TinyLog既不支援分區，也沒有.mrk标記檔案 。
  • 由于沒有标記檔案，它自然無法支援.bin檔案的并行讀取操作，是以它隻适合在非常簡單的場景下使用。

7.3.2. StripeLog

  • StripeLog表引擎的存儲結構由固定的3個檔案組成，它們分别是：
    • data.bin：資料檔案，所有的列字段使用同一個檔案儲存，它們的資料都會被寫入data.bin。
    • index.mrk：資料标記，儲存了資料在data.bin檔案中的位置資訊。利用資料标記能夠使用多 個線程，以并行的方式讀取data.bin内的壓縮資料塊，進而提升資料查詢的性能。
    • sizes.json：中繼資料檔案，記錄了data.bin和index.mrk大小的資訊。

7.3.3. Log

  • Log表引擎結合了TinyLog表引擎和StripeLog表引擎的長處，是日志家族系列中性能最高的表引擎。
  • Log表引擎的存儲結構由3個部分組成：
    • [column].bin：資料檔案，資料檔案按列獨立存儲，每一個列字段都擁有一個與之對應的.bin 檔案。
    • marks.mrk：資料标記，統一儲存了資料在各個[column].bin檔案中的位置資訊。利用資料标 記能夠使用多個線程，以并行的方式讀取.bin内的壓縮資料塊，進而提升資料查詢的性能。
    • sizes.json：中繼資料檔案，記錄了[column].bin和__marks.mrk大小的資訊。 
CREATE TABLE log_1 (
	id UInt64,
	code UInt64
)ENGINE = Log()
INSERT INTO TABLE log_1 SELECT number,number+1 FROM numbers(200)

7.4. 接口類型

7.4.1. Merge

  • 在資料倉庫的設計中，資料按年分表存儲，例如test_table_2018、test_table_2019和 test_table_2020。假如現在需要跨年度查詢這些資料 ?
  • Merge表引擎就如同一層使用了門面模式的代理，它本身不存儲任何資料，也不支援資料寫入。
    • 它的作用就如其名，即負責合并多個查詢的結果集。
    • Merge表引擎可以代理查詢任意數量的資料表，這些查詢會異步且并行執行，并最終合成一個結果集傳回。
  • 被代理查詢的資料表被要求處于同一個資料庫内，且擁有相同的表結構，但是它們可以使用不同的表引擎以及不同的分區定義

8、 資料查詢方式

  • ClickHouse對于SQL語句的解析是大小寫敏感的，這意味着SELECT a和SELECT A表示的語義是不 相同的。 
[WITH expr |(subquery)]
SELECT [DISTINCT] expr
[FROM [db.]table | (subquery) | table_function] [FINAL]
[SAMPLE expr]
[[LEFT] ARRAY JOIN]
[GLOBAL] [ALL|ANY|ASOF] [INNER | CROSS | [LEFT|RIGHT|FULL [OUTER]] ]
JOIN (subquery)|table ON|USING columns_list
[PREWHERE expr]
[WHERE expr]
[GROUP BY expr] [WITH ROLLUP|CUBE|TOTALS]
[HAVING expr]
[ORDER BY expr]
[LIMIT [n[,m]]
[UNION ALL]
[INTO OUTFILE filename]
[FORMAT format]
[LIMIT [offset] n BY columns]
  • 方括号包裹的查詢子句表示其為可選項，是以隻有SELECT子句是必須的，
  • ClickHouse對于查詢文法的解析也大緻是按照上面各個子句排列的順序進行的。

8.1. With子句

  • 支援CTE（Common Table Expression，公共表表達式），以增強查詢語句的表達。
  • 在改用CTE的形式後，可以極大地提高語句的可讀性和可維護性。
  • 定義變量
    • 這些變量能夠在後續的查詢子句中被直接通路。 
    • WITH 10 AS start
SELECT number FROM system.numbers
WHERE number > start
LIMIT 5
  • 調用函數
    • 通路SELECT子句中的列字段，并調用函數做進一步的加工處理。 
    • WITH SUM(data_uncompressed_bytes) AS bytes
SELECT database , formatReadableSize(bytes) AS format FROM
system.columns
GROUP BY database
ORDER BY bytes DESC
  • 定義子查詢 
    • -- 子查詢
WITH (
	SELECT SUM(data_uncompressed_bytes) FROM system.columns
) AS total_bytes

SELECT database , (SUM(data_uncompressed_bytes) / total_bytes) * 100
AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC
  • 子查詢中重複（嵌套）使用WITH
    • 嵌套使用WITH子句 
    • WITH (
	round(database_disk_usage)
) AS database_disk_usage_v1

SELECT database,database_disk_usage, database_disk_usage_v1
FROM (
-- 嵌套
	WITH (
		SELECT SUM(data_uncompressed_bytes) FROM system.columns
	) AS total_bytes
	SELECT database , (SUM(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage FROM system.colum
GROUP BY database
ORDER BY database_disk_usage DESC
)

8.2. From子句

  • FROM子句表示從何處讀取資料，目前支援如下3種形式
    • 從資料表中取數

      • SELECT WatchID FROM hits_v1
    • 從子查詢中取數

      • SELECT MAX_WatchID FROM (SELECT MAX(WatchID) AS MAX_WatchID FROM hits_v1)
    • 從表函數中取數

      • SELECT number FROM numbers(5)
  • 在ClickHouse中，并沒有資料庫中常見的DUAL虛拟表，取而代之的是system.one。 
  • SELECT 1
SELECT 1 FROM system.one
  • 在FROM子句後，可以使用Final修飾符。
    • 它可以配合CollapsingMergeTree和Versioned-CollapsingMergeTree等表引擎進行查詢操作，以強制在查詢過程中合并
    • 但由于Final修飾符會降低查詢性能，是以應該盡可能避免使用它。

8.3. Sample子句

  • 能夠實作資料抽樣的功能，使查詢僅傳回采樣資料而不是全部資料，進而有效減少查詢負載。
  • SAMPLE子句隻能用于MergeTree系列引擎的資料表，并且要求在CREATE TABLE時聲明SAMPLE BY抽樣表達式 
-- Sample Key聲明的表達式必須也包含在主鍵的聲明中
-- Sample Key必須是Int類型，如若不是，ClickHouse在進行CREATE TABLE操作時也不會報
錯
CREATE TABLE hits_v1 (
	CounterID UInt64,
	EventDate DATE,
	UserID UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, intHash32(UserID))
SAMPLE BY intHash32(UserID)
  • SAMPLE子句目前支援如下3種用法
    1. SAMPLE factor
      • SAMPLE factor表示按因子系數采樣，其中factor表示采樣因子，它的取值支援0～1之間 的小數。
      • 如果factor設定為0或者1，則效果等同于不進行資料采樣。 
      • SELECT count() * 10 FROM hits_v1 SAMPLE 0.1
SELECT CounterID, _sample_factor FROM hits_v1 SAMPLE 0.1 LIMIT 2
    2. SAMPLE rows
      • SAMPLE rows表示按樣本數量采樣，其中rows表示至少采樣多少行資料，它的取值必須 是大于1的整數。
      • 如果rows的取值大于表内資料的總行數，則效果等于rows=1 
      • SELECT count() FROM hits_v1 SAMPLE 10000
SELECT CounterID,_sample_factor FROM hits_v1 SAMPLE 100000 LIMIT 1
    3. SAMPLE factor OFFSET n
      • SAMPLE factor OFFSET n表示按因子系數和偏移量采樣，其中factor表示采樣因子，n 表示偏移多少資料後才開始采樣，它們兩個的取值都是0～1之間的小數。 
      • -- 最終的查詢會從資料的0.5處開始，按0.4的系數采樣資料
SELECT CounterID FROM hits_v1 SAMPLE 0.4 OFFSET 0.5
-- 最終的查詢會從資料的二分之一處開始，按0.1的系數采樣資料
SELECT CounterID,_sample_factor FROM hits_v1 SAMPLE 1/10 OFFSET 1/2
      • 如果在計算OFFSET偏移量後，按照SAMPLE比例采樣出現了溢出，則資料會被自動截斷

8.4. Array Join子句

  • ARRAY JOIN子句允許在資料表的内部，與數組或嵌套類型的字段進行JOIN操作，進而将一行數組展開為多行。接下來讓我們看看它的基礎用法。
  • 一條SELECT語句中，隻能存在一個ARRAY JOIN（使用子查詢除外）。目前支援INNER和LEFT兩種
    • INNER ARRAY JOIN 預設
    • LEFT ARRAY JOIN 左連接配接 
CREATE TABLE query_v1(
	title String,
	value Array(Int8)
) ENGINE = Log
INSERT INTO query_v1 VALUES ('food', [1,2,3]), ('fruit', [3,4]), ('meat', []);
SELECT title,value FROM query_v1;

8.5. Join 子句

  • JOIN子句可以對左右兩張表的資料進行連接配接
  • JOIN的文法包含連接配接精度和連接配接類型兩部分
  • JOIN查詢還可以根據其執行政策被劃分為本地查詢和遠端查詢。
ClickHouseClickHouse
  • 目前支援ALL、ANY和ASOF三種類型。如果不主動聲明，則預設是ALL。
    • all 如果左表内的一行資料，在右表中有多行資料與之連接配接比對，則傳回右表中全部連接配接的資料。
    • any 如果左表内的一行資料，則僅傳回右表中第一行連接配接的資料。
    • asof ASOF是一種模糊連接配接，它允許在連接配接鍵之後追加定義一個模糊連接配接的比對條件asof_column；僅傳回了右表中第一行連接配接比對的資料。
  • 連接配接類型
    • INNER JOIN表示内連接配接，在查詢時會以左表為基礎逐行周遊資料，
    • OUTER JOIN表示外連接配接，它可以進一步細分為左外連接配接（LEFT）、右外連接配接（RIGHT）和全外連接配接（FULL）三種形式。
    • CROSS JOIN表示交叉連接配接，它會傳回左表與右表兩個資料集合的笛卡兒積。
  • 查詢優化
    • 為了能夠優化JOIN查詢性能，首先應該遵循左大右小的原則 ，無論使用的是哪種連接配接方式，右表都會被全部加載到記憶體中與左表進行比較。
    • JOIN查詢目前沒有緩存的支援
    • 如果是在大量次元屬性補全的查詢場景中，則建議使用字典代替JOIN查詢
    • 連接配接查詢的空值是由預設值填充的，這與其他資料庫所采取的政策不同（由Null填充）。

8.6. WHERE與PREWHERE子句

  • WHERE子句基于條件表達式來實作資料過濾。如果過濾條件恰好是主鍵字段，則能夠進一步借助索引加速查詢；
  • PREWHERE目前隻能用于MergeTree系列的表引擎，它可以看作對WHERE的一種優化，其作用與WHERE相同，均是用來過濾資料。

  • ClickHouse實作了自動優化的功能，會在條件合适的情況下将WHERE替換為PREWHERE。

    如果想開啟這項特性，需要将optimize_move_to_prewhere設定為1

8.7. GROUP BY子句

  • GROUP BY又稱聚合查詢

  • 聚合查詢目前還能配合WITH ROLLUP、WITHCUBE和WITH TOTALS三種修飾符擷取額外的彙總信

    息。

8.7.1. WITH ROLLUP

  • 按照聚合鍵從右向左上卷資料，基于聚合函數依次生成分組小計和總計。
  • 設聚合鍵的個數為n，則最終會生成小計的個數為n+1。

8.7.2. WITH CUBE

  • CUBE會像立方體模型一樣，基于聚合鍵之間所有的組合生成小計資訊。如果設聚合鍵的個數為n，則最終小計組合的個數為2的n次方。

8.7.3. WITH TOTALS

  • 使用TOTALS修飾符後，會基于聚合函數對所有資料進行總計

8.8. Having子句

  • HAVING子句需要與GROUP BY同時出現，不能單獨使用。它能夠在聚合計算之後實作二次過濾資料。

8.9. ORDER BY子句

  • ORDER BY子句通過聲明排序鍵來指定查詢資料傳回時的順序。
  • ORDER BY在使用時可以定義多個排序鍵，每個排序鍵後需緊跟ASC（升序）或DESC（降序）來确定排列順序。如若不寫，則預設為ASC（升序）。
  • 空值處理

    • NULLS LAST

      ​ 資料的排列順序為其他值（value）→NaN→NULL。

    • NULLS FIRST

      ​ 資料的排列順序為NULL→NaN→其他值（value）

      ​ ORDER BY v1 DESC NULLS FIRST

8.10. LIMIT BY子句

  • LIMIT BY子句和大家常見的LIMIT所有不同，它運作于ORDER BY之後和LIMIT之前，能夠按照指定分組，最多傳回前n行資料（如果資料少于n行，則按實際數量傳回）
  • 常用于TOP N的查詢場景。LIMIT BY的正常文法如下：

    LIMIT n BY express

8.11. LIMIT子句

  • LIMIT子句用于傳回指定的前n行資料，常用于分頁場景
  • LIMIT n

    SELECT number FROM system.numbers LIMIT 10
  • LIMIT n OFFSET m

    SELECT number FROM system.numbers LIMIT 10 OFFSET 5
  • LIMIT m，n

    SELECT number FROM system.numbers LIMIT 5 ,10

5、 副本與分片

ClickHouseClickHouse
大資料 資料庫 big data ClickHouse dbms olap
上一篇: ClickHouse環境搭建
下一篇: 如何将MongoDB與Node.js結合使用

繼續閱讀