表引擎

表引擎即表的類型，在雲資料庫ClickHouse中決定了如何儲存和讀取資料、是否支援索引、是否支援主備複製等。雲資料庫ClickHouse支援的表引擎，請參見下表。

本文以主鍵為例進行介紹。雲資料庫ClickHouse的SQL文法中主鍵用於去重，保持資料唯一，而在MergeTree表引擎中，其主要作用是加速查詢，即便在Compaction完成後，主鍵相同的資料行也仍舊共同存在。

樣本如下。

1. 建立表test_tbl，主鍵為id和create_time，並且按照主鍵進行儲存排序，按照create_time進行資料分區。

   CREATE TABLE test_tbl ON CLUSTER default (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = MergeTree()
      PARTITION BY create_time
        ORDER BY  (id, create_time)
        PRIMARY KEY (id, create_time)
        SETTINGS index_granularity=8192;

2. 寫入主鍵重複的資料。

   insert into test_tbl values(1, '2019-12-13', null);
   insert into test_tbl values(1, '2019-12-13', null);
   insert into test_tbl values(2, '2019-12-14', null);
   insert into test_tbl values(3, '2019-12-15', null);
   insert into test_tbl values(3, '2019-12-15', null);

3. 查詢資料。

   select * from test_tbl;

   查詢結果如下。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

4. 由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。

   optimize table test_tbl final;

5. 重新查詢資料。

   select * from test_tbl;

   查詢結果如下，主鍵重複的資料仍存在。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘

雖然ReplacingMergeTree表引擎提供了主鍵去重的能力，但是仍然存在很多限制，因此ReplacingMergeTree表引擎更多被用於確保資料最終被去重，而無法保證查詢過程中主鍵不重複，主要限制如下。

• 在分布式情境下，相同主鍵的資料可能被分布到不同節點上，不同分區間可能無法去重。

• 在沒有徹底optimize之前，可能無法達到主鍵去重的效果，比如部分資料已經被去重，而另外一部分資料仍舊有主鍵重複。

• optimize是後台動作，無法預測具體執行時間點。

• 手動執行optimize在海量資料情境下需要消耗大量時間，無法滿足業務即時查詢的需求。

樣本如下。

1. 建立表test_tbl_replacing。

   CREATE TABLE test_tbl_replacing (
     id UInt16,
     create_time Date,
     comment Nullable(String)
   ) ENGINE = ReplacingMergeTree()
      PARTITION BY create_time
        ORDER BY  (id, create_time)
        PRIMARY KEY (id, create_time)
        SETTINGS index_granularity=8192;

2. 寫入主鍵重複的資料。

   insert into test_tbl_replacing values(1, '2019-12-13', null);
   insert into test_tbl_replacing values(1, '2019-12-13', null);
   insert into test_tbl_replacing values(2, '2019-12-14', null);
   insert into test_tbl_replacing values(3, '2019-12-15', null);
   insert into test_tbl_replacing values(3, '2019-12-15', null);

3. 查詢資料。

   select * from test_tbl_replacing;

   查詢結果如下。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘ 

4. 由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。

   optimize table test_tbl_replacing final;

5. 重新查詢資料。

   select * from test_tbl_replacing;

   查詢結果如下，主鍵重複的資料已消除。

   ┌─id─┬─create_time─┬─comment──┐
   │  1 │ 2019-12-13  │   NULL   │
   │  2 │ 2019-12-14  │   NULL   │
   │  3 │ 2019-12-15  │   NULL   │
   └────┴─────────────┴──────────┘     

CollapsingMergeTree表引擎用於消除ReplacingMergeTree表引擎的功能限制。該表引擎要求在建表語句中指定一個標記列Sign，按照Sign的值將行分為兩類：Sign=1的行稱為狀態行，用於新增狀態。Sign=-1的行稱為取消行，用於刪除狀態。

後台Compaction時會將主鍵相同、Sign相反的行進行摺疊（刪除），而尚未進行Compaction的資料，狀態行與取消行同時存在。因此為了能夠達到主鍵摺疊（刪除）的目的，需要業務層進行如下操作。

• 記錄原始狀態行的值，或者在執行刪除狀態操作前先查詢資料庫擷取原始狀態行的值。

  具體原因：執行刪除狀態操作時需要寫入取消行，而取消行中需要包含與原始狀態行主鍵一樣的資料（Sign列除外）。

• 在進行count()、sum(col)等彙總計算時，可能會存在資料冗餘的情況。為了獲得正確結果，業務層需要改寫SQL，將count()、sum(col)分別改寫為sum(Sign)、sum(col * Sign)。

  具體原因如下。

  • 後台Compaction時機無法預測，在發起查詢時，狀態行和取消行可能尚未進行摺疊（刪除）。

  • 雲資料庫ClickHouse無法保證主鍵相同的行落在同一個節點上，不在同一節點上的資料無法進行摺疊（刪除）。

樣本如下。

1. 建立表test_tbl_collapsing。

   CREATE TABLE test_tbl_collapsing
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8
   )
   ENGINE = CollapsingMergeTree(Sign)
   ORDER BY UserID;

2. 插入狀態行Sign=1。

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);

   說明

   如果先插入取消行，再插入狀態行，可能會導致位置亂序，即使強制後台Compaction，也無法進行主鍵摺疊（刪除）。

3. 插入取消行Sign=-1，除Sign列外其他值與插入的狀態行一致。同時，插入一行相同主鍵資料的新狀態行。

   INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);

4. 查詢資料。

   SELECT * FROM test_tbl_collapsing;

   查詢結果如下。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐ 
   │ 4324182021466249494 │    5      │    146   │   1   │ 
   │ 4324182021466249494 │    5      │    146   │  -1   │ 
   │ 4324182021466249494 │    6      │    185   │   1   │ 
   └─────────────────────┴───────────┴──────────┴───────┘

5. 如果您需要對指定列進行彙總計算，以sum(col)為例，為了獲得正確結果，需改寫SQL語句如下。

   SELECT
       UserID,
       sum(PageViews * Sign) AS PageViews,
       sum(Duration * Sign) AS Duration
   FROM test_tbl_collapsing
   GROUP BY UserID
   HAVING sum(Sign) > 0;

   進行彙總計算後，查詢結果如下。

   ┌────────UserID───────┬─PageViews─┬─Duration──┐ 
   │ 4324182021466249494 │    6      │    185    │ 
   └─────────────────────┴───────────┴───────────┘

6. 由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。

   optimize table test_tbl_collapsing final;

7. 重新查詢資料。

   SELECT * FROM test_tbl_collapsing;

   查詢結果如下。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐  
   │ 4324182021466249494 │    6      │    185   │   1   │ 
   └─────────────────────┴───────────┴──────────┴───────┘

樣本如下。

1. 建立表test_tbl_Versioned。

   CREATE TABLE test_tbl_Versioned
   (
       UserID UInt64,
       PageViews UInt8,
       Duration UInt8,
       Sign Int8,
       Version UInt8
   )
   ENGINE = VersionedCollapsingMergeTree(Sign, Version)
   ORDER BY UserID;

2. 插入取消行Sign=-1。

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

3. 插入狀態行Sign=1、Version=1，其他列值與插入的取消行一致。同時，插入一行相同主鍵資料的新狀態行。

   INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);

4. 查詢資料。

   SELECT * FROM test_tbl_Versioned;

   查詢結果如下。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
   │ 4324182021466249494 │    5      │    146   │  -1    │    1   │
   │ 4324182021466249494 │    5      │    146   │   1    │    1   │
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

5. 如果您需要對指定列進行彙總計算，以sum(col)為例，為了獲得正確結果，需改寫SQL語句如下。

   SELECT
       UserID,
       sum(PageViews * Sign) AS PageViews,
       sum(Duration * Sign) AS Duration
   FROM test_tbl_Versioned
   GROUP BY UserID
   HAVING sum(Sign) > 0;

   進行彙總計算後，查詢結果如下。

   ┌────────UserID───────┬─PageViews─┬─Duration─┐ 
   │ 4324182021466249494 │    6      │    185   │
   └─────────────────────┴───────────┴──────────┘

6. 由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。

   optimize table test_tbl_Versioned final;

7. 重新查詢資料。

   SELECT * FROM test_tbl_Versioned;

   查詢結果如下。

   ┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
   │ 4324182021466249494 │    6      │    185   │   1    │    2   │
   └─────────────────────┴───────────┴──────────┴────────┴────────┘

使用SummingMergeTree表引擎時，需要注意如下幾點。

• 雲資料庫ClickHouse只在後台Compaction時才會對主鍵列進行預先彙總，而Compaction的執行時機無法預測，所以可能存在部分資料已經被預先彙總、部分資料尚未被彙總的情況。因此，在執行彙總計算時，仍需要使用GROUP BY子句。

• 在預先彙總時，雲資料庫ClickHouse會對主鍵列之外的其他所有列進行預彙總。如果這些列是可彙總的（比如數實值型別），則直接sum求和。如果不可彙總（比如String類型），則會隨機播放一個值。

• 通常建議將SummingMergeTree表引擎與MergeTree表引擎組合使用，MergeTree表引擎儲存完整的資料，SummingMergeTree表引擎用於儲存預先彙總的結果。

樣本如下。

1. 建立表test_tbl_summing。

   CREATE TABLE test_tbl_summing
   (
       key UInt32,
       value UInt32
   )
   ENGINE = SummingMergeTree()
   ORDER BY key;

2. 寫入資料。

   INSERT INTO test_tbl_summing Values(1,1),(1,2),(2,1);

3. 查詢資料。

   select * from test_tbl_summing;

   查詢結果如下。

   ┌─key─┬value─┐
   │  1  │  1   │
   │  1  │  2   │
   │  2  │  1   │
   └─────┴──────┘    

4. 由於MergeTree系列表引擎採用類似LSM Tree的結構，很多儲存層處理邏輯直到Compaction期間才會發生，因此需執行optimize語句強制後台Compaction。

   optimize table test_tbl_summing final;

5. 強制後台Compaction後，仍舊需要執行GROUP BY子句進行彙總計算，重新查詢資料。

   SELECT key, sum(value) FROM test_tbl_summing GROUP BY key;

   查詢結果如下，主鍵重複的資料已進行了彙總。

   ┌─key─┬value─┐
   │  1  │  3   │
   │  2  │  1   │
   └─────┴──────┘

AggregatingMergeTree的文法比較複雜，需要結合物化視圖或雲資料庫ClickHouse的特殊資料類型AggregateFunction一起使用。

樣本如下。

• 結合物化視圖使用

  1. 建立明細表visits。

     CREATE TABLE visits
     (
         UserID UInt64,
         CounterID UInt8,
         StartDate Date,
         Sign Int8
     )
     ENGINE = CollapsingMergeTree(Sign)
     ORDER BY UserID;

  2. 對明細表visits建立物化視圖visits_agg_view，並使用sumState和uniqState函數對明細表進行預先彙總。

     CREATE MATERIALIZED VIEW visits_agg_view
     ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
     AS SELECT
         CounterID,
         StartDate,
         sumState(Sign)    AS Visits,
         uniqState(UserID) AS Users
     FROM visits
     GROUP BY CounterID, StartDate;

  3. 寫入資料至明細表visits中。

     INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
     INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

  4. 使用彙總函式sumMerge和uniqMerge對物化視圖進行彙總，並查詢彙總資料。

     SELECT
         StartDate,
         sumMerge(Visits) AS Visits,
         uniqMerge(Users) AS Users
     FROM visits_agg_view
     GROUP BY StartDate
     ORDER BY StartDate

     說明

     函數sum和uniq不能再使用，否則會出現SQL報錯：Illegal type AggregateFunction(sum, Int8) of argument for aggregate function sum...

     查詢結果如下。

     ┌──StartDate──┬─Visits─┬─Users──┐
     │  2019-11-11 │   1    │   1    │
     │  2019-11-12 │   1    │   1    │
     └─────────────┴────────┴────────┘

• 結合特殊資料類型AggregateFunction使用

  1. 建立明細表detail_table。

     CREATE TABLE detail_table
     (   CounterID UInt8,
         StartDate Date,
         UserID UInt64
     ) ENGINE = MergeTree() 
     PARTITION BY toYYYYMM(StartDate) 
     ORDER BY (CounterID, StartDate);

  2. 寫入資料至明細表detail_table中。

     INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
     INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

  3. 建立彙總表agg_table，其中UserID列的類型為AggregateFunction。

     CREATE TABLE agg_table
     (   CounterID UInt8,
         StartDate Date,
         UserID AggregateFunction(uniq, UInt64)
     ) ENGINE = AggregatingMergeTree() 
     PARTITION BY toYYYYMM(StartDate) 
     ORDER BY (CounterID, StartDate);

  4. 使用彙總函式uniqState將明細表的資料插入至彙總表中。

     INSERT INTO agg_table
     select CounterID, StartDate, uniqState(UserID)
     from detail_table
     group by CounterID, StartDate;

     說明

     不能使用INSERT INTO agg_table VALUES(1, '2019-11-12', 1);語句向彙總表插入資料，否則會出現SQL報錯：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)...

  5. 使用彙總函式uniqMerge對彙總表進行彙總，並查詢彙總資料。

     SELECT uniqMerge(UserID) AS state 
     FROM agg_table 
     GROUP BY CounterID, StartDate;

     查詢結果如下。

     ┌─state─┐
     │   1   │
     │   1   │
     └───────┘