简介
先说一下基于OLAP数据库clickhouse的标签加工流程,比如在页面上创建了一个自定义标签《近7天登陆次数》那么这个标签的元数据就会被存到mysql之类的OLTP数据库中如下:
label_id | label_name | field_name | label_sql |
---|---|---|---|
1 | 近7天登陆次数 | count_login_by_seven_day | select count(user_id) from <行为埋点表> group by user_id |
field_name存的就是这个标签在clickhouse大宽表中的字段,label_sql就是这个标签值的规则,后台会定时跑批扫描标签字段表,然后根据标签规则执行insert into <大宽表> select user_id,count(user_id) from sys_user a left join <行为埋点表> b on a.user_id=b.user_id(大概意思),最后宽表提供给画像分析使用
问题总结
以上为实际工作中最开始的加工方案,因没有做详细调研和优化,只为项目出结果(你懂得。。。)现归纳有以下问题点:
- 因为分析类系统数据量庞大,逐条update太慢,本身ck对于update delete就不友好,所以选择整张表insert into t1 select a,b,exp(c) from t1
- 整张表insert造成大量重复无用功,因每次只能加工一列,其他列不便,缺重新写一遍磁盘
- 阻塞式加工造成大量任务拥堵
优化
基于以上问题,对clickhouse进行了充分的调研,了解到了一些ck的符合此场景的特性
- clickhouse表引擎的选择
如果在集群的shard配置中增加internal_replication参数并将其设置为true(默认为false),那么Distributed表在该shard中只会选择一个replica并对其写入数据。此时,如果使用ReplicatedMergeTree作为本地表的引擎,则在该shard内多个replica副本之间的数据复制会交由ReplicatedMergeTree自己处理,不再由Distributed负责,从而为其减负。
比如ck集群是5分片,两备份,那么我们使用ReplicatedMergeTree引擎就可以只操作每个分片中的一个node,与他互为备份的node数据由ck自己去同步就可,这样我们就可以分担单台ck节点的压力(因之前是某一个节点提交任务,所有的写数据都由Distributed表引擎去分发,不但依赖带宽,还造成单节点ck压力巨大),使用5个线程去同步提交5个任务。但是问题又来了,大部分标签加工需要基于user_id去join表,如果node1的a表中有1用户,但是在user表中1用户却在node1机器上,那结果显然是不正确的。 - 分片键
clickhouse集群表可以利用hash算法指定分片键,例如 Distributed(cluster, database, table ,sipHash64(user_id)),这样同样的user_id不管是在什么表中,都会被分配到相同的节点,这样1的遗留问题就解决了。 - 数据分区
利用日期字段进行数据分区,减少查询数据扫描量。 - 表结构优化
最重要的一点,单标签单物理表,也就是每个自定义标签存储在自己的表中
这样就可以最大限度的使用多线程去并行提交任务,最大限度的解决堵塞的问题,最终在把所有的标签join到大宽表中,以供系统分析使用。
本方案与郑磊老师共同完成