巧妙利用表函数将MySQL数据导入到ClickHouse

如果在已有的MySql中已经有了表和数据,那么怎么能快速将其导入到ClickHouse中呢?

其实可以利用一下建表语句方便快速的来生成ClickHouse的表和数据

CREATE TABLE ${表名} ENGINE = MergeTree ORDER BY ${主键名} AS 
SELECT * FROM mysql('${MySql的IP:PORT}', 'MySql的数据库名', 'MySql的表名', 'MySql的用户名', 'MySql的密码');

🔔重要: 1: MySQL的必需有主键且不能为空

2: ORDER BY 后的字段大小写要和MySQL的大小写一样

3: 需要注意的是建表的时候需要注意两边的字段类型，特别是MySql的Decimal的类型，ClickHouse建表需要注意更改为 Float64 类型。

MySql里有Decimal列的解决方案

注意ClickHouse创建的表映射，经过操作发现对于MySQL的Deciaml类型的不能映射到ClickHouse中，ClickHouse映射的类型是String类型了.下面告诉你一种快捷方法:

1: 创建Mysql到CLickhouse的映射表并插入1条数据:

CREATE TABLE bfs_fsn ENGINE = MergeTree ORDER BY id AS 
SELECT * FROM mysql('10.42.134.136:4000', 'dw', 'bfs_fsn', 'root', 'xyz') 
limit 0;

📝注意: 最后的 limit 0

2: 查询创建的表结构复制下来

show create table bfs_fsn；

#输出:
CREATE TABLE default.bfs_fsn
(
    `id` UInt64,
    `fsn_filename` String,
    `record_num` UInt32,
    `fsn_datetime` DateTime,
    `tf_flag` UInt32,
    `error_code` String,
    `money_flag` String,
    `ver` UInt32,
    `valuta` UInt32,
    `charnum` String,
    `sno` String,
    `machinesno` String,
    `reserve1` UInt32,
    `machinesno_length` UInt32,
    `machine_length` UInt32,
    `machine_type` UInt32,
    `money_type` UInt32,
    `reserve2` UInt32,
    `img_str` String,
    `accno` String,
    `create_time` DateTime
)
ENGINE = MergeTree
ORDER BY id
SETTINGS index_granularity = 8192

3: 将第2步输出的表结构里的所有在MySql里是 Decimal 的类型的列圈闭进行变更成 ClickHouse里的Float64 类型

4: 从MySql插入数据

insert into bfs_fsn SELECT * FROM mysql('10.42.134.136:4000', 'dw', 'bfs_fsn', 'root', 'xyz');

字段映射表

ClickHouse	MySQL	Comment
UInt8, UInt16, UInt32, UInt64	TINYINT UNSIGNED, SMALLINT UNSIGNED, INT UNSIGNED, BIGINT UNSIGNED
Int8, Int16, Int32, Int64	TINYINT SIGNED, SMALLINT SIGNED, INT SIGNED, BIGINT SIGNED
Float32, Float64	FLOAT, DOUBLE	Supports inf, -inf, nan, recommended NOT to use by ClickHouse!
String	BLOB, TEXT, VARCHAR, VARBINARY	No encoding. Recommended to use UTF-8. In fact behaves like a BLOB.
FixedString(n)	CHAR, BINARY	\0 padded. Less functions available than String, in fact it behaves like BINARY.
Date	DATE	UNIX epoch date up to 2038.
DateTime	DATETIME, TIMESTAMP	UNIX epoch timestamp up to 2038.
Enum	ENUM	Similar to MySQL ENUM. Behaves like Int8/16.
Array(type)	n.a.	Array of type. Closest equivalent in MySQL is JSON? Not well supported.
Tuple()	n.a.
Nested()	n.a.	Closest equivalent in MySQL is JSON?
AggregateFunction()	n.a.
Set	n.a.
Expression	n.a.

---

<<<<<<<<<<<< [完] >>>>>>>>>>>>