ClickHouse-入门学习笔记

学习背景：目前一直在做BI相关，我们的指标表数据量随之时间推移不断上升，导致查服务越来越慢。问题产生，我老大@Michael Gu见招拆招，提到了clickhouse列式存储数据库，聚合性能非常优秀，纵然mysql的索引也非常强大，但是在数据量大的情况下也有些吃力，因此对它深度学习，便于以后迁移数据。

提示：clickhouse官网：https://clickhouse.com/docs/zh/

数据结构：

注：数字代表位数

有符号整型
1. int8：【与java的byte类型范围一致】
2. int16：【与java的short类型范围一致】
3. int32：【与java的int类型范围一致】
4. int64：【与java的long类型范围一致】
无符号整型
1. Uint8：【0~2^8-1】
2. Uint16：【0~2^16-1】
3. Uint32：【0~2^32-1】
4. Uint64：【0~2^64-1】
浮点型
1. float32：java中的float
2. float64：java中的double。金钱相关的不要用float，要用decimal
布尔
1. 没有单独存放布尔的数据类型，但是可以用int8，0和1来代表
Decimal（金额相关的）
1. decimal32：相当于decimal(9-s,s)，例如：decimal(5)，整数加小数一共9位，小数点后5位，不会四舍五入。
2. decimal64：相当于decimal(18-s,s)，同上。
3. decimal128：相当于decimal(38-s,s)，同上。
字符串
1. String：同java
2. FixedString(N)：当字符串的字节长度不为N的时候，会向字符串的右边补空字节来达到N长度。
enum枚举类型
1. 用来保存：String和Integer的对应关系。
时间类型：
1. date：年月日
2. dateTime：年月日时分秒
3. dateTime64：年月日时分秒亚秒
Array(T)：数组类型
1. T类型元素组成的数组
2. 对二维数组支持的不太好
3. 用法：array('1','2')。或者['1','2']，类型必须一致，不然会报错。
Nullable(数据类型)：
1. 使用null会对性能产生影响，可以使用业务中无意义的数据进行代替。'' or -1 or 'null'。

表引擎作用：

管理数据的存储方式，例如：不能在merge Tree中存储多维数组。写到哪里以及从哪里读区取例如：本地path：var/lib/clickHouse/。同时也可以集成hadoop和kafka，那么此时，数据就在远程。
支持哪些查询以及如何支持。
支持并发数据访问，线程级的并行。
对索引的支持。
支持多线程的请求。
数据复制参数
注意：引擎的名称大小写敏感。

所有的表引擎及其特点

TinyLog：以列文件的形式保存在磁盘上，不支持索引，没有并发控制，一般存储少量数据。
1. 生产环境作用不大。
Memory：
1. 基于内存，快，不靠谱，重启后数据消失，读写不会阻塞，不支持索引，简单查询有非常高的性能。

MergeTree：ClickHouse中最牛逼的存储引擎

MergeTree

# 建表语句与mysql类似，注意分区字段，它也可以有主键（主键不一定唯一，可以重复），同时也会创建索引。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

# order by字段是必须的。
# 具体的字段解释官网有解释：https://clickhouse.com/docs/zh/engines/table-engines/mergetree-family/versionedcollapsingmergetree/

部分关键字解析：
1. partition by：分区字段，避免全表扫描，缩小范围，优化查询速度。注意，不要使用错误最终只产生一个分区。分区的本质就是分目录。

MergeTree是以列文件+索引文件+表定义文件组成。linux下，clickhouse的所有文件都存放在/var/lib/clickhouse/下，

我们需要关心两个文件，data：存放数据的文件。metadata：表结构文件。

文件名称	文件作用
bin文件	数据文件
mrk文件：标记文件	在索引文件和数据文件之前起到了桥梁的作用
primary.idx文件	主键索引文件，加快执行效率
min_max_create_time	分区键的最大最小值
checksums	检验文件的正确性

并行：分区后，对涉及到跨区查询的处理，clickhouse会进行并行处理
数据写入和分区合并
1. 同一批次插入的数据会产生一个临时分区，不会并入任何一个分区中，写入后的某个时刻，clickhouse才会将插入的文件合并到已有分区中。
2. 强制合并命令：
  1. optimize table [表名] final，强制合并表数据。
  2. optimize table partition '分区名' final，强制合并表数据。
primary_key主键索引：
1. 主键可以包含多个列。
2. 没有唯一约束：意味着可以重复，平时主键放在where条件中。
3. 它是稀疏索引，类似于redis的跳表。优点：索引的数据量小，配合索引粒进行二分查找，避免全表扫描。
4. 索引粒官方默认为为8192，可以打开一个库，通过 show create table [表名] 就可以查看到。修改的场景为：几万条数据的id都是重复的，好几万个1，那么此时创建的稀疏索引没有意义。
order by(可以是多个字断)
1. 创建表必写，分区内进行排序，如果不排序，稀疏索引无法找数。
2. order by的字段必须是主键的前缀，且不能跳过一个字段。
二级索引/跳数索引
1. 创建方式：在所有的字段后面加。在一级索引上再次组索引段。
```
INDEX [给当前索引的命名] [当前索引使用的字段] TYPE [minmax] GRANULARITY 粒度值
```
2. 二级索引的作用：
  1. 保证在id重复的时候也能更好的确定范围，提升查询速度
数据TTL
1. 可以对表或列设置其存活时间，对离线场景作用不大，适合于实时场景，例如：最新数据进行用户画像。
2. 字段创建方式
  1. 在需要进行TTL的字段后面追加：TTL [数据的创建时间且数据的创建时间必须为DateTime类型且这个列不能为主键id] + interval [阈值] [SECOND/MINNUTE/DAY/WEEK/YEAR/YEAR/MONTH/HOUR]。时间单位具体的请看clickhouse官网表引擎方面。
3. 表的创建方式：
  1. alter table 表名 MODIFY TTL [数据的创建时间且数据的创建时间必须为DateTime类型且这个列不能为主键id] + interval [阈值] [SECOND/MONTH/HOUR]
4. 时间到期后的动作：
  1. delete 默认行为
  2. move to disk ‘aaa’

ReplacingMergeTree
1. 是MergeTree的一个变种，存储过程完全继承mergeTree，增加了去重功能。
  1. MergeTree ReplacingMergeTree
    primary key
    可以重复，不具备唯一约束
    
    功能。
    根据order by的字段进行去重
2. 去重时机
  1. 只有在合并的时候才会进行去重，合并数据的时机是未知的，所以只能保证数据的最终一致性。
3. 去重范围
  1. 只能在分区范围内进行去重，不能执行跨分区的去重。
4. 建表语句
  1. ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  (
      name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
      name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
      ...
  ) ENGINE = ReplacingMergeTree([ver])
  [PARTITION BY expr]
  [ORDER BY expr]
  [SAMPLE BY expr]
  [SETTINGS name=value, ...]
```
2. ENGINE = ReplacingMergeTree([ver])
  1. 括号里的内容是重复时该保留哪条数据的依据，如果传入了字段，以最大的作为保留，如果没有声明，则以最后插入的进行保留，相当于版本控制工具。
SummingMergeTree
1. 更擅长于做聚合场景，如果使用普通的mergeTree的话，对存储空间的开销和临时聚合的开销都是比较大的。
2. SummingMergeTree是进行预聚合的一种引擎，预聚合的时机为合并数据时。且只能在本分区内进行聚合。
3. 建表语句
  1. ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
  (
      name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
      name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
      ...
  ) ENGINE = SummingMergeTree([columns]) 注意：columns必须是数值类型
  [PARTITION BY expr]
  [ORDER BY expr]
  [SAMPLE BY expr]
  [SETTINGS name=value, ...]
```
4. 在同一批次插入的才会被聚合，在插入完毕并合并的那一刻已经聚合好了。
5. 开发建议：
  1. 设计聚合表的话，流水号，唯一键可以去掉，只保留聚合维度，也就是手动指定聚合列。
  2. 虽然引擎帮咋们做了预聚合，但是，sql语句该怎么写还是要怎么写的，但是根据他的特性，还是有部分数据没有聚合进去, select sum(*) from table where **。
各种引擎的使用带来的一些问题
1. 数据幂等性问题（定时任务执行一半失败，重启定时任务，第二次全量插入完成）
  1. 重复写入带来的数据重复问题，采用ReplacingMergeTree，但是用了这个引擎并不能彻底的解决这个问题，如果分两波合并（注：去重操作会在合并过程中执行，合并时机未知），还是会出现数据不一致问题。再者，线上操作合并不现实，且是否会影响到业务。
ClickHouse的sql与标准sql不一样的地方
1. Insert语法基本相同
2. 关注Update和Delete语法
  1. update和delete被称为Mutation查询，该操作支持 MergeTree系列表，包含支持复制功能的表。可以看作是alter的一种，Mutation是一种很重的操作，且不支持事务，重的原因在他会放弃数据原有的分区，重建分区，一段时间后再合并分区，所以最好一次批量变更好，不要频繁的进行小数量的变更。
  2. 删除
    1. ```
    ALTER TABLE [db.]table DELETE WHERE filter_expr;
```
3. 更新
  1. ```
  ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr;
```
3. select
  1. 支持子查询
  2. 支持with子句，相当于一张临时表。
  3. 支持各种join，但是join无法使用缓存，尽量避免使用join，下面是join的工作原理：
    1. 单节点的数据片
    2. 分布式的数据片
    3. 针对这种情况：
      1. 我们mysql/hive的join，是小表驱动大表。
      2. 但是在clickhouse中，如果要小表驱动大表，内存会超出限制，导致语句都跑不完。
4. 严格来讲，clickhouse不适合做upsert
  1. 用别的方案也可以作为代替，比如说版本号，或者标记字段。
5. 对函数的支持
  1. 官网的函数文档：简介 | ClickHouse文档，非常清晰全面。
6. group by增加了一些语句
  1. with rollup：上卷
  2. with cube ：多维分析，将group by 的字段进行更细粒度的划分
  3. with total：总计
  4. 使用方式：正常语句后进行追加对应的单词即可
7. Alter语法与Mysql一致
  1. 新增字段
  2. 修改字段类型：注意小转大可以，但是大转小不行。
  3. 删除字段
8. 副本
  1. 保障数据的高可用，及时一台clickhouse宕机，也可以从别的节点获取数据。
  3. 互为副本，目的是去中心化，但是并没有解决数据的横向扩容，也就是分布式的数据分片。
  4. 要解决分布式的数据分片问题，需要引入Distributed表引擎把数据拼接在一起进行使用。但是并不是所有的企业都只会进行副本保障高可用，不会进行数据分片，目的是为了避免降低查询性能以及减少操作集群的复杂性。
  5. 读取过程
  6. 写入过程
集成的其他引擎：
1. mysql，mongo，RabbitMq，相当于打通一个通道，省区了数据导入的过程。
2. 例：mysql引擎的使用可以访问官网来进一步了解https://clickhouse.com/docs/zh/engines/database-engines/mysql/