ClickHouse入门实战

北漂IT民工_程序员_ZG

已于 2024-12-02 23:53:10 修改

阅读量4.1k

点赞数 5

分类专栏：研究自创文章标签： clickhouse 数据库

于 2023-04-14 00:48:13 首次发布

本文链接：https://blog.csdn.net/weixin_41919486/article/details/129541198

版权

研究同时被 2 个专栏收录

42 篇文章

订阅专栏

自创

24 篇文章

订阅专栏

一、ClickHouse简介

对于其他乱起八糟的简介，我就不写了，只写干货.
ClickHouse总结一个字：快
1、ClickHouse是列式数据库，特别适合大数据量对列的聚合，计数，求和等统计操作原因优于行式存储，数据量可以大到10亿到百亿级别。
2、真正的列式数据库管理系统，ClickHouse不单单是一个数据库，它是一个数据库管理系统。因为它允许在运行时创建表和数据库、加载数据和运行查询，而无需重新配置或重启服务。
3、常见列式数据库HBase，BigTable，Cassandra，HyperTable。在这些列式数据库中，你可以得到每秒数十万的吞吐能力，但是无法得到每秒几亿行的吞吐能力。
4、数据压缩，由于某一列的数据类型都是相同的，针对于数据存储更容易进行数据压缩，每一列选择更优的数据压缩算法，大大提高了数据的压缩比重，由于数据压缩比更好，一方面节省了磁盘空间，另一方面对于 cache 也有了更大的发挥空间。
5、ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询。高性能自然需要高配服务器。

点击进入: ClickHouse官网文档

二、ClickHouse单机版安装

下面教程是通过安装包安装的，如果想要用docker安装，可以查看我的另外一篇博客，点击进入docker安装clickhouse

2.1、安装准备环境

2.1.1、Centos 取消打开文件数限制

vim /etc/security/limits.conf
vim /etc/security/limits.d/20-nproc.conf
##编辑上面两个文件，添加下面内容，记得两个文件都添加，在文件末尾添加
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

2.1.2、安装ClickHouse依赖环境

yum install -y libtool
yum install -y *unixODBC*

2.1.3、CentOS 取消 SELINUX

#修改/etc/selinux/config 中的 SELINUX=disabled
vim /etc/selinux/config

2.2、单机安装

2.2.1、安装包准备

可自行去官网把下图中的四个安装包下载下来, 下载地址：http://repo.red-soft.biz/repos/clickhouse/stable/el7/

在这里插入图片描述

2.2.2、安装

上传到centos中的某一目录中，在目录中执行如下命令。

rpm -ivh *.rpm

安装过程中，出现下面情况，是让你输入默认密码的，自己随便输入，一般输入root即可。也可以不设置密码，直接按回车键即可

在这里插入图片描述

查看安装情况

sudo rpm -qa|grep clickhouse

2.2.3、修改配置文件

把 <listen_host>::</listen_host> 的注释打开，这样的话才能让 ClickHouse 被除本机以外的服务器访问

vim /etc/clickhouse-server/config.xml

2.2.4、启动clickhouse

注意，启动clickhouse是开机自启的。

#查看clickhouse状态
systemctl status clickhouse-server
#启动clickhouse
systemctl start clickhouse-server
#重启clickhouse
systemctl restart clickhouse-server


#查看防火墙状态
systemctl status firewalld.service
#暂时关闭防火墙
systemctl stop firewalld.service
#永久关闭防火墙
systemctl disable firewalld.service
#使用该命令，是禁制clickhouse开机自启，不过一般我们都是需要开机自启，
#所以不执行下面这条命令了
systemctl disable clickhouse-server

2.2.5、终端链接clickhouse，终端进入数据库命令

#如果在上面没设置默认密码，执行clickhouse-client -m即可
clickhouse-client -m
#如果在上面设置了默认密码，执行clickhouse-client -udefault --password，然后按回车输入密码，就可以进入了
clickhouse-client -udefault --password 密码

2.3、ClickHouse一些默认路径

bin/     ====>  /usr/bin/    #可执行文件路径
conf/   ====> /etc/clickhouse-server     #配置文件路径
lib/      ====> /var/lib/clickhouse      #数据存放路径
log/     ====> /var/log/clickhouse       #日志路径

2.4、clickhouse端口和clickhouse命令行增删改查

这个去看我docker安装clickhouse单机版，应该都有写的。
点击进入docker安装clickhouse

2.5、ClickHouse通过命令行导出导入数据

#从生产环境导出
clickhouse-client -u XXX ---password XXX --query="select * from rightcloud.ops_bill_detail FORMAT CSV" > test.csv

#导入到本地调试(记得本地要有该库和表)
#本地新装好的clickhouse，是默认没用户的，把-u去掉就行
clickhouse-client -u XXX --password XXX --query="insert into rightcloud.ops_bill_detail FORMAT CSV" < test.csv

三、数据类型

和其他语言的数据类型都相似，我就不一一说了，官方文档上写的很好，参考下官网： https://clickhouse.com/docs/zh/sql-reference/data-types

四、表引擎

表引擎（即表的类型）决定了：
1、数据的存储方式和位置。
2、写到哪里以及从哪里读取数据。
3、支持哪些查询以及如何支持。
4、并发数据访问。
5、索引的使用（如果存在）。
6、是否可以执行多线程请求。数据复制参数。
大白话就是有些不同的表引擎支持不同的特性，可能有些支持索引，有些不支持索引，
所以需要了解表引擎，才能写出来高性能sql。

参考官网的表引擎，写的很详细，点击进入官网表引擎

4.1 MergeTree

1、MergeTree 引擎是ClickHouse中最常用的，地位相当于mysql的innodb,下面介绍下MergeTree引擎的一些特性
2、Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

建表语句

create table t_student_mg(
id UInt32,
stu_id String,
total_score Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,stu_id);

插入数据

insert into t_student_mg values
(101,'stu_001',500,'2023-06-01 12:00:00') ,
(102,'stu_002',520,'2023-06-01 11:00:00'),
(102,'stu_004',666,'2023-06-01 12:00:00'),
(102,'stu_002',720,'2023-06-01 13:00:00'),
(102,'stu_002',222,'2023-06-01 13:00:00'),
(102,'stu_002',555,'2023-06-02 12:00:00');

4.1.1 partition by 分区

分区非必选的属性。

1、分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理，即一个线程处理一个分区内的数据，单条查询就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。说明ClickHouse在高性能的同时，是需要高服务器的。

2、大多数情况下，不需要使用分区键。即使需要使用，也不需要使用比月更细粒度的分区键。永远也别使用过细粒度的分区键。以单表一亿数据为例，分区大小控制在10-30个为最佳。

3、那些有相同分区表达式值的数据片段才会合并。这意味着你不应该用太精细的分区方案。否则，会因为文件系统中的文件数量过多和需要打开的文件描述符过多，导致 SELECT 查询效率不佳。

4、还有就是一般我们都是使用的是日期作为分区键，同一分区内有序，不同分区不能保证有序。

5、分区数据合并，任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作。 ClickHouse 也可以手动合并分区，手动把临时分区合并到已有分区中
6、在dbeaver是看不到分区效果的，要在clickhouse命令行客户端看得到。

#手动合并分区命令
optimize table 表名称 final;

4.1.2 primary key 主键

主键是非必选的属性。

1、ClickHouse 中的主键，和其他数据库不太一样，它只提供了数据的一级索引(稀疏索引)，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。

2、index granularity指的是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

3、稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。实际稀疏索引就是某种形式的二分查找，能够定位到对应的 index granularity,避免了全表扫描

4、默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

4.1.3 order by 排序

排序是必选的属性。

1、order by 排序，是对分区内的排序

2、主键必须是 order by 字段的前缀字段，比如 order by 字段是 (id,stu_id) 那么主键必须是 id 或者(id,stu_id)。

4.1.4 二级索引(跳数索引)

二级索引是非必选的属性。

后面讲哈，基本很少用到，即使用到，不会也没关系

4.1.5 数据TTL(过期时间)

数据TTL是非必选的属性。

1、ClickHouse中分为表级TTL和列级TTL，表级TTL就是表中的某些行数据到期了，会自动删除；列级TTL则是对表中的某些字段设置过期时间，一旦过期，表中的该字段则会变成0。
2、只谈表级TTL，因为列级TTL基本很少用，用的时候百度下就可以了。
3、涉及判断的字段，下面语句的create_time, 必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段。
4、建表时，指定表级TTL, 下面语句中，说明只保留create_time值是1月内的数据。
5、使用过期时间的单位:SECOND、MINUTE、MINUTE、DAY、WEEK、MONTH、QUARTER YEAR
6、下面语句中，最后写的DELETE，可以不写，因为默认就是DELETE。 DELETE表示到期了自动删除。还可以写成TO VOLUME 'a' 或TO DISK 'a' 都表示将数据移动到磁盘 a上; 写成GROUP BY 表示聚合过期行。

create table t_student_mg2(
id UInt32,
stu_id String,
total_score Decimal(16,2),
create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,stu_id)
TTL create_time + INTERVAL 1 MONTH DELETE

4.2 ReplacingMergeTree

1、ReplacingMergeTree是基于MergeTree 延申出的一个表引擎，它存储特性完全继承 MergeTree，只是多了一个去重的功能。虽然 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。去重按照order by的字段去重

2、数据的去重只会在合并的过程中出现；去重只会在分区内部进行去重，不能执行跨分区的去重。

3、ReplacingMergeTree() 填入的参数为版本字段，重复数据保留版本字段值最大的。下面sql中数据如果去重，保留create_time最大的数据。如果create_time相同则按插入顺序保留最后一笔。如果不填版本字段，默认按照插入顺序保留最后一条。

#表创建
create table t_student_mg3(
id UInt32,
sku_id String,
total_score Decimal(16,2) ,
create_time Datetime 
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);

4.3 SummingMergeTree

1、该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

2、对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。
ClickHouse 为了这种场景，提供了一种能够“预聚合”的引擎 SummingMergeTree。该引擎聚合的依据依然是 order by 的字段，相当于按照 order by 的字段做了一次 Group By

3、以 SummingMergeTree（）中指定的列作为汇总数据列，可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列。
以order by 的列为准，作为维度列。
不在一个分区的数据不会被聚合。
只有在同一批次插入(第一次插入)或分片合并时才会进行聚合

create table t_student_mg4(
id UInt32,
sku_id String,
total_score Decimal(16,2) ,
create_time Datetime 
) engine =SummingMergeTree(total_score)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id );

3、设计聚合表的话，尽可能的所有字段全部是维度、度量或者时间戳。

4、查sql时有可能还没得到汇总值，因为可能包含一些还没来得及聚合数据。
如果要是获取汇总值，还是需要使用 sum 进行聚合，这样效率会有一定的提高，但本身 ClickHouse 是列式存储的，效率提升不是很高。

五、sql增删改查

5.1 insert

1、和Mysql语法一致

#标准sql插入语句
insert into [table_name] values(…),(….) 
#从表到表的插入
insert into [table_name] select a,b,c from [table_name_2]

5.2 Update 和 Delete

1、ClickHouse 支持 Delete 和 Update ，但是删除和更新是一种很重的操作，且不支持事务。之所以说重，是因为每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

#删除语句
alter table t_student_mg delete where stu_id ='stu_001';

#更新语句
alter table t_student_mg update total_score=toDecimal32(2000,2) where id = 102;

2、删除和更新后，虽然通过sql查看确实是生效了。但是数据没从磁盘删除，只有在触发分区合并的时候，才会删除旧数据释放磁盘空间。

5.3 SELECT

关于select查询，官网给的很详细了，点击进入官网sql查询。

注意，建表语句中的order by和查询语句中的order by没关联，不要搞混了。
查询中的order by,和mysql中语句一样，后面可以跟任意字段。

1、 ClickHouse 基本上与MySQL 差别不大，可以按照Mysql语句写。

2、支持子查询

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

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

#该sql查询如下图所示
SELECT * from t_student_mg;

在这里插入图片描述

5、with rollup：从右至左去掉维度进行小计。
下面查询是从右至左去掉维度,前三条是正常分组查的，第四第五条是去掉stu_id维度统计的，也就是以Id为维度统计的。第六条是去掉stu_id，id统计的，也就是所有数据在一个组内统计的。

select id , stu_id,sum(total_score)  from t_student_mg group by id,stu_id with rollup;

在这里插入图片描述

6、with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计。和第五条一样理解即可

select id , stu_id,sum(total_score) from t_student_mg group by 
id,stu_id with cube;

在这里插入图片描述

7、with totals: 只计算合计

select id , stu_id,sum(total_score) from t_student_mg group by 
id,stu_id with totals;

在这里插入图片描述

5.3 alter操作

1、alert操作指的是通过sql，修改、新增、删除表的字段。

#新增字段，after col1指的是新增的字段在col1字段后面
alter table tableName add column newcolname String after col1;
#举例:
alter table action_log modify column created_dt DateTime64 comment '创建日期';
#修改字段类型
alter table tableName modify column 字段名称 String;
#删除字段
alter table tableName drop column 字段名称;

六、建表优化和语法优化

6.1、建表优化

1、官方已经指出 Nullable 类型几乎总是会拖累性能,因为存储 Nullable 列时需要创建一个额外的文件来存储NULL的标记，并且Nullabe 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值(例如用-1表示空)。
2、分区设置，查看上面（4.1.1 partition by 分区）的规则。
3、写入和删除优化，尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力。
4、不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3次写入操作，每次操作写入2w~5w条数据(依服务器性能而定)。