ClickHouse學(xué)習(xí)系列之八【數(shù)據(jù)導(dǎo)入遷移&同步】

背景

　　在介紹了一些ClickHouse相關(guān)的系列文章之后，大致對(duì)ClickHouse有了比較多的了解。它是一款非常優(yōu)秀的OLAP數(shù)據(jù)庫(kù)，為了更好的來(lái)展示其強(qiáng)大的OLAP能力，本文將介紹一些快速導(dǎo)入大量數(shù)據(jù)到ClickHouse的方法。如：通過(guò)文件、遠(yuǎn)程數(shù)據(jù)庫(kù)等方式。

說(shuō)明

一、其他數(shù)據(jù)庫(kù)導(dǎo)入到ClickHouse

MySQL可以做為ClickHouse的外部存儲(chǔ)類型，還有其他的存儲(chǔ)類型，如：MongoDB、PostgreSQL、HDFS、JDBC、ODBC、Kafka、File、RabbitMQ、S3等等，具體的可以看官網(wǎng)說(shuō)明。本文介紹MySQL、MongoDB、File三種方式的導(dǎo)入，前2種方式相當(dāng)于鏈表。

① MySQL數(shù)據(jù)導(dǎo)入到ClickHouse

方法一： 外部引擎，建立遠(yuǎn)程表

MySQL引擎允許對(duì)存儲(chǔ)在遠(yuǎn)程 MySQL 服務(wù)器上的數(shù)據(jù)執(zhí)行 SELECT 和 INSERT 查詢，不能執(zhí)行DELETE 和 UPDATE。

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],
    ...
) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])
SETTINGS
    [connection_pool_size=16, ]
    [connection_max_tries=3, ]
    [connection_auto_close=true ]
;

建議：列名相同。參數(shù) external_table_functions_use_nulls 表示如何處理null值，默認(rèn)1，可選值0。1：可以為null，0：不能為null，使用默認(rèn)值代替。MySQL的參數(shù)說(shuō)明：

host:port — MySQL 地址
database — MySQL數(shù)據(jù)庫(kù)名
table — MySQL表名
user — MySQL 用戶
password — MySQL用戶密碼
replace_query — 默認(rèn)0，對(duì)應(yīng)replace into。設(shè)置1這會(huì)用replace into 代替 insert into
on_duplicate_clause — 默認(rèn)0，對(duì)應(yīng) ON DUPLICATE KEY。設(shè)置1這會(huì)代替 insert into，和replace_query互斥

例子：

CREATE TABLE testdb.test
(
    `id` UInt32,
    `c1` Date COMMENT 'c1',
    `c2` DateTime COMMENT 'c2',
    `c3` String COMMENT 'c3',
    `c4` UInt32 COMMENT 'c4'
)
ENGINE = MySQL('10.10.10.10:3306','test','test','dba','dba')

注意：在查詢遠(yuǎn)程表的時(shí)候，除了where條件會(huì)帶入到遠(yuǎn)程的MySQL中，其余的條件（聚合），包含limit都會(huì)在ClickHouse本地執(zhí)行，而遠(yuǎn)程則執(zhí)行全表掃描。最后可以對(duì)該表執(zhí)行 SELECT 和 INSERT 查詢，不能執(zhí)行DELETE 和 UPDATE。

此后就可以在ClickHouse上創(chuàng)建符合要求的引擎表，如MergeTree引擎，再通過(guò)以下SQL來(lái)進(jìn)行導(dǎo)入數(shù)據(jù)。

 insert into ck_tb select * from my_tb

方法二：和方法一類似，不過(guò)方法二是直接用了mysql函數(shù)來(lái)進(jìn)行遠(yuǎn)程訪問(wèn)：建立需要的引擎，再用mysql函數(shù)進(jìn)行遠(yuǎn)程導(dǎo)入，可以指定列來(lái)導(dǎo)入數(shù)據(jù)，把*改成具體列名。

insert into ck_tb select * from mysql('host:port', 'database', 'table', 'user', 'password')

mysql函數(shù)里的參數(shù)可以參考方法一的說(shuō)明。
例子：

-- 建立表
CREATE TABLE testdb.test_ck1
(
    `id` UInt32,
    `c1` Date COMMENT 'c1',
    `c2` DateTime COMMENT 'c2',
    `c3` String COMMENT 'c3',
    `c4` UInt32 COMMENT 'c4'
)
ENGINE = MergeTree
PARTITION BY c1
ORDER BY id

-- 插入數(shù)據(jù)
INSERT INTO test_ck1 SELECT *
FROM mysql('10.10.10.10:3306','test','test','dba','dba')

方法三：create + select

CREATE TABLE [IF NOT EXISTS] [db.]table_name
ENGINE = ENGINE
AS
SELECT *
FROM mysql('host:port', 'database', 'table', 'user', 'password')

mysql函數(shù)里的參數(shù)可以參考方法一的說(shuō)明。

例子：

create table test_ck2 engine = MergeTree order by id as select * from mysql('10.10.10.10:3306','test','test','dba','dba');
或
create table test_ck2 engine = Log as select * from mysql('10.10.10.10:3306','test','test','dba','dba');

通過(guò)該方法的導(dǎo)入，不需要事先建立表，它會(huì)自動(dòng)根據(jù)select出來(lái)的數(shù)據(jù)按照需要?jiǎng)?chuàng)建符合要求的類型。

② MongoDB數(shù)據(jù)導(dǎo)入到ClickHouse

MongoDB引擎允許對(duì)存儲(chǔ)在遠(yuǎn)程 MongoDB 服務(wù)器上的數(shù)據(jù)執(zhí)行 SELECT 查詢，不能執(zhí)行 INSERT、DELETE 和 UPDATE。

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MongoDB(host:port, database, collection, user, password);

建議：列名相同。MongoDB的參數(shù)說(shuō)明：

host:port — MongoDB 地址.
database — MongoDB 數(shù)據(jù)庫(kù)名
collection — MongoDB 集合名
user — MongoDB 用戶
password — MongoDB 密碼

例子：

CREATE TABLE mongo_table
(
    key UInt64, 
    data String
) ENGINE = MongoDB('mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse');

注意：相對(duì)于MySQL，MongoDB遠(yuǎn)程表還不允許INSERT。此后就可以在ClickHouse上創(chuàng)建符合要求的引擎表，如MergeTree引擎，再通過(guò)以下SQL來(lái)進(jìn)行導(dǎo)入數(shù)據(jù)。

insert into ck_tb select * from mon_tb

MongoDB目前只作為一個(gè)外部引擎，不像MySQL還能作為一個(gè)函數(shù)進(jìn)行遠(yuǎn)程操作，所以MySQL數(shù)據(jù)處理中的方法二、三不適用于MongoDB數(shù)據(jù)的處理。

③ File數(shù)據(jù)導(dǎo)入到ClickHouse：外部引擎和函數(shù)

和MySQL引擎導(dǎo)入方法類似，支持并發(fā)讀，不支持并發(fā)插入。不支持ALTER、索引和副本

方法一：外部引擎（建立遠(yuǎn)程表）

CREATE TABLE file_engine_table (name String, value UInt32) ENGINE=File(Format)

File函數(shù)里的參數(shù)Format的取值可以看文檔，這里說(shuō)明下csv的例子。

-- 建立表
create table csv_table(id UInt64,name String)engine = File('CSV');

-- 在表目錄里創(chuàng)建文件或則導(dǎo)入文件，必須命名為data.CSV
$ cat data.CSV 
1,a
2,b
3,c
4,d
5,e

-- 這樣，在表里就可以看到數(shù)據(jù)了
:) select * from csv_table;

┌─id─┬─name─┐
│  1 │ a    │
│  2 │ b    │
│  3 │ c    │
│  4 │ d    │
│  5 │ e    │
└────┴──────┘

注意：File引擎的表，可以對(duì)其進(jìn)行SELECT、INSERT，不能進(jìn)行DELETE、UPDATE。此后就可以在ClickHouse上創(chuàng)建符合要求的引擎表，如MergeTree引擎，再通過(guò)以下SQL來(lái)進(jìn)行導(dǎo)入數(shù)據(jù)。

insert into ck_tb select * from csv_tb

方法二：

通過(guò)file函數(shù)導(dǎo)入數(shù)據(jù)，并且以表的形式展示，格式為：

select * from file(path, format, structure)

path — 參數(shù)user_files_path下的相對(duì)路徑，支持以下格式：*、?、{abc,def} 和 {N..M} ，其中 N、M — 數(shù)字、'abc'、'def' — 字符串。
format — 文件格式
structure — 表結(jié)構(gòu)。格式：'column1_name column1_type, column2_name column2_type, ...'

在config.xml文件中找到參數(shù)user_files_path，在該參數(shù)指定的目錄下創(chuàng)建一個(gè)csv文件：

$ cat test.csv 
1,a,123
2,b,234
3,c,345
4,d,456
5,e,567

然后通過(guò)SQL查詢：

:) SELECT * FROM file('test.csv', 'CSV', 'column1 UInt32, column2 String, column3 UInt32');
┌─column1─┬─column2─┬─column3─┐
│       1 │ a       │     123 │
│       2 │ b       │     234 │
│       3 │ c       │     345 │
│       4 │ d       │     456 │
│       5 │ e       │     567 │
└─────────┴─────────┴─────────┘

如果該目錄下有多個(gè)csv文件：test.csv、test1.csv、test2.csv，則可以通過(guò)通配符來(lái)進(jìn)行全部加載讀取：

:) SELECT * FROM file('test*.csv', 'CSV', 'column1 UInt32, column2 String, column3 UInt32');

┌─column1─┬─column2─┬─column3─┐
│       1 │ aaa     │     123 │
│       2 │ bbb     │     234 │
│       3 │ ccc     │     345 │
│       4 │ ddd     │     456 │
│       5 │ eee     │     567 │
└─────────┴─────────┴─────────┘
┌─column1─┬─column2─┬─column3─┐
│       1 │ a       │     123 │
│       2 │ b       │     234 │
│       3 │ c       │     345 │
│       4 │ d       │     456 │
│       5 │ e       │     567 │
└─────────┴─────────┴─────────┘
┌─column1─┬─column2─┬─column3─┐
│       1 │ aa      │     123 │
│       2 │ bb      │     234 │
│       3 │ cc      │     345 │
│       4 │ dd      │     456 │
│       5 │ ee      │     567 │
└─────────┴─────────┴─────────┘

此后就可以在ClickHouse上創(chuàng)建符合要求的引擎表，如MergeTree引擎，再通過(guò)以下SQL來(lái)進(jìn)行導(dǎo)入數(shù)據(jù)。

:) insert into csv_table_ck SELECT * FROM file('test*.csv', 'CSV', 'column1 UInt32, column2 String, column3 UInt32')

方法三： create + select

CREATE TABLE [IF NOT EXISTS] [db.]table_name
ENGINE = ENGINE 
AS
SELECT *
FROM file(path, format, structure)

例子：

:) create table csv_table_ck1 engine = MergeTree ORDER BY column1 as  SELECT * FROM file('test*.csv', 'CSV', 'column1 UInt32, column2 String, column3 UInt32');

注意：需要在file函數(shù)的參數(shù)structure指定的表結(jié)構(gòu)字段中選取字段進(jìn)行 ORDER BY 的指定設(shè)置。并且通過(guò)該方法的導(dǎo)入，不需要事先建立表，它會(huì)自動(dòng)根據(jù)select出來(lái)的數(shù)據(jù)按照需要?jiǎng)?chuàng)建符合要求的類型。

方法四：也可以通過(guò)輸入流來(lái)進(jìn)行數(shù)據(jù)的導(dǎo)入，clickhouse-local

$ echo -e "1,2\n3,4" | clickhouse-local -q "CREATE TABLE table (a Int64, b Int64) ENGINE = File(CSV, stdin); SELECT a, b FROM table; DROP TABLE table"

二、MySQL同步到ClickHouse（實(shí)驗(yàn)階段）

ClickHouse推出了MaterializeMySQL數(shù)據(jù)庫(kù)引擎，用于將MySQL服務(wù)器中的表映射到ClickHouse中。ClickHouse服務(wù)做為MySQL副本，讀取Binlog并執(zhí)行DDL和DML請(qǐng)求，實(shí)現(xiàn)了基于MySQL Binlog機(jī)制的業(yè)務(wù)數(shù)據(jù)庫(kù)實(shí)時(shí)同步功能：支持全量和增量同步，首次創(chuàng)建數(shù)據(jù)庫(kù)引擎時(shí)進(jìn)行一次全量復(fù)制，之后通過(guò)監(jiān)控binlog變化進(jìn)行增量數(shù)據(jù)同步。阿里云上也有介紹說(shuō)明。

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') [SETTINGS ...]

host:port — MySQL 地址
database — MySQL 數(shù)據(jù)庫(kù)名
user — MySQL 用戶名，具有MySQL庫(kù)的RELOAD、REPLICATION SLAVE、REPLICATION CLIENT以及SELECT PRIVILEGE權(quán)限
password — MySQL 密碼

使用MaterializeMySQL數(shù)據(jù)庫(kù)引擎，同步到ClickHouse集群上表的默認(rèn)引擎為ReplacingMergeTree，并會(huì)在表中增加2個(gè)虛擬列：

_version — 事務(wù)計(jì)數(shù)器，記錄數(shù)據(jù)版本信息。UInt64類型。
_sign — 刪除標(biāo)記，標(biāo)記該行是否刪除。TypeInt8類型:

1 — 未刪除
-1 — 已刪除

測(cè)試：

MaterializeMySQL數(shù)據(jù)庫(kù)引擎需要開(kāi)啟allow_experimental_database_materialize_mysql參數(shù)。即需要設(shè)置為1：

SET allow_experimental_database_materialize_mysql = 1

以上SET 只是更改了當(dāng)前會(huì)話中的值，分布式 DDL 在單獨(dú)的會(huì)話中執(zhí)行，SET 不影響它。應(yīng)該在服務(wù)器配置中全局啟用 allow_experimental_database_materialize_mysql 設(shè)置，如：

修改user.xml：

    <profiles>
        <!-- Default settings. -->
        <default>
...
            <allow_experimental_database_materialize_mysql>1</allow_experimental_database_materialize_mysql>
        </default>
...
    </profiles>

修改config.xml：

    <distributed_ddl>
...
        <path>/clickhouse/task_queue/ddl</path>
        <allow_experimental_database_materialize_mysql>1</allow_experimental_database_materialize_mysql>
...
    </distributed_ddl>

修改這些配置是動(dòng)態(tài)生效的，可以查看該參數(shù)是否修改成功：

:) SELECT *　FROM system.settings　WHERE name = 'allow_experimental_database_materialize_mysql';

┌─name──────────────────────────────────────────┬─value─┬─changed─┬─description─────────────────────────────────────────────────┬─min──┬─max──┬─readonly─┬─type─┐
│ allow_experimental_database_materialize_mysql │ 1     │       1 │ Allow to create database with Engine=MaterializeMySQL(...). │ ???? │ ???? │        0 │ Bool │
└───────────────────────────────────────────────┴───────┴─────────┴─────────────────────────────────────────────────────────────┴──────┴──────┴──────────┴──────┘

現(xiàn)在可以開(kāi)始MaterializeMySQL數(shù)據(jù)庫(kù)引擎來(lái)同步MySQL數(shù)據(jù)庫(kù)了。需要注意的是MySQL的binlog格式為Row，并且開(kāi)啟GTID。

①：創(chuàng)建MaterializeMySQL數(shù)據(jù)庫(kù)：同步MySQL的ck_test庫(kù)中的表

:) create database mysql_2_ck ENGINE = MaterializeMySQL('10.10.10.10:3306','ck_test','test','test');

②：全量同步

-- 全量：
通過(guò) select * from tb 拉取MySQL的全量數(shù)據(jù)進(jìn)行同步，再在本地ClickHouse上回放

③：增量同步

-- 增量：
通過(guò)訂閱MySQL binlog來(lái)進(jìn)行增量同步，在mysql上可以看到 Binlog Dump GTID 的訂閱線程，再在本地ClickHouse上回放

④：記錄MySQL Binlog信息

-- 記錄 binlog 信息
在<path>指定的目錄下的metadata/dbname中保存binlog和position：<path>/metadata/dbname/.metadata

通過(guò)①~⑤同步關(guān)系已經(jīng)搭建完成，為了測(cè)試同步效果，進(jìn)行測(cè)試：測(cè)試同步包括：insert，update，delete，alter，create，drop，truncate等大部分DML和DDL操作

說(shuō)明：在①中已經(jīng)建立了ClickHouse【mysql_2_ck】和MySQL【ck_test】庫(kù)的同步，后續(xù)操作在該庫(kù)中進(jìn)行。

新建表
-- mysql> CREATE TABLE employees (
    ->     emp_no      INT             NOT NULL,
    ->     birth_date  DATE            NOT NULL,
    ->     first_name  VARCHAR(14)     NOT NULL,
    ->     last_name   VARCHAR(16)     NOT NULL,
    ->     gender      CHAR(3)         NOT NULL,    
    ->     hire_date   DATE            NOT NULL,
    ->     PRIMARY KEY (emp_no)
    -> ) ENGINE=INNODB;

-- clickhouse :) show create table employees\G
statement: CREATE TABLE mysql_2_ck.employees
(
    `emp_no` Int32,
    `birth_date` Date,
    `first_name` String,
    `last_name` String,
    `gender` String,
    `hire_date` Date,
    `_sign` Int8 MATERIALIZED 1,
    `_version` UInt64 MATERIALIZED 1,
    INDEX _version _version TYPE minmax GRANULARITY 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(emp_no, 4294967)
ORDER BY tuple(emp_no)
SETTINGS index_granularity = 8192


新增數(shù)據(jù)
-- mysql> INSERT INTO `employees` VALUES (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
    -> (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
    -> (10003,'1959-12-03','Parto','Bamford','M','1986-08-28'),
    -> (10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01'),
    -> (10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12'),
    -> (10006,'1953-04-20','Anneke','Preusig','F','1989-06-02'),
    -> (10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10'),
    -> (10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15'),
    -> (10009,'1952-04-19','Sumant','Peac','F','1985-02-18'),
    -> (10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24'),
    -> (10011,'1953-11-07','Mary','Sluis','F','1990-01-22'),
    -> (10012,'1960-10-04','Patricio','Bridgland','M','1992-12-18'),
    -> (10013,'1963-06-07','Eberhardt','Terkki','M','1985-10-20'),
    -> (10014,'1956-02-12','Berni','Genin','M','1987-03-11'),
    -> (10015,'1959-08-19','Guoxiang','Nooteboom','M','1987-07-02'),
    -> (10016,'1961-05-02','Kazuhito','Cappelletti','M','1995-01-27'),
    -> (10017,'1958-07-06','Cristinel','Bouloucos','F','1993-08-03'),
    -> (10018,'1954-06-19','Kazuhide','Peha','F','1987-04-03');

-- clickhouse :) select * from mysql_2_ck.employees;
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name───┬─gender─┬──hire_date─┐
│  10002 │ 2143-11-07 │ Bezalel    │ Simmel      │ F      │ 1985-11-21 │
│  10006 │ 2132-09-24 │ Anneke     │ Preusig     │ F      │ 1989-06-02 │
│  10011 │ 2133-04-13 │ Mary       │ Sluis       │ F      │ 1990-01-22 │
│  10015 │ 2139-01-23 │ Guoxiang   │ Nooteboom   │ M      │ 1987-07-02 │
│  10016 │ 2140-10-06 │ Kazuhito   │ Cappelletti │ M      │ 1995-01-27 │
└────────┴────────────┴────────────┴─────────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10004 │ 2133-10-05 │ Chirstian  │ Koblick   │ M      │ 1986-12-01 │
│  10009 │ 2131-09-24 │ Sumant     │ Peac      │ F      │ 1985-02-18 │
│  10013 │ 2142-11-11 │ Eberhardt  │ Terkki    │ M      │ 1985-10-20 │
│  10018 │ 2133-11-23 │ Kazuhide   │ Peha      │ F      │ 1987-04-03 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10003 │ 2139-05-09 │ Parto      │ Bamford   │ M      │ 1986-08-28 │
│  10007 │ 2136-10-27 │ Tzvetan    │ Zielinski │ F      │ 1989-02-10 │
│  10010 │ 2142-11-05 │ Duangkaew  │ Piveteau  │ F      │ 1989-08-24 │
│  10014 │ 2135-07-19 │ Berni      │ Genin     │ M      │ 1987-03-11 │
│  10017 │ 2137-12-10 │ Cristinel  │ Bouloucos │ F      │ 1993-08-03 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10001 │ 2133-02-06 │ Georgi     │ Facello   │ M      │ 1986-06-26 │
│  10005 │ 2134-06-27 │ Kyoichi    │ Maliniak  │ M      │ 1989-09-12 │
│  10008 │ 2137-07-26 │ Saniya     │ Kalloufi  │ M      │ 1994-09-15 │
│  10012 │ 2140-03-10 │ Patricio   │ Bridgland │ M      │ 1992-12-18 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘


刪除數(shù)據(jù)
-- mysql> delete from employees where emp_no >10010;
Query OK, 8 rows affected (0.01 sec)

-- clickhouse :) select * from mysql_2_ck.employees;
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10001 │ 2133-02-06 │ Georgi     │ Facello   │ M      │ 1986-06-26 │
│  10005 │ 2134-06-27 │ Kyoichi    │ Maliniak  │ M      │ 1989-09-12 │
│  10008 │ 2137-07-26 │ Saniya     │ Kalloufi  │ M      │ 1994-09-15 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10004 │ 2133-10-05 │ Chirstian  │ Koblick   │ M      │ 1986-12-01 │
│  10009 │ 2131-09-24 │ Sumant     │ Peac      │ F      │ 1985-02-18 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10003 │ 2139-05-09 │ Parto      │ Bamford   │ M      │ 1986-08-28 │
│  10007 │ 2136-10-27 │ Tzvetan    │ Zielinski │ F      │ 1989-02-10 │
│  10010 │ 2142-11-05 │ Duangkaew  │ Piveteau  │ F      │ 1989-08-24 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10002 │ 2143-11-07 │ Bezalel    │ Simmel    │ F      │ 1985-11-21 │
│  10006 │ 2132-09-24 │ Anneke     │ Preusig   │ F      │ 1989-06-02 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘


修改數(shù)據(jù)
-- mysql> update employees set hire_date = hire_date+1 where emp_no <10005;
Query OK, 4 rows affected (0.01 sec)

-- clickhouse :) select * from mysql_2_ck.employees;
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10001 │ 2133-02-06 │ Georgi     │ Facello   │ M      │ 1986-06-27 │
│  10005 │ 2134-06-27 │ Kyoichi    │ Maliniak  │ M      │ 1989-09-12 │
│  10008 │ 2137-07-26 │ Saniya     │ Kalloufi  │ M      │ 1994-09-15 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10003 │ 2139-05-09 │ Parto      │ Bamford   │ M      │ 1986-08-29 │
│  10007 │ 2136-10-27 │ Tzvetan    │ Zielinski │ F      │ 1989-02-10 │
│  10010 │ 2142-11-05 │ Duangkaew  │ Piveteau  │ F      │ 1989-08-24 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10002 │ 2143-11-07 │ Bezalel    │ Simmel    │ F      │ 1985-11-22 │
│  10006 │ 2132-09-24 │ Anneke     │ Preusig   │ F      │ 1989-06-02 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘
┌─emp_no─┬─birth_date─┬─first_name─┬─last_name─┬─gender─┬──hire_date─┐
│  10004 │ 2133-10-05 │ Chirstian  │ Koblick   │ M      │ 1986-12-02 │
│  10009 │ 2131-09-24 │ Sumant     │ Peac      │ F      │ 1985-02-18 │
└────────┴────────────┴────────────┴───────────┴────────┴────────────┘


修改表結(jié)構(gòu)
- 新增字段
-- mysql> alter table employees add age int after gender;
Query OK, 0 rows affected (0.08 sec)

-- clickhouse :) desc mysql_2_ck.employees;
┌─name───────┬─type────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ emp_no     │ Int32           │              │                    │         │                  │                │
│ birth_date │ Date            │              │                    │         │                  │                │
│ first_name │ String          │              │                    │         │                  │                │
│ last_name  │ String          │              │                    │         │                  │                │
│ gender     │ String          │              │                    │         │                  │                │
│ age        │ Nullable(Int32) │              │                    │         │                  │                │
│ hire_date  │ Date            │              │                    │         │                  │                │
│ _sign      │ Int8            │ MATERIALIZED │ 1                  │         │                  │                │
│ _version   │ UInt64          │ MATERIALIZED │ 1                  │         │                  │                │
└────────────┴─────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

- 刪除字段
-- mysql> alter table employees drop age;
Query OK, 0 rows affected (0.09 sec)

-- clickhouse :) desc mysql_2_ck.employees;
┌─name───────┬─type───┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ emp_no     │ Int32  │              │                    │         │                  │                │
│ birth_date │ Date   │              │                    │         │                  │                │
│ first_name │ String │              │                    │         │                  │                │
│ last_name  │ String │              │                    │         │                  │                │
│ gender     │ String │              │                    │         │                  │                │
│ hire_date  │ Date   │              │                    │         │                  │                │
│ _sign      │ Int8   │ MATERIALIZED │ 1                  │         │                  │                │
│ _version   │ UInt64 │ MATERIALIZED │ 1                  │         │                  │                │
└────────────┴────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

新增索引
-- ClickHouse不會(huì)同步

刪除索引
-- ClickHouse不會(huì)同步

修改字段長(zhǎng)度
-加長(zhǎng)
-- mysql> alter table employees modify age bigint;

-- clickhouse :) desc employees;
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ emp_no     │ Int32            │              │                    │         │                  │                │
│ birth_date │ Date             │              │                    │         │                  │                │
│ first_name │ String           │              │                    │         │                  │                │
│ last_name  │ String           │              │                    │         │                  │                │
│ gender     │ String           │              │                    │         │                  │                │
│ age        │ Nullable(Int64)  │              │                    │         │                  │                │
│ address    │ Nullable(String) │              │                    │         │                  │                │
│ hire_date  │ Date             │              │                    │         │                  │                │
│ _sign      │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version   │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

-改短
-- mysql> alter table employees modify age int;

-- clickhouse :) desc employees;
┌─name───────┬─type─────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ emp_no     │ Int32            │              │                    │         │                  │                │
│ birth_date │ Date             │              │                    │         │                  │                │
│ first_name │ String           │              │                    │         │                  │                │
│ last_name  │ String           │              │                    │         │                  │                │
│ gender     │ String           │              │                    │         │                  │                │
│ age        │ Nullable(Int32)  │              │                    │         │                  │                │
│ address    │ Nullable(String) │              │                    │         │                  │                │
│ hire_date  │ Date             │              │                    │         │                  │                │
│ _sign      │ Int8             │ MATERIALIZED │ 1                  │         │                  │                │
│ _version   │ UInt64           │ MATERIALIZED │ 1                  │         │                  │                │
└────────────┴──────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

修改字段名
-- ClickHouse 不支持，同步報(bào)錯(cuò)

修改字段備注
-- ClickHouse 不會(huì)同步

清空表
-- mysql> truncate table employees;
Query OK, 0 rows affected (0.02 sec)

-- ClickHouse:) select * from mysql_2_ck.employees;
0 rows in set. Elapsed: 0.002 sec. 

重命名表
-- ClickHouse 正常同步

刪除表
-- ClickHouse 正常同步

在測(cè)試中發(fā)現(xiàn)有以下幾個(gè)情況會(huì)導(dǎo)致復(fù)制同步異常，可能不全，后續(xù)有情況會(huì)繼續(xù)更新，目前發(fā)現(xiàn)的有：

沒(méi)有主鍵
字段類型為：Enum、bit、time、json
修改字段名

出現(xiàn)同步異常之后，會(huì)導(dǎo)致正常的ClickHouse上的表也不能讀取，修復(fù)則需要?jiǎng)h除整個(gè)庫(kù)，再重新同步。如果使用MaterializeMySQL同步MySQL，不允許以上導(dǎo)致同步異常的情況發(fā)生。

因?yàn)?a target="_blank" rel="noopener nofollow">MaterializeMySQL目前屬于實(shí)驗(yàn)階段，如果需要此功能，需要做好各種測(cè)試和驗(yàn)證。

除了自帶的MaterializeMySQL可以同步MySQL數(shù)據(jù)之外，還可以用 Bifrost 來(lái)進(jìn)行同步，該同步工具可以避免MaterializeMySQL同步字段類型的問(wèn)題，單也有一些限制。具體的可以看官方介紹，該工具支持支持全量，增量同步。

總結(jié)

本文介紹了幾種ClickHouse的導(dǎo)入方法，以及MySQL同步到ClickHouse的方法，這種數(shù)據(jù)批量導(dǎo)入和同步極大的方便了遷移數(shù)據(jù)的成本。后期官方如果完善MaterializeMySQL，相信ClickHouse將會(huì)進(jìn)一步提高在OLAP上的使用率。

posted @ 2021-07-22 14:49 jyzhou 閱讀(10186) 評(píng)論(0) 收藏舉報(bào)

刷新頁(yè)面返回頂部

DBA's Record

ClickHouse學(xué)習(xí)系列之八【數(shù)據(jù)導(dǎo)入遷移&同步】

背景

說(shuō)明

一、其他數(shù)據(jù)庫(kù)導(dǎo)入到ClickHouse

① MySQL數(shù)據(jù)導(dǎo)入到ClickHouse

② MongoDB數(shù)據(jù)導(dǎo)入到ClickHouse

③ File數(shù)據(jù)導(dǎo)入到ClickHouse：外部引擎和函數(shù)

二、MySQL同步到ClickHouse（實(shí)驗(yàn)階段）

總結(jié)

公告