酷站欣赏,建立网站费用大概需要多少钱,湖南公众信息网官网,内江做网站笔者在近一两年接触了Clickhouse数据库#xff0c;在项目中也进行了一些实践#xff0c;但一直都没有一些技术文章的沉淀#xff0c;近期打算做个系列#xff0c;通过一些具体的场景将Clickhouse的用法进行沉淀和分享#xff0c;供大家参考。 首先我们假设一个Clickhouse数… 笔者在近一两年接触了Clickhouse数据库在项目中也进行了一些实践但一直都没有一些技术文章的沉淀近期打算做个系列通过一些具体的场景将Clickhouse的用法进行沉淀和分享供大家参考。 首先我们假设一个Clickhouse数据表
CREATE TABLE Test_Table (page_id String, /* 页面ID */user_id String, /* 用户ID */is_slow String, /* 请求是否慢 */url String, /* 请求URL */
) ENGINE MergeTree()
ORDER BY (page_id, device_id);
这个表格的字段含义如注释该表主要存储的数据是:
每个用户user_id在某个页面page_id发起的请求url是否耗时较长is_slow耗时较长我们简称“慢请求”
其中一个用户可能在一个或多个页面发起一个或多个请求每个请求可能是慢请求也可能不是
is_slow的内容是枚举值即true 和 false 数据表格有了我们还要设定一个业务需求
我们期望以页面的纬度查看每个页面中有多少用户遇到了慢请求并算出在这个页面慢请求用户占在所有页面发起请求的总用户数无论是否发起了慢请求的百分比 从业务角度上比较好理解这个结果其实就是想知道这个页面的慢影响了多少人占比是多少 从研发者的角度一个页面一般是一个固定的团队成员开发甚至就是一个团队成员开发所以这个角度主要面向问题的解决和责任的划分同时也让项目的管理者技术的管理者知道目前用户体验的痛点在哪里 场景有了业务需求也有了那么如何实现呢 首先我们要获取的内容有两个一个是“每个页面中有多少用户遇到了慢请求” 这个比较简单 SELECT page_id, count(DISTINCT device_id) AS slow_user_countFROM Test_TableWHEREand is_slow truegroup by page_id 另一个要获取的是“在所有页面发起请求的总用户数” SELECT count(DISTINCT device_id) AS total_user_countFROM Test_Table 最后我们需要获得“在这个页面慢请求用户占在所有页面发起请求的总用户数无论是否发起了慢请求的百分比” 这里一般来说比较常见的方法是使用With来进行拼接
WITH slow_users AS (SELECT page_id, count(DISTINCT device_id) AS slow_user_countFROM Test_TableWHERE is_slow truegroup by page_id
),
total_users AS (SELECT count(DISTINCT device_id) AS total_user_countFROM Test_Table
)
SELECTslow_users.page_id,slow_users.slow_user_count,total_users.total_user_count,slow_user_count * 100.0 / total_users.total_user_count AS slow_user_percentage
FROMslow_users,total_users
ORDER BYslow_user_percentage DESC; 嗯这个需求做完了是不是很简单貌似不熟悉ClickHouse的同学也能写 但既然笔者来写这篇文章肯定不是想用这么简单的方案更何况这个SQL本身还是有问题 第一个问题是去重函数的性能问题 首先在Clickhouse里面有多个去重计数的函数主要包含两类 1.非精确去重函数uniq、uniqHLL12、uniqCombined 2.精确去重函数uniqExact、groupBitmap 从官网资料上来看 在非精确去重函数中 uniq函数使用自适应采样算法 uniqHLL12函数使用的是HyperLogLog 算法 uniqCombined函数使用三种算法的组合数组、哈希表和包含错误修正表的HyperLogLog算法 官方推荐uniq和uniqCombined函数不推荐uniqCombined函数 同时对于uniq和uniqCombined的区别上官方给出的建议是 在精确去重函数中 uniqExact函数是uniq系列方法中的一个比 uniq 使用更多的内存因为状态的大小随着不同值的数量的增加而无界增长。参数可以是Tuple, Array, Date, DateTime, String或数字类型。
groupBitmap函数比较特殊参数得是一个无符号整数列算法主要用的是“位图或聚合计算”
从这篇文章中查看了两个函数的源码
/ count(distinct)
// HashSetTable
void merge(const Self rhs){if (!this-hasZero() rhs.hasZero()){this-setHasZero();this-m_size;}for (size_t i 0; i rhs.grower.bufSize(); i)if (!rhs.buf[i].isZero(*this))this-insert(rhs.buf[i].getValue());}
// groupBitmap
// RoaringBitmapWithSmallSet
void merge(const RoaringBitmapWithSmallSet r1){if (r1.isLarge()){if (isSmall())toLarge();*rb | *r1.rb;}else{for (const auto x : r1.small)add(x.getValue());}}uniqExact函数使用了HashSetTable数据结构来解决这里是比较费内存的所以耗时也比较长
groupBitmap函数使用了RoaringBitmap一个低内存去重方案具体的算法参考
大数据分析常用去重算法分析『Bitmap篇』 从行业测试的结果上来看 从这篇文章来看这几个方法的效果如下 可以看到精确去重函数的耗时是比较长的非精确去重函数的误差在0.5%以内而在实际的也场景中很多数据分析平台更多的是需要一个数量级的概念而不需要一个精确的数据比如一个产品的UV为2600万这个2600万就是一个概略数字且随着变动越大故可以用非精确去重函数
在上面的SQL中DISTINCT方法实际上是在用uniqExact也就是最耗时的精确去重函数在这个场景下我们假设用户数据量比较多请求数据量也比较多我们更关注哪个页面问题多问题大而不是有精确的多少个慢请求数符合非精确去重函数的场景
那这里进行修改
/* 精确但耗时的方法 */
COUNT(DISTINCT device_id) FILTER (WHERE is_slow true) AS slow_user_count/* 不精确但快速的方法*/
uniqIf(device_id, is_slow true) AS slow_user_count知识点上图中除了将DISTINCT修改uniq外还增加了如果增加了IF判断应该怎么写的语法 第二个问题是With用法的性能问题 在Clickhouse说起来With有两种用法
一种是通用SQL常见的用法 with alias as (…)这个叫CTEcommon table expression是SQL定义中的一部份按照这篇文章来看 The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution. CTEs often act as a bridge to transform the data in source tables to the format expected by the query. A common table expression, or CTE, is a temporary named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement. Each SQL CTE is like a named query, whose result is stored in a virtual table (a CTE) to be referenced later in the main query. 就是建立一个虚拟表来存储中间数据然后进行使用值得一提的是子查询和CTE嵌套的性能理论上是一样的但后者的可读性更好不过在某些关系型数据库的引擎上略有区别但本质上区别不大
比如上一章节根据业务输出的带With的SQL可以转换成以下嵌套子查询
/*不带with版本*/
SELECT page_id,COUNT(DISTINCT device_id) FILTER (WHERE is_slow true) AS slow_user_count,(SELECT COUNT(DISTINCT device_id)FROM Test_Table) AS total_user_count,slow_user_count * 100.0 / total_user_count AS slow_user_percentage
FROM Test_Table
WHERE is_slow true
group by page_id
ORDER BY slow_user_percentage DESC;
但意义不大因为性能没啥变化两种方式都是二次查询读两次盘
另一种是with (…) as alias这个是ClickHouse的宏展开一样的能力是Clickhouse独有的语法
根据官方的文档主要有四种用法
1.使用常量作为变量
WITH 2019-08-01 15:23:00 as ts_upper_bound
SELECT *
FROM hits
WHEREEventDate toDate(ts_upper_bound) ANDEventTime ts_upper_bound;
2.封装表达式
WITH sum(bytes) as s
SELECTformatReadableSize(s),table
FROM system.parts
GROUP BY table
ORDER BY s;
3.使用标量子查询的结果这个和前面的with有点像但不能用.xxx的形式获取值且Select只能一个值
/* this example would return TOP 10 of most huge tables */
WITH(SELECT sum(bytes)FROM system.partsWHERE active) AS total_disk_usage
SELECT(sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;
4.在子查询中重用表达式
WITH test1 AS (SELECT i 1, j 1 FROM test1)
SELECT * FROM test1;
其中第一个、第二个相当于直接替换没有啥影响而第三种、第四种和CTE的作用差不多都会逐个去执行SQL也就意味着二次查询读两次盘
所以看起来使用With无法避免二次读盘的问题
那这里有没有一次读盘就可以解决这里的问题呢看起来group by分组前后的数据做数值计算也是一个经典场景
那这里就得用到Clickhouse经典的窗口函数和物化视图了
窗口函数这篇文章有比较详细的介绍
物化试图这篇文章有比较详细的介绍
先看结果SQL
/* 一条sql的版本*/
SELECT page_id,uniqIf(device_id, is_slow true) AS slow_user_count,uniq(device_id) AS page_user_count,uniqMerge(uniqState(device_id)) OVER () as total_user_count
FROM Test_Table
group by page_id
前两个一个是某个页面慢请求的用户数一个该页面所欲请求的用户数
第三个需要拆开来看 uniqState是一个物化视图的方法可以理解成一个AggregateFunction类型的数据的中间状态这里可以理解基于每个页面都生成了一个数组存储对应的用户名单
而uniqMerge可以将多个AggregateFunction类型的中间状态组合计算为最终的聚合结果比如以下两个SQL是等价的
SELECT uniq(UserID) FROM tableSELECT uniqMerge(state) FROM (SELECT uniqState(UserID) AS state FROM table GROUP BY RegionID)
当然在这里用到的是uniqState 和 uniqMerge
这里可以换成任何以-State和-Merge为后缀的方法
回到这个SQL这里uniqMerge(uniqState(device_id)) OVER () 相当于合并了“基于每个页面都生成的数组每个数组存储对应的用户名单”即访问所有页面的所有用户数
这样就比较优雅的实现了不用with的问题且这里的性能也是比较快的
