作者：rayyao，TEG数据分析组员工

计费平台部数据团队于2022年3月初get了他们的第一个ClickHouse~ 本文主要内容为在探索使用过程中的记录

计费平台部数据团队服务于多个业务，在Clickhouse之前，我们已经使用过TDW、TDSQL(MySQL)、PgSQL等数据库。TDW中存储的主要是底层的明细数据表和一些画像宽表；Pg表主要存储BI表；MySQL则和Oceanus配合使用，承载实时数据。随着数据越来越多，分析的场景越来越丰富，这三种数据库逐渐不再能满足数据分析的需求。于是，我们引进Clickhouse，希望能借助Clickhouse，更快更好的实现对数据的深入分析。

ClickHouse

OLAP数据库

Clickhouse是一个OLAP数据库。OLAP（OnLine Analytical Processing），和OLTP（OnLine Transaction Processsing）不同，更侧重于数据分析的场景，重点在于支持复杂的分析查询，对大量的历史数据进行查询、分析以支持决策。

优缺点与常用场景

我们目前测评出的优点和缺点有：

优点：

查询速度非常快，查明细数据和聚合数据都可以用
可连接小马BI，对于以小马BI作为可视化工具的我们来说，是一个很大的优点
有很多分析函数，好用

缺点

1.join性能一般，join大表很容易内存GG

DB::Exception: Memory limit (for query) exceeded: would use 9.68 GiB (attempt to allocate chunk of 1073447553 bytes), maximum: 9.31 GiB: While executing ConvertingAggregatedToChunksTransform.

2.使用洛子的TDW入库任务（TDW入库Clickhouse（通用）），数据量大的时候略慢

3.和Hive SQL/MySQL/PgSQL有亿点点不同，要先学习适应一下

常用场景：

OLAP场景的特点：

读多于写
大宽表，读大量行但是少量列，结果集较小
数据批量写入，且数据不更新或少更新
无需事务，数据一致性要求低
灵活多变，不适合预先建模

作为一个OLAP数据库，Clickhouse通常的使用场景高度契合OLAP场景：

用户画像相关：用户画像、标签具有数据多、维度多、每次查询只查询少量列的特点，因此非常适合用Clickhouse来存储和查询；除画像查询之外，与用户画像相关的人群包分包、人群分析都可以考虑用Clickhouse，并且还可以使用位图提高性能；
实时数仓：由于实时数仓要求查询速度快，并且QPS也不会特别高，可以选用Clickhouse做实时数仓的查询；其问题点在于海量数据的写入，不能一条一条写，而要批量写入，尽可能减少写入频次、增大每次写入量；我们目前尝试使用Oceanus+clickhouse的组合，Oceanus提供了写入clickhouse的接口，非常方便（甚至在腾讯云上这两个还是捆绑售卖）；还可以采用物化视图预聚合，实现一些聚合数据的快速查询；
用户行为分析：借助Clickhouse丰富的OLAP函数，能够轻松实现对留存、转化的计算；
除此之外，在AB实验、异动归因、BI等场景中也可以使用Clickhouse，实现更灵活、快速的查询。

一些名词

引擎

MergeTree family

MergeTree引擎是clickhouse中最常用的引擎，适合于往表里插入大量数据，绝大部分情况下使用MergeTree引擎即可；

MergeTree引擎名字的由来：Clickhouse在写入数据时，以片段写入且不可修改（这种写入方式比一次性写入快一些），为了防止片段过多，过一段时间后台会合并同分区片段，所以叫MergeTree（合并树）；MergeTree与LSM Tree类似，但并不是LSM Tree。
MergeTree是这个家族中最基础的引擎，在此只上，还有各种各样的组合： [Replicated] [Replacing/Summing/Aggregating/Collapsing/ VersionedCollapsing/GraphiteMerge] MergeTree 不同的前缀会带来不同的特征，比如Replicated是支持数据副本，Replacing是删除重复数据

集群 cluster、节点 node 、副本 replicate、分片 shard

集群，是副本和分片的基础，是由多个节点组成的
分片，包含的是数据的不同部分；要获取所有数据，需要读所有的分片；分片的作用是水平切分，缓解单一服务器的压力；
副本，包含的是数据的相同部分；要获取所有数据，读一个副本即可；副本带来的是数据的冗余，保证的是数据的安全；
只有建表时用了Replicated关键字，才会有数据副本；在这种情况下，副本的同步依赖Zookeeper
查看相关信息

SELECT * FROM system.clusters LIMIT 2 FORMAT Vertical;

分区

clickhouse的分区有无对查询无影响，影响的主要是更新、插入和删除

应用手册

入门

我们的Clickhouse是从腾讯云上购买的。

安装与访问数据库

1.可以安装一个clickhouse-client来访问CK。主要参考云数据仓库 ClickHouse 快速入门-文档中心-腾讯云-腾讯云，以及询问腾讯云小助手。首先telnet一下，看机器能否连通；

能够连通的话，就可以下载了；如果遇到以下错误，一直卡在connecting页面，只能让腾讯云小助手给了一份安装包。

2.也可以访问腾讯云Clickhouse控制台（登录 - 腾讯云）,通过CDW studio使用CK。

创建表

创建db

CREATE DATABASE IF NOT EXISTS dmp ON CLUSTER default_cluster;

创建表（高可用模式下）

CREATE TABLE testdb.account ON CLUSTER default_cluster(accountid UInt16,name String,address String,year UInt64) ENGINE =ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/testdb/account', '{replica}') ORDER BY (accountid);

clickhouse-client 内的SQL 不能换行，或者每行后面写 \，或者打开clickhouse-client时加-m
clickhouse关键字大小写严格比如String不能写成string
要加cluster，否则入库会失败；即使是用similar 的方式建表也要写 cluster
nullable：Clickhouse默认情况下不支持null；如果传入null的话，会变成对应数据类型的默认值；比如一个字段是int，传入null会变成0，一个字段是string，传入null会变成"；如果需要这个字段支持null的话，建表时可以指定nullable：

y Nullable(Int8)

order by 的选择相当于其他数据库中的索引，通过order by指定，可以是单一维度，也可以是组合维度；和其他数据库中选择索引的原则类似，通常需要满足where中最常用的在前、查询频率大的在前原则；基数特别大的不适合做索引列，比如用户表的userid字段；字段的区分度适中即可，因为ck的索引是稀疏索引，采用的是按照固定的粒度抽样作为实际的索引值，不是mysql的二叉树，所以不建议使用区分度特别高的字段。
表生命周期管理：可以在创建表时指定TTL：

CREATE TABLE example_table
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';

更新TTL：

Alter table t_dw_ed_qshop_cgi_pageinfo MODIFY TTL parseDateTimeBestEffort(imp_date) + Interval 1 month;
+ Interval 1 month;

其中d 必须是DateTime/Date，如果不是的话要将字段转成DateTime/Date；例如在上面例子中就将imp_date字段（string）转换成了datetime；

详细可参考clickhouse官方文档 - table_engine-mergetree-ttl（MergeTree | ClickHouse Docs）

数据导入

TDW -> Clickhouse：腾讯内可以采用TDW洛子入库的方式，即US洛子任务 -- TDW出库到Clickchouse（通用）；
这里存在一个幂等问题，会导致数据重复或数据不一致的情况；解决这个问题，建议建表时采用replicated mergetree类似的引擎。
更详细的解释可以参考 US平台实现Tdw数据出库到ClickHouse之方法指导和原理概述。

实际使用

实时数仓

在使用clickhouse之前，我们的实时数仓是由oceanus+mysql来做的，即用oceanus读取kafka/tdmq/tdbank的数据，处理后插入到mysql中；随着业务的发展，数据越来越多，mysql的查询越来越慢。为了提高查询速度，避免因为查询太慢把实时数据变成了“伪实时数据”，我们尝试把mysql换成clickhouse。正如前面所说，clickhouse在这里的优点在于查询快，缺点在于写入，出库的时候理论上是不能小批量数据直接出库到ch的，网络上有原生flink->kafka处理成batch->clickhouse 这样的做法；幸运的是对于我们而言，oceanus提供了比较成熟的出库方案，直接接clickhouse做sink出库。我们用的oceanus1，这里有个要注意的点是oceanus版本一定要调到1.14；调版本的方法：新增一个版本然后就可以改oceanus的版本了。

用户行为分析

Clickhouse提供了非常丰富的分析函数，我们现在也只使用了其中一部分。

rentention函数计算留存：

参数聚合函数 | ClickHouse Docs

所需数据格式：（至少）两列，其中一列为时间，另一列为uin 以下示例日期为statis_date(string，格式YYYYMMDD，因此用了parseDateTimeBestEffort 转了一次格式）

select \
uin, \
retention(\
    toDate(parseDateTimeBestEffort(statis_date)) = '2022-04-02', \
    toDate(parseDateTimeBestEffort(statis_date)) = '2022-04-03', \
    toDate(parseDateTimeBestEffort(statis_date)) >= '2022-04-03'  and toDate(parseDateTimeBestEffort(statis_date)) <= '2022-04-05'\
    ) as r \
from table \
group by uin

rentention函数实际上为一个聚合函数，返回的是一个array；1 代表满足条件，0 不满足 [1,0,1]代表用户在4.2活跃，在4.3没活跃，在4.3-4.5至少有一天活跃

同理，要计算4.2的留存情况：

WITH toDate('2022-04-02') AS tt \
select  tt, \
    SUM(r[1]) AS uv, \
    SUM(r[2]) / SUM(r[1]) AS `次留`, \
    SUM(r[3]) / SUM(r[1]) AS `3日内留存` \
from \
(select \
uin, \
retention(\
    toDate(parseDateTimeBestEffort(statis_date)) = tt, \
    toDate(parseDateTimeBestEffort(statis_date)) = toDate(addDays(tt,1)), \
    toDate(parseDateTimeBestEffort(statis_date)) >= toDate(addDays(tt,1))  and toDate(parseDateTimeBestEffort(statis_date)) <= toDate(addDays(tt,3))\
    ) as r \
from table \
group by uin )

sequenceMatch计算用户路径：

找出满足pattern的用户，满足即为1，不满足为0；例如下面的sql，是去找到埋点上报满足format1 -> format2 -> format3的用户，其中format2到foramt3之间可以有任意数量的事件。

SELECT * FROM (\
SELECT \
  qquin, \
  sequenceMatch('(?1)(?2).*(?3)')( \
    CAST(substring(tradetime, 1, 10),'UInt64'),\
    iformat = 'format1',\
    iformat = 'format2',\
    iformat = 'format3'\
  ) AS seq_count\
FROM table\
WHERE imp_date = '20220411'\
GROUP BY qquin \
)  t \
where seq_count =1

如果一个埋点没有被定义为事件，那么它不会在sequencematch中被分析，这个埋点出现/不出现/出现在链条中等情况都不会影响最终结果

sequenceCount：

计算一个用户满足了几次pattern，pattern和sequencematch的pattern沿用一个规则。

SELECT seq_count, count(distinct qquin) as uv\
FROM( \
SELECT \
  qquin, \
  sequenceCount('(?1)(?2)(?3)')( \
    CAST(substring(tradetime, 1, 10),'UInt64'),\
    iformat = 'format1',\
    iformat = 'format2',\
    iformat = 'format3'\
  ) AS seq_count\
FROM table\
WHERE imp_date = '20220411'\
GROUP BY qquin) \
GROUP BY seq_count

以上SQL的意思是有一个用户在4.11日满足以下pattern三次 'format1'-> 'format2'-> 'format3'

windowsFunnel函数计算转化：

用于给定行为的情况下，计算路径转化计算给定埋点情况下，完成了1步/2步/3步的用户都有多少：

SELECT level, count(*) as uv 
FROM (
SELECT qquin, 
windowFunnel(86400)(CAST(substring(tradetime, 1, 10),'UInt64'), iformat = 'format1',iformat = 'format2', iformat = 'format3') AS level     
FROM t_dw_ed_qshop_cgi_pageinfo    
WHERE imp_date = '20220411'   
GROUP BY qquin 
) t 
group by level;

array类函数计算用户路径：

不指定起点和终点，求用户路径

Select result, count(distinct qquin) as uv\
FROM\
(SELECT \
    qquin ,  \
   arrayStringConcat(arrayCompact(arrayMap(x->x.1,\
    arraySort(x->x.2, arrayMap( (x, y) -> (x, y),groupArray(iformat),groupArray(time))))),'->') as result\
FROM \
(\
    SELECT\
        imp_date,\
        CAST(substring(tradetime, 1, 10), 'UInt64') AS time,\
        iformat,\
        qquin\
    FROM t_dw_ed_qshop_cgi_pageinfo\
    WHERE (imp_date = '20220411') AND ((iformat LIKE 'click%') OR (iformat LIKE 'pageview.wxshop.zq_result'))\
) AS t\
GROUP BY qquin)\
Group by result \
order by uv desc\
limit 10

去掉了中间的一个arrayfilter，因为不需要起点终点限制，基本参考ClickHouse实战|留存、路径、漏斗、session_FusionInsight_EI企业智能_华为云论坛。

2.指定终点，求用户路径，以用户最后一次到达终点事件为参考，中途如果出现了终点事件不会把它作为终点来看待，会把它作为一个途经点放进去

SELECT result, count(distinct qquin) as uv \
FROM\
(SELECT \
    qquin ,  \
   arrayStringConcat(arrayCompact(arrayMap(x->x.1,\
    arraySort(x->x.2,\
   arrayFilter((x,y)->((y-x.2) <3600 AND (y-x.2)>=0), arrayMap( (x, y) -> (x, y),groupArray(iformat),groupArray(time)), arrayWithConstant(length(groupArray(time)), maxIf(time, iformat = 'pageview.wxshop.zq_result')))))),'->') as result\
FROM \
(\
    SELECT\
        imp_date,\
        CAST(substring(tradetime, 1, 10), 'UInt64') AS time,\
        iformat,\
        qquin\
    FROM t_dw_ed_qshop_cgi_pageinfo\
    WHERE (imp_date = '20220411')  AND ((iformat LIKE 'click%') OR (iformat = 'pageview.wxshop.zq_result'))\
) AS t\
GROUP BY qquin\
)\
WHERE result <> '' \
GROUP BY result\
ORDER BY uv desc \
limit 10

指定路径终点为format1，和1）相比不同在于 arrayFilter((x,y)->((y-x.2) <3600 AND (y-x.2)>=0)... 这一个arrayFilter限制了只取终点前3600秒发生的事件

3.指定起点，求用户路径与指定终点类似，这里指定的是format1作为起点

SELECT result, count(distinct qquin) as uv \
FROM\
(SELECT \
    qquin ,  \
   arrayStringConcat(arrayCompact(arrayMap(x->x.1,\
    arraySort(x->x.2,\
   arrayFilter((x,y)->((x.2-y) <3600) AND y!=0 AND (x.2-y)>=0, arrayMap( (x, y) -> (x, y),groupArray(iformat),groupArray(time)), arrayWithConstant(length(groupArray(time)), minIf(time, iformat = 'format1')))))),'->') as result\
FROM \
(\
    SELECT\
        imp_date,\
        CAST(substring(tradetime, 1, 10), 'UInt64') AS time,\
        iformat,\
        qquin\
    FROM t_dw_ed_qshop_cgi_pageinfo\
    WHERE (imp_date = '20220411') AND ((iformat LIKE 'click%') OR iformat = 'format1')\
) AS t\
GROUP BY qquin\
)\
WHERE result <> '' \
GROUP BY result\
ORDER BY uv desc \
limit 10

用户画像系统

由于用户画像表通常是大宽表，符合维度较多、查询时每次只使用少数维度的特点，我们将用户画像表由TDW/mysql表替换为Clickhouse表。

TIPS & 报错记录

Clickhouse并不是万能的，需要合理选择适用场景~ 并且Clickhouse的SQL又（相对比较）难写，对表的要求又高（指大宽表），每次快速查询背后都是工程师的头发~
Join时大表join小表小表放在右边，因为右边的表会读入内存
因为join性能不加，有的时候可以用subquery替代join
除法精度：如果要进行高精度的除法计算，通常会转化成Decimal，要注意在进行出发运算是，toDecimal32的精度S取被除数的值，并要求被除数的S必须大于除数S，否则报错 toDecimal32(toDecimal32(click_uv,0)/toDecimal32(out_uv,0),3) 结果的小数位数为0 toDecimal32(click_uv,3)/toDecimal32(out_uv,1) 结果的小数位数为3 toDecimal32(click_uv,1)/toDecimal32(out_uv,3)

DB::Exception: Attempt to read after eof: Cannot parse Int32 from String, because value is too short: while executing 'FUNCTION toInt32(last_hy_day_ttds :: 0) -> toInt32(last_hy_day_ttds) Int32 : 1'.

字符串转数字异常，有些为空或者非数字的字符导致转换失败

6.内存不足 SET max_memory_usage = 128000000000; #临时加内存，128G，前提是有这么多内存clickhouse 报内存不足的问题。

7.drop replicated表的时候无法立刻drop clickhouse删除表的时候不会立刻删除，而是（一般）在480秒后删除，具体时间由以下参数控制：

config.xml
<database_atomic_delay_before_drop_table_sec>480</database_atomic_delay_before_drop_table_sec>

感谢youzhihe，dennybliu的指导和支持。

参考

OLAP引擎选型
从TDW到ClickHouse的最佳实践
US平台实现Tdw数据出库到ClickHouse之方法指导和原理概述
Clickhouse在大数据分析平台-留存分析上的应用
[ClickHouse] TDW出库ClickHouse（bulkload）
# ClickHouse tips #6: Filtering data in subqueries to avoid joins
【WeOLAP】Clickhouse查询优化指南
ClickHouse 11.副本与分片 1. 副本 | hnbian
ClickHouse学习系列之四【副本&分片部署说明】 - jyzhou - 博客园
Clickhouse写入问题汇总 - Yisany - 博客园
clickhouse 高阶函数retention查询留存语法、次日留存、三日留存、7日留存小吴-斌的博客-CSDN博客clickhouse retention函数
ClickHouse利器—如何提高留存计算速度 - 掘金
How to pick an ORDER BY / PRIMARY KEY / PARTITION BY for the MergeTree-family table | Altinity Knowledge Base
ClickHouse实战 | 留存、路径、漏斗、session
参数聚合函数 | ClickHouse文档
[ClickHouse day2核心技术篇：实时OLAP处理引擎核心技术深入剖析篇ClickHouse_安安csdn的博客-CSDN博客]

网站首页 > 技术文章正文

ClickHouse超强学习指南

ClickHouse

OLAP数据库

优缺点与常用场景

一些名词

引擎

集群 cluster、节点 node 、副本 replicate、分片 shard

分区

应用手册

入门

安装与访问数据库

创建表

数据导入

实际使用

实时数仓

用户行为分析

array类函数计算用户路径：

用户画像系统

TIPS & 报错记录

参考

猜你喜欢

网站首页 > 技术文章 正文

ClickHouse超强学习指南

ClickHouse

OLAP数据库

优缺点与常用场景

一些名词

引擎

集群 cluster、节点 node 、副本 replicate、分片 shard

分区

应用手册

入门

安装与访问数据库

创建表

数据导入

实际使用

实时数仓

用户行为分析

array类函数计算用户路径：

用户画像系统

TIPS & 报错记录

参考

猜你喜欢

网站首页 > 技术文章正文