4、clickhouse的Log系列表引擎、外部集成表引擎和其他特殊的表引擎介绍及使用

ClickHouse系列文章

1、ClickHouse介绍
2、clickhouse安装与简单验证（centos）
3、ClickHouse表引擎-MergeTree引擎
4、clickhouse的Log系列表引擎、外部集成表引擎和其他特殊的表引擎介绍及使用
5、ClickHouse查看数据库容量、表的指标、表分区、数据大小等

---

文章目录

• ClickHouse系列文章
• 一、Log系列表引擎
• • 1、应用场景
  • 2、Log系列表引擎的特点
  • • 1）、共性特点
    • 2）、区别
  • 3、TinyLog表引擎使用
  • 4、StripLog表引擎使用
  • 5、Log表引擎使用
• 二、外部集成表引擎
• • 1、HDFS使用方式
  • 2、MySQL使用方式
  • 3、JDBC使用方式
  • 4、Kafka使用方式
• 三、其他特殊的表引擎
• • 1、Memory表引擎
  • 2、Distributed表引擎

---

本文主要介绍Log系列表引擎、外部集成表引擎和其他特殊的表引擎介绍及使用。
本文使用前提参考该系列文章中的部署与验证。
本文主要分为三部分，即Log系列表引擎、外部集成表引擎和其他特殊的表引擎介绍。

Clickhouse是一个高性能且开源的数据库管理系统，主要用于在线分析处理(OLAP)业务。它采用列式存储结构，可使用SQL语句实时生成数据分析报告，另外它还支持索引，分布式查询以及近似计算等特性，凭借其优异的表现，ClickHouse在各大互联网公司均有广泛地应用。

官网：https://clickhouse.com/
中文官网：https://clickhouse.com/docs/zh

一、Log系列表引擎

1、应用场景

Log系列表引擎功能相对简单，主要用于快速写入小表(1百万行左右的表)，然后全部读出的场景。即一次写入多次查询。

2、Log系列表引擎的特点

1）、共性特点

• 数据存储在磁盘上
• 当写数据时，将数据追加到文件的末尾
• 不支持并发读写，当向表中写入数据时，针对这张表的查询会被阻塞，直至写入动作结束
• 不支持索引
• 不支持原子写：如果某些操作(异常的服务器关闭)中断了写操作，则可能会获得带有损坏数据的表
• 不支持ALTER操作(这些操作会修改表设置或数据，比如delete、update等等)

2）、区别

• TinyLog是Log系列引擎中功能简单、性能较低的引擎。它的存储结构由数据文件和元数据两部分组成。其中，数据文件是按列独立存储的，也就是说每一个列字段都对应一个文件。除此之外，TinyLog不支持并发数据读取。
• StripLog支持并发读取数据文件，当读取数据时，ClickHouse会使用多线程进行读取，每个线程处理一个单独的数据块。另外，StripLog将所有列数据存储在同一个文件中，减少了文件的使用数量。
• Log支持并发读取数据文件，当读取数据时，ClickHouse会使用多线程进行读取，每个线程处理一个单独的数据块。Log引擎会将每个列数据单独存储在一个独立文件中。

3、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);

进入默认数据存储目录，查看数据存储形式，可以看出：TinyLog引擎表每一列都对应的文件。

## 查看sizes.json数据
## 在sizes.json文件内使用JSON格式记录了每个．bin文件内对应的数据大小的信息

当我们执行ALTER操作时会报错，说明该表引擎不支持ALTER操作

-- 以下操作会报错：
-- DB::Exception: Mutations are not supported by storage TinyLog.
ALTER TABLE emp_tinylog DELETE WHERE emp_id = 5;
ALTER TABLE emp_tinylog UPDATE age = 30 WHERE emp_id = 4;

4、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);
-- 查询数据
-- 由于是分两次插入数据，所以查询时会有两个数据块

进入默认数据存储目录，查看底层数据存储形式

可以看出StripeLog表引擎对应的存储结构包括三个文件：

• data.bin：数据文件，所有的列字段使用同一个文件保存，它们的数据都会被写入data.bin。

• index.mrk：数据标记，保存了数据在data.bin文件中的位置信息(每个插入数据块对应列的offset)，利用数据标记能够使用多个线程，以并行的方式读取data.bin内的压缩数据块，从而提升数据查询的性能。

• sizes.json：元数据文件，记录了data.bin和index.mrk大小的信息

• 注意

StripeLog引擎将所有数据都存储在了一个文件中，对于每次的INSERT操作，ClickHouse会将数据块追加到表文件的末尾
StripeLog引擎同样不支持ALTER UPDATE 和ALTER DELETE 操作

5、Log表引擎使用

Log引擎表适用于临时数据，一次性写入、测试场景。Log引擎结合了TinyLog表引擎和StripeLog表引擎的长处，是Log系列引擎中性能最高的表引擎。

CREATE TABLE emp_log (
  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);

-- 查询数据
-- 由于是分两次插入数据，所以查询时会有两个数据块

进入默认数据存储目录，查看底层数据存储形式

Log引擎的存储结构包含三部分：

• 列.bin：数据文件，数据文件按列单独存储
• __marks.mrk：数据标记，统一保存了数据在各个.bin文件中的位置信息。利用数据标记能够使用多个线程，以并行的方式读取。.bin内的压缩数据块，从而提升数据查询的性能。
• sizes.json：记录了.bin和__marks.mrk大小的信息
• 注意

Log表引擎会将每一列都存在一个文件中，对于每一次的INSERT操作，都会对应一个数据块。

二、外部集成表引擎

ClickHouse提供了许多与外部系统集成的方法，包括一些表引擎。这些表引擎与其他类型的表引擎类似，可以用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

例如直接读取HDFS的文件或者MySQL数据库的表。这些表引擎只负责元数据管理和数据查询，而它们自身通常并不负责数据的写入，数据文件直接由外部系统提供。

外部集成的表引擎，目前支持的有：

1、HDFS使用方式

更多信息点击链接：外部表HDFS使用

ENGINE = HDFS(URI, format)
-- URI：HDFS文件路径
-- format：文件格式，比如CSV、JSON、TSV等

-- 示例
CREATE TABLE hdfs_engine_table (name String, value UInt32) ENGINE=HDFS('hdfs://hdfs1:9000/other_storage', 'TSV')

INSERT INTO hdfs_engine_table VALUES ('one', 1), ('two', 2), ('three', 3)
SELECT * FROM hdfs_engine_table LIMIT 2
┌─name─┬─value─┐
│ one  │     1 │
│ two  │     2 │
└──────┴───────┘

注意：

• 读取和写入可以并行
• 不支持:

ALTER 和 SELECT…SAMPLE 操作
索引
复制

• 路径中的通配符

多个路径组件可以具有 globs。 对于正在处理的文件应该存在并匹配到整个路径模式。 文件列表的确定是在 SELECT 的时候进行（而不是在 CREATE 的时候）。

*替代任何数量的任何字符，除了 / 以及空字符串。
?代替任何单个字符.
{some_string,another_string,yet_another_one} — 替代任何字符串 ‘some_string’, ‘another_string’, ‘yet_another_one’.
{N…M}替换 N 到 M范围内的任何数字，包括两个边界的值.

2、MySQL使用方式

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],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);
-- 注意：对于MySQL表引擎，不支持UPDATE和DELETE操作，比如执行下面命令时，会报错：
-- 执行更新
ALTER TABLE mysql_engine_table UPDATE name = 'hanmeimei' WHERE id = 1;
-- 执行删除
ALTER TABLE mysql_engine_table DELETE WHERE id = 1;
-- 报错
DB::Exception: Mutations are not supported by storage MySQL.

3、JDBC使用方式

JDBC表引擎不仅可以对接MySQL数据库，还能够与PostgreSQL等关系型数据库。为了实现JDBC连接，ClickHouse使用了clickhouse-jdbc-bridge的查询代理服务。

• 首先下载clickhouse-jdbc-bridge，然后按照ClickHouse的github中的步骤进行编译，编译完成之后会有一个clickhouse-jdbc-bridge-1.0.jar的jar文件。

• 除了需要该文件之外，还需要JDBC的驱动文件。本文使用的是MySQL，所以还需要下载MySQL驱动包。将MySQL的驱动包和clickhouse-jdbc-bridge-1.0.jar文件放在了/opt/softwares路径下，执行如下命令：

[root@server2 softwares]# java -jar clickhouse-jdbc-bridge-1.0.jar  --driver-path .  --listen-host server2
# 其中--driver-path是MySQL驱动的jar所在的路径，listen-host是代理服务绑定的主机。默认情况下，绑定的端口是：9019。

• 然后再配置/etc/clickhouse-server/config.xml，在文件中添加如下配置，然后重启服务。

<jdbc_bridge>
    <host>server2</host>
    <port>9019</port>
</jdbc_bridge>

4、Kafka使用方式

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'host:port',
    kafka_topic_list = 'topic1,topic2,...',
    kafka_group_name = 'group_name',
    kafka_format = 'data_format'[,]
    [kafka_row_delimiter = 'delimiter_symbol',]
    [kafka_schema = '',]
    [kafka_num_consumers = N,]
    [kafka_max_block_size = 0,]
    [kafka_skip_broken_messages = N,]
    [kafka_commit_every_batch = 0,]
    [kafka_thread_per_consumer = 0]
-- kafka_broker_list ：逗号分隔的brokers地址 (localhost:9092).
-- kafka_topic_list ：Kafka 主题列表，多个主题用逗号分隔.
-- kafka_group_name ：消费者组.
-- kafka_format – Message format. 比如JSONEachRow、JSON、CSV等等

当我们一旦查询完毕之后，ClickHouse会删除表内的数据，其实Kafka表引擎只是一个数据管道，我们可以通过物化视图的方式访问Kafka中的数据。
一般常见的使用方式如下：

• 首先创建一张Kafka表引擎的表，用于从Kafka中读取数据
• 然后再创建一张普通表引擎的表，比如MergeTree，面向终端用户使用
• 最后创建物化视图，用于将Kafka引擎表实时同步到终端用户所使用的表中

三、其他特殊的表引擎

其中含有很多种比较特殊的表引擎，当前支持的有：

1、Memory表引擎

Memory表引擎直接将数据保存在内存中，数据既不会被压缩也不会被格式转换。当ClickHouse服务重启的时候，Memory表内的数据会全部丢失。一般在测试时使用。

CREATE TABLE table_memory (
    id UInt64,
    name String
  ) ENGINE = Memory();

2、Distributed表引擎

Distributed表引擎是分布式表的代名词，它自身不存储任何数据，数据都分散存储在某一个分片上，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作。
一张分布式表底层会对应多个本地分片数据表，由具体的分片表存储数据，分布式表与分片表是一对多的关系。
Distributed表引擎的定义形式如下所示

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

-- 各个参数的含义分别如下：
-- cluster_name：集群名称，与集群配置中的自定义名称相对应。
-- database_name：数据库名称
-- table_name：表名称
-- sharding_key：可选的，用于分片的key值，在数据写入的过程中，分布式表会依据分片key的规则，将数据分布到各个节点的本地表。

创建分布式表是读时检查的机制，也就是说对创建分布式表和本地表的顺序并没有强制要求。
同样值得注意的是，在上面的语句中使用了ON CLUSTER分布式DDL，这意味着在集群的每个分片节点上，都会创建一张Distributed表，这样便可以从其中任意一端发起对所有分片的读、写请求。

以上简单介绍了CH的其他几种系列的表引擎，更多信息查看官网。