# ClickHouse引擎概述 #### ClickHouse表引擎介绍 表引擎在ClickHouse中的作用十分关键，不同的表引擎决定了： - 数据存储和读取的位置 - 支持哪些查询方式 - 能否并发式访问数据 - 能不能使用索引 - 是否可以执行多线程请求 - 数据复制使用的参数 其中MergeTree和Distributed是ClickHouse表引擎中最重要，也是最常使用的两个引擎，本文将重点进行介绍。 其他表引擎详细可以参考官网链接：。 - **MergeTree系列引擎** MergeTree用于高负载任务的最通用和功能最强大的表引擎，其主要有以下关键特征： - 基于分区键（partitioning key）的数据分区分块存储 - 数据索引排序（基于primary key和order by） - 支持数据复制（带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, ...] ``` **使用示例：** ``` CREATE TABLE default.test ( name1 DateTime, name2 String, name3 String, name4 String, name5 Date, ... ) ENGINE = MergeTree() PARTITION BY toYYYYMM(name5) ORDER BY (name1, name2) SETTINGS index_granularity = 8192 ``` 示例参数说明如下： - **ENGINE = MergeTree()**：MergeTree表引擎。 - **PARTITION BY** toYYYYMM(name4)：分区，示例数据将以月份为分区，每个月份一个文件夹。 - **ORDER BY**：排序字段，支持多字段的索引排序，第一个相同的时候按照第二个排序依次类推。 - **index_granularity = 8192**：排序索引的颗粒度，每8192条数据记录一个排序索引值。 如果被查询的数据存在于分区或排序字段中，能极大降低数据查找时间。 - ReplacingMergeTree 该引擎和MergeTree的不同之处在于它会删除排序键值相同的重复项。ReplacingMergeTree适合于清除重复数据节省存储空间，但是它不保证重复数据不出现，一般不建议使用。 **建表语法：** ``` 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, ...] ``` - SummingMergeTree 当合并SummingMergeTree表的数据片段时，ClickHouse会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著地减少存储空间并加快数据查询的速度。 **建表语法：** ``` 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, ...] ``` **使用示例：** 创建一个SummingMergeTree表testTable： ``` CREATE TABLE testTable ( id UInt32, value UInt32 ) ENGINE = SummingMergeTree() ORDER BY id ``` 插入表数据： ``` INSERT INTO testTable Values(5,9),(5,3),(4,6),(1,2),(2,5),(1,4),(3,8); INSERT INTO testTable Values(88,5),(5,5),(3,7),(3,5),(1,6),(2,6),(4,7),(4,6),(43,5),(5,9),(3,6); ``` 在未合并parts查询所有数据： ``` SELECT * FROM testTable ┌─id─┬─value─┐ │ 1 │ 6 │ │ 2 │ 5 │ │ 3 │ 8 │ │ 4 │ 6 │ │ 5 │ 12 │ └───┴──── ┘ ┌─id─┬─value─┐ │ 1 │ 6 │ │ 2 │ 6 │ │ 3 │ 18 │ │ 4 │ 13 │ │ 5 │ 14 │ │ 43 │ 5 │ │ 88 │ 5 │ └───┴──── ┘ ``` ClickHouse还没有汇总所有行，如果需要通过ID进行汇总聚合，需要用到sum和GROUP BY子句： ``` SELECT id, sum(value) FROM testTable GROUP BY id ┌─id─┬─sum(value)─┐ │ 4 │ 19 │ │ 3 │ 26 │ │ 88 │ 5 │ │ 2 │ 11 │ │ 5 │ 26 │ │ 1 │ 12 │ │ 43 │ 5 │ └───┴───────┘ ``` 手工执行合并操作： ``` OPTIMIZE TABLE testTable ``` 此时再查询testTable表数据： ``` SELECT * FROM testTable ┌─id─┬─value─┐ │ 1 │ 12 │ │ 2 │ 11 │ │ 3 │ 26 │ │ 4 │ 19 │ │ 5 │ 26 │ │ 43 │ 5 │ │ 88 │ 5 │ └───┴──── ┘ ``` SummingMergeTree根据ORDER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。 后台执行合并操作时才会进行数据的预先聚合，而合并操作的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。 - AggregatingMergeTree AggregatingMergeTree是预先聚合引擎的一种，用于提升聚合计算的性能。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, ...] ``` **使用示例：** AggregatingMergeTree无单独参数设置，在分区合并时，在每个数据分区内，会按照ORDER BY聚合，使用何种聚合函数，对哪些列字段计算，则是通过定义AggregateFunction函数类型实现，例如： ``` create table test_table ( name1 String, name2 String, name3 AggregateFunction(uniq,String), name4 AggregateFunction(sum,Int), name5 DateTime ) ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(name5) ORDER BY (name1,name2) PRIMARY KEY name1; ``` AggregateFunction类型的数据在写入和查询时需要分别调用\*state、\*merge函数，\*表示定义字段类型时使用的聚合函数。如上示例表test_table定义的name3、name4字段分别使用了uniq、sum函数，那么在写入数据时需要调用uniqState、sumState函数，并使用INSERT SELECT语法。 ``` insert into test_table select '8','test1',uniqState('name1'),sumState(toInt32(100)),'2021-04-30 17:18:00'; insert into test_table select '8','test1',uniqState('name1'),sumState(toInt32(200)),'2021-04-30 17:18:00'; ``` 在查询数据时也需要调用对应的函数uniqMerge、sumMerge： ``` select name1,name2,uniqMerge(name3),sumMerge(name4) from test_table group by name1,name2; ┌─name1─┬─name2─┬─uniqMerge(name3)─┬─sumMerge(name4)─┐ │ 8 │ test1 │ 1 │ 300 │ └──── ┴──── ┴──────────┴───────── ┘ ``` AggregatingMergeTree更常用的方式是结合物化视图使用，物化视图即其他数据表上层的一种查询视图。详细可以参考： - CollapsingMergeTree CollapsingMergeTree它通过定义一个sign标记位字段记录数据行的状态。如果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, ...] ``` **使用示例：** 具体的使用示例可以参考：。 - VersionedCollapsingMergeTree VersionedCollapsingMergeTree表引擎在建表语句中新增了一列version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。 **建表语法：** ``` 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, ...] ``` **使用示例：** 具体的使用示例可以参考：。 - GraphiteMergeTree GraphiteMergeTree引擎用来存储时序数据库Graphite的数据。 **建表语法：** ``` CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( Path String, Time DateTime, Value , Version ... ) ENGINE = GraphiteMergeTree(config_section) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...] ``` **使用示例：** 具体的使用示例可以参考：。 - **Replicated\*MergeTree引擎** ClickHouse中的所有MergeTree家族引擎前面加上Replicated就成了支持副本的合并树引擎。 ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/zh-cn_image_0000001903092818.png "点击放大") Replicated系列引擎借助ZooKeeper实现数据的同步，创建Replicated复制表时通过注册到ZooKeeper上的信息实现同一个分片的所有副本数据进行同步。 **Replicated表引擎的创建模板：** ``` ENGINE = Replicated*MergeTree('ZooKeeper存储路径','副本名称', ...) ``` Replicated表引擎需指定两个参数： - ZooKeeper存储路径：ZooKeeper中该表相关数据的存储路径，建议规范化，如：*/clickhouse/tables/{shard}* /*数据库名/表名。* - 副本名称，一般用**{replica}**即可。 - **Distributed表引擎** Distributed表引擎本身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据到集群中的各个节点，分布式表需要和其他本地数据表一起协同工作。分布式表会将接收到的读写任务分发到各个本地表，而实际上数据的存储在各个节点的本地表中。 图1Distributed ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/zh-cn_image_0000001903252706.png) **Distributed表引擎的创建模板：** ``` ENGINE = Distributed(cluster_name, database_name, table_name, [sharding_key]) ``` Distributed表参数解析如下： - cluster_name：集群名称，在对分布式表执行读写的过程中，使用集群的配置信息查找对应的ClickHouse实例节点。 - database_name：数据库名称。 - table_name：数据库下对应的本地表名称，用于将分布式表映射到本地表上。 - sharding_key：分片键（可选参数），分布式表会按照这个规则，将数据分发到各个本地表中。 **Distributed表引擎** **使用示例**： ``` --先创建一个表名为test的ReplicatedMergeTree本地表 CREATE TABLE default.test ON CLUSTER default_cluster_1 ( `EventDate` DateTime, `id` UInt64 ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id --基于本地表test创建表名为test_all的Distributed表 CREATE TABLE default.test_all ON CLUSTER default_cluster_1 ( `EventDate` DateTime, `id` UInt64 ) ENGINE = Distributed(default_cluster_1, default, test, rand()) ``` **分布式表创建规则：** - 创建Distributed表时需加上**on cluster** *cluster_name*，这样建表语句在某一个ClickHouse实例上执行一次即可分发到集群中所有实例上执行。 - 分布式表通常以本地表加"_all"命名。它与本地表形成一对多的映射关系，之后可以通过分布式表代理操作多张本地表。 - 分布式表的表结构尽量和本地表的结构一致。如果不一致，在建表时不会报错，但在查询或者插入时可能会发生异常。 #### ClickHouse数据类型说明 MRS的ClickHouse服务数据类型如[表1]所示。 ClickHouse完整数据类型介绍，请参考[开源官方数据类型介绍](https://clickhouse.com/docs/en/sql-reference/data-types/?spm=a2c4g.11186623.0.0.66a5717fx3eNhl)。 ![](https://support.huaweicloud.com/cmpntguide-lts-mrs/public_sys-resources/caution_3.0-zh-cn.png) 禁止开启异步写入参数（async_insert），开启异步写入参数可能存在数据可靠性问题。 表1ClickHouse数据类型 | 分类 | 关键字 | 数据类型 | 描述 | |:---|:---|:---|:---| | 数据类型 | Int8 | Int8 | 取值范围：-128\~127 | | 数据类型 | Int16 | Int16 | 取值范围：-32768\~32767 | | 数据类型 | Int32 | Int32 | 取值范围：-2147483648\~2147483647 | | 数据类型 | Int64 | Int64 | 取值范围：-9223372036854775808\~9223372036854775807 | | 浮点类型 | Float32 | 单精度浮点数 | 同C语言Float类型，单精度浮点数在机内占4个字节，用32位二进制描述。 | | 浮点类型 | Float64 | 双精度浮点数 | 同C语言Double类型，双精度浮点数在机内占8个字节，用64位二进制描述。 | | Decimal类型 | Decimal | Decimal | 有符号的定点数，可在加、减和乘法运算过程中保持精度。支持几种写法： - Decimal(P, S) - Decimal32(S) - Decimal64(S) - Decimal128(S) 说明： - P：精度，有效范围：\[1:38\]，决定可以有多少个十进制数字（包括分数）。 - S：规模，有效范围：\[0：P\]，决定数字的小数部分中包含的小数位数。 | | 字符串类型 | String | 字符串 | 字符串可以是任意长度的。它可以包含任意的字节集，包含空字节。因此，字符串类型可以代替其他DBMSs中的VARCHAR、BLOB、CLOB等类型。 | | 字符串类型 | FixedString | 固定字符串 | 当数据的长度恰好为N个字节时，FixedString类型是高效的。在其他情况下，这可能会降低效率。可以有效存储在FixedString类型的列中的值的示例： - 二进制表示的IP地址 - 语言代码（ru_RU, en_US ... ） - 货币代码（RUB ... ） - 二进制表示的哈希值（MD5使用FixedString（16），SHA256使用FixedString（32）） | | 时间日期类型 | Date | 日期 | 用两个字节存储，表示从1970-01-01（无符号）到当前的日期值。日期中没有存储时区信息。 | | 时间日期类型 | DateTime | 时间戳 | 用四个字节（无符号的）存储Unix时间戳。允许存储与日期类型相同的范围内的值。最小值为1970-01-01 00:00:00。时间戳类型值精确到秒（没有闰秒）。时区使用启动客户端或服务器时的系统时区。 | | 时间日期类型 | DateTime64 | DateTime64 | 此类型允许以日期（date）加时间（time）的形式来存储一个时刻的时间值。 | | 布尔型 | Boolean | Boolean | ClickHouse没有单独的类型来存储布尔值。可以使用UInt8类型，取值限制为0或1。 | | 数组类型 | Array | Array | Array(T)，由T类型元素组成的数组。T可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse对多维数组的支持有限。例如，不能在MergeTree表中存储多维数组。 | | 元组类型 | Tuple | Tuple | Tuple(T1, T2, ...)，元组，其中每个元素都有单独的类型，不能在表中存储元组（除了内存表）。它们可以用于临时列分组。在查询中，IN表达式和带特定参数的lambda函数可以来对临时列进行分组。 | | Domains数据类型 | Domains | Domains | Domains类型是特定实现的类型： IPv4是与UInt32类型保持二进制兼容的Domains类型，用于存储IPv4地址的值。它提供了更为紧凑的二进制存储的同时支持识别可读性更加友好的输入输出格式。 | | 枚举类型 | Enum8 | Enum8 | 取值范围：-128\~127 Enum保存'string' = integer的对应关系，例如：Enum8('hello' = 1, 'world' = 2) | | 枚举类型 | Enum16 | Enum16 | 取值范围：-32768\~32767 | | 可为空 | Nullable | Nullable | 除非在ClickHouse服务器配置中另有说明，否则NULL是任何Nullable类型的默认值。Nullable类型字段不能包含在表索引中。 可以与TypeName的正常值存放一起。例如，Nullable(Int8)类型的列可以存储Int8类型值，而没有值的行将存储NULL。 | | 嵌套类型 | nested | nested | 嵌套的数据结构就像单元格内的表格。嵌套数据结构的参数（列名和类型）的指定方式与CREATE TABLE查询中的指定方式相同。每个表行都可以对应于嵌套数据结构中的任意数量的行。 示例：Nested(Name1 Type1, Name2 Type2, ...) |