ClickHouse使用MySQL数据库引擎
MySQL作为一种关系型数据库管理系统，主要擅长OLTP(在线事务处理)场景，能高效处理增删改查等事务操作，保证数据一致性和可靠性。
而ClickHouse是一种列式存储的OLAP(在线分析处理)数据库，专为高性能分析而设计，能够快速处理大规模数据的复杂分析查询。
两者结合的关联主要体现在：
- 数据同步：可以将MySQL中的事务数据实时同步到ClickHouse中进行分析处理，例如配置MySQL数据表与ClickHouse表的实时同步。
- 优势互补：MySQL处理日常事务操作，ClickHouse处理大规模数据分析，各自发挥所长。
- 一站式HTAP解决方案：通过云平台(如阿里云RDS)的整合，使得用户可以在一个平台上同时获得事务处理和分析处理的能力，简化了运维和管理。
这种组合能够支持各种业务场景：业务报表统计、交互式运营分析、对账以及实时数仓等。数据可以在MySQL中进行事务处理，然后同步到ClickHouse中进行快速分析，从而实现"事务在线处理和在线分析的一体化"。
MySQL 数据库引擎介绍
MySQL 数据库引擎是 ClickHouse 提供的一种集成引擎，这不是指 ClickHouse 本身使用的存储引擎（如 MergeTree），它允许你直接在 ClickHouse 中查询存储在远程 MySQL 服务器上的数据。
核心概念：
- 外部数据源: MySQL 数据库引擎视远程 MySQL 服务器为一个外部数据源。
- 代理查询: 当你查询使用 MySQL 引擎创建的 ClickHouse 数据库或表时，ClickHouse 会将查询（或其一部分）转发给远程 MySQL 服务器执行。
- 数据不存储在 ClickHouse: 使用这个引擎时，数据仍然物理存储在 MySQL 中。ClickHouse 只是充当了一个查询代理或网关。
工作原理：
- 创建连接: 你在 ClickHouse 中创建一个使用 MySQL 引擎的数据库或表，并提供连接到远程 MySQL 服务器所需的信息（主机、端口、数据库名、用户名、密码）。
- 查询执行: 当你在 ClickHouse 中对这个 MySQL 引擎支持的表或数据库执行 SELECT 查询时：
- ClickHouse 解析查询。
- 它将查询中需要从 MySQL 获取数据的部分，尽可能地转换为 MySQL 兼容的 SQL 语句。
- ClickHouse 连接到远程 MySQL 服务器。
- 它将转换后的 SQL 查询发送给 MySQL 执行。
- MySQL 执行查询并返回结果集给 ClickHouse。
- ClickHouse 接收数据，并在需要时进行后续处理（例如，与其他 ClickHouse 本地表进行 JOIN，或者进行 ClickHouse 支持的聚合/函数计算）。
主要用途和优势：
- 数据联合查询 (Data Federation): 可以在单个 ClickHouse 查询中，联合查询 ClickHouse 本地表和远程 MySQL 表的数据。这对于需要整合分析来自不同系统的数据非常有用。
- 平滑迁移/数据探索: 在将数据完全迁移到 ClickHouse 之前，可以使用 ClickHouse 强大的分析能力来查询和分析现有的 MySQL 数据。
- 简化 ETL: 对于某些简单的只读场景，可以避免构建复杂的数据抽取、转换、加载（ETL）流程，直接查询 MySQL。
- 利用 ClickHouse 的查询功能: 可以利用 ClickHouse 的 SQL 方言和函数来处理从 MySQL 获取的数据（注意：数据的 获取 速度受限于 MySQL 和网络）。
如何使用：
有两种主要的使用方式：
方式一：创建 MySQL 数据库引擎的数据库 (推荐)
这种方式更方便，ClickHouse 会自动映射指定 MySQL 数据库中的所有表。
|
1
2
|
CREATE DATABASE mysql_db_alias -- ClickHouse 中的数据库别名
ENGINE = MySQL('mysql_host:port', 'mysql_database_name', 'mysql_user', 'mysql_password');
|
- mysql_db_alias: 你在 ClickHouse 中为这个远程 MySQL 数据库起的名称。
- mysql_host:port: MySQL 服务器的主机名和端口（例如 ‘localhost:3306’ 或 ‘mysql.example.com:3306’）。
- mysql_database_name: 要连接的 MySQL 数据库的名称。
- mysql_user: 连接 MySQL 的用户名。
- mysql_password: 连接 MySQL 的密码。
创建成功后，你可以像查询普通 ClickHouse 数据库一样查询它：
|
1
2
3
4
5
6
7
8
9
10
|
-- 查看 MySQL 数据库中的表
SHOW TABLES FROM mysql_db_alias;
-- 查询 MySQL 中的某个表
SELECT * FROM mysql_db_alias.some_mysql_table WHERE condition LIMIT 10;
-- 与 ClickHouse 本地表进行 JOIN
SELECT c.data, m.name
FROM clickhouse_local_table AS c
JOIN mysql_db_alias.users AS m ON c.user_id = m.id;
|
方式二：创建 MySQL 引擎的表
这种方式允许你只映射 MySQL 中的单个表到 ClickHouse 中。你需要显式定义 ClickHouse 表的结构，并且这个结构需要与 MySQL 中的表结构兼容。
|
1
2
3
4
5
6
7
8
9
|
CREATE TABLE mysql_table_alias -- ClickHouse 中的表别名
(
-- 定义列名和 ClickHouse 数据类型，需要与 MySQL 表兼容
id UInt64,
name String,
created_at DateTime
-- ... 其他列
)
ENGINE = MySQL('mysql_host:port', 'mysql_database_name', 'actual_mysql_table_name', 'mysql_user', 'mysql_password');
|
- mysql_table_alias: 你在 ClickHouse 中为这个远程 MySQL 表起的名称。
- 列定义：你需要定义列名和 ClickHouse 的数据类型。ClickHouse 会尝试进行类型映射，但最好确保类型兼容（例如 MySQL INT -> ClickHouse Int32 或 UInt32，VARCHAR -> String，DATETIME -> DateTime）。
- actual_mysql_table_name: 远程 MySQL 中实际的表名。
- 其他参数同上。
查询方式：
|
1
|
SELECT * FROM mysql_table_alias WHERE id > 100;
|
场景演示示例
准备一台Linux服务器，以Ubuntu 22.04为例，已安装docker和docker-compose环境。
目标场景:
- 运行一个 MySQL 容器，并创建一个示例数据库和表。
- 运行一个 ClickHouse 容器。
- 在 ClickHouse 中，创建一个指向 MySQL 容器中数据库的 MySQL 数据库引擎实例。
- 能够通过 ClickHouse 查询 MySQL 中的数据。
1. 创建项目目录结构:
在你喜欢的位置创建一个项目文件夹，例如 ch_mysql_demo。
|
1
2
|
mkdir ch_mysql_demo
cd ch_mysql_demo
|
2. 创建 docker-compose.yml 文件:
在 ch_mysql_demo 目录下创建 docker-compose.yml 文件，内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
name: 'ch_mysql_demo'
services:
mysql-server:
image: mysql:8.0 # 使用 MySQL 8.0 镜像
container_name: mysql-server
hostname: mysql-server # 容器主机名，ClickHouse 将使用它连接
restart: always
environment:
MYSQL_ROOT_PASSWORD: TestPassword!@#$ # 设置 root 密码 (生产环境请使用更安全的方式)
MYSQL_DATABASE: demo_db # 创建一个名为 demo_db 的数据库
MYSQL_USER: demo_user # 创建一个用户
MYSQL_PASSWORD: UserPassword123 # 设置用户的密码 (生产环境请使用更安全的方式)
volumes:
- mysql_data:/var/lib/mysql # 持久化 MySQL 数据
- ./mysql_init:/docker-entrypoint-initdb.d # 挂载初始化脚本目录
ports:
- "3306:3306" # 将 MySQL 端口映射到宿主机，方便调试
networks:
- ch_mysql_network
clickhouse-server:
image: clickhouse/clickhouse-server:latest # 使用最新的 ClickHouse 镜像
container_name: clickhouse-server
hostname: clickhouse-server
restart: always
ports:
- "8123:8123" # ClickHouse HTTP 接口
- "9000:9000" # ClickHouse 原生 TCP 接口
volumes:
- clickhouse_data:/var/lib/clickhouse/ # 持久化 ClickHouse 数据
- clickhouse_logs:/var/log/clickhouse-server/ # 持久化 ClickHouse 日志
networks:
- ch_mysql_network
depends_on:
- mysql-server # 确保 MySQL 容器先启动
ulimits: # 推荐为 ClickHouse 提高文件描述符限制
nofile:
soft: 262144
hard: 262144
volumes:
mysql_data: # Docker 管理的数据卷
clickhouse_data:
clickhouse_logs:
networks:
ch_mysql_network: # 自定义桥接网络，让容器可以通过服务名通信
driver: bridge
|
3. 创建 MySQL 初始化脚本 (可选但推荐):
为了方便演示，我们可以在 MySQL 启动时自动创建一些示例数据。在 ch_mysql_demo 目录下创建一个名为 mysql_init 的子目录，并在其中创建一个 .sql 文件，例如 init.sql：
创建 mysql_init/init.sql 文件，内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
-- 这个脚本会在 MySQL 容器第一次启动时自动执行
-- 使用我们通过环境变量创建的数据库
USE demo_db;
-- 创建一个示例表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入一些示例数据
INSERT INTO products (name, price) VALUES
('Laptop', 1200.50),
('Mouse', 25.00),
('Keyboard', 75.99),
('Monitor', 300.00);
-- 可以添加更多表和数据...
-- 确保 demo_user 对 demo_db 有权限 (通常环境变量创建用户时会自动授权，但显式添加更保险)
-- GRANT ALL PRIVILEGES ON demo_db.* TO 'demo_user'@'%'; -- 在某些 MySQL 镜像版本可能需要
-- FLUSH PRIVILEGES;
|
4. 启动容器:
在 ch_mysql_demo 目录下运行：
这会以后台模式下载镜像（如果本地没有）并启动 MySQL 和 ClickHouse 容器。MySQL 会运行初始化脚本。
查看运行的容器
|
1
2
3
4
5
|
root@localhost:~/ch_mysql_demo# docker compose ps
NAME IMAGE COMMAND SERVICE CREATED STATUS PORTS
clickhouse-server clickhouse/clickhouse-server:latest "/entrypoint.sh" clickhouse-server 14 minutes ago Up 14 minutes 0.0.0.0:8123->8123/tcp, :::8123->8123/tcp, 0.0.0.0:9000->9000/tcp, :::9000->9000/tcp, 9009/tcp
mysql-server mysql:8.0 "docker-entrypoint.s…" mysql-server 14 minutes ago Up 14 minutes 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp
root@localhost:~/ch_mysql_demo#
|
5. 在 ClickHouse 中连接 MySQL:
等待几秒钟让 MySQL 完全启动并初始化。然后，连接到 ClickHouse 容器：
|
1
|
docker exec -it clickhouse-server clickhouse-client
|
进入 ClickHouse 命令行后，执行以下 SQL 命令来创建 MySQL 数据库引擎：
|
1
2
3
4
5
6
7
|
-- 创建一个 ClickHouse 数据库，它映射到远程 MySQL 的 demo_db
CREATE DATABASE mysql_remote_db
ENGINE = MySQL('mysql-server:3306', 'demo_db', 'demo_user', 'UserPassword123');
--^-- MySQL 容器的服务名和端口
--^-- MySQL 数据库名
--^-- MySQL 用户名
--^-- MySQL 密码 (与 docker-compose 中设置的一致)
|
6. 通过 ClickHouse 查询 MySQL 数据:
现在你可以像查询 ClickHouse 本地数据库一样查询 mysql_remote_db 了：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
-- 查看 MySQL 数据库中的表 (通过 ClickHouse)
SHOW TABLES FROM mysql_remote_db;
/* 预期输出类似:
┌─name─────┐
│ products │
└──────────┘
*/
-- 查询 MySQL 的 products 表
SELECT * FROM mysql_remote_db.products;
/* 预期输出:
┌─id─┬─name─────┬──price─┬──────────created_at─┐
│ 1 │ Laptop │ 1200.50 │ 2023-10-27 10:00:00 │
│ 2 │ Mouse │ 25.00 │ 2023-10-27 10:00:00 │
│ 3 │ Keyboard │ 75.99 │ 2023-10-27 10:00:00 │
│ 4 │ Monitor │ 300.00 │ 2023-10-27 10:00:00 │
└────┴──────────┴─────────┴─────────────────────┘
(created_at 时间会是你运行时的实际时间)
*/
-- 使用 ClickHouse 的函数处理来自 MySQL 的数据
SELECT
name,
round(toDecimal64(price, 2)) AS rounded_price,
upper(name) AS upper_name
FROM mysql_remote_db.products
WHERE toDecimal64(price, 2) > 50;
/* 预期输出:
┌─name─────┬─rounded_price─┬─upper_name─┐
│ Laptop │ 1201 │ LAPTOP │
│ Keyboard │ 76 │ KEYBOARD │
│ Monitor │ 300 │ MONITOR │
└──────────┴───────────────┴────────────┘
*/
-- 退出 ClickHouse 客户端
exit;
|
7. 清理环境:
当你完成实验后，可以停止并移除容器、网络和数据卷：
-v 参数会同时删除关联的数据卷（mysql_data, clickhouse_data, clickhouse_logs），如果你想保留数据，则不加 -v。
这个 docker-compose 文件和相关步骤提供了一个基础的 ClickHouse + MySQL 集成环境，可以在此基础上进行更复杂的查询和实验。在生产环境中使用更安全的密码管理方式（例如 Docker Secrets 或环境变量注入）。
