我要评论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 数据库中的所有表。
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-> clickhouseint32或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环境。
目标场景:
- 运行一个 mysql 容器，并创建一个示例数据库和表。
- 运行一个 clickhouse 容器。
- 在 clickhouse 中，创建一个指向 mysql 容器中数据库的
mysql数据库引擎实例。 - 能够通过 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数据库引擎的实现的文章就介绍到这了,更多相关clickhouse mysql数据库引擎内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论