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

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

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

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

## 创建表
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

## 忽略建表
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,002LINE 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=,

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>

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