云数据库ClickHouse支持的表引擎分为MergeTree、Log、Integrations和Special四个系列。本文主要对这四类表引擎进行概要介绍,并通过示例介绍常用表引擎的功能。

概述

表引擎即表的类型,在云数据库ClickHouse中决定了如何存储和读取数据、是否支持索引、是否支持主备复制等。云数据库ClickHouse支持的表引擎,请参见下表。
系列 描述 表引擎 特点
MergeTree MergeTree系列引擎适用于高负载任务,支持大数据量的快速写入并进行后续的数据处理,通用程度高且功能强大。

该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。

MergeTree 用于插入极大量的数据到一张表中,数据以数据片段的形式一个接着一个的快速写入,数据片段按照一定的规则进行合并。
ReplacingMergeTree 用于解决MergeTree表引擎相同主键无法去重的问题,可以删除主键值相同的重复项。
CollapsingMergeTree 在建表语句中新增标记列Sign,用于消除ReplacingMergeTree表引擎的如下功能限制。
  • 在分布式场景下,相同主键的数据可能被分布到不同节点上,不同分片间可能无法去重。
  • 在没有彻底optimize之前,可能无法达到主键去重的效果,比如部分数据已经被去重,而另外一部分数据仍旧有主键重复。
  • optimize是后台动作,无法预测具体执行时间点。
  • 手动执行optimize在海量数据场景下需要消耗大量时间,无法满足业务即时查询的需求。
VersionedCollapsingMergeTree 在建表语句中新增Version列,用于解决CollapsingMergeTree表引擎乱序写入导致无法正常折叠(删除)的问题。
SummingMergeTree 用于对主键列进行预先聚合,将所有相同主键的行合并为一行,从而大幅度降低存储空间占用,提升聚合计算性能。
AggregatingMergeTree 预先聚合引擎的一种,用于提升聚合计算的性能,可以指定各种聚合函数。
GraphiteMergeTree 用于存储Graphite数据并进行汇总,可以减少存储空间,提高Graphite数据的查询效率。
Log Log系列引擎适用于快速写入小表(1百万行左右的表)并读取全部数据的场景。
该系列引擎的共同特点如下。
  • 数据被追加写入磁盘中。
  • 不支持deleteupdate
  • 不支持索引。
  • 不支持原子性写。
  • insert会阻塞select操作。
TinyLog 不支持并发读取数据文件,格式简单,查询性能较差,适用于暂存中间数据。
StripeLog 支持并发读取数据文件,将所有列存储在同一个大文件中,减少了文件数,查询性能比TinyLog好。
Log 支持并发读取数据文件,每个列会单独存储在一个独立文件中,查询性能比TinyLog好。
Integrations Integrations系列引擎适用于将外部数据导入到云数据库ClickHouse中,或者在云数据库ClickHouse中直接使用外部数据源。 Kafka 将Kafka Topic中的数据直接导入到云数据库ClickHouse
MySQL 将MySQL作为存储引擎,直接在云数据库ClickHouse中对MySQL表进行select等操作。
JDBC 通过指定JDBC连接串读取数据源。
ODBC 通过指定ODBC连接串读取数据源。
HDFS 直接读取HDFS上特定格式的数据文件。
Special Special系列引擎适用于特定的功能场景。 Distributed 本身不存储数据,可以在多个服务器上进行分布式查询。
MaterializedView 用于创建物化视图。
Dictionary 将字典数据展示为一个云数据库ClickHouse表。
Merge 本身不存储数据,可以同时从任意多个其他表中读取数据。
File 直接将本地文件作为数据存储。
NULL 写入数据被丢弃,读取数据为空。
Set 数据总是保存在RAM中。
Join 数据总是保存在内存中。
URL 用于管理远程HTTP、HTTPS服务器上的数据。
View 本身不存储数据,仅存储指定的SELECT查询。
Memory 数据存储在内存中,重启后会导致数据丢失。查询性能极好,适合于对于数据持久性没有要求的1亿以下的小表。在云数据库ClickHouse中,通常用来做临时表。
Buffer 为目标表设置一个内存Buffer,当Buffer达到了一定条件之后会写入到磁盘。
说明 表引擎的更多信息,具体请参见表引擎介绍

MergeTree

MergeTree表引擎主要用于海量数据分析,支持数据分区、存储有序、主键索引、稀疏索引和数据TTL等。MergeTree表引擎支持云数据库ClickHouse的所有SQL语法,但是部分功能与标准SQL存在差异。

本文以主键为例进行介绍。云数据库ClickHouse的SQL语法中主键用于去重,保持数据唯一,而在MergeTree表引擎中,其主要作用是加速查询,即便在Compaction完成后,主键相同的数据行也仍旧共同存在。
说明 MergeTree表引擎的更多信息,具体请参见MergeTree
示例如下。
  1. 创建表test_tbl,主键为idcreate_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│ ᴺᵁᴸᴸ    
     1│ 2019-12-13│ ᴺᵁᴸᴸ      
     2│ 2019-12-14│ ᴺᵁᴸᴸ  
     3│ 2019-12-15│ ᴺᵁᴸᴸ  
     3│ 2019-12-15│ ᴺᵁᴸᴸ         
  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│ ᴺᵁᴸᴸ      
     1│ 2019-12-13│ ᴺᵁᴸᴸ       
     2│ 2019-12-14│ ᴺᵁᴸᴸ  
     3│ 2019-12-15│ ᴺᵁᴸᴸ     
     3│ 2019-12-15│ ᴺᵁᴸᴸ

ReplacingMergeTree

为了解决MergeTree表引擎相同主键无法去重的问题,云数据库ClickHouse提供了ReplacingMergeTree表引擎,用于删除主键值相同的重复项。

虽然ReplacingMergeTree表引擎提供了主键去重的能力,但是仍然存在很多限制,因此ReplacingMergeTree表引擎更多被用于确保数据最终被去重,而无法保证查询过程中主键不重复,主要限制如下。
  • 在分布式场景下,相同主键的数据可能被分布到不同节点上,不同分片间可能无法去重。
  • 在没有彻底optimize之前,可能无法达到主键去重的效果,比如部分数据已经被去重,而另外一部分数据仍旧有主键重复。
  • optimize是后台动作,无法预测具体执行时间点。
  • 手动执行optimize在海量数据场景下需要消耗大量时间,无法满足业务即时查询的需求。
说明 ReplacingMergeTree表引擎的更多信息,具体请参见ReplacingMergeTree
示例如下。
  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│ ᴺᵁᴸᴸ       
     1│ 2019-12-13│ ᴺᵁᴸᴸ      
     2│ 2019-12-14│ ᴺᵁᴸᴸ   
     3│ 2019-12-15│ ᴺᵁᴸᴸ    
     3│ 2019-12-15│ ᴺᵁᴸᴸ     
  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│ ᴺᵁᴸᴸ      
     2│ 2019-12-14│ ᴺᵁᴸᴸ   
     3│ 2019-12-15│ ᴺᵁᴸᴸ      

CollapsingMergeTree

CollapsingMergeTree表引擎用于消除ReplacingMergeTree表引擎的功能限制。该表引擎要求在建表语句中指定一个标记列Sign,按照Sign的值将行分为两类:Sign=1的行称为状态行,用于新增状态。Sign=-1的行称为取消行,用于删除状态。
说明 CollapsingMergeTree表引擎虽然解决了主键相同数据即时删除的问题,但是状态持续变化且多线程并行写入情况下,状态行与取消行位置可能乱序,导致无法正常折叠(删除)。
后台Compaction时会将主键相同、Sign相反的行进行折叠(删除),而尚未进行Compaction的数据,状态行与取消行同时存在。因此为了能够达到主键折叠(删除)的目的,需要业务层进行如下操作。
  • 记录原始状态行的值,或者在执行删除状态操作前先查询数据库获取原始状态行的值。

    具体原因:执行删除状态操作时需要写入取消行,而取消行中需要包含与原始状态行主键一样的数据(Sign列除外)。

  • 在进行count()sum(col)等聚合计算时,可能会存在数据冗余的情况。为了获得正确结果,业务层需要改写SQL,将count()sum(col)分别改写为sum(Sign)sum(col * Sign)
    具体原因如下。
    • 后台Compaction时机无法预测,在发起查询时,状态行和取消行可能尚未进行折叠(删除)。
    • 云数据库ClickHouse无法保证主键相同的行落在同一个节点上,不在同一节点上的数据无法进行折叠(删除)。
说明 CollapsingMergeTree表引擎的更多信息,具体请参见CollapsingMergeTree
示例如下。
  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

VersionedCollapsingMergeTree

为了解决CollapsingMergeTree表引擎乱序写入导致无法正常折叠(删除)问题,云数据库ClickHouse提供了VersionedCollapsingMergeTree表引擎,在建表语句中新增一列Version,用于在乱序情况下记录状态行与取消行的对应关系。后台Compaction时会将主键相同、Version相同、Sign相反的行折叠(删除)。

与CollapsingMergeTree表引擎类似,在进行count()sum(col)等聚合计算时,业务层需要改写SQL,将count()sum(col)分别改写为sum(Sign)sum(col * Sign)

说明 VersionedCollapsingMergeTree表引擎的更多信息,具体请参见VersionedCollapsingMergeTree
示例如下。
  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=1Version=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

SummingMergeTree表引擎用于对主键列进行预先聚合,将所有相同主键的行合并为一行,从而大幅度降低存储空间占用,提升聚合计算性能。

使用SummingMergeTree表引擎时,需要注意如下几点。
  • 云数据库ClickHouse只在后台Compaction时才会对主键列进行预先聚合,而Compaction的执行时机无法预测,所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此,在执行聚合计算时,仍需要使用GROUP BY子句。
  • 在预先聚合时,云数据库ClickHouse会对主键列之外的其他所有列进行预聚合。如果这些列是可聚合的(比如数值类型),则直接sum求和。如果不可聚合(比如String类型),则会随机选择一个值。
  • 通常建议将SummingMergeTree表引擎与MergeTree表引擎组合使用,MergeTree表引擎存储完整的数据,SummingMergeTree表引擎用于存储预先聚合的结果。
说明 SummingMergeTree表引擎的更多信息,具体请参见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

AggregatingMergeTree表引擎也是预先聚合引擎的一种,用于提升聚合计算的性能。与SummingMergeTree表引擎的区别在于,SummingMergeTree表引擎用于对非主键列进行sum聚合,而AggregatingMergeTree表引擎可以指定各种聚合函数。

AggregatingMergeTree的语法比较复杂,需要结合物化视图或云数据库ClickHouse的特殊数据类型AggregateFunction一起使用。
说明 AggregatingMergeTree表引擎的更多信息,具体请参见AggregatingMergeTree
示例如下。
  • 结合物化视图使用
    1. 创建明细表visits。
      CREATE TABLE visits
      (
          UserID UInt64,
          CounterID UInt8,
          StartDate Date,
          Sign Int8
      )
      ENGINE = CollapsingMergeTree(Sign)
      ORDER BY UserID;
    2. 对明细表visits建立物化视图visits_agg_view,并使用sumStateuniqState函数对明细表进行预先聚合。
      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. 使用聚合函数sumMergeuniqMerge对物化视图进行聚合,并查询聚合数据。
      SELECT
          StartDate,
          sumMerge(Visits) AS Visits,
          uniqMerge(Users) AS Users
      FROM visits_agg_view
      GROUP BY StartDate
      ORDER BY StartDate
      说明 函数sumuniq不能再使用,否则会出现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