ClickHouse 使用介绍

orange大数据技术探索者

已于 2024-01-28 13:12:02 修改

阅读量1.2k

点赞数

分类专栏： # 大数据其他技术文章标签： clickhouse 大数据 olap

于 2023-06-06 16:40:36 首次发布

本文链接：https://blog.csdn.net/weixin_43283487/article/details/128673939

版权

大数据其他技术专栏收录该内容

11 篇文章 1 订阅

订阅专栏

1 CK 介绍

1.1 clickhouse简介

ClickHouse 是俄罗斯的 Yandex 于 2016 年开源的用于在线分析处理查询（OLAP :Online Analytical Processing）MPP架构的列式存储数据库（DBMS：Database Management System），能够使用 SQL 查询实时生成分析数据报告。ClickHouse的全称是Click Stream，Data WareHouse。

clickhouse可以做用户行为分析，流批一体

线性扩展和可靠性保障能够原生支持 shard + replication

clickhouse没有走hadoop生态，采用 Local attached storage 作为存储

1.2 clickhouse特点

1、列式存储：

行式存储的好处：

想查找某个人所有的属性时，可以通过一次磁盘查找加顺序读取就可以；但是当想查所有人的年龄时，需要不停的查找，或者全表扫描才行，遍历的很多数据都是不需要的。

列式存储的好处

对于列的聚合、计数、求和等统计操作优于行式存储

由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重

数据压缩比更好，一方面节省了磁盘空间，另一方面对于cache也有了更大的发挥空间

列式存储不支持事务

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

3、多样化引擎：目前包括合并树、日志、接口和其他四大类20多种引擎。

4、高吞吐写入能力：

ClickHouse采用类LSM Tree的结构，数据写入后定期在后台Compaction。通过类 LSM tree的结构， ClickHouse在数据导入时全部是顺序append写，写入后数据段不可更改，在后台compaction时也是多个段merge sort后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力。

5、数据分区与线程及并行：

ClickHouse将数据划分为多个partition，每个partition再进一步划分为多个index granularity(索引粒度)，然后通过多个CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下， 单条 Query 就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。

所以， ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务并不是强项。

6、ClickHouse 像很多 OLAP 数据库一样，单表查询速度优于关联查询，而且 ClickHouse的两者差距更为明显。

关联查询：clickhouse会将右表加载到内存。

1.3 clickhouse为什么快？

C++可以利用硬件优势

摒弃了hadoop生态

数据底层以列式存储

利用单节点的多核并行处理

为数据建立索引一级、二级、稀疏索引

使用大量的算法处理数据

支持向量化处理

预先设计运算模型-预先计算

分布式处理数据

2 SQL语法和数据类型

和标准的sql语法部分地方差不多，但是有些地方又有较大区别

支持子查询

支持 CTE(Common Table Expression 公用表表达式 with 子句)

支持各种JOIN，但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，ClickHouse 也会视为两条新 SQL

窗口函数(官方正在测试中...)

不支持自定义函数

GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

Delete 和 Update 的能力很重，一般不要这样操作

数据类型没什么特别的，不过支持enum是和其他大数据不一样的地方

整形 INT：Int8 、Int16 、Int32、 Int64

浮点型：Float32 - float 和 Float64 – double，一般数据值比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量。

布尔值：没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1

Decimal：一般金额字段、汇率、利率等字段为了保证小数点精度，都使用 Decimal 进行存储

字符串：String-任意长度、FixedString(N)-固定长度

枚举enum：对一些状态、类型的字段算是一种空间优化，也算是一种数据约束

enum实际使用中往往因为一些数据内容的变化增加一定的维护成本，甚至是数据丢失问题。所以谨慎使用


创建一个待优enum字段的表
CREATE TABLE t_enum
(
    x Enum8('hello' = 1, 'world' = 2)
)
ENGINE = TinyLog;

3 引擎

引擎决定了数据的存储位置、存储结构、表的特征（是否修改操作DDL、DDL、是否支持并发操作）

3.1 数据库引擎

基本上都是默认的Ordinary，简单了解一下即可

Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。

Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表

Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除

Lazy：日志引擎，此类数据库下只能使用Log系列的表引擎

MySQL：MySQL引擎，将远程的MySQL服务器中的表映射到ClickHouse中,常用语数据的合并。

MaterializeMySQL：MySQL数据同步；将MySQL数据全量或增量方式同步到clickhouse中，解决mysql服务并发访问压力过大的问题

3.2 表引擎

CK的特点，有3种大类型，每个类型又有好几个子类型

	特点	优势	劣势		用途
TinyLog	列文件的形式保存在磁盘		不支持索引没有并发控制	StripeLog Log TinyLog	一般练习测试用
Memory	数据直接存在内存	读写不会阻塞简单查询性能极高	不支持索引服务重启数据消失		一般不会用于生产
MergeTree	最常用且强大的表引擎支持索引和分区	存储的数据按主键排序可以使用分区支持数据副本支持数据采样		MergeTree ReplacingMergeTree SummingMergeTree AggregatingMergeTree CollapsingMergeTree VersionedCollapsingMergeTree GraphiteMergeTree	及其子引擎常用于生产环境

MergeTree

核心参数：

分区 partition by	降低扫描的范围，优化查询速度对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理	任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的15分钟左右，ClickHouse 会自动执行合并操作(可手动通过 optimize 执行)，把临时分区的数据，合并到已有分区中	CREATE TABLE table_with_where ( id UInt32, date DateTime, name Int TTL date + INTERVAL 1 MONTH ) ENGINE = MergeTree PARTITION BY toYYYYMM(date) ORDER BY id TTL date + INTERVAL 1 MONTH DELETE WHERE toDayOfWeek(date) = 1;
主键 primary key	数据的一级索引，但是却不是唯一约束通过稀疏索引快速查找，设置一般依据查询语句中的 where 条件	数据是可以重复的
排序，必填 order by	分区内的数据按照哪些字段顺序进行有序保存主键必须是 order by 字段的前缀字段
二级索引	为非主键字段的查询发挥作用	INDEX a total_amount TYPE minmax GRANULARITY 5
数据 TTL	支持字段和表级别的生命周期	TTL datetime + INTERVAL 1 MONTH

稀疏索引：稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描

稀疏索引图解

ReplacingMergeTree

为了解决MergeTree相同主键无法去重的问题，用来做去重。但是实际上不保证没有重复的数据出现

去重只会在分区内部进行去重，不能执行跨分区的去重

只有同一批插入(新版本)或合并分区时才会进行去重，合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理

认定重复的数据保留，版本字段值最大的

如果版本字段相同则按插入顺序保留最后一笔

SummingMergeTree

和 doris 的 sum 聚合类似，支持对主键列进行预先聚合。在后台Compaction时，会将主键相同的多行进行sum求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能

官方建议和MergeTree一起使用，将完整的数据存储在MergeTree表中，并且使用SummingMergeTree来存储聚合数据

以SummingMergeTree()中指定的列作为汇总数据列

可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列

以 order by 的列为准，作为维度列

其他的列按插入顺序保留第一行

不在一个分区的数据不会被聚合

只有在同一批次插入(新版本)或分片合并时才会进行聚合，可能会有未聚合的明细数据，所以sql还是要写sum


select total_amount from XXX where province_name=’’ and create_date=’xxx’
替换为
select sum(total_amount) from province_name=’’ and create_date=‘xxx’

AggregatingMergeTree

功能类似doris aggregate模型，与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数

改变了数据片段的合并逻辑。 ClickHouse 会将一个数据片段内所有具有相同主键（准确的说是排序键）的行替换成一行，这一行会存储一系列聚合函数的状态

可以使用AggregatingMergeTree表来做增量数据的聚合统计，包括物化视图的数据聚合

CollapsingMergeTree

ClickHouse实现了CollapsingMergeTree来消除ReplacingMergeTree的限制。该引擎要求在建表语句中指定一个标记列Sign，后台Compaction时会将主键相同、Sign相反的行进行折叠，也即删除。

CollapsingMergeTree将行按照Sign的值分为两类：Sign=1的行称之为状态行，Sign=-1的行称之为取消行。

每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。

在后台Compaction时，状态行与取消行会自动做折叠（删除）处理。而尚未进行Compaction的数据，状态行与取消行同时存在。

因此为了能够达到主键折叠（删除）的目的，需要业务层进行适当改造：

执行删除操作需要写入取消行，而取消行中需要包含与原始状态行一样的数据（Sign列除外）。所以在应用层需要记录原始状态行的值，或者在执行删除操作前先查询数据库获取原始状态行

由于后台Compaction时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；另外，ClickHouse无法保证primary key相同的行落在同一个节点上，不在同一节点上的数据无法折叠。因此在进行count(*)、sum(col)等聚合计算时，可能会存在数据冗余的情况。为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)

CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。

VersionedCollapsingMergeTree

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除

与CollapsingMergeTree类似， 为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)

4 其他

4.1 分片集群和副本

这两个特点或概念是大数据很常见的了，简单看一下

副本：保障数据的高可用性，即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据

分片集群：解决横向扩容问题，通过分片把一份完整的数据进行切分，不同的分片分布到不同的节点上，再通过 Distributed 表引擎把数据拼接起来一同使用

但是ClickHouse 的集群是表级别的，实际企业中，大部分做了高可用，但是没有用分片，避免降低查询性能以及操作集群的复杂性

4.2 物化视图

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

视图语法：


CREATE [ MATERIALIZED ] VIEW [ IF NOT EXISTS ] [ db.] table_name [ TO [ db.] name ] 
[ ENGINE = engine ] [ POPULATE ] AS
SELECT
...

4.3 CK数据一致性

4.n

EXPLAIN SYNTAX 可以查看语法优化

和Hive等不同，DateTime 不需要经过函数转换处理，执行效率高、可读性好，一般不设置类型为String或者Long

空值存储类型，CK的null影响性能且无法索引，一般设置默认值

一般按天分区，以单表一亿数据为例，分区大小控制在 10-30 个为最佳

索引查询频率大的在前原则，基数特别大的不适合做索引列，如用户表的 userid 字段;通常筛选后的数据满足在百万以内为最佳