浅析mysql迁移到clickhouse的5种方法

数据迁移需要从mysql导入clickhouse, 总结方案如下，包括clickhouse自身支持的三种方式，第三方工具两种。

				 
				?

									create table engin mysql

									create table [if not exists] [db.]table_name [on cluster cluster]

									(

									 name1 [type1] [default|materialized|alias expr1] [ttl expr1],

									 name2 [type2] [default|materialized|alias expr2] [ttl expr2],

									 ...

									 index index_name1 expr1 type type1(...) granularity value1,

									 index index_name2 expr2 type type2(...) granularity value2

									) engine = mysql('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']);

官方文档:https://clickhouse.yandex/docs/en/operations/table_engines/mysql/

注意，实际数据存储在远端mysql数据库中，可以理解成外表。

可以通过在mysql增删数据进行验证。

				 
				?

									insert into select from

									-- 先建表

									create table [if not exists] [db.]table_name [on cluster cluster]

									(

									 name1 [type1] [default|materialized|alias expr1],

									 name2 [type2] [default|materialized|alias expr2],

									 ...

									) engine = engine

									-- 导入数据

									insert into [db.]table [(c1, c2, c3)] select 列或者* from mysql('host:port', 'db', 'table_name', 'user', 'password')

可以自定义列类型，列数，使用clickhouse函数对数据进行处理，比如

				 
				?

									select todate(xx) from mysql("host:port","db","table_name","user_name","password")

									create table as select from

									create table [if not exists] [db.]table_name

									engine =log

									as

									select *

									from mysql('host:port', 'db', 'article_clientuser_sum', 'user', 'password')

网友文章: http://jackpgao.github.io/2018/02/04/clickhouse-use-mysql-data/

不支持自定义列，参考资料里的博主写的 engin=mergetree 测试失败。

可以理解成 create table 和 insert into select 的组合

altinity/clickhouse-mysql-data-reader

altinity公司开源的一个python工具，用来从mysql迁移数据到clickhouse(支持binlog增量更新和全量导入)，但是官方readme和代码脱节，根据quick start跑不通。

				 
				?

									## 创建表

									clickhouse-mysql \

									 --src-host=127.0.0.1 \

									 --src-user=reader \

									 --src-password=qwerty1# \

									 --table-templates-with-create-database \

									 --src-table=airline.ontime > create_clickhouse_table_template.sql

									## 修改脚本

									vim create_clickhouse_table_template.sql

									## 导入建表

									clickhouse-client -mn < create_clickhouse_table_template.sql

									## 数据导入

									clickhouse-mysql \

									 --src-host=127.0.0.1 \

									 --src-user=reader \

									 --src-password=qwerty1# \

									 --table-migrate \

									 --dst-host=127.0.0.1 \

									 --dst-table=logunified \

									 --csvpool

官方文档: https://github.com/altinity/clickhouse-mysql-data-reader#mysql-migration-case-1—migrate-existing-data

注意，上述三种都是从mysql导入clickhouse，如果数据量大，对于mysql压力还是挺大的。下面介绍两种离线方式(streamsets支持实时，也支持离线)

csv

				 
				?

									## 忽略建表

									clickhouse-client \

									 -h host \

									 --query="insert into [db].table format csv" < test.csv

但是如果源数据质量不高，往往会有问题，比如包含特殊字符(分隔符，转义符)，或者换行。被坑的很惨。

				 
				?

									自定义分隔符, --format_csv_delimiter="|"

									遇到错误跳过而不中止， --input_format_allow_errors_num=10 最多允许10行错误, --input_format_allow_errors_ratio=0.1 允许10%的错误

									csv 跳过空值(null) ，报 code: 27. db::exception: cannot parse input: expected , before: xxxx: (at row 69) error: garbage after nullable(date): "8,002<line feed>0205" sed ' :a;s/,,/,\\n,/g;ta' |clickhouse-client -h host --query "insert into [db].table format csv" 将 ,, 替换成 ,\n,

									python clean_csv.py --src=src.csv --dest=dest.csv --chunksize=50000 --cols --encoding=utf-8 --delimiter=,

clean_csv.py参考我另外一篇032-csv文件容错处理

streamsets

streamsets支持从mysql或者读csv全量导入，也支持订阅binlog增量插入，参考我另外一篇 025-大数据etl工具之streamsets安装及订阅mysql binlog 。

本文只展示从mysql全量导入clickhouse

本文假设你已经搭建起streamsets服务

浅析mysql迁移到clickhouse的5种方法

启用并重启服务

浅析mysql迁移到clickhouse的5种方法

上传mysql和clickhouse的jdbc jar和依赖包

便捷方式，创建pom.xml，使用maven统一下载

				 
				?

									<project xmlns="http://maven.apache.org/pom/4.0.0" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance"

									 xsi:schemalocation="http://maven.apache.org/pom/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

									 <modelversion>4.0.0</modelversion>

									 <groupid>com.anjia</groupid>

									 <artifactid>demo</artifactid>

									 <packaging>jar</packaging>

									 <version>1.0-snapshot</version>

									 <name>demo</name>

									 <url>http://maven.apache.org</url>

									 <dependencies>

									 <dependency>

									 <groupid>ru.yandex.clickhouse</groupid>

									 <artifactid>clickhouse-jdbc</artifactid>

									 <version>0.1.54</version>

									 </dependency>

									 <dependency>

									 <groupid>mysql</groupid>

									 <artifactid>mysql-connector-java</artifactid>

									 <version>5.1.47</version>

									 </dependency>

									 </dependencies>

									</project>

如果本地装有maven，执行如下命令

				 
				?

									mvn dependency:copy-dependencies -doutputdirectory=lib -dincludescope=compile

所有需要的jar会下载并复制到lib目录下

浅析mysql迁移到clickhouse的5种方法

然后拷贝到 streamsets /opt/streamsets-datacollector-3.9.1/streamsets-libs-extras/streamsets-datacollector-jdbc-lib/lib/ 目录下

浅析mysql迁移到clickhouse的5种方法

重启streamsets服务

浅析mysql迁移到clickhouse的5种方法

总结

以上所述是小编给大家介绍的mysql迁移到clickhouse的5种方法,希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对服务器之家网站的支持！

如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

原文链接：https://anjia0532.github.io/2019/07/17/mysql-to-clickhouse/

浅析mysql迁移到clickhouse的5种方法

相关文章

热门资讯