How to import all MySQL table data into the clichhouse library

1. Environment

• tidb06 mysql5.7.32

• ##tidb05 clickhouse20.8.3.18

2. Create a test library table and write test data

tidb06 library creates a copy account:

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'click_rep'@'172.16.0.246' identified by 'jwts996';flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)

tidb06 library creates a test library table test01.tb2 and Write test data:

CREATE TABLE `tb2` (
`id` int(8) NOT NULL AUTO_INCREMENT, 
`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(20) COLLATE utf8_unicode_ci NOT NULL, 
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`) #主键ID
) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO tb2(username,password,create_time) values('tomcat', 'xiaohuahua',now());
INSERT INTO tb2(username,password,create_time) values('java', 'xiaohuahua',now());
root@tidb06 14:01:  [test01]> select * from tb2;
+----+----------+------------+---------------------+
| id | username | password   | create_time         |
+----+----------+------------+---------------------+
|  1 | tomcat   | xiaohuahua | 2021-07-21 14:01:50 |
|  2 | java     | xiaohuahua | 2021-07-21 14:01:59 |
+----+----------+------------+---------------------+
2 rows in set (0.00 sec)

Clickhouse library table creation method:

CREATE TABLE tb2 ENGINE = MergeTree PARTITION BY toYYYYMM(create_time) ORDER BY create_time AS SELECT * FROM mysql('172.16.0.247:3306', 'test01', 'tb2', 'click_rep', 'jwts996');

Tips:clichhouse table The requirement must contain at least one time field

tidb05 :) CREATE TABLE tb2 ENGINE = MergeTree PARTITION BY toYYYYMM(create_time) ORDER BY create_time AS SELECT * FROM mysql('172.16.0.247:3306', 'test01', 'tb2', 'click_rep', 'jwts996');

CREATE TABLE tb2
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time AS
SELECT *
FROM mysql('172.16.0.247:3306', 'test01', 'tb2', 'click_rep', 'jwts996')

Ok.

0 rows in set. Elapsed: 0.014 sec. 

tidb05 :) select * from tb2;

SELECT *
FROM tb2

┌─id─┬─username─┬─password───┬─────────create_time─┐
│  1 │ tomcat   │ xiaohuahua │ 2021-07-21 14:01:50 │
│  2 │ java     │ xiaohuahua │ 2021-07-21 14:01:59 │
└────┴──────────┴────────────┴─────────────────────┘

2 rows in set. Elapsed: 0.002 sec.

The above is the detailed content of How to import all MySQL table data into the clichhouse library. For more information, please follow other related articles on the PHP Chinese website!