ClickHouse使用MySQL數(shù)據(jù)庫(kù)引擎

MySQL作為一種關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，主要擅長(zhǎng)OLTP(在線事務(wù)處理)場(chǎng)景，能高效處理增刪改查等事務(wù)操作，保證數(shù)據(jù)一致性和可靠性。

而ClickHouse是一種列式存儲(chǔ)的OLAP(在線分析處理)數(shù)據(jù)庫(kù)，專為高性能分析而設(shè)計(jì)，能夠快速處理大規(guī)模數(shù)據(jù)的復(fù)雜分析查詢。

兩者結(jié)合的關(guān)聯(lián)主要體現(xiàn)在：

1. 數(shù)據(jù)同步：可以將MySQL中的事務(wù)數(shù)據(jù)實(shí)時(shí)同步到ClickHouse中進(jìn)行分析處理，例如配置MySQL數(shù)據(jù)表與ClickHouse表的實(shí)時(shí)同步。
2. 優(yōu)勢(shì)互補(bǔ)：MySQL處理日常事務(wù)操作，ClickHouse處理大規(guī)模數(shù)據(jù)分析，各自發(fā)揮所長(zhǎng)。
3. 一站式HTAP解決方案：通過(guò)云平臺(tái)(如阿里云RDS)的整合，使得用戶可以在一個(gè)平臺(tái)上同時(shí)獲得事務(wù)處理和分析處理的能力，簡(jiǎn)化了運(yùn)維和管理。

這種組合能夠支持各種業(yè)務(wù)場(chǎng)景：業(yè)務(wù)報(bào)表統(tǒng)計(jì)、交互式運(yùn)營(yíng)分析、對(duì)賬以及實(shí)時(shí)數(shù)倉(cāng)等。數(shù)據(jù)可以在MySQL中進(jìn)行事務(wù)處理，然后同步到ClickHouse中進(jìn)行快速分析，從而實(shí)現(xiàn)"事務(wù)在線處理和在線分析的一體化"。

MySQL 數(shù)據(jù)庫(kù)引擎介紹

MySQL 數(shù)據(jù)庫(kù)引擎是 ClickHouse 提供的一種集成引擎，這不是指 ClickHouse 本身使用的存儲(chǔ)引擎（如 MergeTree），它允許你直接在 ClickHouse 中查詢存儲(chǔ)在遠(yuǎn)程 MySQL 服務(wù)器上的數(shù)據(jù)。

核心概念：

• 外部數(shù)據(jù)源: MySQL 數(shù)據(jù)庫(kù)引擎視遠(yuǎn)程 MySQL 服務(wù)器為一個(gè)外部數(shù)據(jù)源。
• 代理查詢: 當(dāng)你查詢使用 MySQL 引擎創(chuàng)建的 ClickHouse 數(shù)據(jù)庫(kù)或表時(shí)，ClickHouse 會(huì)將查詢（或其一部分）轉(zhuǎn)發(fā)給遠(yuǎn)程 MySQL 服務(wù)器執(zhí)行。
• 數(shù)據(jù)不存儲(chǔ)在 ClickHouse: 使用這個(gè)引擎時(shí)，數(shù)據(jù)仍然物理存儲(chǔ)在 MySQL 中。ClickHouse 只是充當(dāng)了一個(gè)查詢代理或網(wǎng)關(guān)。

工作原理：

1. 創(chuàng)建連接: 你在 ClickHouse 中創(chuàng)建一個(gè)使用 MySQL 引擎的數(shù)據(jù)庫(kù)或表，并提供連接到遠(yuǎn)程 MySQL 服務(wù)器所需的信息（主機(jī)、端口、數(shù)據(jù)庫(kù)名、用戶名、密碼）。
2. 查詢執(zhí)行: 當(dāng)你在 ClickHouse 中對(duì)這個(gè) MySQL 引擎支持的表或數(shù)據(jù)庫(kù)執(zhí)行 SELECT 查詢時(shí)：
   • ClickHouse 解析查詢。
   • 它將查詢中需要從 MySQL 獲取數(shù)據(jù)的部分，盡可能地轉(zhuǎn)換為 MySQL 兼容的 SQL 語(yǔ)句。
   • ClickHouse 連接到遠(yuǎn)程 MySQL 服務(wù)器。
   • 它將轉(zhuǎn)換后的 SQL 查詢發(fā)送給 MySQL 執(zhí)行。
   • MySQL 執(zhí)行查詢并返回結(jié)果集給 ClickHouse。
   • ClickHouse 接收數(shù)據(jù)，并在需要時(shí)進(jìn)行后續(xù)處理（例如，與其他 ClickHouse 本地表進(jìn)行 JOIN，或者進(jìn)行 ClickHouse 支持的聚合/函數(shù)計(jì)算）。

主要用途和優(yōu)勢(shì)：

1. 數(shù)據(jù)聯(lián)合查詢 (Data Federation): 可以在單個(gè) ClickHouse 查詢中，聯(lián)合查詢 ClickHouse 本地表和遠(yuǎn)程 MySQL 表的數(shù)據(jù)。這對(duì)于需要整合分析來(lái)自不同系統(tǒng)的數(shù)據(jù)非常有用。
2. 平滑遷移/數(shù)據(jù)探索: 在將數(shù)據(jù)完全遷移到 ClickHouse 之前，可以使用 ClickHouse 強(qiáng)大的分析能力來(lái)查詢和分析現(xiàn)有的 MySQL 數(shù)據(jù)。
3. 簡(jiǎn)化 ETL: 對(duì)于某些簡(jiǎn)單的只讀場(chǎng)景，可以避免構(gòu)建復(fù)雜的數(shù)據(jù)抽取、轉(zhuǎn)換、加載（ETL）流程，直接查詢 MySQL。
4. 利用 ClickHouse 的查詢功能: 可以利用 ClickHouse 的 SQL 方言和函數(shù)來(lái)處理從 MySQL 獲取的數(shù)據(jù)（注意：數(shù)據(jù)的 獲取 速度受限于 MySQL 和網(wǎng)絡(luò)）。

如何使用：

有兩種主要的使用方式：

方式一：創(chuàng)建 MySQL 數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù) (推薦)

這種方式更方便，ClickHouse 會(huì)自動(dòng)映射指定 MySQL 數(shù)據(jù)庫(kù)中的所有表。

CREATE DATABASE mysql_db_alias -- ClickHouse 中的數(shù)據(jù)庫(kù)別名
ENGINE = MySQL('mysql_host:port', 'mysql_database_name', 'mysql_user', 'mysql_password');

• mysql_db_alias: 你在 ClickHouse 中為這個(gè)遠(yuǎn)程 MySQL 數(shù)據(jù)庫(kù)起的名稱。
• mysql_host:port: MySQL 服務(wù)器的主機(jī)名和端口（例如 ‘localhost:3306’ 或 ‘mysql.example.com:3306’）。
• mysql_database_name: 要連接的 MySQL 數(shù)據(jù)庫(kù)的名稱。
• mysql_user: 連接 MySQL 的用戶名。
• mysql_password: 連接 MySQL 的密碼。

創(chuàng)建成功后，你可以像查詢普通 ClickHouse 數(shù)據(jù)庫(kù)一樣查詢它：

-- 查看 MySQL 數(shù)據(jù)庫(kù)中的表
SHOW TABLES FROM mysql_db_alias;

-- 查詢 MySQL 中的某個(gè)表
SELECT * FROM mysql_db_alias.some_mysql_table WHERE condition LIMIT 10;

-- 與 ClickHouse 本地表進(jìn)行 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;

方式二：創(chuàng)建 MySQL 引擎的表

這種方式允許你只映射 MySQL 中的單個(gè)表到 ClickHouse 中。你需要顯式定義 ClickHouse 表的結(jié)構(gòu)，并且這個(gè)結(jié)構(gòu)需要與 MySQL 中的表結(jié)構(gòu)兼容。

CREATE TABLE mysql_table_alias -- ClickHouse 中的表別名
(
    -- 定義列名和 ClickHouse 數(shù)據(jù)類型，需要與 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 中為這個(gè)遠(yuǎn)程 MySQL 表起的名稱。
• 列定義：你需要定義列名和 ClickHouse 的數(shù)據(jù)類型。ClickHouse 會(huì)嘗試進(jìn)行類型映射，但最好確保類型兼容（例如 MySQL INT -> ClickHouse Int32 或 UInt32，VARCHAR -> String，DATETIME -> DateTime）。
• actual_mysql_table_name: 遠(yuǎn)程 MySQL 中實(shí)際的表名。
• 其他參數(shù)同上。

查詢方式：

SELECT * FROM mysql_table_alias WHERE id > 100;

場(chǎng)景演示示例

準(zhǔn)備一臺(tái)Linux服務(wù)器，以Ubuntu 22.04為例，已安裝docker和docker-compose環(huán)境。

目標(biāo)場(chǎng)景:

1. 運(yùn)行一個(gè) MySQL 容器，并創(chuàng)建一個(gè)示例數(shù)據(jù)庫(kù)和表。
2. 運(yùn)行一個(gè) ClickHouse 容器。
3. 在 ClickHouse 中，創(chuàng)建一個(gè)指向 MySQL 容器中數(shù)據(jù)庫(kù)的 MySQL 數(shù)據(jù)庫(kù)引擎實(shí)例。
4. 能夠通過(guò) ClickHouse 查詢 MySQL 中的數(shù)據(jù)。

1. 創(chuàng)建項(xiàng)目目錄結(jié)構(gòu):

在你喜歡的位置創(chuàng)建一個(gè)項(xiàng)目文件夾，例如 ch_mysql_demo。

mkdir ch_mysql_demo
cd ch_mysql_demo

2. 創(chuàng)建 docker-compose.yml 文件:

在 ch_mysql_demo 目錄下創(chuàng)建 docker-compose.yml 文件，內(nèi)容如下：

name: 'ch_mysql_demo'

services:
  mysql-server:
    image: mysql:8.0 # 使用 MySQL 8.0 鏡像
    container_name: mysql-server
    hostname: mysql-server # 容器主機(jī)名，ClickHouse 將使用它連接
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: TestPassword!@#$ # 設(shè)置 root 密碼 (生產(chǎn)環(huán)境請(qǐng)使用更安全的方式)
      MYSQL_DATABASE: demo_db          # 創(chuàng)建一個(gè)名為 demo_db 的數(shù)據(jù)庫(kù)
      MYSQL_USER: demo_user           # 創(chuàng)建一個(gè)用戶
      MYSQL_PASSWORD: UserPassword123   # 設(shè)置用戶的密碼 (生產(chǎn)環(huán)境請(qǐng)使用更安全的方式)
    volumes:
      - mysql_data:/var/lib/mysql     # 持久化 MySQL 數(shù)據(jù)
      - ./mysql_init:/docker-entrypoint-initdb.d # 掛載初始化腳本目錄
    ports:
      - "3306:3306" # 將 MySQL 端口映射到宿主機(jī)，方便調(diào)試
    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 數(shù)據(jù)
      - clickhouse_logs:/var/log/clickhouse-server/ # 持久化 ClickHouse 日志
    networks:
      - ch_mysql_network
    depends_on:
      - mysql-server # 確保 MySQL 容器先啟動(dòng)
    ulimits: # 推薦為 ClickHouse 提高文件描述符限制
      nofile:
        soft: 262144
        hard: 262144

volumes:
  mysql_data: # Docker 管理的數(shù)據(jù)卷
  clickhouse_data:
  clickhouse_logs:

networks:
  ch_mysql_network: # 自定義橋接網(wǎng)絡(luò)，讓容器可以通過(guò)服務(wù)名通信
    driver: bridge

3. 創(chuàng)建 MySQL 初始化腳本 (可選但推薦):

為了方便演示，我們可以在 MySQL 啟動(dòng)時(shí)自動(dòng)創(chuàng)建一些示例數(shù)據(jù)。在 ch_mysql_demo 目錄下創(chuàng)建一個(gè)名為 mysql_init 的子目錄，并在其中創(chuàng)建一個(gè) .sql 文件，例如 init.sql：

mkdir mysql_init

創(chuàng)建 mysql_init/init.sql 文件，內(nèi)容如下：

-- 這個(gè)腳本會(huì)在 MySQL 容器第一次啟動(dòng)時(shí)自動(dòng)執(zhí)行
-- 使用我們通過(guò)環(huán)境變量創(chuàng)建的數(shù)據(jù)庫(kù)
USE demo_db;

-- 創(chuàng)建一個(gè)示例表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入一些示例數(shù)據(jù)
INSERT INTO products (name, price) VALUES
('Laptop', 1200.50),
('Mouse', 25.00),
('Keyboard', 75.99),
('Monitor', 300.00);

-- 可以添加更多表和數(shù)據(jù)...

-- 確保 demo_user 對(duì) demo_db 有權(quán)限 (通常環(huán)境變量創(chuàng)建用戶時(shí)會(huì)自動(dòng)授權(quán)，但顯式添加更保險(xiǎn))
-- GRANT ALL PRIVILEGES ON demo_db.* TO 'demo_user'@'%'; -- 在某些 MySQL 鏡像版本可能需要
-- FLUSH PRIVILEGES;

4. 啟動(dòng)容器:

在 ch_mysql_demo 目錄下運(yùn)行：

docker-compose up -d

這會(huì)以后臺(tái)模式下載鏡像（如果本地沒(méi)有）并啟動(dòng) MySQL 和 ClickHouse 容器。MySQL 會(huì)運(yùn)行初始化腳本。

查看運(yùn)行的容器

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 完全啟動(dòng)并初始化。然后，連接到 ClickHouse 容器：

docker exec -it clickhouse-server clickhouse-client

進(jìn)入 ClickHouse 命令行后，執(zhí)行以下 SQL 命令來(lái)創(chuàng)建 MySQL 數(shù)據(jù)庫(kù)引擎：

-- 創(chuàng)建一個(gè) ClickHouse 數(shù)據(jù)庫(kù)，它映射到遠(yuǎn)程 MySQL 的 demo_db
CREATE DATABASE mysql_remote_db
ENGINE = MySQL('mysql-server:3306', 'demo_db', 'demo_user', 'UserPassword123');
--^-- MySQL 容器的服務(wù)名和端口
--^-- MySQL 數(shù)據(jù)庫(kù)名
--^-- MySQL 用戶名
--^-- MySQL 密碼 (與 docker-compose 中設(shè)置的一致)

6. 通過(guò) ClickHouse 查詢 MySQL 數(shù)據(jù):

現(xiàn)在你可以像查詢 ClickHouse 本地?cái)?shù)據(jù)庫(kù)一樣查詢 mysql_remote_db 了：

-- 查看 MySQL 數(shù)據(jù)庫(kù)中的表 (通過(guò) ClickHouse)
SHOW TABLES FROM mysql_remote_db;

/* 預(yù)期輸出類似:
┌─name─────┐
│ products │
└──────────┘
*/

-- 查詢 MySQL 的 products 表
SELECT * FROM mysql_remote_db.products;

/* 預(yù)期輸出:
┌─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 時(shí)間會(huì)是你運(yùn)行時(shí)的實(shí)際時(shí)間)
*/

-- 使用 ClickHouse 的函數(shù)處理來(lái)自 MySQL 的數(shù)據(jù)
SELECT
    name,
    round(toDecimal64(price, 2)) AS rounded_price,
    upper(name) AS upper_name
FROM mysql_remote_db.products
WHERE toDecimal64(price, 2) > 50;

/* 預(yù)期輸出:
┌─name─────┬─rounded_price─┬─upper_name─┐
│ Laptop   │          1201 │ LAPTOP     │
│ Keyboard │            76 │ KEYBOARD   │
│ Monitor  │           300 │ MONITOR    │
└──────────┴───────────────┴────────────┘
*/

-- 退出 ClickHouse 客戶端
exit;

7. 清理環(huán)境:

當(dāng)你完成實(shí)驗(yàn)后，可以停止并移除容器、網(wǎng)絡(luò)和數(shù)據(jù)卷：

docker-compose down -v

-v 參數(shù)會(huì)同時(shí)刪除關(guān)聯(lián)的數(shù)據(jù)卷（mysql_data, clickhouse_data, clickhouse_logs），如果你想保留數(shù)據(jù)，則不加 -v。

這個(gè) docker-compose 文件和相關(guān)步驟提供了一個(gè)基礎(chǔ)的 ClickHouse + MySQL 集成環(huán)境，可以在此基礎(chǔ)上進(jìn)行更復(fù)雜的查詢和實(shí)驗(yàn)。在生產(chǎn)環(huán)境中使用更安全的密碼管理方式（例如 Docker Secrets 或環(huán)境變量注入）。

到此這篇關(guān)于ClickHouse使用MySQL數(shù)據(jù)庫(kù)引擎的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)ClickHouse MySQL數(shù)據(jù)庫(kù)引擎內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論