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：
|
1 |
mkdir mysql_init |
创建 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 目录下运行：
|
1 |
docker-compose up -d |
这会以后台模式下载镜像（如果本地没有）并启动 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. 清理环境:
当你完成实验后，可以停止并移除容器、网络和数据卷：
|
1 |
docker-compose down -v |
-v 参数会同时删除关联的数据卷（mysql_data, clickhouse_data, clickhouse_logs），如果你想保留数据，则不加 -v。
这个 docker-compose 文件和相关步骤提供了一个基础的 ClickHouse + MySQL 集成环境，可以在此基础上进行更复杂的查询和实验。在生产环境中使用更安全的密码管理方式（例如 Docker Secrets 或环境变量注入）。
