Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to import MySQL table data into clichhouse library in full amount of related knowledge, detailed and easy to understand, simple and fast operation, with certain reference value, I believe that everyone will have some gains after reading this article on how to import MySQL table data into clichhouse library in full amount, let's take a look at it.

I. Environment

tidb06 mysql5.7.32

tidb05 clickhouse20.8.3.18

Second, create test library table to write test data

tidb06 Library Create replication 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 Create test library table test01.tb2 and write test data:

CREATE TABLE `tb2`(`id` int(8) NOT NULL AUTO_INCREMENT, `username` varchar(20) COLATE utf8_unicode_ci NOT NULL,`password` varchar(20) COLATE utf8_unicode_ci NOT NULL, `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT', PRIMARY KEY (`id`) #MAIN KEY ID) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET= utf8 COLATE =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');

Tip:clichhouse tables require 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 tb2ENGINE = MergeTreePARTITION BY toYYYYMM(create_time)ORDER BY create_time ASSELECT *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. That's all for "How to import MySQL table data into clichhouse". Thank you for reading! I believe everyone has a certain understanding of "how to import MySQL table data into clichhouse database" knowledge. If you still want to learn more knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.