clickhouse-(03)-库和表引擎

库引擎

MySQL引擎

允许连接到远程MySQL服务器上的数据库，并执行INSERT和SELECT查询以在ClickHouse和MySQL之间交换数据。

Mysql数据库引擎翻译请求语句，并发送给MYSQL服务器，因此你可以执行像SHOW TABLES 或者 SHOW CREATE TABLE这样的操作。

但是您不能执行以下查询：

RENAME
CREATE TABLE
ALTER

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

引擎参数

host:port — MySQL服务器地址。
database —远程数据库名称。
user — MySQL用户。
password - 用户密码。

Ordinary

也就是默认引擎，使用时无需在建库时刻意声明，在此数据库下的表可以使用任意的类型的表引擎。官网中也有介绍。

表引擎

ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。对于大多数正式的任务，应该使用MergeTree族中的引擎

1.日志引擎系列--Log系列

这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的，相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。

共性是：

数据被顺序append写到磁盘上。
不支持delete、update。
不支持index。
不支持原子性写。
insert会阻塞select操作。

区别是：

TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据。
Log：支持并发读取数据文件，查询性能比TinyLog好；每个列会单独存储在一个独立文件中。并发读取，写入操作则阻塞读取和其它写入。Log 引擎适用于临时数据，write-once 表以及测试或演示目的。
StripeLog：支持并发读取数据文件，查询性能比TinyLog好；将所有列存储在同一个大文件中，减少了文件个数。

2.Special系列

Special系列的表引擎，大多是为了特定场景而定制的。这里挑选几个简单介绍，不做详述。

Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中，通常用来做临时表。
Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。
File：直接将本地文件作为数据存储。
Null：写入数据被丢弃、读取数据为空。

3.MergeTree系列--合并树引擎系列

Log、Special、Integration主要用于特殊用途，场景相对有限。MergeTree系列才是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。

3.1MergeTree

MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree支持所有ClickHouse SQL语法，但是有些功能与MySQL并不一致，比如在MergeTree中主键并不用于去重MergeTree 引擎系列的基本理念如下：当有巨量数据要插入到表中时，需要高效地一批批写入数据片段，并希望这些数据片段在后台按照一定规则合并。应对表的并发访问，我们使用多版本机制。换言之，当同时读和更新表时，数据从当前查询到的一组片段中读取。没有冗长的的锁。插入不会阻碍读取。对表的读操作是自动并行的。

特点：

存储的数据按主键排序。
允许使用分区（在指定了主键的情况下）。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。
支持数据副本。
支持数据采样。

 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

存储 ( 以主键排序, 以分区为合并单元. )

表由按主键排序的数据片段组成。
当数据被插入到表中时，会分成数据片段并按主键的字典序排序。
不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不会合并来自不同分区的数据片段。这个合并机制并不保证相同主键的所有行都会合并到同一个数据片段中。

主键与排序键

稀疏索引让你能操作有巨量行的表。因为这些索引是常驻内存（RAM）的。
ClickHouse 不要求主键唯一。所以，你可以插入多条具有相同主键的行。
主键中列的数量并没有明确的限制。
长的主键会对插入性能和内存消耗有负面影响。
默认情况下主键跟排序键相同。指定一个跟排序键（用于排序数据片段中行的表达式）不一样的主键（用于计算写到索引文件的每个标记值的表达式）是可以的。这种情况下，主键表达式元组必须是排序键表达式元组的一个前缀。

如下建表DDL所示，test_tbl的主键为(id, create_time)，并且按照主键进行存储排序，按照create_time进行数据分区，数据保留最近一个月。

CREATE TABLE test_tbl (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;
     
insert into test_tbl values(0, '2019-12-12', null);
insert into test_tbl values(0, '2019-12-12', null);
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);
查询数据： 可以看到虽然主键id、create_time相同的数据只有3条数据，但是结果却有5行。
select count(*) from test_tbl;
┌─count()─┐
│       5 │
└─────────┘
 
select * from test_tbl;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
由于MergeTree采用类似LSM tree的结构，很多存储层处理逻辑直到Compaction期间才会发生。因此强制后台compaction执行完毕，再次查询，发现仍旧有5条数据。
optimize table test_tbl final;
 
 
select count(*) from test_tbl;
┌─count()─┐
│       5 │
└─────────┘
 
select * from test_tbl;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘

3.2ReplacingMergeTree(去重表)

该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。适用于在后台清除重复数据以节省空间，但不保证不存在重复。数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要指望使用它，因为 OPTIMIZE 语句会引发对大量数据的读和写。

合并的时候，ReplacingMergeTree 从所有具有相同主键的行中选择一行留下：如果 ver 列未指定，选择最后一条。如果 ver 列已指定，选择 ver 值最大的版本。

虽然ReplacingMergeTree提供了主键去重的能力，但是仍旧有以下限制：

在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧有主键重复。
在分布式场景下，相同primary key的数据可能被sharding到不同节点上，不同shard间可能无法去重。
optimize是后台动作，无法预测具体执行时间点。
手动执行optimize在海量数据场景下要消耗大量时间，无法满足业务即时查询的需求。

案例

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)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;
 
-- 写入主键重复的数据
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(0, '2019-12-12', null);
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);
 
-- 查询，可以看到未compaction之前，主键重复的数据，仍旧存在。
select count(*) from test_tbl_replacing;
┌─count()─┐
│       5 │
└─────────┘
 
select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
 
 
-- 强制后台compaction：
optimize table test_tbl_replacing final;
 
 
-- 再次查询：主键重复的数据已经消失。
select count(*) from test_tbl_replacing;
┌─count()─┐
│       3 │
└─────────┘
 
select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘

3.3CollapsingMergeTree(折叠表)

ClickHouse实现了CollapsingMergeTree来消除ReplacingMergeTree的功能限制。该引擎要求在建表语句中指定一个标记列Sign，后台Compaction时会将主键相同、Sign相反的行进行折叠，也即删除。

CollapsingMergeTree将行按照Sign的值分为两类：Sign=1的行称之为状态行，Sign=-1的行称之为取消行。

每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。

在后台Compaction时，状态行与取消行会自动做折叠（删除）处理。而尚未进行Compaction的数据，状态行与取消行同时存在。

因此为了能够达到主键折叠（删除）的目的，需要业务层进行适当改造：

执行删除操作需要写入取消行，而取消行中需要包含与原始状态行主键一样的数据（Sign列除外）。所以在应用层需要记录原始状态行的值，或者在执行删除操作前先查询数据库获取原始状态行。
由于后台Compaction时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；另外，ClickHouse无法保证primary key相同的行落在同一个节点上，不在同一节点上的数据无法折叠。因此在进行count(*)、sum(col)等聚合计算时，可能会存在数据冗余的情况。为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;
 
-- 插入状态行，注意sign一列的值为1
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);
 
-- 插入一行取消行，用于抵消上述状态行。注意sign一列的值为-1，其余值与状态行一致；
-- 并且插入一行主键相同的新状态行，用来将PageViews从5更新至6，将Duration从146更新为185.
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);
 
-- 查询数据：可以看到未Compaction之前，状态行与取消行共存。
SELECT * FROM UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
 
-- 为了获取正确的sum值，需要改写SQL： 
-- sum(PageViews) => sum(PageViews * Sign)、 
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘
 
 
-- 强制后台Compaction
optimize table UAct final;
 
-- 再次查询，可以看到状态行、取消行已经被折叠，只剩下最新的一行状态行。
select * from UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

3.4VersionedCollapsingMergeTree(带版本的折叠表)

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。

与CollapsingMergeTree类似，为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

CREATE TABLE UAct_version
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;
 
 
-- 先插入一行取消行，注意Signz=-1, Version=1
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);
-- 后插入一行状态行，注意Sign=1, Version=1；及一行新的状态行注意Sign=1, Version=2，将PageViews从5更新至6，将Duration从146更新为185。
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);
 
 
-- 查询可以看到未compaction情况下，所有行都可见。
SELECT * FROM UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
 
 
-- 为了获取正确的sum值，需要改写SQL： 
-- sum(PageViews) => sum(PageViews * Sign)、 
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct_version
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘
 
 
-- 强制后台Compaction
optimize table UAct_version final;
 
 
-- 再次查询，可以看到即便取消行与状态行位置乱序，仍旧可以被正确折叠。
select * from UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         6 │      185 │    1 │       2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

3.5SummingMergeTree(聚合表)

ClickHouse通过SummingMergeTree来支持对主键列进行预先聚合。在后台Compaction时，会将主键相同的多行进行sum求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。

值得注意的是：

ClickHouse只在后台Compaction时才会进行数据的预先聚合，而compaction的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。
在预先聚合时，ClickHouse会对主键列之外的其他所有列进行预聚合。如果这些列是可聚合的（比如数值类型），则直接sum；如果不可聚合（比如String类型），则随机选择一个值。
通常建议将SummingMergeTree与MergeTree配合使用，使用MergeTree来存储具体明细，使用SummingMergeTree来存储预先聚合的结果加速查询。

CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key
 
-- 插入数据
INSERT INTO summtt Values(1,1),(1,2),(2,1)
 
-- compaction前查询，仍存在多行
select * from summtt;
┌─key─┬─value─┐
│   1 │     1 │
│   1 │     2 │
│   2 │     1 │
└─────┴───────┘
 
-- 通过GROUP BY进行聚合计算
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘
 
-- 强制compaction
optimize table summtt final;
 
-- compaction后查询，可以看到数据已经被预先聚合
select * from summtt;
┌─key─┬─value─┐
│   1 │     3 │
│   2 │     1 │
└─────┴───────┘
 
 
-- compaction后，仍旧需要通过GROUP BY进行聚合计算
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

3.6AggregatingMergeTree

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

AggregatingMergeTree的语法比较复杂，需要结合物化视图或ClickHouse的特殊数据类型AggregateFunction一起使用。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。

示例一：配合物化视图使用。

CREATE TABLE visits(    UserID UInt64,    CounterID UInt8,    StartDate Date,    Sign Int8)ENGINE = CollapsingMergeTree(Sign)ORDER BY UserID; -- 对明细表建立物化视图，该物化视图对明细表进行预先聚合-- 注意：预先聚合使用的函数分别为： sumState, uniqState。对应于写入语法<agg>-State.CREATE MATERIALIZED VIEW visits_agg_viewENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)AS SELECT    CounterID,    StartDate,    sumState(Sign)    AS Visits,    uniqState(UserID) AS UsersFROM visitsGROUP BY CounterID, StartDate; -- 插入明细数据INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);INSERT INTO visits VALUES(1, 1, '2019-11-12', 1); -- 对物化视图进行最终的聚合操作-- 注意：使用的聚合函数为 sumMerge， uniqMerge。对应于查询语法<agg>-Merge.SELECT    StartDate,    sumMerge(Visits) AS Visits,    uniqMerge(Users) AS UsersFROM visits_agg_viewGROUP BY StartDateORDER BY StartDate; -- 普通函数 sum, uniq不再可以使用-- 如下SQL会报错： Illegal type AggregateFunction(sum, Int8) of argument SELECT    StartDate,    sum(Visits),    uniq(Users)FROM visits_agg_viewGROUP BY StartDateORDER BY StartDate;

示例二：配合特殊数据类型AggregateFunction使用。

-- 建立明细表CREATE TABLE detail_table(   CounterID UInt8,    StartDate Date,    UserID UInt64) ENGINE = MergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate); -- 插入明细数据INSERT INTO detail_table VALUES(0, '2019-11-11', 1);INSERT INTO detail_table VALUES(1, '2019-11-12', 1); -- 建立预先聚合表，-- 注意：其中UserID一列的类型为：AggregateFunction(uniq, UInt64)CREATE TABLE agg_table(   CounterID UInt8,    StartDate Date,    UserID AggregateFunction(uniq, UInt64)) ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate); -- 从明细表中读取数据，插入聚合表。-- 注意：子查询中使用的聚合函数为 uniqState， 对应于写入语法<agg>-StateINSERT INTO agg_tableselect CounterID, StartDate, uniqState(UserID)from detail_tablegroup by CounterID, StartDate -- 不能使用普通insert语句向AggregatingMergeTree中插入数据。-- 本SQL会报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)INSERT INTO agg_table VALUES(1, '2019-11-12', 1); -- 从聚合表中查询。-- 注意：select中使用的聚合函数为uniqMerge，对应于查询语法<agg>-MergeSELECT uniqMerge(UserID) AS state FROM agg_table GROUP BY CounterID, StartDate;

4.Integration系列

该系统表引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

Kafka：将Kafka Topic中的数据直接导入到ClickHouse。
MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作。
JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源。
HDFS：直接读取HDFS上的特定格式的数据文件；

————————————————
版权声明：本文为CSDN博主「J小白Y」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Jarry_cm/article/details/106210148

posted @ 2021-06-05 16:15 宁君阅读(679) 评论(0) 编辑收藏举报

刷新页面返回顶部

宁君