Clickhouse系列之使用Mysql表引擎查询Mysql数据以及实时同步Mysql数据至Clickhouse案例实操

阿杰笔记
366 阅读3分钟

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第10天，点击查看活动详情

前言

MySQL表引擎允许我们将ClickHouse连接到MySQL，这个过程使用的是Http方式请求。SELECT和INSERT语句可以在ClickHouse或MySQL表中进行，其次我们将操作实时同步Mysql数据至Clickhouse案例实操。本次会演示了如何使用MySQL表引擎和Mysql物化使用的基本方法。

正文

注意：本次实验是在有Mysql和Clickhouse环境可正常使用的前提条件下测试。

案例一

需要注意的是，这次试验是Clickhouse数据是通过Http的方式操作Mysql，Clickhouse是不存储Mysql表中的数据。

配置Mysql(以下在Mysql实例操作)

a) 创建库

CREATE DATABASE db1;

b) 创建表

CREATE TABLE db1.table1 (
  id INT,
  column1 VARCHAR(255)
);

c) 插入测试数据

INSERT INTO db1.table1
  (id, column1)
VALUES
  (1, 'abc'),
  (2, 'def'),
  (3, 'ghi');

d) 创建一个Clickhouse用户，并授权

CREATE USER 'mysql_clickhouse'@'%' IDENTIFIED BY 'Password123!';
GRANT ALL PRIVILEGES ON *.* TO 'mysql_clickhouse'@'%';
创建Mysql表引擎(以下在Clickhouse实例操作)
CREATE TABLE mysql_table1 (
  id UInt64,
  column1 String
)
ENGINE = MySQL('mysql-host.domain.com','db1','table1','mysql_clickhouse','Password123!')

参数注意事项：

  • mysql-host.domain.com 为Mysql的IP或者主机名
  • db1 为Mysql的库名
  • table1 为Mysql的表名
  • mysql_clickhouse 为Mysql的账户名
  • Password123! 为Mysql的账户名对应的密码
测试数据注入

a) 在Mysql实例插入数据

INSERT INTO db1.table1
(id, column1)
VALUES
(4, 'jkl');

b) 查询Mysql和Clickhouse两个表中的数据

SELECT
    id,
    column1
FROM mysql_table1

c) 在Clickhouse表中插入数据

INSERT INTO mysql_table1
  (id, column1)
VALUES
  (5,'mno')

d) 同样，我们在Mysql和Clickhouse中查看两个表数据

select id,column1 from db1.table1;

案例二(大数据场景使用)

需要注意的是，这次是通过物化表实现数据同步，即Mysql数据实时同步至Clickhouse表中，Clickhouse是存储Mysql的数据，查询也是走Clickhouse socket查询。

MaterializedMySQL数据库引擎允许我们在ClickHouse中定义一个数据库，其中包含MySQL数据库中的所有现有表以及这些表中的所有数据。在MySQL端，可以继续进行DDL和DML操作，ClickHouse可以检测到更改并充当MySQL数据库的副本。本文实战将讲述如何配置MySQL和ClickHouse来实现此复制。 materializemysql_1440-22.jpg

配置Mysql(以下在Mysql实例操作)

a) 配置Mysql 配置MySQL数据库以允许复制和本机身份验证。ClickHouse仅适用于本机密码验证。将以下条目添加到/etc/my.cnf：

default-authentication-plugin = mysql_native_password
gtid-mode = ON
enforce-gtid-consistency = ON

b) Mysql建表、创建用户、授权和案例一一致

配置Clickhouse (以下在Clickhouse实例操作)

a) 开启实验功能

set allow_experimental_database_materialized_mysql = 1;

b) 创建库引擎

CREATE DATABASE db1_mysql
ENGINE = MaterializedMySQL(
  'mysql-host.domain.com:3306',
  'db1',
  'clickhouse_user',
  'ClickHouse_123'
);

参数注意事项：

  • mysql-host.domain.com:3306 为Mysql的IP或者主机名
  • db1 为Mysql的库名
  • clickhouse_user 为Mysql的账户名
  • ClickHouse_123 为Mysql的账户名对应的密码
测试数据注入

a) 在Mysql插入数据

INSERT INTO db1.table_1
  (id, column1)
VALUES
  (4, 'jkl');

b) 在Clickhouse表中查询数据

SELECT
    id,
    column1
FROM db1_mysql.table_1

c) 我们修改下Mysql DDL

alter table db1.table_1 add column column2 varchar(10) after column1;

d) 我们在Mysql插入一条数据

INSERT INTO db1.table_1
  (id, column1, column2)
VALUES
  (5, 'mno', 'pqr');

e) 现在我们在Clickhouse中查看数据

SELECT
    id,
    column1,
    column2
FROM db1_mysql.table_1

值得注意的是，查看结果我们会发现，Clickhouse对于新增的一列，它会以NULL进行填充。

总结

这一节我们首先讲解了Clickhouse通过Http方式请求Mysql查询数据和插入数据，但是Clickhouse是不存储Mysql数据的，可以认为Clickhouse是Mysql的一个客户端而已。然后我们讲解了基于物化库行式对Mysql数据同步至Clickhouse方式，这种方式是通过Binlog方式同步，Clickhouse作为Mysql的一个副本了，因此数据也是存在Clickhouse当中的，因此这种方式使用场景很广，比如数据同步,数据备份等。

avatar
文章
阅读
粉丝