文章目录
- 第1章 ClickHouse 入门
- 1.1 ClickHouse 的特点
- 1.1.1 列式存储
- 1.1.2 DBMS 的功能
- 1.1.3 多样化引擎
- 1.1.4 高吞吐写入能力
- 1.1.5 数据分区与线程级并行
- 1.1.6 性能对比
- 第2章 ClickHouse 的安装
- 第3章 数据类型
- 第4章 表引擎
- 4.1 表引擎的使用
- 4.2 TinyLog
- 4.3 Memory
- 4.4 MergeTree
- 4.4.1 partition by 分区(可选)
- 4.4.2 primary key 主键(可选)
- 4.4.3 order by(必选)
- 4.4.4 二级索引
- 4.4.5 数据 TTL
- 4.5 ReplacingMergeTree
- 4.6 SummingMergeTree
- 第5章 SQL 操作
- 5.1 Insert
- 5.2 Update 和 Delete
- 5.3 查询操作
- 5.4 alter 操作
- 5.5 导出数据
- 第6章 副本
- 6.1 副本写入流程
- 6.2 配置步骤
- 第7章 分片集群
- 7.1 集群写入流程(3 分片 2 副本共 6 个节点)
- 7.2 集群读取流程 ( 3 分片 2 副本共 6 个 节 点)
- 7.3 3 分片 2 副本共 6 个节点集群配置(供参考)
- 7.4 配置三节点版本集群及副本
本博客内容出自尚硅谷b站公开课,如有侵权,请联系博主删除
第1章 ClickHouse 入门
ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的列式存储数据库(DBMS),使用 C++语言编写,主要用于在线分析处理查询(OLAP),能够使用 SQL 查询实时生成分析数据报告。
1.1 ClickHouse 的特点
1.1.1 列式存储
略
列式储存的好处:
- 对于列的聚合,计数,求和等统计操作原因优于行式存储。
- 由于某一列的数据类型都是相同的,针对于数据存储更容易进行数据压缩,每一列选择更优的数据压缩算法,大大提高了数据的压缩比重。
- 由于数据压缩比更好,一方面节省了磁盘空间,另一方面对于 cache 也有了更大的发挥空间。
1.1.2 DBMS 的功能
几乎覆盖了标准 SQL 的大部分语法,包括 DDL 和 DML,以及配套的各种函数,用户管理及权限管理,数据的备份与恢复。
1.1.3 多样化引擎
ClickHouse 和 MySQL 类似,把表级的存储引擎插件化,根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类 20 多种引擎。
1.1.4 高吞吐写入能力
ClickHouse 采用类 LSM Tree 的结构,数据写入后定期在后台 Compaction。通过类LSM tree 的结构,ClickHouse 在数据导入时全部是顺序 append 写,写入后数据段不可更改,在后台 compaction 时也是多个段 merge sort 后顺序写回磁盘。顺序写的特性,充分利用了磁盘的吞吐能力,即便在 HDD 上也有着优异的写入性能。
官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力,按照每行 100Byte 估算,大约相当于 50W-200W 条/s 的写入速度。
1.1.5 数据分区与线程级并行
ClickHouse 将数据划分为多个 partition,每个 partition 再进一步划分为多个 index granularity(索引粒度),然后通过多个 CPU 核心分别处理其中的一部分来实现并行数据处理。在这种设计下,单条 Query 就能利用整机所有 CPU。极致的并行处理能力,极大的降低了查询延时。
所以,ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu,就不利于同时并发多条查询。所以对于高 qps 的查询业务,ClickHouse 并不是强项。
1.1.6 性能对比
略
第2章 ClickHouse 的安装
略
第3章 数据类型
同另一篇专门ck的文章
略
第4章 表引擎
4.1 表引擎的使用
表引擎是 ClickHouse 的一大特色。可以说, 表引擎决定了如何存储表的数据。包括:
- 数据的存储方式和位置,写到哪里以及从哪里读取数据。
- 支持哪些查询以及如何支持。
- 并发数据访问。
- 索引的使用(如果存在)。
- 是否可以执行多线程请求。
- 数据复制参数。
表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎,以及引擎使用的相关参数。
特别注意:引擎的名称大小写敏感
4.2 TinyLog
略
4.3 Memory
内存引擎,数据以未压缩的原始形式直接保存在内存当中,服务器重启数据就会消失。读写操作不会相互阻塞,不支持索引。简单查询下有非常非常高的性能表现(超过 10G/s)。
一般用到它的地方不多,除了用来测试,就是在需要非常高的性能,同时数据量又不太大(上限大概 1 亿行)的场景。
4.4 MergeTree
ClickHouse 中最强大的表引擎当属 MergeTree(合并树)引擎及该系列(*MergeTree)中的其他引擎,支持索引和分区,地位可以相当于 innodb 之于 Mysql。 而且基于 MergeTree,还衍生除了很多小弟,也是非常有特色的引擎。
建表语句
create table t_order_mt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id);
插入数据
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
MergeTree 其实还有很多参数(绝大多数用默认值即可),但是三个参数是更加重要的,也涉及了关于 MergeTree 的很多概念。
4.4.1 partition by 分区(可选)
作用
学过 hive 的应该都不陌生,分区的目的主要是降低扫描的范围,优化查询速度
如果不填
只会使用一个分区。
分区目录
MergeTree 是以列文件+索引文件+表定义文件组成的,但是如果设定了分区那么这些文件就会保存到不同的分区目录中。
并行
分区后,面对涉及跨分区的查询统计,ClickHouse 会以分区为单位并行处理。
数据写入与分区合并
任何一个批次的数据写入都会产生一个临时分区,不会纳入任何一个已有的分区。写入后的某个时刻(大概 10-15 分钟后),ClickHouse 会自动执行合并操作(等不及也可以手动通过 optimize 执行),把临时分区的数据,合并到已有分区中。
optimize table xxxx final;
4.4.2 primary key 主键(可选)
ClickHouse 中的主键,和其他数据库不太一样,它只提供了数据的一级索引,但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。
主键的设定主要依据是查询语句中的 where 条件。
根据条件通过对主键进行某种形式的二分查找,能够定位到对应的 index granularity, 避免了全表扫描。·
index granularity
: 直接翻译的话就是索引粒度,指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值,除非该列存在大量重复值,比如在一个分区中几万行才有一个不同数据。
稀疏索引:
稀疏索引的好处就是可以用很少的索引数据,定位更多的数据,代价就是只能定位到索引粒度的第一行,然后再进行进行一点扫描。
4.4.3 order by(必选)
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。
order by 是 MergeTree 中唯一一个必填项,甚至比 primary key 还重要,因为当用户不设置主键的情况,很多处理会依照 order by 的字段进行处理(比如后面会讲的去重和汇总)。
要求:主键必须是 order by 字段的前缀字段。
比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id)
4.4.4 二级索引
目前在 ClickHouse 的官网上二级索引的功能是被标注为实验性的。
略
4.4.5 数据 TTL
TTL 即 Time To Live,MergeTree 提供了可以管理数据或者列的生命周期的功能。
(1)列级别 TTL
创建测试表
create table t_order_mt2(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time+interval 10 SECOND,
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);
插入数据(注意:根据实际时间改变)
insert into t_order_mt2 values
(106,'sku_001',1000.00,'2020-06-12 22:52:30'),
(107,'sku_002',2000.00,'2020-06-12 22:52:30'),
(110,'sku_003',600.00,'2020-06-13 12:00:00');
手动合并,查看效果 到期后,指定的字段数据归 0
(2)表级 TTL
下面的这条语句是数据会在 create_time 之后 10 秒丢失
alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;
涉及判断的字段必须是 Date 或者 Datetime 类型,推荐使用分区的日期字段。
能够使用的时间周期:
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
4.5 ReplacingMergeTree
ReplacingMergeTree 是 MergeTree 的一个变种,它存储特性完全继承 MergeTree,只是多了一个去重的功能。 尽管 MergeTree 可以设置主键,但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据,可以借助这个 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);
ReplacingMergeTree() 填入的参数为版本字段,重复数据保留版本字段值最大的。如果不填版本字段,默认按照插入顺序保留最后一条。
向表中插入数据
insert into t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00') ,
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
执行第一次查询
hadoop102 :) select * from t_order_rmt;
手动合并
OPTIMIZE TABLE t_order_rmt FINAL;
再执行一次查询
hadoop102 :) select * from t_order_rmt;
通过测试得到结论
- 实际上是使用 order by 字段作为唯一键
- 去重不能跨分区
- 只有合并才会进行去重
- 认定重复的数据保留,版本字段值最大的
- 如果版本字段相同则按插入顺序保留最后一笔
4.6 SummingMergeTree
对于不查询明细,只关心以维度进行汇总聚合结果的场景。如果只使用普通的 MergeTree 的话,无论是存储空间的开销,还是查询时临时聚合的开销都比较大。
ClickHouse 为了这种场景,提供了一种能够“预聚合”的引擎 SummingMergeTree
案例演示
创建表
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 );
插入数据
insert into t_order_smt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');
执行第一次查询
hadoop102 :) select * from t_order_smt;
手动合并
OPTIMIZE TABLE t_order_smt FINAL;
再执行一次查询
hadoop102 :) select * from t_order_smt;
通过结果可以得到以下结论
- 以 SummingMergeTree()中指定的列作为汇总数据列
- 可以填写多列必须数字列,如果不填,以所有非维度列且为数字列的字段为汇总数据列
- 以 order by 的列为准,作为维度列
- 其他的列按插入顺序保留第一行
- 不在一个分区的数据不会被聚合
开发建议
设计聚合表的话,唯一键值、流水号可以去掉,所有字段全部是维度、度量或者时间戳。
问题
能不能直接执行以下 SQL 得到汇总值
select total_amount from XXX where province_name=’’ and create_date=’xxx’
不行,可能会包含一些还没来得及聚合的临时明细
如果要是获取汇总值,还是需要使用 sum 进行聚合,这样效率会有一定的提高,但本身 ClickHouse 是列式存储的,效率提升有限,不会特别明显。
select sum(total_amount) from province_name=’’ and create_date=‘xxx’
第5章 SQL 操作
基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句,ClickHouse 基本都支 持,这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。
5.1 Insert
基本与标准 SQL(MySQL)基本一致
标准
insert into [table_name] values(…),(….)
从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]
5.2 Update 和 Delete
ClickHouse 提供了 Delete 和 Update 的能力,这类操作被称为 Mutation 查询,它可以看做 Alter 的一种。
虽然可以实现修改和删除,但是和一般的 OLTP 数据库不一样,Mutation 语句是一种很“重”的操作,而且不支持事务。 “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区,重建新分区。
所以尽量做批量的变更,不要进行频繁小数据的操作。
删除操作
alter table t_order_smt delete where sku_id ='sku_001';
修改操作
alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102;
由于操作比较“重”,所以 Mutation 语句分两步执行,同步执行的部分其实只是进
行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候,才会
删除旧数据释放磁盘空间,一般不会开放这样的功能给用户,由管理员完成。
5.3 查询操作
ClickHouse 基本上与标准 SQL 差别不大
- 支持子查询
- 支持 CTE(Common Table Expression 公用表表达式 with 子句)
- 支持各种 JOIN, 但是 JOIN 操作无法使用缓存,所以即使是两次相同的 JOIN 语句,ClickHouse 也会视为两条新 SQL
- 窗口函数(官方正在测试中…)
- 不支持自定义函数
- GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。
插入数据
hadoop102 :) alter table t_order_mt delete where 1=1;
insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
(103,'sku_004',2500.00,'2020-06-01 12:00:00'),
(104,'sku_002',2000.00,'2020-06-01 12:00:00'),
(105,'sku_003',600.00,'2020-06-02 12:00:00'),
(106,'sku_001',1000.00,'2020-06-04 12:00:00'),
(107,'sku_002',2000.00,'2020-06-04 12:00:00'),
(108,'sku_004',2500.00,'2020-06-04 12:00:00'),
(109,'sku_002',2000.00,'2020-06-04 12:00:00'),
(110,'sku_003',600.00,'2020-06-01 12:00:00');
with rollup:从右至左去掉维度进行小计
hadoop102 :) select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;
with cube : 从右至左去掉维度进行小计,再从左至右去掉维度进行小计
hadoop102 :) select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;
with totals: 只计算合计
hadoop102 :) select id,sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;
5.4 alter 操作
同 MySQL 的修改字段基本一致
新增字段
alter table tableName add column newcolname String after col1;
修改字段类型
alter table tableName modify column newcolname String;
删除字段
alter table tableName drop column newcolname;
5.5 导出数据
clickhouse-client --query "select * from t_order_mt where create_time='2020-06-01 12:00:00'" --format CSVWithNames> /opt/module/data/rs1.csv1
更多支持格式参照:
https://clickhouse.tech/docs/v19.14/en/interfaces/formats/#csvwithnames
第6章 副本
副本的目的主要是保障数据的高可用性,即使一台 ClickHouse 节点宕机,那么也可以从其他服务器获得相同的数据。
6.1 副本写入流程
6.2 配置步骤
略
第7章 分片集群
副本虽然能够提高数据的可用性,降低丢失风险,但是每台服务器实际上必须容纳全量数据,对数据的横向扩容没有解决。
要解决数据水平切分的问题,需要引入分片的概念。通过分片把一份完整的数据进行切分,不同的分片分布到不同的节点上,再通过 Distributed 表引擎把数据拼接起来一同使用。
Distributed 表引擎本身不存储数据,有点类似于 MyCat 之于 MySql,成为一种中间件,通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。
注意:ClickHouse 的集群是表级别的,实际企业中,大部分做了高可用,但是没有用分片,避免降低查询性能以及操作集群的复杂性。
7.1 集群写入流程(3 分片 2 副本共 6 个节点)
7.2 集群读取流程 ( 3 分片 2 副本共 6 个 节 点)
7.3 3 分片 2 副本共 6 个节点集群配置(供参考)
略
7.4 配置三节点版本集群及副本
略