ClickHouse day2核心技术篇：实时OLAP处理引擎核心技术深入剖析篇ClickHouse

本文链接：https://blog.csdn.net/weixin_42072280/article/details/118859294

文章目录

1. 本次课程介绍
2. ClickHouse 企业应用最佳实战
3. 本次课程总结

接上一篇：ClickHouse day1. 架构设计篇：实时OLAP处理引擎 ClickHouse架构体系深入剖析篇

第一天回放链接：https://qcs.h5.xeknow.com/s/37qJd9
第二天回放链接：https://qcs.h5.xeknow.com/s/2OKW31
第三天回放链接：https://qcs.h5.xeknow.com/s/16gFJS

注意：本文档非原创，来源于上面视频课程的课件。只做学习使用，如有侵权，请告知删除，谢谢！

本文档对应的pdf已上传，参见：下载地址

1. 本次课程介绍

1.1. 本次系列课程介绍

OLAP 之 ClickHouse 和 Doris 谁与争锋？ClickHouse 和 Doris 深度大 PK ？
首次完整揭秘 ClickHouse 核心特性，知其然，知其所以然
彻底揭秘千亿级企业 ClickHouse 实时处理引擎架构设计、核心技术设计、运行机理全流程;
彻底揭秘千亿级企业 ClickHouse 在企业大数据业务场景下的应用实践;
Doris 源码核心作者揭秘 Doris 架构设计核心原理;
首次全方位深度对比 ClickHouse 和 Doris 两大 OLAP 利器。

1.2. 昨日课程总结

上一次课程主要讲解的内容，是讲解为什么 CLickHouse 能做到查询分析的速度那么快？

1.3. 今日课程大纲

今天主要讲解 ClickHouse 的一些典型分析应用案例，重点就是告诉你，一些大厂在做技术选型的时候，也就是因为 ClickHouse 的这些特点才使用的。

今天的主要内容大致如下：

TopK：求最高频次 K 个数 TopN：最大的 N 个值
窗口分析函数
同比环比
漏斗分析 windowFunnel
如何利用 clickhouse 实现去重计数
ClickHouse 整合 BitMap

2. ClickHouse 企业应用最佳实战

2.1. ClickHouse 介绍和适用场景

ClickHouse 是"战斗民族"俄罗斯搜索巨头 Yandex 公司开源的一个极具"战斗力"的实时数据分析数据库，是面向 OLAP 的分布式列式 DBMS，圈内人戏称为“喀秋莎数据库”。ClickHouse 有一个简称 “CK”，与 Hadoop、Spark 这些巨无霸组件相比，ClickHouse 很轻量级，其特点包括：分布式、列式存储、异步复制、线性扩展、支持数据压缩和最终数据一致性，其数据量级在 PB 级别。

ClickHouse 的适用场景：

01、绝大多数请求都是用于读访问，而且不是单点访问, 都是范围查询，或者全表扫描
02、数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
03、数据只是添加到数据库，没有必要修改
04、读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
05、表很“宽”，即表中包含大量的列
06、查询频率相对较低（通常每台服务器每秒查询数百次或更少）
07、对于简单查询，允许大约50毫秒的延迟
08、列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
09、在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
10、不需要事务
11、数据一致性要求较低
12、每次查询中只会查询一个大表。除了一个大表，其余都是小表
13、查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

知识点补充：
1.全量更新,就是所有参数都传给后台,全部入库。
2.增量更新,则是传一部分参数,然后更新传递的参数。

最后的结论：ClickHouse 的优势，是适用于大数据量级的单张大宽表的聚合查询分析。除此之外的需求，相对来，都不是 ClickHouse 擅长的事情。在使用的时候，慎重。

2.2. ClickHouse 经典大厂分析案例

# 启动 ClickHouse 服务
nohup clickhouse-server --config-file=/etc/clickhouse-server/config.xml 1>~/logs/clickhouse_std.log
2>~/logs/clickhouse_err.log &
# 启动 客户端
clickhouse-client --host=localhost --port=9977 -m

2.2.1. TopK 求最高频次和 TopN 最大的 N 个值

TopK：求解数据集中，出现次数最多的 K 个元素

TopN：求解数据集中，最大的 N 个元素

-- 创建库
create database nxtest;
use nxtest;

-- 创建表
CREATE TABLE nx_topK_test (a Int32,b Int32,c Int32) ENGINE = Memory;

-- 插入数据
insert into nx_topK_test (a,b,c) values (1,2,5),(1,2,4),(1,3,8),(1,3,2),(1,4,6),(2,3,3),(2,3,7),(2,3,8),(2,4,9),(2,5,6), (3,3,4),(3,3,7),(3,3,5),(3,4,9),(3,5,6);

-- 查看展示
show tables;
SELECT * FROM nx_topK_test;
SELECT * FROM nx_topK_test ORDER BY a ASC;
select a,b,c from nx_topK_test order by a asc,c desc;

-- 统计每一个元素的个数
select a, c, count(*) as total from nx_topK_test group by a, c;

在这里插入图片描述

-- 统计每一个元素的个数
select a, c, count(*) as total from nx_topK_test group by a, c order by a, c;

在这里插入图片描述

关于 topK 函数
topK(2)( c)：求表中的按照 c 字段做元素的个数统计，2表示返回，个数最多的 2 个元素
2: 2个元素
c: 表中的C字段

-- 默认，topk 只取，取到出现次数最多的 k 个元素
SELECT a, topK(2)(c) FROM nx_topK_test group by a;

在这里插入图片描述
Q：为什么会是这个结果呢？

A：查看表中的所有元素，以a来分组的话，c中的数值都是只出现一次的，所以取表中的前两个元素。

SELECT * FROM nx_topK_test;

在这里插入图片描述
如果要求最大的N个值呢？
求每一组中最大的两个元素？

select a,c from nx_topK_test order by a asc, c desc;

在这里插入图片描述

-- 所以，需要先按照要求，给数据进行排序，然后进行 topk 操作，就可以取到出现次数最多的k个元素
SELECT a, topK(2)(c) FROM (select a,c from nx_topK_test order by a asc, c desc) group by a order by a;

在这里插入图片描述
总结：利用topK来求topN，可以是可以，但是有要求，要求求topN的字段数据不能重复。

topK 的本意，是求得每组数据中，按照指定字段计算出现次数最多的 k 个元素。但是利用 topK 也可以求得每组最大或者最小的几个元素。但是有一个前提，就是该字段的数据不能重复。这就是昨天在讲的时候，为什么要先排序，而且我的数据，是没有重复的。所以能实现。如果 c 字段的数据，不重复，则可以实现求得每组最大或者最小的前 k 个元素。

那么假设该表中的 c 字段有重复数据呢？

-- 创建表
CREATE TABLE nx_topK_test1 (a Int32,b Int32,c Int32) ENGINE = Memory;

-- 插入数据
insert into nx_topK_test1 (a,b,c) values (1,2,3),(1,2,4),(1,3,4),(1,3,5),(1,4,5),(1,3,5),(2,3,7),(2,3,8),(2,4,8),
(2,5,6),(2,5,6),(2,5,6),(3,3,6),(3,3,6),(3,3,9),(3,4,9),(3,5,6),(3,5,8);

select * from nx_topK_test1;

在这里插入图片描述

SELECT a, topK(2)(c) FROM nx_topK_test1 group by a;

在这里插入图片描述

如果想求得最大的，则先对 c 字段进行去重，然后使用 topK 函数：

-- 组合
select a, groupArray(c) as c from nx_topK_test1 group by a;

在这里插入图片描述

-- 集合去重
select a, arrayDistinct(groupArray(c)) as c from nx_topK_test1 group by a;

在这里插入图片描述

-- 去重之后的 c 字段和 a 字段组成新表
select a, arrayJoin(arrayDistinct(groupArray(c))) as c from nx_topK_test1 group by a;

在这里插入图片描述

-- 排序
select a, arrayJoin(arrayDistinct(groupArray(c))) as c from nx_topK_test1 group by a order by a, c desc;

在这里插入图片描述

-- 最终实现：在去重表的基础之上，再利用 topK 求 topN 实现
select b.a as a, topK(2)(b.c) as c from (select a, arrayJoin(arrayDistinct(groupArray(c))) as c from nx_topK_test1 group
by a order by a, c desc) b group by b.a;

在这里插入图片描述
再想想，还有其他办法么？

问题场景有四种：

a 字段分组，c 字段无重复，求 topK
a 字段分组，c 字段无重复，求 topN
a 字段分组，c 字段有重复，求 topK
a 字段分组，c 字段有重复，求 topN

最终解决方案：topK 函数

2.2.2. 窗口分析函数

Window Functions 在 clickhouse 的需求和呼声很高，早期的版本需要借助 array 函数，在 21.1 版本进行了开窗函数的初步支持。

标准SQL语法：分析函数 over（partition by 列名 order by 列名）

示例：sum(pv) over(partition by a1 order by a2 rows between A and B) as sumResult

分析函数分类

聚合类
avg(列名)、sum(列名)、count(列名)、max(列名)、min(列名)
排名类
row_number() 按照值排序时产生一个自增编号，不会重复
rank() 按照值排序时产生一个自增编号，值相等时会重复，会产生空位
dense_rank() 按照值排序时产生一个自增编号，值相等时会重复，不会产生空位
其他类
lag (列名,往前的行数,[行数为null时的默认值，不指定为null])
lead (列名,往后的行数,[行数为null时的默认值，不指定为null])
ntile(n) 用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。

查询版本：
select version();

需要设置参数：
SET allow_experimental_window_functions = 1;

创建表：
create table nx_window_data_test(id String, score UInt8) engine=MergeTree() order by id;

生成测试数据：
insert into nx_window_data_test(id,score) values
('A', 90),
('A', 80),
('A', 88),
('A', 86),
('B', 91),
('B', 95),
('B', 90),
('C', 88),
('C', 89),
('C', 90);

-- 查询数据
select * from nx_window_data_test;

在这里插入图片描述

-- 排序
select * from nx_window_data_test order by id, score desc;

在这里插入图片描述

-- 计算分组累加：
select id, score, sum(score) over(partition by id order by score) sum from nx_window_data_test;
select id, score, max(score) over(partition by id) max from nx_window_data_test;
select id, score, min(score) over(partition by id order by score) min from nx_window_data_test;
select id, score, avg(score) over(partition by id order by score) avg from nx_window_data_test;
select id, score, count(score) over(partition by id order by score) count from nx_window_data_test;
select

在这里插入图片描述

目前已经支持 min，max，avg，count，sum 等分组函数。

看具体需求：每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数

创建表：
create table exercise_pv(id String, month String, pv UInt32) engine=MergeTree() order by id;

插入数据：
insert into exercise_pv(id, month, pv) values
('A', '2015-01', 33),
('A', '2015-02', 10),
('A', '2015-03', 38),
('A', '2015-04', 20),
('B', '2015-01', 30),
('B', '2015-02', 15),
('B', '2015-03', 44),
('B', '2015-04', 35);

查询数据：
select * from exercise_pv;

需求的结果：
id month pv max sum
('A', '2015-01', 33), 33 33
('A', '2015-02', 10), 33 43
('A', '2015-03', 38), 38 81
('A', '2015-04', 20), 38 101
('B', '2015-01', 30),
('B', '2015-02', 15),
('B', '2015-03', 44),
('B', '2015-04', 35);

实现需求：
select
id,
month,
pv,
sum(pv) over(partition by id order by month) sum,
max(pv) over(partition by id order by month) max
from exercise_pv;

查询数据：
select * from exercise_pv;

在这里插入图片描述

实现需求：
select
id,
month,
pv,
sum(pv) over(partition by id order by month) sum,
max(pv) over(partition by id order by month) max
from exercise_pv;

在这里插入图片描述
最后补充：如果不适用窗口分析去做实现，推荐使用自连接方式去实现！

over(partition by id order by month rows betwen A and B

A： N preceding 当前行的前 N 行开始
B： N following 当前行往后N 行结束

如果当前需求不允许使用窗口分析函数，那么可以考虑使用自链接来实现！

需求：
期末考试完了之后，校长想知道每个班的前三名！（3/n）
期末考试完了之后，校长想知道每个班的前1/3名的同学名单（n * 1/3）

2.2.3. 同比环比

先弄清楚这两对概念：

跟去年同期比：同比增长率 =(本期数 - 同期数) / 同期数 = (202105 - 202005) / 202005
去跟上个月比：环比增长率 =(本期数 - 上期数) / 上期数 = (202105 - 202104) / 202104

同比：今年这个月 - 去年这个月 = 增长值，增长值 / 去年这个月（202102 - 202002） / 202002
环比：今年这个月今年这个月的上个月（202102 - 202101） / 202101

构造数据：
with toDate('2020-01-01') as start_date select toStartOfMonth(start_date + (number*31)) month_start, (number+20)*100
amount from numbers(24);

在这里插入图片描述


同比环比案例：
WITH toDate('2020-01-01') AS start_date
SELECT
toStartOfMonth(start_date + (number * 31)) AS month_start,
(number + 20) * 100 AS amount,
neighbor(amount, -12) AS prev_year_amount,
neighbor(amount, -1) AS prev_month_amount
FROM numbers(24);

在这里插入图片描述


WITH toDate('2020-01-01') AS start_date
SELECT
toStartOfMonth(start_date + (number * 31)) AS month_start,
(number + 20) * 100 AS amount,
neighbor(amount, -12) AS prev_year_amount,
neighbor(amount, -1) AS prev_month_amount,
if(prev_year_amount = 0, -999, amount - prev_year_amount) as year_inc,
if(prev_year_amount = 0, -999, round((amount - prev_year_amount) / prev_year_amount, 4)) AS year_over_year,
if(prev_year_amount = 0, -999, amount - prev_month_amount) as month_inc,
if(prev_month_amount = 0, -999, round((amount - prev_month_amount) / prev_month_amount, 4)) AS month_over_month
FROM numbers(24);

neighbor 函数可以说是 lag() 与 lead() 的合体，它可以根据指定的 offset，向前或者向后获取到相应字段的值，其完整定义为：

neighbor(column, offset[, default_value])

一年有 12 个月，这个是固定不变的！但是一个月有多少天？这个 28 29 30 31 ，把每个月份的天数，都补齐为 31

2.2.4. 漏斗分析 windowFunnel

漏斗模型：主要涉及到转化！

下单成交漏斗分析：

1、广告曝光 1000W
2、点击 20W
3、详情页 19W
4、加入购物车 2W
5、下单 1W
6、支付 8000
7、支付成功 7500
8、收货成功，结束订单 6000

创建表：
CREATE TABLE nx_window_funnel_test (uid String, eventid String, eventTime UInt64) ENGINE = Memory;

插入数据：	
insert into nx_window_funnel_test (uid,eventid,eventTime) values
('A','login',20200101),
('A','view',20200102),
('A','buy',20200103),
('B','login',20200101),
('B','view',20200102),
('C','login',20200101),
('C','buy',20200102),
('D','login',20200101),
('D','view',20200103),
('D','buy',20200102),
('E','login',20200101),
('E','view',20200104),
('E','buy',20200106);
('G','view',20200101),
('G','buy',20200102),

查询结果：
select * from nx_window_funnel_test order by uid;

在这里插入图片描述

查询 SQL：
SELECT
uid,
windowFunnel(3)(eventTime, eventid = 'login', eventid = 'view', eventid = 'buy') AS res
FROM nx_window_funnel_test
GROUP BY uid
order by uid;

在这里插入图片描述
表示A走了3步，b走了2步，c走了1步…

windowFunnel 的官网解释：

Returned value：Integer. The maximum number of consecutive triggered conditions from the chain within the sliding time window. All the chains in the selection are analyzed.
返回值：int类型。返回满足在指定滑动窗口内的连续触发条件的最大值。所有被选择的条件链都会被分析

select count(*) as total from (SELECT
uid,
windowFunnel(6)(eventTime, eventid = 'login', eventid = 'view', eventid = 'buy') AS res
FROM nx_window_funnel_test
GROUP BY uid
order by uid) b where b.res >= 3;

2.2.5. 如何利用 clickhouse 实现去重计数
需求场景：JD 想统计一下 06.18 这一天到底多少个用户使用 JD 购物？

1、只要来一个用户进入到 JD 平台，JD 必然会记录日志，日志中，必然会记录，这一日志是属于哪一个用户的。 userid
select count(distinct userid) as name_count from log; 计算得到一个精确值 97846544
2、有一些需求场景，不需要非得计算得到非常精确地值：9900W，clickhouse 提供了高效近似计算算法

select count(distinct name) as name_count from table;

1、非精确去重函数：uniq、uniqHLL12、uniqCombined、uniqCombined64
2、精确去重函数：uniqExact、groupBitmap

1、整形值精确去重场景，groupBitmap 比 uniqExact 快很多
2、groupBitmap 仅支持整形值去重， uniqExact 支持任意类型（Tuple、Array、Date、DateTime、String和数字类型）去重。
3、非精确去重场景，uniq 在精准度上有优势。
4、uniq 是近似去重，千万级用户，精确度能达到 99% 以上，uniqExact 是精确去重，和 mysql 的 count distinct 功能相同，比如统计 uv。

关注和了解：
近似去重算法：HyperLogLog 算法
精确去重算法：BitMap 算法相关。

准备数据：
在 MySQL 数据库中，有一个 nx_job 里面有一张表： job ： 55875 条记录！

创建库：
create database if not exists nx_job_test1 ENGINE = MySQL('bigdata02:3306', 'nx_job', 'root', 'QWer_1234');

创建表：
use nxtest3;

create database if not exists nx_job1;
use nx_job1;

create table job
(
id UInt32,
t_job String,
t_addr String,
t_tag String,
t_com String,
t_money String,
t_edu String,
t_exp String,
t_type String,
t_level String
) ENGINE = MergeTree() order by id;

插入数据：
insert into job(id, t_job, t_addr, t_tag, t_com, t_money ,t_edu ,t_exp ,t_type ,t_level)
select id, t_job, t_addr, t_tag, t_com, t_money ,t_edu ,t_exp ,t_type ,t_level from nx_job_test1.job;
数据多插入几次，因为后面要做去重

查询数据：
select * from job limit 3;
select count(*) as total from job;

-- 精确去重：普通实现
select count(distinct id) as total from job;
select countDistinct(id) as total from job;
-- 精确去重：CK实现
select uniqExact(id) from job;
select groupBitmap(id) from job;
-- 近似去重：CK实现
select uniq(id) from job;
select uniqHLL12(id) from job;

比如，腾讯的游戏营销活动分析系统奕星在进行去重方案选择的时候，其实对比了很多方案：https://cloud.tencent.com/developer/article/177052
5

基于 TDW 临时表的方案，在 pysql 中循环对每个活动执行对应的 hiveSQL 来完成 T+1 时效的计算
基于实时计算 + 文件增量去重的方案，虽然可在 Storm 中进行 HLL 近似去重，但是内存资源有限，无法给出精确的结果和最终的号码包文件，而且导致每日新增几十万小文件
基于实时计算 + LevelDB 增量去重方案，LevelDB 是 KV 存储，key 存文件名，value 存储文件内容，可执行毫秒级去重，可在 10s 内导出千万量
级的数据。但是扩展性较差，数据回溯困难。
基于 ClickHouse 的解决方案，灵活，扩展性强，轻量级。

奕星在经过多种方案对比之后，最终选择了 ClickHouse，去重服务通过简单的 SQL 查询就能实现，例如下面这条 SQL 就是查询 LOL 官网某个页面在 9月 6 日这 1 天的 UV：

select uniqExact(uvid) from tbUv where date='2020-09-06' and url='http://lol.qq.com/main.shtml'；

2.2.6. ClickHouse 整合 BitMap

需求：大大表链接

CK 痛点： CK 不适合做大大表链接

假设现在有两张大表做 join 操作，试想一下，如何解决？

每个 bit 位表示一个数字 id，对于 40 亿个的用户 id，只需要 40 亿 bit 位，约 477m 大小 = （4 * 10^9 / 8 / 1024 / 1024）

在这里插入图片描述
通过单个 bitmap 可以完成精确去重操作，通过多个 bitmap 的 and、or、xor、andnot 等位操作完成留存分析、漏斗分析、用户画像分析等场景的计算。

关于 ClickHouse 的位图函数：https://clickhouse.tech/docs/zh/sql-reference/functions/bitmap-functions/

需求案例一：电信用户每个月的话费统计。收费标准：只要打了电话，或者有流量记录，则记为1，则收取1块钱。

0100110101000101010100101010101 这串数字一共31位的，每一位代表某个月某一天，如电信的号码某一天有通话记录就置成1，没有为0
1101010101010101010100101010100 这串数字一共31位的，每一位代表某个月某一天，如电信的号码某一天有流量记录就置成1，没有为0
1101110101010101…

这个问题该如何解呢？

电信流量日志：
1、记录日志：
流量：userid,telephone1,date,url,number,…
通话：telephone1，telephone2, date, totaltime
2、效果
每个用户，每天都有很多条日志
3、结果sql
select count(distinct date) as total from table group by user where month = 2021-07 ;

需求案例二：统计微信过去连续7天都发朋友圈的用户集。

每天的朋友圈信息，构建成一张表（110）经过 bitmap 的构建，每天的 15E 用户是否发朋友圈的信息，就被构建成了一个长度为 15E 的二进制序列
1号用户发了3条朋友圈，就是3条信息
2号用户发了2条朋友圈，就是2条信息
3号用户没有发朋友圈

7 张表做 join 链接

第一天 table1 1号所有用户发朋友圈的信息的表 = bitmap = 101010101010110101010101011010101 = s1， s1.length = 15E

第二天 table2 2号所有用户发朋友圈的信息的表 = bitmap = 101010101010110101010101011010101 = s2

第三天 table3 3号所有用户发朋友圈的信息的表 = bitmap = 101010101010110101010101011010101 = s3
…

最后的计算就变成了： s1 & s2 & s3 & s4 & s5 & s6 & s7 = result，最终在统计这个 result 序列中的 1 个个数即可

10E = 480M， 15E = 720M ， 720M * 7 = 5G

3. 本次课程总结

今天主要讲解 ClickHouse 的一些典型分析应用案例，重点就是告诉你，一些大厂在做技术选型的时候，也就是因为 ClickHouse 的这些特点才使用的。
今天的主要内容大致如下：

TopK 求最高频次和最大的 K 个值
窗口分析函数
同比环比
漏斗分析 windowFunnel
如何利用 clickhouse 实现去重计数
ClickHouse 整合 BitMap

最后的结论：ClickHouse 的优势，是适用于大数据量级的单张大宽表的聚合查询分析。除此之外的需求，相对来，都不是 ClickHouse 擅长的事情。
在使用的时候，慎重。

ClickHouse 适合的场景：

01、绝大多数请求都是用于读访问，而且不是单点访问, 都是范围查询，或者全表扫描
02、数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作
03、数据只是添加到数据库，没有必要修改
04、读取数据时，会从数据库中提取出大量的行，但只用到一小部分列
05、表很“宽”，即表中包含大量的列
06、查询频率相对较低（通常每台服务器每秒查询数百次或更少）
07、对于简单查询，允许大约50毫秒的延迟
08、列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）
09、在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）
10、不需要事务
11、数据一致性要求较低
12、每次查询中只会查询一个大表。除了一个大表，其余都是小表
13、查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小