ClickHouse-sync-to-MySQLDate

前言

ClickHouse 这款产品大家都听说过很快，但是到底有多恐怖？
ClickHouse 到底是什么？

介绍

ClickHouse最初是为 Yandex.Metrica 世界第二大Web分析平台而开发的。多年来一直作为该系统的核心组件被该系统持续使用着。目前为止，该系统在ClickHouse中有超过13万亿条记录，并且每天超过200多亿个事件被处理。它允许直接从原始数据中动态查询并生成报告。本文简要介绍了ClickHouse在其早期发展阶段的目标。

Yandex.Metrica基于用户定义的字段，对实时访问、连接会话，生成实时的统计报表。这种需求往往需要复杂聚合方式，比如对访问用户进行去重。构建报表的数据，是实时接收存储的新数据。

截至2014年4月，Yandex.Metrica每天跟踪大约120亿个事件（用户的点击和浏览）。为了可以创建自定义的报表，我们必须存储全部这些事件。同时，这些查询可能需要在几百毫秒内扫描数百万行的数据，或在几秒内扫描数亿行的数据。

什么是ClickHouse ?

ClickHouse 是面向 OLAP 的分布式列式 DBMS.

ClickHouse的显著特性

真正的面向列的DBMS
数据高效压缩
磁盘存储的数据
多核并行处理

在多个服务器上分布式处理
- SQL语法支持

向量化引擎
实时数据更新
索引
适合在线查询
支持近似预估计算
支持嵌套的数据结构
支持数组作为数据类型
支持限制查询复杂性以及配额
复制数据复制和对数据完整性的支持

OLAP场景的关键特征

大多数是读请求
数据总是以相当大的批(> 1000 rows)进行写入
不修改已添加的数据
每次查询都从数据库中读取大量的行，但是同时又仅需要少量的列
宽表，即每个表包含着大量的列
较少的查询(通常每台服务器每秒数百个查询或更少)
对于简单查询，允许延迟大约50毫秒
列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)
处理单个查询时需要高吞吐量（每个服务器每秒高达数十亿行）
事务不是必须的
对数据一致性要求低
每一个查询除了一个大表外都很小
查询结果明显小于源数据，换句话说，数据被过滤或聚合后能够被盛放在单台服务器的内存中

列式数据库更适合OLAP场景的原因

列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)，下面详细解释了原因(通过图片更有利于直观理解)：
行式
row_oriented
列式
column_oriented
看到差别了么？下面将详细介绍为什么会发生这种情况。
1.针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。
2.由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。
3.由于I/O的降低，这将帮助更多的数据被系统缓存。

$ clickhouse-client
ClickHouse client version 0.0.52053.
Connecting to localhost:9000.
Connected to ClickHouse server version 0.0.52053.

:) SELECT CounterID, count() FROM hits GROUP BY CounterID ORDER BY count() DESC LIMIT 20

SELECT
    CounterID,
    count()
FROM hits
GROUP BY CounterID
ORDER BY count() DESC
LIMIT 20

┌─CounterID─┬──count()─┐
│    114208 │ 56057344 │
│    115080 │ 51619590 │
│      3228 │ 44658301 │
│     38230 │ 42045932 │
│    145263 │ 42042158 │
│     91244 │ 38297270 │
│    154139 │ 26647572 │
│    150748 │ 24112755 │
│    242232 │ 21302571 │
│    338158 │ 13507087 │
│     62180 │ 12229491 │
│     82264 │ 12187441 │
│    232261 │ 12148031 │
│    146272 │ 11438516 │
│    168777 │ 11403636 │
│   4120072 │ 11227824 │
│  10938808 │ 10519739 │
│     74088 │  9047015 │
│    115079 │  8837972 │
│    337234 │  8205961 │
└───────────┴──────────┘

20 rows in set. Elapsed: 0.153 sec. Processed 1.00 billion rows, 4.00 GB (6.53 billion rows/s., 26.10 GB/s.)

:)

ClickHouse SQL

Creating a Table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

Example of sections setting

1	ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

MySQL 数据导入测试

测试一

# du出的表大小
5.5G    test_1.ibd
# ClickHouse操作语句
CREATE TABLE test_1
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('host:port', 'dbtest', 'test_1', 'user', 'password') 
# 耗时和平均速度
0 rows in set. Elapsed: 137.251 sec. Processed 18.59 million rows, 7.34 GB (135.43 thousand rows/s., 53.48 MB/s.)

测试二

# 另一个表
20G     test_2.ibd
CREATE TABLE test_2
ENGINE = MergeTree
ORDER BY id AS
SELECT *
FROM mysql('host:port', 'dbtest', 'test_2', 'user', 'password') 
# 不知道为啥这表这么快就导入了 貌似是行少，但是表的总大小大啊
0 rows in set. Elapsed: 44.389 sec. Processed 13.03 million rows, 1.44 GB (293.44 thousand rows/s., 32.35 MB/s.)

参考

ClickHouse 官方资料