ClickHouse(二)数据库引擎和表引擎

学点啥玩意 2022-03-10

710

数据库引擎

数据库引擎是创建数据库(database)的时候声明的，默认使用的引擎是Atomic。还可以使用一些外部数据库引擎如MySQL，它们会将语句转发到远程库上执行。最后值得一提的是一种叫MaterializedMySQL的引擎，它可以实时的将MySQL的binlog同步过来在ClickHouse上执行，并提供了一个查询视图，使得用户查出来的结果和源库上的结果是一样的。

Atomic

它支持非阻塞的DROP TABLE
、RENAME TABLE
和原子化的EXCHANGE TABLES t1 AND t2
。

特点

Atomic数据库中的所有表都会有一个唯一的UUID，这个UUID会使用到它的存储中，即表的数据会被存储到/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/
，其中xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy
就是生成的UUID，xxx
则是UUID的前三位。
RENAME TABLE
时，并不会更改表的UUID，也会移动表数据，所以如果有一个查询正在使用该表，也不会影响RENAME操作立即完成。
DROP/DETACH TABLES
不会删除数据，只是将元数据移动到/clickhouse_path/metadata_dropped/
标记为已删除，并由后台线程在后续的时间里完成删除动作。

MySQL

MySQL引擎允许将远程的MySQL数据库中的表映射到ClickHouse，并允许你在CK上执行insert和select语句，操作远程数据库上的数据。

创建语法

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

参数说明:

host:port
— MySQL服务地址和端口号
database
— MySQL数据库名称
user
— MySQL用户名
password
— MySQL用户密码

创建好MySQL引擎的数据库后，可以直接在库里面看到源库上的表和数据，并可以执行查询和插入操作。

支持语法

insert into table values
，插入是支持的；
select * from table
，支持查询，也可以用ClickHouse上的函数，但是它会将源表的对应列全拉过来再执行查询；
show tables from databse
，列出源库上的表，支持的；
delete|update|truncate table|alter|rename|create table
，不支持；

MaterializedMySQL

这是个实验性的功能，还没有完全成熟。用到这个引擎，ClickHouse会作为MySQL的一个从库运行，拉取binlog并同步执行它的操作。
为了MaterializedMySQL的正确工作，有一些必须设置的MySQL端配置设置:

default_authentication_plugin = mysql_native_password，因为 MaterializedMySQL 只能授权使用该方法。
gtid_mode = on，因为基于GTID的日志记录是提供正确的 MaterializedMySQL复制的强制要求。

创建语法

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]
[TABLE OVERRIDE table1 (...), TABLE OVERRIDE table2 (...)]

参数说明:

host:port
— MySQL服务地址和端口号
database
— MySQL数据库名称
user
— MySQL用户名
password
— MySQL用户密码

SETTINGS说明

max_rows_in_buffer — 允许在内存中缓存数据的最大行数(对于单个表和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值:65 505。
max_bytes_in_buffer - 允许在内存中缓存数据的最大字节数(对于单个表和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 1 048 576。
max_rows_in_buffers - 允许在内存中缓存数据的最大行数(用于数据库和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 65 505。
max_bytes_in_buffers - 允许在内存中缓存数据的最大字节数(用于数据库和无法查询的缓存数据)。当超过这个数字时，数据将被物化。默认值: 1 048 576。
max_flush_data_time - 允许数据在内存中缓存的最大毫秒数(对于数据库和无法查询的缓存数据)。当超过这个时间，数据将被物化。默认值: 1000。
max_wait_time_when_mysql_unavailable - MySQL不可用时的重试间隔(毫秒)。负值禁用重试。默认值:1000。
allows_query_when_mysql_lost—允许在MySQL丢失时查询物化表。默认值:0(false)。

使用说明

对于MySQL上的表，应该每个表都要有primary key。
MySQL DDL 语句会被转换成对应的ClickHouse DDL 语句，比如：(ALTER, CREATE, DROP, RENAME). 如果ClickHouse 无法解析某些语句DDL 操作，则会跳过。
MaterializedMySQL中的表不支持直接的 INSERT， DELETE 和 UPDATE 查询。
同步到ClickHouse的表实际上会被存储在db.inner.table_name
的表中，源表的insert语句会被解析成insert一条_sign=1
的数据；delete语句会被解析成insert一条_sign=-1
的数据；update语句会根据主键有没有被修改，被解析成insert _sign=-1
+insert _sign=1
或者是一条insert _sign=1
语句。
查询MaterializedMySQL中的表时实际上查到的是一个视图，里面会合并主键的多条记录，生成最新的一条。

表引擎

表引擎是ClickHouse数据库里面很重要的一个概念，因为它决定了表数据的存储方式以及查询、合并、复制数据的方式。不同的表引擎在底层设计是就是很不一样的。
表引擎根据功能的不同，大概分为以下几种大类：

MergeTree家族

功能最强大的一类引擎，数据都是LSM方式写入的，并不定时进行合并。

MergeTree: 具有最好的性能，但是没有按主键去重的能力；
ReplacingMergeTree: 可以按照主键在合并的时候去重；
CollapsingMergeTree: 增加了一个_sign
标识位，可以按照这个标识位折叠数据；
VersionedCollapsingMergeTree: 在CollapsingMergeTree的基础上再增加了version
标识位，可以按照版本折叠；
SummingMergeTree: 可以按照order by的维度字段进行汇总合并；
AggregatingMergeTree: 跟SummingMergeTree类似，但可以指定聚合方法。
Replicated*MergeTree系列: 为上面的引擎增加副本复制的能力。

Log家族

具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）并在以后整体读取它们时，该类型的引擎是最有效的。
Log将数据存储在磁盘上，写入的时候会将数据追加到文件末尾，而且不支持mutation和索引功能。

TinyLog
StripeLog
Log

集成引擎

引用外部数据库，会将SQL放到远程库上执行。

Kafka: 可以连接到Kafka上读取上面的数据；
MySQL: 可以读取MySQL表；
ODBC: 可以连接ODBC驱动支持的任意表；
JDBC: 可以连接JDBC驱动支持的任意表；
HDFS: 可以连接到HDFS上读取表。

其他特殊功能引擎

比较特殊的是字典，它会被加载到每个节点的内存，一般在需要关联小维表时会用到它。

Dictionary: 声明一个能被加载到内存的字典，在不想关联维表的时候可以用它。

MergeTree

MergeTree是一种LSM-Tree，它的原理是数据不断增量产生（没错，更新和删除也是以APPEND的方式追加到数据集中的）然后组织成一个个数据片段(part)，一个一个地写入，后台不定时的对数据片段进行合并。

适用场景

MergeTree无疑是功能强大的，它已经具备了列式存储的许多优良特性，对于海量数据的存储和查询是非常友好的。它对某些场景的支持和某些场景的不适用都非常明显，下面将讨论一二。

它比较适用于数据写入后不再做修改的场景，而且对因重试而产生的重复数据不敏感，比如说持续的日志采集
不太适用于会有大批量数据更新或删除的场景，因为它合并时未不会删除重复数据，导致它的数据膨胀问题无法解决，运行效率将会越来越慢
适用于大批量数据聚合汇总的场景，而不太适用于少量数据点查的场景，因为它实现的是稀疏索引，想要点查最好还是用Hbase
不太适用于持续的流式写入，最好根据服务器压力进行攒批写入，减少数据片段碎片的产生

建表语句

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

参数说明

ON CLUSTER cluster
- 如果运行上集群上，指定集群则会在集群上所有的节点都建表
ENGINE
- 表引擎，MergeTree引擎没有参数
ORDER BY
- 必选排序字段，排序和去重会根据这里指定的字段优先进行，如果PRIMARY KEY没指定则默认和ORDER BY字段一样，如果不想指定列，可以写成ORDER BY tuple()
PARTITION BY
- 分区字段，如果不指定那数据全在一个all分区
PRIMARY KEY
- 主键字段，生成稀疏索引时会用到这个，必须是ORDER BY字段的前缀，也就是PRIMARY KEY(a, b)，那么ORDER BY 必须为(a, b, *)
SAMPLE BY
- 采样字段
TTL
- 可以指定列/行上的生命周期，超出时间则会被置0或者移除，用法TTL date_col + INTERVAl 1 DAY
SETTINGS
- 其它配置项，想了解，查官网手册吧
INDEX
- 指定二级索引，可以加快点查速度

数据存储方式

表由按主键排序的数据片段组成，当插入请求被接收时，在内存先会按照主键进行排序，并按照分区切割，写到后台存储。ClickHouse后台线程不定时对片段进行合并，但是不同分区的片段不会合并到一起，相同主键的行如果不在同一个分区，那它们不会被合并到同一个文件中。
part有两种存储格式：Wide和Compact，Wide格式下每一列会被存储到单个文件中，Compact格式下所有的列都存储在同一个文件中。采用哪种格式是受数据库参数控制的，根据大小或者行数阈值，如果太小了会存储在Compact格式的文件中。
每个数据片段被逻辑的分割成颗粒（granules），颗粒是ClickHouse中进行数据查询时的最小不可分割数据集。ClickHouse不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记。ClickHouse会为每个数据片段创建一个稀疏索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse都会存储类似标记。这些标记让您可以在列文件中直接找到数据。

主键和索引

主键以主键字段从左往右逐个组合，而且必须为ORDER BY字段的前缀，也就是说可以设置得ORDER BY 字段少，如果不指定PRIMARY KEY那么默认和ORDER BY 字段一样。主键在数据文件primary.idx中存储。
主键排序好之后，并不会为每个键值记录位置信息（比如说，记录的偏移量），而是采用跳表的形式，每隔K行才做一个标记，所以它实现的是稀疏索引。同时，每个列数据文件也会生成对应的.mrk2文件，记录它的稀疏索引。在进行数据查找时，会先在主键中找到对应的标记和偏移量，再到对应的列文件中把相应的标识和偏移量找出来。
主键列越多，查询效率一般来说越高（所有列都能在查询时用上的话），但是对插入性能和内存消耗有一定的影响。
在WHERE/PREWHERE语句中，从左到右使用到主键列做以下操作可以用到索引：相等/不相等的比较；对主键列或者分区列做IN运算；前缀LIKE 'ABC%'；部分函数运算。

列和表的TTL

TTL用于设置列和表的生命周期——当列的生命周期到期时，它会被设置成默认值；当表的的生命周期到期时（即某些行已经过期），那这些过期行会被从表中删除或者移走。但是请注意，生命周期检测发生在数据合并时，或者受merge_with_ttl_timeout
参数的影响。
另外，主键不能设置列TTL。同时，TTL的计算必须要用到表里的某个时间列。

虚拟列

虚拟列是引擎自动为表查询加上的列，一般以下划线开头，用户不该把自己的列名设置得跟虚拟列列名一样。

_part
- 分区名称。
_part_index
- 作为请求的结果，按顺序排列的分区数。
_partition_id
- 分区名称。
_part_uuid
- 唯一部分标识符（如果 MergeTree 设置assign_part_uuids 已启用）。
_partition_value
- partition by 表达式的值（元组）。
_sample_factor
- 采样因子（来自请求）。

ReplacingMergeTree

该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。
但是数据的去重是在数据合并时进行的，所以它并没有一个确定的时间。有某些时间内，数据是有可能会有重复数据的存在的。同时，不同分区的数据不会合并，所以必须保证分区间排序键的唯一性。

适用场景

适用于按照排序字段进行增量更新(删除)的场景，但是要求更新（删除）事件不要乱序到达
因为不保证数据时刻都是合并去重过的，想要得到准确的去重后的数据，必须使用以下方法：

查询前OPTIMIZE，不推荐，合并时会影响性能；

查询时在表名后面加FINAL，更加不推荐，经测试效率会慢而且不指定分区时只返回单分区的数据；

对去重键做GROUP BY，在想保留最新值的列f1上使用argMax(f1, f2)方法，argMax会在f2最大值时取对应的f1值，如果f2有多个最大值，则取其中最新的一条。这种方法对于*MergeTree引擎都适用，也会带来一定的性能损耗。

建表语句

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的是一样的
引擎设置里面的ver
参数：类型为 UInt*, Date 或 DateTime，如果ver
未指定，做合并时会保留最后一条，如果有指定，则保留ver
值最大的一条。

CollapsingMergeTree

这种引擎提供了一种按照排序字段和sign折叠的能力，跟ReplacingMergeTree不太一样的是，它要求在原值和更新值之间加入一条sign=-1的原值数据，方便取消原值并在去重时参与计算。
当一直往CollapsingMergeTree里面插入sign=1的更新数据时，它的行为跟ReplacingMergeTree基本是一样的。

适用场景

比较适用于类似于MySQL binlog同步更新的写入场景，也就是说在更新时会同时插入一条sign=-1的原值数据和一条sign=1的新值数据；在删除时会插入sign=-1的原值数据
在使用聚合时要使用上sign，即原sum(amount) --> sum(amount * sign); count(1) -- > sum(sign); count(distinct id) --> count(distinct id) from ( select ... group by id having sum(sign) = 1)
折叠算法，折叠后相同排序键的行被减少到不超过2行：

如果sign=-1和sign=1的记录数量一样多而且最后一个是sign=1时，保留第一个sign=-1和最后一个sign=1；

如果sign=1的记录比sign=-1的数量多一个或以上时，保留最后一个sign=1；

如果sign=-1的数量比sign=1的数量多一个或以上时，保留最后一个sign=-1；

其他场景下，不会保留任何行。

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

参数说明

其他参数和MergeTree的是一样的
引擎设置里面的sign
参数：状态列，类型为Int8，它的值限制为[-1, 1]这两个值，合并数据时会根据规则将sign参与计算。

VersionedCollapsingMergeTree

带版本的折叠合并树引擎，它在CollapsingMergeTree的基础上加入了版本的概念，从而允许数据乱序，即-1和1的顺序不一致。

适用场景

并发写入，数据有可能乱序，而且数据上天然就带有版本信息的时候
折叠算法：在sign的基础上，把version相同的排序键进行折叠；
汇总算法: sum(amount) --> sum(amount * sign) group by version... 增加了version分组字段

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

参数说明

其他参数和MergeTree的是一样的
sign
：状态列，类型为Int8，它的值限制为[-1, 1]这两个值，合并数据时会根据规则将sign参与计算。
version
：版本列，类型为UInt*，数据会根据version一致的进行合并。

SummingMergeTree

这种引擎会将排序列以外的数值列自动汇总，也就是相同排序列的数据经过合并后，只会保留一行，而数值会自动汇总，其他类型的列则只保留最早的记录。

适用场景

可以与MergeTree配合使用，MergeTree保留明细数据，SummingMergeTree则保留汇总数据
因为合并时机并不是确定的，所以查询时还是需要自己写汇总函数进行聚合

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns])
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

参数说明

其他参数和MergeTree的是一样的
引擎配置中的columns
：自己定义的汇总列元组，如果不指定，则会自动把所有数值列进行汇总。

AggregatingMergeTree

可以根据一定规则把同排序列的多条数据合并成一条，常用在物化视图逻辑里面使用。

适用场景

看官方的例子是用到一个聚合物化视图上了

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...]

例子

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

Replicated*MergeTree

这个系列是用来支持副本复制的，如果表是分片表，则每个分片也可以有自己的副本。
需要跟ZooKeeper配合来使用

TinyLog

这是一种最简单的表引擎，每列会单独存放在一个压缩文件里，写入时，数据会往末尾进行追加。

适用场景

小数据量，而且写不频繁
如果并发的读取数据是允许的
同时读写会导致读操作失败
同时写操作会导致数据被破坏
不支持索引，必须整列数据读入

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = TinyLog()
[SETTINGS name=value, ...]

Log

相比TinyLog，Log会生成标记文件，可以快速跳过某些行。

适用场景

可以并发读
写入会阻塞读
也不支持索引

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Log()
[SETTINGS name=value, ...]

StripeLog

StripeLog 引擎将所有列存储在一个文件中。对每一次 Insert 请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。它也会生成标记文件。

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = StripeLog()

Dictionary

Dictionary引擎可以将字典转换成一个表来使用

适用场景

好像这样没啥鸟用，对于想直接查询字典里面所有的值的时候有点用

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) 
engine = Dictionary(dictionary_name)

参数说明

dictionary_name
：字典的名字

数据库 clickhouse

文章转载自学点啥玩意，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

ClickHouse(二)数据库引擎和表引擎

数据库引擎

Atomic

特点

MySQL

创建语法

支持语法

MaterializedMySQL

创建语法

使用说明

表引擎

MergeTree家族

Log家族

集成引擎

其他特殊功能引擎

MergeTree

适用场景

建表语句

数据存储方式

主键和索引

列和表的TTL

虚拟列

ReplacingMergeTree

适用场景

建表语句

CollapsingMergeTree

适用场景

建表语句

VersionedCollapsingMergeTree

适用场景

建表语句

SummingMergeTree

适用场景

建表语句

AggregatingMergeTree

适用场景

建表语句

例子

Replicated*MergeTree

TinyLog

适用场景

建表语句

Log

适用场景

建表语句

StripeLog

建表语句

Dictionary

适用场景

建表语句

评论