快捷導(dǎo)航

將MySQL的表數(shù)據(jù)全量導(dǎo)入clichhouse庫(kù)中

更新時(shí)間：2022年03月21日 11:26:23 作者：運(yùn)維之美

這篇文章主要介紹了將MySQL的表數(shù)據(jù)全量導(dǎo)入clichhouse庫(kù)中，詳細(xì)介紹全量導(dǎo)出MySQL數(shù)據(jù)到clickhouse表的相關(guān)內(nèi)容，需要的小伙伴可以參考一下

一、環(huán)境

tidb06 mysql5.7.32
tidb05 clickhouse20.8.3.18

二、創(chuàng)建測(cè)試庫(kù)表寫(xiě)入測(cè)試數(shù)據(jù)

tidb06庫(kù)創(chuàng)建復(fù)制賬戶(hù):

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庫(kù)創(chuàng)建測(cè)試庫(kù)表test01.tb2并且寫(xiě)入測(cè)試數(shù)據(jù)：

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 '創(chuàng)建時(shí)間',
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庫(kù)的建表創(chuàng)建方法:

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');

提示:clichhouse表中要求必須至少包含一個(gè)時(shí)間字段

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.?

到此這篇關(guān)于將MySQL的表數(shù)據(jù)全量導(dǎo)入clichhouse庫(kù)中的文章就介紹到這了,更多相關(guān)MySQL表數(shù)據(jù)導(dǎo)入clichhouse庫(kù)中內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: