ClickHouse使用MySQL数据库引擎的实现
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">ClickHouse使用MySQL数据库引擎</a></li><li><a href="#_label1">MySQL 数据库引擎介绍</a></li><li><a href="#_label2">场景演示示例</a></li></ul></div><p class="maodian"><a name="_label0"></a></p><h2>ClickHouse使用MySQL数据库引擎</h2><p>MySQL作为一种关系型数据库管理系统，主要擅长OLTP(在线事务处理)场景，能高效处理增删改查等事务操作，保证数据一致性和可靠性。</p>
<p>而ClickHouse是一种列式存储的OLAP(在线分析处理)数据库，专为高性能分析而设计，能够快速处理大规模数据的复杂分析查询。</p>
<p>两者结合的关联主要体现在：</p>
<ol><li><strong>数据同步</strong>：可以将MySQL中的事务数据实时同步到ClickHouse中进行分析处理，例如配置MySQL数据表与ClickHouse表的实时同步。</li><li><strong>优势互补</strong>：MySQL处理日常事务操作，ClickHouse处理大规模数据分析，各自发挥所长。</li><li><strong>一站式HTAP解决方案</strong>：通过云平台(如阿里云RDS)的整合，使得用户可以在一个平台上同时获得事务处理和分析处理的能力，简化了运维和管理。</li></ol>
<p>这种组合能够支持各种业务场景：业务报表统计、交互式运营分析、对账以及实时数仓等。数据可以在MySQL中进行事务处理，然后同步到ClickHouse中进行快速分析，从而实现"事务在线处理和在线分析的一体化"。</p>
<p class="maodian"><a name="_label1"></a></p><h2>MySQL 数据库引擎介绍</h2>
<p>MySQL 数据库引擎是 ClickHouse 提供的一种集成引擎，这不是指 ClickHouse 本身使用的存储引擎（如 MergeTree），它允许你直接在 ClickHouse 中查询存储在远程 MySQL 服务器上的数据。</p>
<p><strong>核心概念：</strong></p>
<ul><li><strong>外部数据源:</strong> MySQL 数据库引擎视远程 MySQL 服务器为一个外部数据源。</li><li><strong>代理查询:</strong> 当你查询使用 MySQL 引擎创建的 ClickHouse 数据库或表时，ClickHouse 会将查询（或其一部分）转发给远程 MySQL 服务器执行。</li><li><strong>数据不存储在 ClickHouse:</strong> 使用这个引擎时，数据<strong>仍然</strong>物理存储在 MySQL 中。ClickHouse 只是充当了一个查询代理或网关。</li></ul>
<p><strong>工作原理：</strong></p>
<ol><li><strong>创建连接:</strong> 你在 ClickHouse 中创建一个使用 <code>MySQL</code> 引擎的数据库或表，并提供连接到远程 MySQL 服务器所需的信息（主机、端口、数据库名、用户名、密码）。</li><li><strong>查询执行:</strong> 当你在 ClickHouse 中对这个 MySQL 引擎支持的表或数据库执行 <code>SELECT</code> 查询时：<ul><li>ClickHouse 解析查询。</li><li>它将查询中需要从 MySQL 获取数据的部分，尽可能地转换为 MySQL 兼容的 SQL 语句。</li><li>ClickHouse 连接到远程 MySQL 服务器。</li><li>它将转换后的 SQL 查询发送给 MySQL 执行。</li><li>MySQL 执行查询并返回结果集给 ClickHouse。</li><li>ClickHouse 接收数据，并在需要时进行后续处理（例如，与其他 ClickHouse 本地表进行 <code>JOIN</code>，或者进行 ClickHouse 支持的聚合/函数计算）。</li></ul></li></ol>
<p><strong>主要用途和优势：</strong></p>
<ol><li><strong>数据联合查询 (Data Federation):</strong> 可以在单个 ClickHouse 查询中，联合查询 ClickHouse 本地表和远程 MySQL 表的数据。这对于需要整合分析来自不同系统的数据非常有用。</li><li><strong>平滑迁移/数据探索:</strong> 在将数据完全迁移到 ClickHouse 之前，可以使用 ClickHouse 强大的分析能力来查询和分析现有的 MySQL 数据。</li><li><strong>简化 ETL:</strong> 对于某些简单的只读场景，可以避免构建复杂的数据抽取、转换、加载（ETL）流程，直接查询 MySQL。</li><li><strong>利用 ClickHouse 的查询功能:</strong> 可以利用 ClickHouse 的 SQL 方言和函数来处理从 MySQL 获取的数据（注意：数据的 获取 速度受限于 MySQL 和网络）。</li></ol>
<p><strong>如何使用：</strong></p>
<p>有两种主要的使用方式：</p>
<p><strong>方式一：创建 MySQL 数据库引擎的数据库 (推荐)</strong></p>
<p>这种方式更方便，ClickHouse 会自动映射指定 MySQL 数据库中的所有表。</p>
<div class="jb51code"><pre class="brush:sql;">CREATE DATABASE mysql_db_alias -- ClickHouse 中的数据库别名
ENGINE = MySQL('mysql_host:port', 'mysql_database_name', 'mysql_user', 'mysql_password');
</pre></div>
<ul><li><code>mysql_db_alias</code>: 你在 ClickHouse 中为这个远程 MySQL 数据库起的名称。</li><li><code>mysql_host:port</code>: MySQL 服务器的主机名和端口（例如 ‘localhost:3306’ 或 ‘mysql.example.com:3306’）。</li><li><code>mysql_database_name</code>: 要连接的 MySQL 数据库的名称。</li><li><code>mysql_user</code>: 连接 MySQL 的用户名。</li><li><code>mysql_password</code>: 连接 MySQL 的密码。</li></ul>
<p>创建成功后，你可以像查询普通 ClickHouse 数据库一样查询它：</p>
<div class="jb51code"><pre class="brush:sql;">-- 查看 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;
</pre></div>
<p><strong>方式二：创建 MySQL 引擎的表</strong></p>
<p>这种方式允许你只映射 MySQL 中的单个表到 ClickHouse 中。你需要显式定义 ClickHouse 表的结构，并且这个结构需要与 MySQL 中的表结构兼容。</p>
<div class="jb51code"><pre class="brush:sql;">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');
</pre></div>
<ul><li><code>mysql_table_alias</code>: 你在 ClickHouse 中为这个远程 MySQL 表起的名称。</li><li>列定义：你需要定义列名和 ClickHouse 的数据类型。ClickHouse 会尝试进行类型映射，但最好确保类型兼容（例如 MySQL <code>INT</code> -> ClickHouse <code>Int32</code> 或 <code>UInt32</code>，<code>VARCHAR</code> -> <code>String</code>，<code>DATETIME</code> -> <code>DateTime</code>）。</li><li><code>actual_mysql_table_name</code>: 远程 MySQL 中实际的表名。</li><li>其他参数同上。</li></ul>
<p>查询方式：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM mysql_table_alias WHERE id > 100;
</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>场景演示示例</h2>
<p>准备一台Linux服务器，以Ubuntu 22.04为例，已安装docker和docker-compose环境。</p>
<p><strong>目标场景:</strong></p>
<ol><li>运行一个 MySQL 容器，并创建一个示例数据库和表。</li><li>运行一个 ClickHouse 容器。</li><li>在 ClickHouse 中，创建一个指向 MySQL 容器中数据库的 <code>MySQL</code> 数据库引擎实例。</li><li>能够通过 ClickHouse 查询 MySQL 中的数据。</li></ol>
<p><strong>1. 创建项目目录结构:</strong></p>
<p>在你喜欢的位置创建一个项目文件夹，例如 <code>ch_mysql_demo</code>。</p>
<div class="jb51code"><pre class="brush:sql;">mkdir ch_mysql_demo
cd ch_mysql_demo
</pre></div>
<p><strong>2. 创建 </strong><code>docker-compose.yml</code><strong> 文件:</strong></p>
<p>在 <code>ch_mysql_demo</code> 目录下创建 <code>docker-compose.yml</code> 文件，内容如下：</p>
<div class="jb51code"><pre class="brush:yaml;">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
</pre></div>
<p><strong>3. 创建 MySQL 初始化脚本 (可选但推荐):</strong></p>
<p>为了方便演示，我们可以在 MySQL 启动时自动创建一些示例数据。在 <code>ch_mysql_demo</code> 目录下创建一个名为 <code>mysql_init</code> 的子目录，并在其中创建一个 <code>.sql</code> 文件，例如 <code>init.sql</code>：</p>
<div class="jb51code"><pre class="brush:bash;">mkdir mysql_init
</pre></div>
<p>创建 <code>mysql_init/init.sql</code> 文件，内容如下：</p>
<div class="jb51code"><pre class="brush:sql;">-- 这个脚本会在 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;
</pre></div>
<p><strong>4. 启动容器:</strong></p>
<p>在 <code>ch_mysql_demo</code> 目录下运行：</p>
<div class="jb51code"><pre class="brush:bash;">docker-compose up -d
</pre></div>
<p>这会以后台模式下载镜像（如果本地没有）并启动 MySQL 和 ClickHouse 容器。MySQL 会运行初始化脚本。</p>
<p>查看运行的容器</p>
<div class="jb51code"><pre class="brush:bash;">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#
</pre></div>
<p><strong>5. 在 ClickHouse 中连接 MySQL:</strong></p>
<p>等待几秒钟让 MySQL 完全启动并初始化。然后，连接到 ClickHouse 容器：</p>
<div class="jb51code"><pre class="brush:bash;">docker exec -it clickhouse-server clickhouse-client
</pre></div>
<p>进入 ClickHouse 命令行后，执行以下 SQL 命令来创建 MySQL 数据库引擎：</p>
<div class="jb51code"><pre class="brush:sql;">-- 创建一个 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 中设置的一致)
</pre></div>
<p><strong>6. 通过 ClickHouse 查询 MySQL 数据:</strong></p>
<p>现在你可以像查询 ClickHouse 本地数据库一样查询 <code>mysql_remote_db</code> 了：</p>
<div class="jb51code"><pre class="brush:sql;">-- 查看 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;
</pre></div>
<p><strong>7. 清理环境:</strong></p>
<p>当你完成实验后，可以停止并移除容器、网络和数据卷：</p>
<div class="jb51code"><pre class="brush:bash;">docker-compose down -v
</pre></div>
<p><code>-v</code> 参数会同时删除关联的数据卷（<code>mysql_data</code>, <code>clickhouse_data</code>, <code>clickhouse_logs</code>），如果你想保留数据，则不加 <code>-v</code>。</p>
<p>这个 <code>docker-compose</code> 文件和相关步骤提供了一个基础的 ClickHouse + MySQL 集成环境，可以在此基础上进行更复杂的查询和实验。在生产环境中使用更安全的密码管理方式（例如 Docker Secrets 或环境变量注入）。</p>
頁:
[1]