大数据-ClickHouse技术二（数据库引擎）

无敌小田田

已于 2022-09-27 15:04:19 修改

阅读量1.9k

点赞数 2

分类专栏：大数据文章标签： clickhouse

于 2022-09-25 19:04:47 首次发布

本文链接：https://blog.csdn.net/qq_36602951/article/details/127040702

版权

大数据专栏收录该内容

12 篇文章 4 订阅

订阅专栏

一、ClickHouse有着丰富的数据库引擎技术

官方文档：数据库引擎 | ClickHouse Docs

二、数据库引擎此处只做建议的介绍与使用，具体细节请参考官方网站

1、默认情况下，ClickHouse使用Atomic数据库引擎

它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。

创建数据库

  CREATE DATABASE test[ ENGINE = Atomic];

2、此外还支持：[experimental] MaterializedMySQL

创建ClickHouse数据库，包含MySQL中所有的表，以及这些表中的所有数据。

ClickHouse服务器作为MySQL副本工作。它读取binlog并执行DDL和DML查询

这个功能是实验性的。

创建数据库

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

引擎参数

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

还需要做一些引擎配置与Mysql端配置

3、[experimental] MaterializedPostgreSQL

使用PostgreSQL数据库表的初始数据转储创建ClickHouse数据库，并启动复制过程，即执行后台作业，以便在远程PostgreSQL数据库中的PostgreSQL数据库表上发生新更改时应用这些更改。

ClickHouse服务器作为PostgreSQL副本工作。它读取WAL并执行DML查询。DDL不是复制的，但可以处理（如下所述）。

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

Engine参数

host:port — PostgreSQL服务地址
database — PostgreSQL数据库名
user — PostgreSQL用户名
password — 用户密码

4、Mysql 引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换

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 — MySQL数据库名称
user — MySQL用户名
password — MySQL用户密码

支持的数据类型

MySQL	ClickHouse
UNSIGNED TINYINT	UInt8
TINYINT	Int8
UNSIGNED SMALLINT	UInt16
SMALLINT	Int16
UNSIGNED INT, UNSIGNED MEDIUMINT	UInt32
INT, MEDIUMINT	Int32
UNSIGNED BIGINT	UInt64
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	Date
DATETIME, TIMESTAMP	DateTime
BINARY	FixedString

其他的MySQL数据类型将全部都转换为String.

支持全局变量

SELECT @@version;
SLEECT @@max_allowed_packet;

5、Lazy引擎

在最后一次访问之后，只在RAM中保存expiration_time_in_seconds秒。只能用于*Log表。

它是为存储许多小的*Log表而优化的，对于这些表，访问之间有很长的时间间隔。

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

6、SQLite引擎

允许连接到SQLite数据库，并支持ClickHouse和SQLite交换数据，执行 INSERT 和 SELECT 查询

    CREATE DATABASE sqlite_database 
    ENGINE = SQLite('db_path')

7、PostgreSQL引擎

允许连接到远程PostgreSQL服务。支持读写操作(SELECT和INSERT查询)，以在ClickHouse和PostgreSQL之间交换数据。

在SHOW TABLES和DESCRIBE TABLE查询的帮助下，从远程PostgreSQL实时访问表列表和表结构。

支持表结构修改(ALTER TABLE ... ADD|DROP COLUMN)。如果use_table_cache参数(参见下面的引擎参数)设置为1，则会缓存表结构，不会检查是否被修改，但可以用DETACH和ATTACH查询进行更新。

CREATE DATABASE test_database 
ENGINE = PostgreSQL('host:port', 'database', 'user', 'password'[, `use_table_cache`]);

引擎参数

host:port — PostgreSQL服务地址
database — 远程数据库名次
user — PostgreSQL用户名称
password — PostgreSQL用户密码
schema - PostgreSQL 模式
use_table_cache — 定义数据库表结构是否已缓存或不进行。可选的。默认值： 0.

8、[experimental] Replicated

该引擎基于Atomic引擎。它支持通过将DDL日志写入ZooKeeper并在给定数据库的所有副本上执行的元数据复制。

一个ClickHouse服务器可以同时运行和更新多个复制的数据库。但是同一个复制的数据库不能有多个副本。

CREATE DATABASE testdb ENGINE = Replicated('zoo_path', 'shard_name', 'replica_name') [SETTINGS ...]

引擎参数

zoo_path — ZooKeeper地址，同一个ZooKeeper路径对应同一个数据库。
shard_name — 分片的名字。数据库副本按shard_name分组到分片中。
replica_name — 副本的名字。同一分片的所有副本的副本名称必须不同。

三、表引擎

表引擎（即表的类型）决定了：

数据的存储方式和位置，写到哪里以及从哪里读取数据
支持哪些查询以及如何支持。
并发数据访问。
索引的使用（如果存在）。
是否可以执行多线程请求。
数据复制参数。

1、MergeTree

适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

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

子句

ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。
ORDER BY — 排序键。

可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate) 。

如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。

如果不需要排序，可以使用 ORDER BY tuple().

可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate) 。

如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。

如果不需要排序，可以使用 ORDER BY tuple().

PARTITION BY — 分区键，可选项。

大多数情况下，不需要分使用区键。即使需要使用，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与 ORDER BY 表达式不同）。永远也别使用过细粒度的分区键。不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。

要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

PRIMARY KEY - 如果要选择与排序键不同的主键，在这里指定，可选项

默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句

SAMPLE BY - 用于抽样的表达式，可选项。

如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。

表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：

TTL date + INTERVAl 1 DAY

规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

SETTINGS — 控制 MergeTree 行为的额外参数，可选项：

index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。参考数据存储。
index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
min_index_granularity_bytes - 允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。参考数据存储
enable_mixed_granularity_parts — 是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果您的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。
use_minimalistic_part_header_in_zookeeper — ZooKeeper中数据片段存储方式。如果use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。更多信息参考[服务配置参数](Server Settings | ClickHouse Documentation)这章中的设置描述。
min_merge_bytes_to_use_direct_io — 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。
<a name="mergetree_setting-merge_with_ttl_timeout"></a>
merge_with_ttl_timeout — TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。
write_final_mark — 是否启用在数据片段尾部写入最终索引标记。默认值: 1（不要关闭）。
merge_max_block_size — 在块中进行合并操作时的最大行数限制。默认值：8192
storage_policy — 存储策略。参见使用具有多个块的设备进行数据存储.
min_bytes_for_wide_part,min_rows_for_wide_part 在数据片段中可以使用Wide格式进行存储的最小字节数/行数。您可以不设置、只设置一个，或全都设置。参考：数据存储
max_parts_in_total - 所有分区中最大块的数量(意义不明)
max_compress_block_size - 在数据压缩写入表前，未压缩数据块的最大大小。您可以在全局设置中设置该值(参见max_compress_block_size)。建表时指定该值会覆盖全局设置。
min_compress_block_size - 在数据压缩写入表前，未压缩数据块的最小大小。您可以在全局设置中设置该值(参见min_compress_block_size)。建表时指定该值会覆盖全局设置。
max_partitions_to_read - 一次查询中可访问的分区最大数。您可以在全局设置中设置该值(参见max_partitions_to_read)。

示例配置

ENGINE MergeTree()

PARTITION BY toYYYYMM(EventDate)

ORDER BY (CounterID, EventDate, intHash32(UserID))

SAMPLE BY intHash32(UserID)

SETTINGS index_granularity=8192

在这个例子中，我们设置了按月进行分区。

同时我们设置了一个按用户 ID 哈希的抽样表达式。这使得您可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果您在查询时指定了 SAMPLE 子句。 ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。

已经弃用建表方法

:::attention "注意"
不要在新版项目中使用该方法，可能的话，请将旧项目切换到上述方法。
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
...
) ENGINE [=] MergeTree(date-column [, sampling_expression], (primary, key), index_granularity)

MergeTree() 参数

date-column — 类型为日期的列名。ClickHouse 会自动依据这个列按月创建分区。分区名格式为 "YYYYMM" 。
sampling_expression — 采样表达式。
(primary, key) — 主键。类型 — 元组()
index_granularity — 索引粒度。即索引中相邻『标记』间的数据行数。设为 8192 可以适用大部分场景。

示例

MergeTree(EventDate,

intHash32(UserID),

(CounterID, EventDate, intHash32(UserID)),

8192)

数据存储方式-介绍

表由按主键排序的数据片段（DATA PART）组成。

当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是 (CounterID, Date) 时，片段中数据首先按 CounterID 排序，具有相同 CounterID 的部分按 Date 排序。

不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到同一个数据片段中。

稀疏索引会引起额外的数据读取。当读取主键单个区间范围的数据时，每个数据块中最多会多读 index_granularity * 2 行额外的数据。

稀疏索引使得您可以处理极大量的行，因为大多数情况下，这些索引常驻于内存。

ClickHouse 不要求主键唯一，所以您可以插入多条具有相同主键的行。

您可以在PRIMARY KEY与ORDER BY条件中使用可为空的类型的表达式，但强烈建议不要这么做。为了启用这项功能，请打开allow_nullable_key，NULLS_LAST规则也适用于ORDER BY条件中有NULL值的情况下。

主键的选择

主键中列的数量并没有明确的限制。依据数据结构，您可以在主键包含多些或少些列。这样可以：

改善索引的性能。
如果当前主键是 (a, b) ，在下列情况下添加另一个 c 列会提升性能：
查询会使用 c 列作为条件
很长的数据范围（ index_granularity 的数倍）里 (a, b) 都是相同的值，并且这样的情况很普遍。换言之，就是加入另一列后，可以让您的查询略过很长的数据范围。
改善数据压缩。

ClickHouse 以主键排序片段数据，所以，数据的一致性越高，压缩越好。
在CollapsingMergeTree 和 SummingMergeTree 引擎里进行数据合并时会提供额外的处理逻辑。

在这种情况下，指定与主键不同的 排序键 也是有意义的。

长的主键会对插入性能和内存消耗有负面影响，但主键中额外的列并不影响 SELECT 查询的性能。

可以使用 ORDER BY tuple() 语法创建没有主键的表。在这种情况下 ClickHouse 根据数据插入的顺序存储。如果在使用 INSERT ... SELECT 时希望保持数据的排序，请设置 max_insert_threads = 1。

想要根据初始顺序进行数据查询，使用单线程查询

选择与排序键不同的主键

Clickhouse可以做到指定一个跟排序键不一样的主键，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元组必须是排序键表达式元组的前缀(即主键为(a,b)，排序列必须为(a,b,**))。

索引和分区在查询中的应用

对于 SELECT 查询，ClickHouse 分析是否可以使用索引。如果 WHERE/PREWHERE 子句具有下面这些表达式（作为完整WHERE条件的一部分或全部）则可以使用索引：进行相等/不相等的比较；对主键列或分区列进行IN运算、有固定前缀的LIKE运算(如name like 'test%')、函数运算(部分函数适用)，还有对上述表达式进行逻辑运算。

因此，在索引键的一个或多个区间上快速地执行查询是可能的。下面例子中，指定标签；指定标签和日期范围；指定标签和日期；指定多个标签和日期范围等执行查询，都会非常快。

当引擎配置如下时：

    ENGINE MergeTree() 
PARTITION BY toYYYYMM(EventDate)
 ORDER BY (CounterID, EventDate)
 SETTINGS index_granularity=8192

这种情况下，这些查询：

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse 会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。

上文的查询显示，即使索引用于复杂表达式，因为读表操作经过优化，所以使用索引不会比完整扫描慢。

下面这个例子中，不会使用索引。