clickhouse连接访问mysql

创建MySQL表

创建数据库test和表t1,并向t1表中插入几条数据

CREATE database test ;

use test;

CREATE table t1(
id int,
name varchar(100)
);

INSERT INTO t1 values (1, 'a'),(2, 'b'),(3, 'c');

SELECT * FROM t1;

ClickHouse连接访问MySQL

方式1: 数据库引擎MySQL

用该引擎创建的数据库中,可以执行SELECT、INSERT、SHOW TABLES、SHOW CREATE TABLE 命令，不能执行RENAME、CREATE TABLE、ALTER 命令

# 格式
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

注: 远程mysql中的database名称用不用单引号''包住都可以

# 示例
# 远程mysql数据库名用''包住
CREATE DATABASE test_database engine = MySQL('192.168.100.10:3306', 'test', 'root', 'xxxxx');
# 远程mysql数据库名不用''包住也可以
CREATE DATABASE test_database2 engine = MySQL('192.168.100.10:3306', test, 'root', 'xxxxx');

# 插入数据
INSERT INTO test_database.t1 values (4, 'd');

方式2: 表引擎MySQL

# 格式
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])
SETTINGS
    [ connection_pool_size=16, ]
    [ connection_max_tries=3, ]
    [ connection_wait_timeout=5, ]
    [ connection_auto_close=true, ]
    [ connect_timeout=10, ]
    [ read_write_timeout=300 ]
;

# 示例
# 创建库
CREATE database test_table;
# 字段保持一致
CREATE TABLE test_table.ck_t1
(
    id Int32,
    name String
) ENGINE = MySQL('192.168.100.10:3306', 'test', 't1', 'root', 'xxxxxx');

# 也可以少字段,但不能多字段,否则后续select和insert操作会报错
CREATE TABLE test_table.ck_t2
(
    id Int32
) ENGINE = MySQL('192.168.100.10:3306', 'test', 't1', 'root', 'xxxxxx');

# 插入数据
INSERT INTO test_table.ck_t1 values (5, 'e');
INSERT INTO test_table.ck_t2 values (6);

在MySQL中查看数据

可以发现,在ck中插入的数据, 在mysql中都能看到

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | NULL |
+------+------+
6 rows in set (0.00 sec)

MySQL和ClickHouse数据类型对应关系

MySQL	ClickHouse
UNSIGNED TINYINT	UInt8
TINYINT	Int8
UNSIGNED SMALLINT	UInt16
SMALLINT	Int16
UNSIGNED INT, UNSIGNED MEDIUMINT	UInt32
INT, MEDIUMINT	Int32
UNSIGNED BIGINT	UInt64
BIGINT	Int64
FLOAT	Float32
DOUBLE	Float64
DATE	Date
DATETIME, TIMESTAMP	DateTime
BINARY	FixedString
All other MySQL data types	String

遇到的问题

URL xxx is not allowed in configuration fle

Code: 491. DB:Exception: URL "ipxxx:3306"is not allowed in configuration fle, see <remote url allow hosts>,

检查config.xml, 发现配置了remote_url_allow_hosts, 但没有配置具体的host信息

<!-- 允许的远程URL主机列表 -->
<remote_url_allow_hosts></remote_url_allow_hosts>

修改config.xml为

<remote_url_allow_hosts>
    <host>192.168.100.200</host>
</remote_url_allow_hosts>

修改完后, 再执行create table还是报错, 重启ClickHouse服务以使更改生效, 再create table就正常了

remote_url_allow_hosts配置说明:
1.用于指定哪些主机可以在URL相关的存储引擎和表函数中使用。如果这个配置项不存在，那么所有主机都是被允许的。
2.如果内部host主机名后面指定了端口号，那么只有指定的端口会被允许。如果没有指定端口号，那么任何端口都将被允许。

<yandex>
    <remote_url_allow_hosts>
        <host>example.com</host>
        <host>192.168.1.100</host>
    </remote_url_allow_hosts>
</yandex>

参考

ClickHouse数据库引擎MySQL
https://clickhouse.com/docs/en/engines/database-engines/mysql

ClickHouse表引擎MySQL
https://clickhouse.com/docs/en/engines/table-engines/integrations/mysql