mysql历史表数据迁移到clickhouse库
原创mysql历史表数据迁移到clickhouse库
原创
wangwei-dba
修改于 2021-05-28 09:59:09
修改于 2021-05-28 09:59:09
1.使用mysql引擎的表
创建mysql引擎表：
CREATE TABLE ck_test.tab_datack (
`id` Int32,
`phone_id` Int32,
`phone` Nullable (String),
`callid` Nullable (Int32),
`content` Nullable (String),
`answer` Nullable (String),
`usedtimems` Nullable (String),
`file` Nullable (String),
`addtime` DateTime,
`publish` Nullable (Int8),
`operate` Nullable (String),
`content_time` Nullable (Int32),
`answer_time` Nullable (Int32),
`content_new` Nullable (String)
) ENGINE = MySQL('11x.62.x56.x20:3108', 'pho22_sxxxai19', 'tab_datack', 'gxssiji', 'Gssxxi888*()');
查看创建的表：
localhost :) show tables;
SHOW TABLES
┌─name────────────┐
│ tab_calldata │
│ tab_datack │
│ tab_voicedata │
│ tb_callout_plan │
│ tb_merge_tree │
└─────────────────┘
mysql引擎的表数据不存在clickhouse中而是在远端的mysql中新建一张clickhouse的表引擎为mergetree
CREATE TABLE ck_test.tab_datack_1 (
`id` Int32,
`phone_id` Int32,
`phone` Nullable (String),
`callid` Nullable (Int32),
`content` Nullable (String),
`answer` Nullable (String),
`usedtimems` Nullable (String),
`file` Nullable (String),
`addtime` DateTime,
`publish` Nullable (Int8),
`operate` Nullable (String),
`content_time` Nullable (Int32),
`answer_time` Nullable (Int32),
`content_new` Nullable (String),
INDEX idx_operate operate TYPE minmax GRANULARITY 5
) ENGINE = MergeTree
PARTITION BY toYYYYMM(addtime)
ORDER BY (id)
SETTINGS index_granularity = 8192;把mysql引擎的表数据插入到clickhouse表中
insert into ck_test.tab_datack_1 select * from ck_test.tab_datack;
localhost :) insert into ck_test.tab_datack_1 select * from ck_test.tab_datack;
INSERT INTO ck_test.tab_datack_1 SELECT *
FROM ck_test.tab_datack
↖ Progress: 524.29 thousand rows, 219.97 MB (4.22 thousand rows/s., 1.77 MB/s.)
↑ Progress: 589.82 thousand rows, 248.37 MB (4.19 thousand rows/s., 1.76 MB/s.)
直接使用mysql引擎建表插入
CREATE TABLE tab_voicedata
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('11x.62.x56.x20:3108', 'pho22_sxxxai19', 'tab_datack', 'gxssiji', 'Gssxxi888*()');上述这两种方式如果网络不好或者表很大，插入会很慢
2.利用导入文件的方式迁移mysql表到clickhouse
首先创建clickhouse表
CREATE TABLE ck_test.tab_datack_1 (
`id` Int32,
`phone_id` Int32,
`phone` Nullable (String),
`callid` Nullable (Int32),
`content` Nullable (String),
`answer` Nullable (String),
`usedtimems` Nullable (String),
`file` Nullable (String),
`addtime` DateTime,
`publish` Nullable (Int8),
`operate` Nullable (String),
`content_time` Nullable (Int32),
`answer_time` Nullable (Int32),
`content_new` Nullable (String),
INDEX idx_operate operate TYPE minmax GRANULARITY 5
) ENGINE = MergeTree
PARTITION BY toYYYYMM(addtime)
ORDER BY (id)
SETTINGS index_granularity = 8192;导出mysql表数据到文件：
select tab_datack.* into outfile '/tmp/phone_sxxx_tab_datack.txt' from tab_datack;然后压缩文件打包，并传输到clickhouse服务器上
执行导入操作
cat phone_sxxx_tab_datack.txt | clickhouse-client -h 10.12.11.201 --query="INSERT INTO php_data.tab_datack FORMAT TabSeparated"这种方式速度很快2000万数据导入执行10s左右
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。