数据库引擎与数据表引擎

添码座原创大约 20 分钟

数据库引擎

1. MySQL引擎

用于将远程MySQL服务器中的表映射到Clickhouse，并对这些表进行INSERT和SELECT操作。其实它就是一个Clickhouse操作MySQL数据库的接口。

创建具有MySQL引擎的数据库。

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

Clickhouse和MySQL之间不同数据类型的映射。

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

2. MaterializeMySQL引擎

使用这种引擎时，Clickhouse会像一个从库那样同步MySQL的binlog并执行DDL和DML查询：也就是MySQL和Clickhouse之间形成了主-从关系。

但官方并不推荐在实际生产环境中使用这种引擎，所以目前来说，这个引擎其实这就是个鸡肋。

3. PostgreSQL引擎

用于将远程PostgreSQL服务器中的表映射到Clickhouse，并对这些表进行INSERT和SELECT操作。其实它就是一个Clickhouse操作PostgreSQL数据库的接口。

创建具有PostgreSQL引擎的数据库。

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

Clickhouse和PostgreSQL之间不同数据类型的映射。

Clickhouse	PostgreSQL
Int16	SMALLINT
Int32	INTEGER, MEDIUMINT
Int64	BIGINT
UInt32	SERIAL
UInt64	BIGINT
UInt64	BIGSERIAL
Float32	FLOAT
Float64	DOUBLE
Decimal	DECIMAL, NUMERIC
Date	DATE
DateTime	TIMESTAMP
String	TEXT, CHAR
Nullable(Int32)	INTEGER
Array	ARRAY

4. MaterializedPostgreSQL引擎

使用这种引擎时，Clickhouse会像一个从库那样同步PostgreSQL的WAL并执行DML查询：也就是PostgreSQL和Clickhouse之间形成了主-从关系。

5. SQLite引擎

用于将SQLite中的表映射到Clickhouse，并对这些表进行INSERT和SELECT操作。其实它就是一个Clickhouse操作SQLite数据库的接口。

创建具有SQLite引擎的数据库。

-- 语法
CREATE DATABASE sqlite_database
ENGINE = SQLite('db_path');

Clickhouse和SQLite之间不同数据类型的映射。

Clickhouse	SQLite
Int32	INTEGER
Float32	REAL
String	TEXT, BLOB

6. Lazy引擎

在最后一次访问之后，只在RAM中保存创建时指定的秒数，此类数据库下只能使用Log系列的表引擎。

它是为存储许多小的Log表而优化的，对于这些表，每次访问之间会有较长的时间间隔，所以采取一种延迟加载的方式读取。

创建具有Lazy引擎的数据库。

-- 这里expiration_time_in_seconds = 60
:) CREATE DATABASE testlazy ENGINE = Lazy(60);

7. Atomic引擎

这是Clickhouse默认使用的数据库引擎。

:) CREATE DATABASE testdb[ ENGINE = Atomic];

8. Replicated引擎

截至24.5.1.1763版本，官方仍然将该引擎标准为[experimental]（实验性的），意味着它还并不稳定。

这个引擎基于Atomic，它类似于ReplicatedMergeTree表引擎，通过将DDL日志写入ZooKeeper，就可以在所有其他副本上执行元数据复制。

它的语法如下。

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

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

数据表引擎

表引擎（即表的类型）决定了Clickhouse的行为。

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

数据表引擎包括四大簇：

MergeTree引擎家族：这是Clickhouse中最强大，也是使用最广泛的表引擎系列。
Log引擎家族：为需要写入许多小数据量（少于一百万行）的表而开发的引擎，包括StripeLog、Log和TinyLog。
集成引擎家族：专门用来与外部数据存储与处理系统进行集成的引擎，例如ODBC、JDBC、MySQL、MongoDB、HDFS、Kafka、RabbitMQ等引擎。
特定功能引擎家族，包括Distributed分布式引擎、Join关联表引擎、Memory内存表引擎、Buffer缓冲区引擎、Merge合并引擎等。

1. MergeTree引擎家族

MergeTree引擎家族是Clickhouse的主要成员，占据大部分的功能特性，是Clickhouse显著区别于其他大数据系统的地方。

1.1 MergeTree引擎

该引擎被设计用于插入极大量的数据到一张表中。数据以片段的形式一个接着一个的快速写入，然后在后台按照一定的规则进行合并。

这种批处理方式相比在插入时不断修改（重写）已存储的数据，效率会高出很多。

-- 语法
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
	-- TTL用于设置值的生命周期，它既可以为整张表设置，也可以为每个列字段单独设置
	-- 表级别的TTL会指定数据在磁盘和卷上自动转移的逻辑
	-- TTL子句不能被用于主键字段
	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
-- MergeTree引擎没有参数
) ENGINE = MergeTree()
-- 如果没有显式指定主键，Clickhouse会使用排序键作为主键
ORDER BY expr
-- 可以使用分区
[PARTITION BY expr]
-- 存储的数据按主键排序，且Clickhouse不要求主键唯一，所以可以插入多条具有相同主键的行
[PRIMARY KEY expr]
[SAMPLE BY expr]
-- 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表（可选）
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
-- 可选项，包括稀疏矩阵的index_granularity等
[SETTINGS name=value, ...]

由于default_table_engine=MergeTree参数的存在，因此不必再特别设置ENGINE = MergeTree。

下面两条建表语句效果完全一样。

-- 指定 ENGINE = MergeTree
:) CREATE TABLE my_table
   (
     key UInt64,
     value String
   ) ENGINE = MergeTree()
     ORDER BY key

-- 不指定 ENGINE = MergeTree，默认就是MergeTree
:) CREATE TABLE my_table(
    key UInt64,
    value String
   ) ORDER BY key

关于TTL选项，有如下的建表语句。

:) CREATE TABLE ttl_table
   (
     createtime DateTime,
     prepared Int8 TTL createtime + INTERVAL 1 MONTH,
     deadline Int8 TTL createtime + INTERVAL 2 MONTH,
     name String
   ) ENGINE = MergeTree()
     PARTITION BY toYYYYMM(createtime)
     ORDER BY createtime;

-- 可以将createtime设置为需要的值，调试看看效果（当超过TTL时间后，数据会被清除）
:) INSERT INTO ttl_table VALUES('2024-01-01 10:00:00', 1, 2, 'test');
:) INSERT INTO ttl_table VALUES('2024-02-01 10:00:00', 3, 4, 'test');

┌──────────createtime─┬─prepared──┬─deadline─┬─name─────┐
│ 2024-01-01 10:00:00 │         0 │      0   │     test │
│ 2024-02-01 10:00:00 │         3 │      4   │     test │
└─────────────────────┴───────────┴──────────┴──────────┘

之所以是上面的结果，是因为当前时间是2024-03-02 10:00:00（假设）。

1.2 VersionedCollapsingMergeTree引擎

用直白的话来解释它的作用就是：它以滚动的方式保存数据及其状态。

例如，下面的表记录了用户在网站上访问了多少页面及其滞留时间。

:) CREATE TABLE vcm_table
   (
     userid UInt64,
     pv UInt8,
     duration UInt8,
     sign Int8,
     version UInt8
   )
    -- 当Clickhouse插入数据时，会按主键对行进行排序。 
    -- 如果Version不在主键中，则将其隐式添加到主键作为最后一个字段并使用它进行排序。
    ENGINE = VersionedCollapsingMergeTree(sign, version)
    ORDER BY (userid, pv);

:) INSERT INTO vcm_table VALUES (4324182021466249494, 5, 146, 1, 1);

:) SELECT * FROM vcm_table;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┬─version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

Sign取什么值并不重要，它也可以用-2或其他值来保存取消状态，或者用10来保存新增状态。

当每次用户数据有更新时，它都会提交两行数据（一行旧数据的取消状态，一行新数据的新增状态）。

-- 第一行取消用户的先前状态，它复制已取消状态的所有字段，除了Sign
:) INSERT INTO vcm_table VALUES (4324182021466249494, 5, 146, -1, 1);

-- 第二行则为当前状态的新数据，版本号也跟着递增
:) INSERT INTO vcm_table VALUES (4324182021466249494, 7, 178, 1, 2);

:) SELECT * FROM vcm_table;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┬─version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
│ 4324182021466249494 │         5 │      146 │   -1 │       1 |
│ 4324182021466249494 │         7 │      178 │    1 │       2 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

继续更新时，再提交两行数据。

:) INSERT INTO vcm_table VALUES (4324182021466249494, 7, 178, -1, 2);

:) INSERT INTO vcm_table VALUES (4324182021466249494, 9, 162, 1, 3);

:) SELECT * FROM vcm_table;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┬─version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
│ 4324182021466249494 │         5 │      146 │   -1 │       1 |
│ 4324182021466249494 │         7 │      178 │    1 │       2 |
│ 4324182021466249494 │         7 │      178 │   -1 │       2 |
│ 4324182021466249494 │         9 │      162 │    1 │       3 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

现在，来执行折叠。

:) OPTIMIZE TABLE vcm_table;

-- 折叠后的结果
:) SELECT * FROM vcm_table;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┬─version─┐
│ 4324182021466249494 │         9 │      162 │    1 │       3 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

它永远都只保留最新状态的那一行。至于为什么每次都要提交两条数据，官方在这里给出了解释。

1.3 CollapsingMergeTree引擎

它和前面的VersionedCollapsingMergeTree引擎类似，但不同在于，它会强制以异步的方式执行折叠。

:) CREATE TABLE vcm_table2
   (
     userid UInt64,
     pv UInt8,
     duration UInt8,
     sign Int8
   ) ENGINE = CollapsingMergeTree(sign)
     ORDER BY (userid, pv);

:) INSERT INTO vcm_table2 VALUES (4324182021466249494, 5, 146, 1);

:) SELECT * FROM vcm_table2;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┐
│ 4324182021466249494 │         5 │      146 │    1 |
└─────────────────────┴───────────┴──────────┴──────┘

当每次用户数据有更新时，它也会提交两行数据（一行旧数据的取消状态，一行新数据的新增状态）。

-- 第一行取消用户的先前状态，它复制已取消状态的所有字段，除了sign
:) INSERT INTO vcm_table2 VALUES (4324182021466249494, 5, 146, -1);

-- 第二行则为当前状态的新数据
:) INSERT INTO vcm_table2 VALUES (4324182021466249494, 7, 178, 1);

:) SELECT * FROM vcm_table2;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         7 │      178 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

继续更新时，再提交两行数据：

:) INSERT INTO vcm_table2 VALUES (4324182021466249494, 7, 178, -1);

:) INSERT INTO vcm_table2 VALUES (4324182021466249494, 9, 162, 1);

:) SELECT * FROM vcm_table2;

┌────────userid───────┬─────pv────┬─duration─┬─sign─┐
│ 4324182021466249494 │         9 │      162 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

执行完INSERT INTO后发现，Clickhouse已经通过异步的方式触发了折叠，而无需再手动执行OPTIMIZE TABLE。

1.4 GraphiteMergeTree引擎

Graphite是一款开源的监控绘图工具，可以实时收集、存储、显示基于时间序列的数据（time series data）。

GraphiteMergeTree引擎就用来保存Graphite数据的引擎，还可以对它进行压缩及汇总。

GraphiteMergeTree引擎的配置参数由服务端配置文件config.xml中的<graphite_rollup_example>标签定义。

因为要演示它的功能就必须同时安装并配置Graphite，因此暂时略过。

1.5 AggregatingMergeTree引擎

从名字就能看出来，它是用来做数据聚合处理的。

官方解释是：AggregatingMergeTree会将一个数据片段内所有具有相同主键（准确的说是排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。

例如，某个用户某个月的订单金额汇总，就是一种聚合。

它使用下面两个函数来处理所有列。

AggregateFunction函数。
SimpleAggregateFunction函数。

创建visits表。

:) CREATE TABLE visits
   (
     id String,
     uid String,
     x SimpleAggregateFunction(sum, UInt64),
     y SimpleAggregateFunction(sum, UInt64),
     z AggregateFunction(avg, UInt64)
   ) ENGINE = AggregatingMergeTree()
     ORDER BY (id, uid)

visits表需要通过INSERT INTO...SELECT的方式来插入数据：

:) INSERT INTO visits SELECT 'a', 'b', 1, 1, avgState(toUInt64(1));
:) INSERT INTO visits SELECT 'c', 'd', 2, 3, avgState(toUInt64(2));
:) INSERT INTO visits SELECT 'e', 'f', 4, 5, avgState(toUInt64(3));
:) INSERT INTO visits SELECT 'g', 'h', 6, 7, avgState(toUInt64(4));

然后查询数据。

:) SELECT id, uid, x, y, finalizeAggregation(z) AS z FROM visits;

┌───id───┬───uid───┬───x───┬───y───┬───z───┐
│   a    │    b    │   1   │   1   │   1   |
│   c    │    d    │   2   │   3   │   2   |
│   e    │    f    │   4   │   5   │   3   |
│   g    │    h    │   6   │   7   │   4   |
└────────┴─────────┴───────┴───────┴───────┘

1.6 自定义分区的MergeTree引擎

这种引擎其实就是在MergeTree引擎的表中加入PARTITION BY修饰。

:) CREATE TABLE orders
   (
     id UInt32,
     sku_id String,
     out_trade_no String,
     amount Decimal(16,2),
     createtime Datetime
   ) ENGINE = MergeTree()
     ORDER BY (id, sku_id)
     PARTITION BY toYYYYMMDD(createtime)
     PRIMARY KEY (id);

插入若干条数据。

:) INSERT INTO orders VALUES
   (1, 'sku_1', '2188546969325752478522', 249.00, '2024-01-01 11:00:00'),
   (2, 'sku_2', '2058783693321200258820', 490.00, '2024-01-01 12:00:00'),
   (3, 'sku_3', '3598621444447856211883', 199.00, '2024-01-02 13:00:00'),
   (4, 'sku_4', '4531215879321454522127', 299.00, '2024-01-02 14:00:00'),
   (5, 'sku_5', '6535878998548921786321', 288.00, '2024-02-01 15:00:00'),
   (6, 'sku_6', '7812546931223585231556', 164.00, '2024-02-01 16:00:00'),
   (7, 'sku_7', '8532645415679788432312', 370.00, '2024-02-02 17:00:00');

-- 在Clickhouse-Client中查询才能看到效果（根据订单创建日期来按天分区）
:) SELECT * FROM orders;

┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  7 │ sku_7  │ 8532645415679788432312 │    370 │ 2024-02-02 17:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  5 │ sku_5  │ 6535878998548921786321 │    288 │ 2024-02-01 15:00:00 │
│  6 │ sku_6  │ 7812546931223585231556 │    164 │ 2024-02-01 16:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  3 │ sku_3  │ 3598621444447856211883 │    199 │ 2024-01-02 13:00:00 │
│  4 │ sku_4  │ 4531215879321454522127 │    299 │ 2024-01-02 14:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘
┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  1 │ sku_1  │ 2188546969325752478522 │    249 │ 2024-01-01 11:00:00 │
│  2 │ sku_2  │ 2058783693321200258820 │    490 │ 2024-01-01 12:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘

表的分区可以在system.parts中查到。

:) SELECT partition
   FROM system.parts
   WHERE table = 'orders'

┌─partition─┐
│ 20240101  │
│ 20240102  │
│ 20240201  │
│ 20240202  │
└───────────┘

一般情况下都会根据时间进行分区，分区字段类型建议是DateTime类型，而不是Long类型。

而且分区粒度不要太细，比如秒级别分区键，否则插入数据时很容易报错。

但是粒度也不能太粗，比如日增上千万记录的，就比较适合用小时来分区。

在批量写入数据时，控制好每个批次的数据中涉及到的分区的数量，因为无序的数据将导致涉及到的分区会很多，所以建议写入之前最好对需要导入的数据进行排序。

1.7 ReplacingMergeTree引擎

这个引擎会删除所有参与排序的键值均相同的重复项，默认是保留最新的那一条。

:) CREATE TABLE orders_repeat
   (
     id UInt32,
     sku_id String,
     out_trade_no String,
     amount Decimal(16,2),
     createtime Datetime
   ) ENGINE = ReplacingMergeTree()
     PARTITION BY toYYYYMMDD(createtime) 
     ORDER BY (id, sku_id)
     PRIMARY KEY (id);

插入若干条数据。

:) INSERT INTO orders_repeat VALUES
   (1, 'sku_1', '2188546969325752478522', 249.00, '2024-01-01 11:00:00'),
   (1, 'sku_1', '2058783693321200258820', 490.00, '2024-01-01 12:00:00'),
   (1, 'sku_1', '3598621444447856211883', 199.00, '2024-01-02 13:00:00'),
   (2, 'sku_4', '4531215879321454522127', 299.00, '2024-01-02 14:00:00'),
   (2, 'sku_4', '6535878998548921786321', 288.00, '2024-02-01 15:00:00'),
   (3, 'sku_6', '7812546931223585231556', 164.00, '2024-02-01 16:00:00'),
   (4, 'sku_7', '8532645415679788432312', 370.00, '2024-02-02 17:00:00'),
   (4, 'sku_8', '9156365787547784752458', 310.00, '2024-02-03 18:00:00');

┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  1 │ sku_1  │ 3598621444447856211883 │    199 │ 2024-01-02 13:00:00 │
│  2 │ sku_4  │ 6535878998548921786321 │    288 │ 2024-02-01 15:00:00 │
│  3 │ sku_6  │ 7812546931223585231556 │    164 │ 2024-02-01 16:00:00 │
│  4 │ sku_7  │ 8532645415679788432312 │    370 │ 2024-02-02 17:00:00 │
│  4 │ sku_8  │ 9156365787547784752458 │    310 │ 2024-02-03 18:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘

注意，是要所有的参与排序的字段完全相同才会被认为是重复。

例如，对于ORDER BY (id, sku_id)语句，如果仅仅只是id相同而sku_id不同，那么是不会被判定为重复记录的，就像sku_8那样。

现在继续插入一条数据。

:) INSERT INTO orders_repeat VALUES (1, 'sku_1', '2188546969325752478522', 100.00, '2024-01-01 11:00:00');

┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  1 │ sku_1  │ 3598621444447856211883 │    199 │ 2024-01-02 13:00:00 │
│  2 │ sku_4  │ 6535878998548921786321 │    288 │ 2024-02-01 15:00:00 │
│  3 │ sku_6  │ 7812546931223585231556 │    164 │ 2024-02-01 16:00:00 │
│  4 │ sku_7  │ 8532645415679788432312 │    370 │ 2024-02-02 17:00:00 │
│  4 │ sku_8  │ 9156365787547784752458 │    310 │ 2024-02-03 18:00:00 │
│  1 │ sku_1  │ 3598621444447856211883 │    100 │ 2024-01-02 13:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘

会发现它并没有删除id和sku_id都相同的项。那是因为Clickhouse的有些引擎的执行是异步的，提交之后并不一定能够立即看到变化。

如果想立即看到提交之后的结果，可以使用下面的方式。

-- 下面两条语句等效
:) OPTIMIZE TABLE orders_repeat;
:) SELECT * FROM  orders_repeat FINAL;

┌─id─┬─sku_id─┬─out_trade_no───────────┬─amount─┬──────────createtime─┐
│  1 │ sku_1  │ 3598621444447856211883 │    100 │ 2024-01-02 13:00:00 │
│  2 │ sku_4  │ 6535878998548921786321 │    288 │ 2024-02-01 15:00:00 │
│  3 │ sku_6  │ 7812546931223585231556 │    164 │ 2024-02-01 16:00:00 │
│  4 │ sku_7  │ 8532645415679788432312 │    370 │ 2024-02-02 17:00:00 │
│  4 │ sku_8  │ 9156365787547784752458 │    310 │ 2024-02-03 18:00:00 │
└────┴────────┴────────────────────────┴────────┴─────────────────────┘

需要注意的是：FINAL关键字并不会删除重复项，只是让查询的结果看起来是执行了一样。

1.8 SummingMergeTree引擎

SummingMergeTree引擎可以实现下面的功能。

Clickhouse会把所有主键相同的行合并为一行。
该行中指定的数值类型的列会被汇总求和为一个新值。
合并后的新行中其他列的值是数据相同的第一行的值。

:) CREATE TABLE order_summtt
   (
	 skuid String,
	 price Decimal(16,2),
     numbers UInt32,
	 createtime Datetime
   ) ENGINE = SummingMergeTree(price)
     ORDER BY skuid
     PRIMARY KEY skuid;

插入数据：

:) INSERT INTO order_summtt VALUES
   ('sku_1', 100.00, 1, '2024-01-01 10:00:00'),
   ('sku_2', 200.00, 2, '2024-01-01 11:00:00'),
   ('sku_3', 300.00, 3, '2024-01-01 12:00:00'),
   ('sku_4', 400.00, 4, '2024-01-01 13:00:00'),
   ('sku_1', 200.00, 7, '2024-01-01 16:00:00'),
   ('sku_2', 200.00, 8, '2024-01-01 17:00:00');

┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_1 │   300 │       1 │ 2024-01-01 10:00:00 │
│ sku_2 │   400 │       2 │ 2024-01-01 11:00:00 │
│ sku_3 │   300 │       3 │ 2024-01-01 12:00:00 │
│ sku_4 │   400 │       4 │ 2024-01-01 13:00:00 │
└───────┴───────┴─────────┴─────────────────────┘

1.9 Replicated*MergeTree系列引擎

这个小类又分为很多不同的子引擎。

ReplicatedMergeTree。
ReplicatedSummingMergeTree。
ReplicatedReplacingMergeTree。
ReplicatedAggregatingMergeTree。
ReplicatedCollapsingMergeTree。
ReplicatedVersionedCollapsingMergeTree。
ReplicatedGraphiteMergeTree。

其实就是把副本管理能力和以上诸多不同的表引擎能力进行了组合。

副本是表级别的，不是整个服务器级的。所以，服务器里可以同时有复制表和非复制表。
副本不依赖于分片，每个分片有它自己的副本。
副本是基于数据的，而非表结构的，所以INSERT和ALTER语句会影响到复制表，而CREATE、DROP、ATTACH、DETACH和RENAME语句则不会。
由于RENAME的关系，所以在不同的服务器上，存储相同数据的表（副本）可能会有不同的名称。

ReplicatedMergeTree的使用在前面分区、分片与副本已经演示过了，就不再重复。

1.10 SharedMergeTree引擎

SharedMergeTree系列引擎是ReplicatedMergeTree引擎优化后的云原生替代方案。

之所以说它是系列引擎，是因为每个特定的MergeTree引擎类型都有对应的SharedMergeTree引擎。例如ReplacingSharedMergeTree替代ReplacingReplicatedMergeTree。

SharedMergeTree需要clickhouse-keeper组件的配合实现通信和元数据存储。

想知道表是哪种引擎，可以通过如下SQL语句查看。

-- 语法
SHOW CREATE TABLE <表名>;

2. Log引擎家族

之所以称其为Log引擎是因为：

它们总是将数据保存在磁盘上。
每一列都存储在单独的压缩文件中，例如id.bin文件。
写入时总像日志那样数据将附加到文件末尾。
不支持突变操作（所谓突变操作通常是指使用ALTER或DROP等语句修改表结构或数据，其实就是修改表结构）。
不支持索引。

2.1 TinyLog引擎

它是Log引擎家族中最简单的表引擎，它只写入一次数据write-once，然后多次读取，只支持单线程读取。

它适用于相对较小的表，官方建议最多100万行，将它用于小批量处理的中间数据。

它对并发操作比较敏感：如果读的时候同时写入，则读取操作将抛出异常，且会破坏数据内容。

它既没有也不支持所谓PRIMARY KEY、PARTITION BY、ORDER BY等子句。

:) CREATE TABLE order_tinylog
   (
	 skuid String,
	 price Decimal(16,2),
     numbers UInt32,
	 createtime Datetime
   ) ENGINE = TinyLog();

插入数据：

:) INSERT INTO order_tinylog VALUES
   ('sku_1', 100.00, 1, '2024-01-01 10:00:00'),
   ('sku_2', 200.00, 2, '2024-01-01 11:00:00'),
   ('sku_3', 300.00, 3, '2024-01-01 12:00:00'),
   ('sku_4', 400.00, 4, '2024-01-01 13:00:00'),
   ('sku_5', 200.00, 5, '2024-01-01 16:00:00'),
   ('sku_6', 200.00, 6, '2024-01-01 17:00:00');

┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_1 │   100 │       1 │ 2024-01-01 10:00:00 │
│ sku_2 │   200 │       2 │ 2024-01-01 11:00:00 │
│ sku_3 │   300 │       3 │ 2024-01-01 12:00:00 │
│ sku_4 │   400 │       4 │ 2024-01-01 13:00:00 │
│ sku_5 │   200 │       5 │ 2024-01-01 16:00:00 │
│ sku_6 │   200 │       6 │ 2024-01-01 17:00:00 │
└───────┴───────┴─────────┴─────────────────────┘

2.2 Log引擎

它和TinyLog引擎的主要不同在于，因为存在指向列日志的标记文件，所以它支持多线程读取。

除此之外与TinyLog没什么不同。

2.3 StripeLog引擎

StripeLog引擎将所有列存储在一个文件中，而不像其他两个那样分开存储。

因为存在标记文件，所以它也支持并发读取。

它并不支持ORDER BY子句，官方文档却说它支持。

:) CREATE TABLE order_stripe_log
   (
	 skuid String,
	 price Decimal(16,2),
     numbers UInt32,
	 createtime Datetime
   ) ENGINE = StripeLog()
     -- 加入ORDER BY子句会报错
     ORDER BY createtime DESC;

每插入一次就会在data.bin创建一个数据块（其他两个不会这样做）。

:) INSERT INTO order_stripe_log VALUES ('sku_1', 100.00, 1, '2024-01-01 10:00:00');
:) INSERT INTO order_stripe_log VALUES ('sku_2', 200.00, 2, '2024-01-01 11:00:00');
:) INSERT INTO order_stripe_log VALUES ('sku_3', 300.00, 3, '2024-01-01 12:00:00');
:) INSERT INTO order_stripe_log VALUES ('sku_4', 400.00, 4, '2024-01-01 13:00:00');
:) INSERT INTO order_stripe_log VALUES ('sku_5', 200.00, 5, '2024-01-01 16:00:00');
:) INSERT INTO order_stripe_log VALUES ('sku_6', 200.00, 6, '2024-01-01 17:00:00');

Clickhouse在查询数据时使用多线程，每个线程会读取单独的数据块并在完成后返回结果行。

这样做的结果是，在大多数情况下，输出中块的顺序和输入时相应块的顺序是不同的。

:) SELECT * FROM order_stripe_log;

┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_2 │   200 │       2 │ 2024-01-01 11:00:00 │
└───────┴───────┴─────────┴─────────────────────┘
┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_3 │   300 │       3 │ 2024-01-01 12:00:00 │
└───────┴───────┴─────────┴─────────────────────┘
┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_1 │   100 │       1 │ 2024-01-01 10:00:00 │
└───────┴───────┴─────────┴─────────────────────┘
┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_3 │   300 │       3 │ 2024-01-01 12:00:00 │
└───────┴───────┴─────────┴─────────────────────┘
┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_6 │   200 │       6 │ 2024-01-01 17:00:00 │
└───────┴───────┴─────────┴─────────────────────┘
┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_5 │   200 │       5 │ 2024-01-01 16:00:00 │
└───────┴───────┴─────────┴─────────────────────┘
┌─skuid─┬─price─┬─numbers─┬──────────createtime─┐
│ sku_4 │   400 │       4 │ 2024-01-01 13:00:00 │
└───────┴───────┴─────────┴─────────────────────┘

3. 集成引擎家族

Clickhouse可以与多种外部系统集成，它使用CREATE TABLE或ALTER TABLE来指定要集成外部系统的引擎。

然后从用户的角度来看，这种是透明的，集成看起来就像查询一个正常的外部表，但对它的查询其实是代理给外部系统的。

3.1 MySQL表引擎

使用已有的环境：Docker部署MySQL。

接下来，在Clickhouse中创建对应的表结构并连接MySQL。

:) CREATE TABLE example_mysql_table (
	-- MySQL表引擎不支持诸如PRIMARY KEY、ORDER BY、PARTITION BY之类的关键字
    id INT,
    name VARCHAR(50)
) ENGINE = MySQL('172.16.185.174:3306', 'itechthink', 'example_mysql_table', 'root', '123456');

或者通过类似于复制MySQL表结构的方式来创建Clickhouse表。

:) CREATE TABLE example_mysql_table ENGINE = TinyLog
   AS
   SELECT *
   FROM
   MySQL('172.16.185.174:3306', 'itechthink', 'example_mysql_table', 'root', '123456');

然后插入若干条数据。

:) INSERT INTO example_mysql_table VALUES (1, '李星云'), (2, '王林');

┌─id─┬─name───┐
│  1 │ 李星云  │
│  2 │ 王麻子  │
└────┴────────┘

最后再到MySQL中验证数据是否已经插入成功。

mysql> USE itechthink;
mysql> SELECT * FROM example_mysql_table;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 李星云     |
|  2 | 王麻子     |
+----+-----------+
2 rows in set (0.00 sec)

也可以直接通过MySQL引擎导入进来，前提是表结构要存在且一致。

:) INSERT INTO example_mysql_table 
   SELECT *
   FROM
   MySQL('172.16.185.174:3306', 'itechthink', 'example_mysql_table', 'root', '123456');

┌─id─┬─name───┐
│  1 │ 李星云  │
│  2 │ 王麻子  │
└────┴────────┘

3.2 ODBC和JDBC表引擎

要使用JDBC和ODBC表引擎，需要下载并安装相关的组件。

官方也给出了比较详细的例子。

先配置好MySQL环境（上节中已有）。
在MySQL中创建表结构。
在Clickhouse中创建对应的表结构。
Clickhouse通过ODBC或JDBC表引擎操作数据。

其他诸如PostgreSQL、MongoDB、SQLite、HDFS和Hive等也都大同小异，只不过HDFS和Hive配置更麻烦一些而已。

3.3 Kafka表引擎

使用已有的环境：Docker部署Kafka。

接下来，在Clickhouse中创建对应的表引擎并连接Kafka。

-- 创建同步表
:) CREATE TABLE kafka_queue
   (
     id UInt32,
     name String
   ) ENGINE = Kafka() SETTINGS 
     kafka_broker_list = '192.168.16.4:9092', 
     kafka_topic_list = 'test', 
     kafka_group_name = 'testGroup', 
     kafka_format = 'JSONEachRow';

:) CREATE TABLE kafka_queue_res
   (
     id UInt32,
     name String
   ) ENGINE = MergeTree()
     ORDER BY tuple();

-- 创建物化视图
:) CREATE MATERIALIZED VIEW kafka_queue_res_mv TO kafka_queue_res AS
   SELECT id, name FROM kafka_queue;

发送Kafka数据。

> docker exec -it <容器ID> bash
/> cd /opt/kafka_2.13-2.8.1/bin
/> sh kafka-console-producer.sh --broker-list localhost:9092 --topic test --producer-property "group.id=testGroup"

# 发送数据
> {"id":1, "name":"itechthink"}

在Clickhouse中查询Kafka发送的数据。

:) SELECT * FROM kafka_queue_res;

┌─────id──┬─────name────┐
│       1 │  itechthink │
└─────────┴─────────────┘

更好的的做法应该是借助Flume采集Kafka数据，然后再同步到Clickhouse。

4. 特定功能引擎家族

在特定引擎中用的比较多的可能就是分布式引擎了。

它的用法在之前的分区、分片与副本已经演示过了，就不再重复。

感谢支持

更多内容，请移步《超级个体》。