- 配景:由于clickhouse数据连续增长,导致服务器磁盘不够利用,云服务器的体系盘不能扩容,以是只能进行迁移
- root@jcdata:~/buckup/clickhouse# clickhouse-client -udefault --password 123456
- ClickHouse client version 23.6.2.18 (official build).
- Connecting to 192.168.162.5:9000 as user default.
- Connected to ClickHouse server version 23.6.2 revision 54464.
- jcdata :) show databases
- SHOW DATABASES
- Query id: 9294909d-88d2-4928-a014-64d49bcd1a3d
- ┌─name───────────────┐
- │ INFORMATION_SCHEMA │
- │ default │
- │ information_schema │
- │ system │
- │ traffic-axle-load │
- └────────────────────┘
- 6 rows in set. Elapsed: 0.001 sec.
- jcdata :) show tables;
- SHOW TABLES
- Query id: bc6f3e59-49ad-4ff5-91df-7787d341c9b4
- ┌─name────────────────┐
- │ axle_load_atlas │
- │ traffic_survey_data │
- └─────────────────────┘
- 2 rows in set. Elapsed: 0.005 sec.
- jcdata :)
复制代码 traffic-axle-load数据库是我要迁移的
- #导出数据1
- root@jcdata:~/buckup/clickhouse# clickhouse-client -u default --password=123456 --query "SELECT * FROM \`traffic-axle-load\`.axle_load_atlas" > axle_load_atlas.csv
- #导出数据2
- root@jcdata:~/buckup/clickhouse# clickhouse-client -u default --password=SHJC666 --query "SELECT * FROM \`traffic-axle-load\`.traffic_survey_data" > traffic_survey_data.csv
复制代码- root@jcdata:~/buckup/clickhouse# ll -h
- total 16G
- -rw-r--r-- 1 root root 5.5G Aug 13 17:58 axle_load_atlas.csv
- -rw-r--r-- 1 root root 10G Aug 13 18:01 traffic_survey_data.csv
- #导出完成
复制代码- scp -r * root@ip/root/clickhouse/
复制代码 我是导入到了同样的数据库差别的数据表、按照研发需求导入,提前创建对应的表、表结构要正确
- #导入数据1
- [root@sdgs-server01 clickhouse]# clickhouse-client -u admin --password=123456 --query="insert into \`traffic-axle-load\`.axle FORMAT TSV"< axle_load_atlas.csv
- #新开一个窗口连接到CK
- clickhouse :) select count(*) from axle;
- SELECT count(*)
- FROM axle
- Query id: d41ca2fb-e5a5-4952-b034-deab1ca02413
- ┌─count()─┐
- │ 0 │
- └─────────┘
- 1 row in set. Elapsed: 0.001 sec.
- clickhouse :) select count(*) from axle;
- SELECT count(*)
- FROM axle
- Query id: 86c311a0-6a81-48c8-9ce5-90db940c2214
- ┌──count()─┐
- │ 16267692 │
- └──────────┘
- 1 row in set. Elapsed: 0.002 sec.
- #通过上面可以看到数据正在导入
- #导入数据2
- [root@sdgs-server01 clickhouse]# clickhouse-client -u admin --password=123456 --query="insert into \`traffic-axle-load\`.traffic FORMAT TSV"< traffic_survey_data.csv
- clickhouse :) select count(*) from traffic;
- SELECT count(*)
- FROM traffic
- Query id: 920e0422-0e66-490c-ba3a-a6665a7c255d
- ┌─count()─┐
- │ 0 │
- └─────────┘
- 1 row in set. Elapsed: 0.001 sec.
- clickhouse :) select count(*) from traffic;
- SELECT count(*)
- FROM traffic
- Query id: da399f67-ed7e-4223-8886-115ce60ddf4c
- ┌─count()─┐
- │ 2792470 │
- └─────────┘
- 1 row in set. Elapsed: 0.001 sec.
- #此表一样
复制代码 由于我就两个表,以是只用了clickhouse-client下令完成数据导入和导出
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。 |