将MySQL数据迁徙到clickhouse内操作步骤:
#clickhouse-client
:create database dw;
:use dw;
–导入数据:
CREATE TABLE Orders ENGINE = MergeTree ORDER BY OrderID AS SELECT * FROM mysql(‘10.42.134.136:4000’, ‘dw’, ‘Orders’, ‘root’, ‘xyz’);
CREATE TABLE Orders
ENGINE = MergeTree
ORDER BY OrderID AS
SELECT *
FROM mysql(‘10.42.134.136:4000’, ‘dw’, ‘FactSaleOrders’, ‘root’, ‘xyz’)
Ok.
0 rows in set. Elapsed: 1832.114 sec. Processed 85.71 million rows, 65.27 GB (46.78 thousand rows/s., 35.62 MB/s.)
解释:导入数据8571万行,耗时1832.114,总大小65.27GB 最后一次每秒4.678万行导入,每秒导入35.62MB的数据。
前提条件:
MySQL的主键必须为not null
10.42.134.136:4000 为MySQL的IP地址和端口号
dw 为MySQL的库,作为数据源的
Orders 为MySQL的表,源表
root为MySQL的账号
xyz为MySQL的账号对应的密码 账号需要具备远程连接访问的权限。
注意:
1.order by 后的字段大小写要和MySQL的大小写一样
2.主键列在MySQL中必须定义为NOT NULL
报错:DB::Exception