ClickHouse的表引擎
表引擎是 ClickHouse 的一大特色。可以说， 表引擎决定了如何存储表的数据。 包括：
- 数据的存储方式和位置，写到哪里以及从哪里读取数据。
- 支持哪些查询以及如何支持。 并发数据访问。
- 索引的使用（如果存在）。
- 是否可以执行多线程请求。 数据复制参数。
- 表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎 使用的相关参数。
特别注意：引擎的名称大小写敏感。
分类：
Log 系列表引擎
TinyLog 是 Log 系列引擎中功能简单、性能较低的引擎。它的存储结构由数 据文件和元数据两部分组成。其中，数据文件是按列独立存储的，也就是说每一 个列字段都对应一个文件。除此之外，TinyLog 不支持并发数据读取。
StripLog 支持并发读取数据文件，当读取数据时，ClickHouse 会使用多线程 进行读取，每个线程处理一个单独的数据块。另外，StripLog 将所有列数据存储 在同一个文件中，减少了文件的使用数量。
Log 支持并发读取数据文件，当读取数据时，ClickHouse 会使用多线程进行 读取，每个线程处理一个单独的数据块。Log 引擎会将每个列数据单独存储在一 个独立文件中。
TinyLog
该引擎适用于一次写入，多次读取的场景。对于处理小批数据的中间表可以 使用该引擎。值得注意的是，使用大量的小表存储数据，性能会很低。
CREATE TABLE emp_tinylog (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )
ENGINE=TinyLog();
INSERT INTO emp_tinylog
VALUES (1,'tom','上海',25,'技术部',20000),
(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_tinylog
VALUES (3,'bob',' 北 京 ',33,' 财 务 部 ',50000),
(4,'tony',' 杭 州 ',28,' 销 售 事 部 ',50000);
StripLog
相比 TinyLog 而言，StripeLog 拥有更高的查询性能（拥有.mrk 标记文件，支 持并行查询），同时其使用了更少的文件描述符（所有数据使用同一个文件保存）
CREATE TABLE emp_stripelog (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )
ENGINE=StripeLog;
INSERT INTO emp_stripelog
VALUES (1,'tom','上海',25,'技术部',20000),
(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_stripelog
VALUES (3,'bob',' 北 京 ',33,' 财 务 部 ',50000),
(4,'tony',' 杭 州 ',28,' 销 售 事 部 ',50000);
Log
Log 引擎表适用于临时数据，一次性写入、测试场景。Log 引擎结合了 TinyLog 表引擎和 StripeLog 表引擎的长处，是 Log 系列引擎中性能最高的表引擎。
CREATE TABLE emp_(
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )
ENGINE=Log;
INSERT INTO emp_log
VALUES (1,'tom','上海',25,'技术部',20000),
(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_log
VALUES (3,'bob',' 北 京 ',33,' 财 务 部 ',50000),
(4,'tony',' 杭 州 ',28,' 销 售 事 部 ',50000);
MergeTree 系列表引擎 ♥♥
在所有的表引擎中，最为核心的当属 MergeTree 系列表引擎，这些表引擎拥 有最为强大的性能和最广泛的使用场合。对于非 MergeTree 系列的其他引擎而 言，主要用于特殊用途，场景相对有限。而 MergeTree 系列表引擎是官方主推的 存储引擎，支持几乎所有 ClickHouse 核心功能。
MergeTree
MergeTree 在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数 据片段不可修改。为了避免片段过多，ClickHouse 会通过后台线程，定期合并这 些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往 复合并的特点，也正是合并树名称的由来。
特点：
- 需要指定主键，数据按照主键排序。
- 可以使用分区，可以通过 PARTITION KEY 语句指定分区字段，开发中一般按 月进行分区。
- 支持数据副本，保证安全性。
- 支持数据采样。
CREATE TABLE emp_mergetree (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )
ENGINE=MergeTree()
ORDER BY emp_id
PARTITION BY work_place ;
- 相同分区的数据不会马上合并，而是经过一段时间以后会自动合并。
- 如果没有指定主键，会使用order by字段当成主键，并且主键没有唯一约束性
- 它的功能是为了提高查询效率。
ReplacingMergeTree
MergeTree 表引擎无法对相同主键的数据进行去重，ClickHouse 提供了 ReplacingMergeTree 引擎，可以针对相同主键的数据进行去重，它能够在合并分 区时删除重复的数据。值得注意的是，ReplacingMergeTree 只是在一定程度上解 决了数据重复问题，但是并不能完全保障数据不重复。
CREATE TABLE emp_replacingmergetree (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )
ENGINE=ReplacingMergeTree()
ORDER BY (emp_id,name)
PRIMARY KEY emp_id
PARTITION BY work_place ;
手动触发合并操作：
optimize table emp_replacingmergetree final;
- 在合并分区的数据，会基于order by字段对相同分区内的数据去重，而不是主键字段。
SummingMergeTree
该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数 据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合 并的行中具有数值数据类型的列的汇总值。即如果存在重复的数据，会对对这些 重复的数据进行合并成一条数据，类似于 group by 的效果。 如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条 件是预先明确的，即 GROUP BY 的分组字段是确定的，可以使用该表引擎。
CREATE TABLE emp_summingmergetree (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )ENGINE=SummingMergeTree(salary)
ORDER BY (emp_id,name) -- 注意排序 key 是两个字段
PRIMARY KEY emp_id -- 主键是一个字段
PARTITION BY work_place;
- 在合并分区的数据，会基于order by字段对相同分区内的数据对指定的列做求和操作。
Aggregatingmergetree
该表引擎继承自 MergeTree，可以使用 AggregatingMergeTree 表来做增量数 据统计聚合。如果要按一组规则来合并减少行数，则使用 AggregatingMergeTree 是合适的。AggregatingMergeTree 是通过预先定义的聚合函数计算数据并通过二 进制的格式存入表内。 与 SummingMergeTree 的区别在于：SummingMergeTree 对非主键列进行 sum 聚合，而 AggregatingMergeTree 则可以指定各种聚合函数。
CREATE TABLE emp_aggregatingmergeTree (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary AggregateFunction(sum,Decimal32(2)) COMMENT '工资' )
ENGINE=AggregatingMergeTree()
ORDER BY (emp_id,name) -- 注意排序 key 是两个字段
PRIMARY KEY emp_id -- 主键是一个字段
PARTITION BY work_place ;
# 需要使用 INSERT…SELECT 语句进行数据插入
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));
# 查询
SELECT emp_id, name , sumMerge(salary)
FROM emp_aggregatingmergeTree
GROUP BY emp_id,name;
- 在合并分区的数据，会基于order by字段对相同分区内的数据对指定的列做聚合操作
- 写入数据的时候通过****State声明一个求和状态
- 查询数据的时候***Merge(salary) 进行聚合
AggregatingMergeTree 通常作为物化视图的表引擎，与普通 MergeTree 搭配 使用。
# 创建一个 MereTree 引擎的明细表
# 用于存储全量的明细数据
# 对外提供实时查询
CREATE TABLE emp_mergetree_base (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' )
ENGINE=MergeTree()
ORDER BY (emp_id,name)
PARTITION BY work_place ;
# 创建一张物化视图
# 使用 AggregatingMergeTree 表引擎
CREATE MATERIALIZED VIEW view_emp_agg
ENGINE = AggregatingMergeTree()
PARTITION BY emp_id
ORDER BY (emp_id,name)
AS SELECT emp_id, name, sumState(salary) AS salary
FROM emp_mergetree_base
GROUP BY emp_id,name;
# 向基础明细表 emp_mergetree_base 插入数据
INSERT INTO emp_mergetree_base
VALUES (1,'tom','上海',25,'技术部',20000),
(1,'tom','上海',26,'人事部',10000);
# 查询物化视图
SELECT emp_id, name , sumMerge(salary)
FROM view_emp_agg
GROUP BY emp_id,name;
CollapsingMergeTree
CollapsingMergeTree 就是一种通过以增代删的思路，支持行级数据修改和删 除的表引擎。它通过定义一个 sign 标记位字段，记录数据行的状态。如果 sign 标记为 1，则表示这是一行有效的数据；如果 sign 标记为-1，则表示这行数据需 要被删除。当 CollapsingMergeTree 分区合并时，同一数据分区内，sign 标记为 1 和-1 的一组数据会被抵消删除。
每次需要新增数据时，写入一行 sign 标记为 1 的数据；需要删除数据时， 则写入一行 sign 标记为-1 的数据。
CREATE TABLE emp_collapsingmergetree (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资',
sign Int8 )
ENGINE=CollapsingMergeTree(sign)
ORDER BY (emp_id,name)
PARTITION BY work_place ;
CollapsingMergeTree同样是以ORDER BY 排序键作为判断数据唯一性的依据。
# 插入新增数据,sign=1 表示正常数据
INSERT INTO emp_collapsingmergetree
VALUES (1,'tom','上海',25,'技术部',20000,1);
# 首先插入一条与原来相同的数据(ORDER BY 字段一致),并将 sign 置为-1
INSERT INTO emp_collapsingmergetree
VALUES (1,'tom','上海',25,'技术部',20000,-1);
# 再插入更新之后的数据
INSERT INTO emp_collapsingmergetree
VALUES (1,'tom','上海',25,'技术部',30000,1);
# 执行分区合并操作
optimize table emp_collapsingmergetree;
- 通过定义标记为对相同order by字段的数据，进行删除。传入的时候，必须要先传1，再传-1，才会被删除。
VersionedCollapsingMergeTree
上面提到 CollapsingMergeTree 表引擎对于数据写入乱序的情况下，不能够实 现 数 据 折 叠 的 效 果 。 VersionedCollapsingMergeTree 表 引 擎 的 作 用 与 CollapsingMergeTree 完 全 相 同 ， 它 们 的 不 同 之 处 在 于 ， VersionedCollapsingMergeTree 对数据的写入顺序没有要求，在同一个分区内，任 意顺序的数据都能够完成折叠操作。 VersionedCollapsingMergeTree 使用 version 列来实现乱序情况下的数据折叠。
CREATE TABLE emp_versioned (
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资',
sign Int8, version Int8 )ENGINE=VersionedCollapsingMergeTree(sign, version)
ORDER BY (emp_id,name)
PARTITION BY work_place ;
# 先插入需要被删除的数据，即 sign=-1 的数据
INSERT INTO emp_versioned
VALUES (1,'tom','上海',25,'技术部',20000,-1,1);
# 再插入 sign=1 的数据
INSERT INTO emp_versioned
VALUES (1,'tom','上海',25,'技术部',20000,1,1);
# 在插入一个新版本数据
INSERT INTO emp_versioned
VALUES (1,'tom','上海',25,'技术部',30000,1,2);
# 手动合并
optimize table emp_versioned;
虽然在插入数据乱序的情况下，依然能够实现折叠的效果。之所以能够达到 这种效果，是因为在定义 version 字段之后，VersionedCollapsingMergeTree 会自 动将 version 作为排序条件并增加到 ORDER BY 的末端，就上述的例子而言，最终 的排序字段为 ORDER BY emp_id,name，version desc。
外部集成表引擎
ClickHouse 提供了许多与外部系统集成的方法，包括一些表引擎。这些表引 擎与其他类型的表引擎类似，可以用于将外部数据导入到 ClickHouse 中，或者在 ClickHouse 中直接操作外部数据源。
- JDBC：通过指定 jdbc 连接读取数据源；
- MySQL：将 MySQL 作为数据存储，直接查询其数据；
- HDFS：直接读取 HDFS 上的特定格式的数据文件；
- Kafka：将 Kafka 数据导入 ClickHouse。
HDFS
CREATE TABLE hdfs_engine_table(
emp_id UInt16 COMMENT '员工 id',
name String COMMENT '员工姓名',
work_place String COMMENT '工作地点',
age UInt8 COMMENT '员工年龄',
depart String COMMENT '部门',
salary Decimal32(2) COMMENT '工资' ) ENGINE=HDFS('hdfs://master:9000/ch/hdfs_engine_table', 'CSV');
MySQL
CREATE TABLE mysql_engine_table(
id Int32,
name String )
ENGINE = MySQL(
'master:3306',
'test',
'student',
'root',
'123456');
Kafka
CREATE TABLE kafka_table (
uid UInt64,
phone UInt64,
addr String )
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'master:9092',
kafka_topic_list = 'ck_topic',
kafka_group_name = 'group1',
kafka_format = 'JSONEachRow' ;
当我们一旦查询完毕之后，ClickHouse 会删除表内的数据，其实 Kafka 表引 擎只是一个数据管道，我们可以通过物化视图的方式访问 Kafka 中的数据。 1.首先创建一张 Kafka 表引擎的表，用于从 Kafka 中读取数据；
2.然后再创建一张普通表引擎的表，比如 MergeTree，面向终端用户使用；
3.最后创建物化视图，用于将 Kafka 引擎表实时同步到终端用户所使用的表中。
# 创建 Kafka 引擎表
CREATE TABLE kafka_table_consumer (
uid UInt64,
phone UInt64,
addr String )
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'master:9092',
kafka_topic_list = 'ck_topic',
kafka_group_name = 'group1',
kafka_format = 'JSONEachRow' ;
# 创建一张终端用户使用的表
CREATE TABLE kafka_table_mergetree (
uid UInt64,
phone UInt64,
addr String )
ENGINE=MergeTree()
ORDER BY uid ;
# 创建物化视图，同步数据
CREATE MATERIALIZED VIEW consumer TO kafka_table_mergetree
AS SELECT uid,phone,addr FROM kafka_table_consumer ;
# 查询，多次查询，已经被查询的数据依然会被输出
select * from kafka_table_mergetree;
扫一扫
1186
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
