ClickHouse介绍和使用

官网：https://clickhouse.com/
测试平台：https://play.clickhouse.com/play?user=play

1. 简介

ClickHouse 是俄罗斯搜索巨头 Yandex 公司早 2016年 开源的一个极具 " 战斗力 " 的实时数据分析数据库，开发语言为C++
是一个用于联机分析 (OLAP:Online Analytical Processing) 的列式数据库管理系统(DBMS:Database Management System)，简称 CK
工作速度比传统方法快100-1000倍，ClickHouse 的性能超过了目前市场上可比的面向列的DBMS。每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据。

2. ClickHouse特点

列式存储

几乎覆盖了标准SQL 的大部分语法，包括 DDL 和 DML，以及配套的各种函数，用户管理及权限管理，数据的备份与恢复。

多样化引擎

高吞吐写入能力

官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力，按照每行100Byte 估算，大约相当于 50W-200W 条/s 的写入速度

数据分区与线程级并行

ClickHouse 将数据划分为多个 partition，每个 partition 再进一步划分为多个 index granularity(索引粒度)，然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条 Query 就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。所以，ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务，ClickHouse 并不是强项。

3. 数据类型

3.1. 整型

有符号

Int8、Int16、Int32、Int64、Int128、Int256
1

无符号

UInt8、UInt16、UInt32、UInt64、UInt128、UInt256
1

3.2. 浮点型

float32、float64
1

3.3. Decimal型

Decimal()、Decimal32()、Decimal64()、Decimal128()
1

3.4. 布尔型

可以使用 UInt8 类型，取值限制为 0 或 1

3.5. 字符串

String

FixedString(N)
1
2
3

3.6. 枚举类型

Enum8、Enum16
1

3.7. 时间类型

Date、DateTime、DateTime64
1

4. 表引擎

• 数据的存储方式和位置。
• 支持哪些查询操作以及如何支持。
• 数据的并发访问。
• 数据索引的使用。
• 是否可以支持多线程请求。
• 是否可以支持数据复制。

4.1. TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时测试用。

create table t_tinylog ( id String, name String) engine=TinyLog;
1

4.2. Memory

Memory 表引擎直接将数据保存在内存中，数据即不会被压缩也不会被格式转换，因为基于内存，所以服务重启后会丢失。

4.3. MergeTree

该系列引擎是执行高负载任务的最通用和最强大的表引擎，它们的特点是可以快速插入数据以及进行后续的数据处理。支持索引和分区，地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree，还衍生出很多其他引擎

4.3.1. partition by分区（可选）

作用
分区的目的主要是降低扫描的范围，优化查询速度
如果不填
所有数据都存放到名为all的数据分区
分区目录
MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中
并行
分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。
数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中。

optimize table xxxx final;
1

4.3.2. primary key 主键(可选)

主键特点

• ClickHouse 中的 MergeTree 默认是
  8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。
• 索引字段不会全部存储，而是隔一段距离记录一个，查询时先找到一个区间，然后再去表中查具体位置
• 数据量小，可以指定间隔，间隔默认是8192，索引树不会存储每个索引字段，

• 可以重复
• 稀疏索引
• 索引粒度

4.3.3. order by（必选）

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。
order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照order by 的字段进行处理（比如后面会讲的去重和汇总）。
要求：主键必须是 order by 字段的前缀字段
比如 order by 字段是 (id,sku_id)，那么主键必须是 id

4.3.4. 二级索引

能够为非主键字段设置二级索引

4.3.5. 数据 TTL

列级TTL

建立带有TTL列的表,这里定义total_amount 这一列的数据30s后结束生命周期

create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2)  TTL create_time + interval 30 SECOND,
create_time  Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
1
2
3
4
5
6
7
8
9

表级TTL

可以在MergeTree的表参数中增加TTL表达式 为整张表设置TTL

create table stu(
id Int32,
name String,
create_time DateTime
)engine = MergeTree
partition by toYYYYMM(create_time)
order by id TTL create_time + interval 30  SECOND DELETE ;
1
2
3
4
5
6
7

4.4. ReplacingMergeTree

ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

去重时机
数据的去重只会在分区合并的过程中进行

去重范围
如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。
所以 ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。
ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按照插入顺序保留最后一条。

CREATE TABLE t_order_rmt (
id UInt32,
sku_id String,
total_amount DECIMAL ( 16, 2 ),
create_time DATETIME
) ENGINE = ReplacingMergeTree ( create_time )
PARTITION BY toYYYYMMDD ( create_time )
PRIMARY KEY ( id )
ORDER BY ( id, sku_id );
1
2
3
4
5
6
7
8
9

4.5. SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree
以 SummingMergeTree（）中指定的列作为汇总数据列
以 order by 的列为准，作为维度列
不在一个分区的数据不会被聚合
其他的列按插入顺序保留第一行
对于没来得及合并分区的临时数据，还是需要使用 sum(total_amount) 进行聚合

create table t_order_smt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine = SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id );
1
2
3
4
5
6
7
8
9

5. SQL 操作

基本上来说传统关系型数据库（以 MySQL 为例）的 SQL 语句，ClickHouse 基本都支持

5.1. select

ClickHouse 基本上与标准 SQL 差别不大，支持子查询、各种 JOIN、各种函数…

SELECT [DISTINCT] expr_list
    [FROM [db.]table | (subquery) | table_function] [FINAL]
    [SAMPLE sample_coeff]
    [ARRAY JOIN ...]
    [GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list
    [PREWHERE expr]
    [WHERE expr]
    [GROUP BY expr_list] [WITH TOTALS]
    [HAVING expr]
    [ORDER BY expr_list]
    [LIMIT [n, ]m]
    [UNION ALL ...]
    [INTO OUTFILE filename]
    [FORMAT format]
    [LIMIT n BY columns]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.2. insert

基本与标准 SQL（MySQL）基本一致

标准

insert into [table_name] select a,b,c from [table_name_2]
1

从表到表的插入

insert into [table_name] values(…),(….)
1

5.3. update 和 delete

ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation 查询，它可以看做 Alter 的一种。
虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。
每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作

删除操作

alter table t_order_smt delete where sku_id ='sku_001';
1

修改操作

alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;
1

由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

5.4. alter

同 MySQL 的修改字段基本一致

新增字段

alter table tableName add column newcolname String after col1;
1

删除字段

alter table tableName drop column newcolname;
1

修改字段类型

alter table tableName modify column newcolname String;
1