ClickHouse使用MySQL数据库引擎

MySQL作为一种关系型数据库管理系统，主要擅长OLTP(在线事务处理)场景，能高效处理增删改查等事务操作，保证数据一致性和可靠性。

而ClickHouse是一种列式存储的OLAP(在线分析处理)数据库，专为高性能分析而设计，能够快速处理大规模数据的复杂分析查询。

两者结合的关联主要体现在：

  1. 数据同步：可以将MySQL中的事务数据实时同步到ClickHouse中进行分析处理，例如配置MySQL数据表与ClickHouse表的实时同步。
  2. 优势互补：MySQL处理日常事务操作，ClickHouse处理大规模数据分析，各自发挥所长。
  3. 一站式HTAP解决方案：通过云平台(如阿里云RDS)的整合，使得用户可以在一个平台上同时获得事务处理和分析处理的能力，简化了运维和管理。

这种组合能够支持各种业务场景：业务报表统计、交互式运营分析、对账以及实时数仓等。数据可以在MySQL中进行事务处理，然后同步到ClickHouse中进行快速分析，从而实现"事务在线处理和在线分析的一体化"。

在这里插入图片描述

MySQL 数据库引擎介绍

MySQL 数据库引擎是 ClickHouse 提供的一种集成引擎，这不是指 ClickHouse 本身使用的存储引擎（如 MergeTree），它允许你直接在 ClickHouse 中查询存储在远程 MySQL 服务器上的数据。

核心概念：

  • 外部数据源: MySQL 数据库引擎视远程 MySQL 服务器为一个外部数据源。
  • 代理查询: 当你查询使用 MySQL 引擎创建的 ClickHouse 数据库或表时，ClickHouse 会将查询（或其一部分）转发给远程 MySQL 服务器执行。
  • 数据不存储在 ClickHouse: 使用这个引擎时，数据仍然物理存储在 MySQL 中。ClickHouse 只是充当了一个查询代理或网关。

工作原理：

  1. 创建连接: 你在 ClickHouse 中创建一个使用 MySQL 引擎的数据库或表，并提供连接到远程 MySQL 服务器所需的信息（主机、端口、数据库名、用户名、密码）。
  2. 查询执行: 当你在 ClickHouse 中对这个 MySQL 引擎支持的表或数据库执行 SELECT 查询时：
    • ClickHouse 解析查询。
    • 它将查询中需要从 MySQL 获取数据的部分，尽可能地转换为 MySQL 兼容的 SQL 语句。
    • ClickHouse 连接到远程 MySQL 服务器。
    • 它将转换后的 SQL 查询发送给 MySQL 执行。
    • MySQL 执行查询并返回结果集给 ClickHouse。
    • ClickHouse 接收数据，并在需要时进行后续处理（例如，与其他 ClickHouse 本地表进行 JOIN，或者进行 ClickHouse 支持的聚合/函数计算）。

主要用途和优势：

  1. 数据联合查询 (Data Federation): 可以在单个 ClickHouse 查询中，联合查询 ClickHouse 本地表和远程 MySQL 表的数据。这对于需要整合分析来自不同系统的数据非常有用。
  2. 平滑迁移/数据探索: 在将数据完全迁移到 ClickHouse 之前，可以使用 ClickHouse 强大的分析能力来查询和分析现有的 MySQL 数据。
  3. 简化 ETL: 对于某些简单的只读场景，可以避免构建复杂的数据抽取、转换、加载（ETL）流程，直接查询 MySQL。
  4. 利用 ClickHouse 的查询功能: 可以利用 ClickHouse 的 SQL 方言和函数来处理从 MySQL 获取的数据（注意：数据的 获取 速度受限于 MySQL 和网络）。

如何使用：

有两种主要的使用方式：

方式一：创建 MySQL 数据库引擎的数据库 (推荐)

这种方式更方便，ClickHouse 会自动映射指定 MySQL 数据库中的所有表。

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 数据库一样查询它：

-- 查看 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 中的表结构兼容。

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 中实际的表名。
  • 其他参数同上。

查询方式：

SELECT * FROM mysql_table_alias WHERE id > 100;

场景演示示例

准备一台Linux服务器，以Ubuntu 22.04为例，已安装docker和docker-compose环境。

目标场景:

  1. 运行一个 MySQL 容器，并创建一个示例数据库和表。
  2. 运行一个 ClickHouse 容器。
  3. 在 ClickHouse 中，创建一个指向 MySQL 容器中数据库的 MySQL 数据库引擎实例。
  4. 能够通过 ClickHouse 查询 MySQL 中的数据。

1. 创建项目目录结构:

在你喜欢的位置创建一个项目文件夹，例如 ch_mysql_demo。

mkdir ch_mysql_demo
cd ch_mysql_demo

2. 创建 docker-compose.yml 文件:

在 ch_mysql_demo 目录下创建 docker-compose.yml 文件，内容如下：

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：

mkdir mysql_init

创建 mysql_init/init.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;

4. 启动容器:

在 ch_mysql_demo 目录下运行：

docker-compose up -d

这会以后台模式下载镜像（如果本地没有）并启动 MySQL 和 ClickHouse 容器。MySQL 会运行初始化脚本。

查看运行的容器

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 容器：

docker exec -it clickhouse-server clickhouse-client

进入 ClickHouse 命令行后，执行以下 SQL 命令来创建 MySQL 数据库引擎：

-- 创建一个 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 了：

-- 查看 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. 清理环境:

当你完成实验后，可以停止并移除容器、网络和数据卷：

docker-compose down -v

-v 参数会同时删除关联的数据卷（mysql_data, clickhouse_data, clickhouse_logs），如果你想保留数据，则不加 -v。

这个 docker-compose 文件和相关步骤提供了一个基础的 ClickHouse + MySQL 集成环境，可以在此基础上进行更复杂的查询和实验。在生产环境中使用更安全的密码管理方式（例如 Docker Secrets 或环境变量注入）。

# 数据库 # clickhouse # mysql
Logo
DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

cover

KingbaseES数据库：医疗信创潮涌沅江，国产化信创打造医疗新质生产力

avatar DAMO开发者矩阵
cover

告别迁移焦虑：金仓KDMS V4带你轻松搞定数据库国产化替代

avatar DAMO开发者矩阵
cover

KingbaseES数据库在常德二院全栈国产化信创中产生新质生产力,医疗信创的部署如何实现，如何操作？

avatar DAMO开发者矩阵