快捷導(dǎo)航

淺析mysql遷移到clickhouse的5種方法

更新時(shí)間：2019年07月22日 09:40:55 作者：安家

這篇文章主要介紹了mysql遷移到clickhouse的5種方法，本文給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

數(shù)據(jù)遷移需要從mysql導(dǎo)入clickhouse, 總結(jié)方案如下，包括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/

注意，實(shí)際數(shù)據(jù)存儲(chǔ)在遠(yuǎn)端mysql數(shù)據(jù)庫(kù)中，可以理解成外表。

可以通過(guò)在mysql增刪數(shù)據(jù)進(jìn)行驗(yàn)證。

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
-- 導(dǎo)入數(shù)據(jù)
INSERT INTO [db.]table [(c1, c2, c3)] select 列或者* from mysql('host:port', 'db', 'table_name', 'user', 'password')

可以自定義列類(lèi)型，列數(shù)，使用clickhouse函數(shù)對(duì)數(shù)據(jù)進(jìn)行處理，比如

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

網(wǎng)友文章: http://jackpgao.github.io/2018/02/04/ClickHouse-Use-MySQL-Data/

不支持自定義列，參考資料里的博主寫(xiě)的 ENGIN=MergeTree 測(cè)試失敗。

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

Altinity/clickhouse-mysql-data-reader

Altinity公司開(kāi)源的一個(gè)python工具，用來(lái)從mysql遷移數(shù)據(jù)到clickhouse(支持binlog增量更新和全量導(dǎo)入)，但是官方readme和代碼脫節(jié)，根據(jù)quick start跑不通。

## 創(chuàng)建表
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
## 導(dǎo)入建表
clickhouse-client -mn < create_clickhouse_table_template.sql
## 數(shù)據(jù)導(dǎo)入
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導(dǎo)入clickhouse，如果數(shù)據(jù)量大，對(duì)于mysql壓力還是挺大的。下面介紹兩種離線(xiàn)方式(streamsets支持實(shí)時(shí)，也支持離線(xiàn))

csv

## 忽略建表
clickhouse-client \
 -h host \
 --query="INSERT INTO [db].table FORMAT CSV" < test.csv

但是如果源數(shù)據(jù)質(zhì)量不高，往往會(huì)有問(wèn)題，比如包含特殊字符(分隔符，轉(zhuǎn)義符)，或者換行。被坑的很慘。

自定義分隔符, --format_csv_delimiter="|"
遇到錯(cuò)誤跳過(guò)而不中止， --input_format_allow_errors_num=10 最多允許10行錯(cuò)誤, --input_format_allow_errors_ratio=0.1 允許10%的錯(cuò)誤
csv 跳過(guò)空值(null) ，報(bào) 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文件容錯(cuò)處理

streamsets

streamsets支持從mysql或者讀csv全量導(dǎo)入，也支持訂閱binlog增量插入，參考我另外一篇 025-大數(shù)據(jù)ETL工具之StreamSets安裝及訂閱mysql binlog 。

本文只展示從mysql全量導(dǎo)入clickhouse

本文假設(shè)你已經(jīng)搭建起streamsets服務(wù)

啟用并重啟服務(wù)

上傳mysql和clickhouse的jdbc jar和依賴(lài)包

便捷方式，創(chuàng)建pom.xml，使用maven統(tǒng)一下載

<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，執(zhí)行如下命令

mvn dependency:copy-dependencies -DoutputDirectory=lib -DincludeScope=compile

所有需要的jar會(huì)下載并復(fù)制到lib目錄下

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

重啟streamsets服務(wù)

總結(jié)

以上所述是小編給大家介紹的mysql遷移到clickhouse的5種方法,希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！
如果你覺(jué)得本文對(duì)你有幫助，歡迎轉(zhuǎn)載，煩請(qǐng)注明出處，謝謝！

您可能感興趣的文章: