ClickHouse学习笔记七其他常见类型表引擎

“Everything is table（万物皆为表）” 是 ClickHouse 一个非常有意思的设计思路。数据表是它与外部进行交互的接口层，无论背后连接的是本地文件、HDFS、Zookeeper 还是其他服务，终端用户始终只需面对数据表，使用 SQL 查询语言。

1.1 外部存储类型

直接从其他存储系统读取数据，只负责元数据管理和数据查询，通常不负责数据的写入。

1.1.1 HDFS

ENGINE = HDFS(hdfs_uri, format)

示例：

CREATE TABLE hdfs_table1 (
    id UInt32,
    code String,
    name String
) ENGINE = HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table1', 'CSV');

hdfs_uri 支持通配符：

• *：匹配所有字符

• ?：匹配单个字符

• {M..N}：数字区间

1.1.2 MySQL

ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])

示例：

CREATE TABLE dolphin_scheduler_table (
    id UInt32,
    name String
) ENGINE = MySQL('10.37.129.2:3306', 'escheduler', 't_escheduler_process_definition', 'root', '');

配合物化视图使用，可实时同步 MySQL 数据到本地 MergeTree。不支持 UPDATE/DELETE。

1.1.3 JDBC

需要依赖 clickhouse-jdbc-bridge 代理服务。

ENGINE = JDBC('jdbc:url', 'database', 'table')

示例：

CREATE TABLE t_ds_process_definition (
    id Int32,
    name String
) ENGINE = JDBC('jdbc:postgresql://ip:5432/dolphinscheduler?user=test&password=test', '', 't_ds_process_definition');

1.1.4 Kafka

Kafka 表引擎充当数据管道，与物化视图 + MergeTree 配合使用。

CREATE TABLE kafka_queue (
    id UInt32,
    code String,
    name String
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'hdp1.nauu.com:6667',
    kafka_topic_list = 'sales-queue',
    kafka_group_name = 'chgroup',
    kafka_format = 'JSONEachRow',
    kafka_skip_broken_messages = 100;

正确的使用方式（管道模式）：

-- 1. Kafka 表（数据管道）
CREATE TABLE kafka_queue (...) ENGINE = Kafka() SETTINGS ...;
​
-- 2. 本地表（查询表）
CREATE TABLE kafka_table (id UInt32, code String, name String)
ENGINE = MergeTree() ORDER BY id;
​
-- 3. 物化视图（同步）
CREATE MATERIALIZED VIEW consumer TO kafka_table
AS SELECT id, code, name FROM kafka_queue;

关键配置参数：

• kafka_num_consumers：消费者数量，默认 1

• kafka_skip_broken_messages：允许跳过的解析错误数据行数

• kafka_commit_every_batch：commit 频率（0=整 Block，1=每 Batch）

• stream_poll_timeout_ms：拉取间隔，默认 500ms

• stream_flush_interval_ms：刷新间隔，默认 7500ms

1.1.5 File

直接读取本地文件数据。

ENGINE = File(format)

数据文件保存在 <ch-path>/data/default/[table_name]/data.[format]。

支持自动创建和手动挂载两种方式。

1.2 内存类型

数据从内存中直接访问，查询性能好但受内存限制。

1.2.1 Memory

数据保存在内存中，服务重启后全部丢失。支持并行查询，亿级数据量以内性能与 MergeTree 相当。

CREATE TABLE memory_1 (id UInt64) ENGINE = Memory();

在 ClickHouse 内部，Memory 表作为集群间分发数据的存储载体（如分布式 IN 查询）。

1.2.2 Set

只能间接作为 IN 查询的右侧条件使用，不能直接 SELECT。

CREATE TABLE set_1 (id UInt8) ENGINE = Set();
INSERT INTO set_1 SELECT number FROM numbers(10);
-- 正确用法：
SELECT arrayJoin([1, 2, 3]) AS a WHERE a IN set_1;

1.2.3 Join

专门为 JOIN 查询设计的表引擎。

ENGINE = Join(join_strictness, join_type, key1[, key2, ...])

示例：

CREATE TABLE id_join_tb1 (
    id UInt8,
    price UInt32,
    time DateTime
) ENGINE = Join(ANY, LEFT, id);
​
SELECT id, name, price FROM join_tb1 LEFT JOIN id_join_tb1 USING (id);

也可通过 joinGet 函数访问：

SELECT joinGet('id_join_tb1', 'price', toUInt8(1));

1.2.4 Buffer

充当数据写入的缓冲区，满足阈值条件时自动刷新到目标表。

ENGINE = Buffer(database, table, num_layers,
    min_time, max_time,
    min_rows, max_rows,
    min_bytes, max_bytes)

示例：

CREATE TABLE buffer_to_memory_1 AS memory_1
ENGINE = Buffer(default, memory_1, 16, 10, 100, 10000, 1000000, 10000000, 100000000);

刷新条件（满足任意一个即触发）：

• 所有最小阈值都已满足

• 至少有一个最大阈值满足

• 单次写入数据行数 > max_rows 或 > max_bytes

1.3 日志类型

适用于数据量小（100 万以下）、查询简单、一次写入多次查询的场景。不支持索引和分区，不支持并发读写。

1.3.1 TinyLog

按列独立存储，每个字段一个 .bin 文件 + sizes.json 元数据。

CREATE TABLE tinylog_1 (id UInt64, code UInt64) ENGINE = TinyLog();

1.3.2 StripeLog

3 个固定文件：data.bin（所有列）、index.mrk（标记）、sizes.json。

CREATE TABLE spripelog_1 (id UInt64, price Float32) ENGINE = StripeLog();

1.3.3 Log

按列独立存储 + 统一标记文件 __marks.mrk + sizes.json。性能最高的日志引擎。

CREATE TABLE log_1 (id UInt64, code UInt64) ENGINE = Log();

1.4 接口类型

自身不存储任何数据，像黏合剂一样整合其他数据表。

1.4.1 Merge

合并多个同结构数据表的查询结果。

ENGINE = Merge(database, table_name)  -- table_name 支持正则表达式
​
CREATE TABLE test_table_all AS test_table_2018
ENGINE = Merge(currentDatabase(), '^test_table_');

查询时可通过虚拟字段 _table 过滤数据来源。

1.4.2 Dictionary

数据字典的代理封装，通过表查询字典。

CREATE TABLE tb_test_flat_dict (
    id UInt64, code String, name String
) ENGINE = Dictionary(test_flat_dict);

使用 Dictionary 引擎创建数据库，自动为每个字典创建代理表：

CREATE DATABASE test_dictionaries ENGINE = Dictionary;

1.4.3 Distributed

分布式表的透明代理，详见第 10 章。

1.5 其他类型

1.5.1 Live View

类似事件监听器，对 SQL 查询结果进行准实时监听。

CREATE TABLE origin_table1 (id UInt64) ENGINE = Log;
CREATE LIVE VIEW lv_origin AS SELECT COUNT(*) FROM origin_table1;
WATCH lv_origin;

1.5.2 Null

类似 Unix 的 /dev/null。写入数据自动忽略，查询返回空表。

CREATE TABLE null_table1 (id UInt8) ENGINE = Null;

常与物化视图搭配使用：数据写入 Null 表 → 同步到物化视图 → Null 表本身不保留数据。

1.5.3 URL

等价于 HTTP 客户端，通过 HTTP/HTTPS 访问远端 REST 服务。

ENGINE = URL('url', format)
​
CREATE TABLE url_table (name String)
ENGINE = URL('http://client1.nauu.com:3000/users', JSONEachRow);

SELECT 转换为 GET 请求，INSERT 转换为 POST 请求。

1.6 本章小结