Clickhouse-收集埋点数据并结合业务数据分析

这应该算是Clickhouse初体验了。

先说需求

• 公司需要统计网站www.dataoke.com(公司核心业务，欢迎大家体验)的埋点数据。之前用的都是hadoop+hive+flink大数据那套，感觉有点笨重，决定换一套轻量的技术方案。
• 需要实时收集网站上的埋点数据，同时结合业务数据（存在mysql的）进行定时分析。
• 对于埋点数据本身，基本没有删改，都是插入和查询操作。
• 上游埋点数据是通过转发写入kafka中，也就是说上游需要对接kafka
• 在定时分析时需要能关联查mysql中的业务数据

确定技术选型

最终在适合OLAP型的数据库中选择了clickhouse

• 高性能：ClickHouse 采用列式存储和查询技术，能够高效地处理大规模数据集，实现快速查询。
• 支持kafka：ClickHouse 可以通过自带的kafka引擎实时消费kafka中的数据。
• 支持mysql：ClickHouse 可以通过自带的mysql引擎映射指定数据库的表，或是通过MaterializeMySQL引擎模拟从库实时同步指定数据库的数据
• 支持分区：ClickHouse 支持分区表，对于每日（分区）数据查询的表现将更好。
• 支持数据压缩：ClickHouse 支持多种数据压缩算法，如 ZSTD、LZ4、GZIP 等。数据压缩可以减少磁盘占用空间，并提高查询效率。

整体流程图

技术落地

1、创建埋点数据表

 clickhouse : )  
 CREATE TABLE logcoll (
 event_time DateTime,
 event_seq String,
 event_type String,
 ........
 .....
 ENGINE = ReplacingMergeTree(event_time) ORDER BY (event_seq) PARTITION BY toYYYYMMDD(event_time);

这里使用event_time（埋点上报）时间做为分区，同时用event_seq（uuid）来保证数据唯一性。

2、创建kafka连接表
该表使用kafka引擎，用于实时消费kafka中的数据到clickhouse

CREATE TABLE kafka_to_logcoll(
event_time String,
event_seq String,
event_type String,
...
...
)ENGINE = Kafka() SETTINGS kafka_broker_list = '192.168.xxx.xxx:9092',kafka_topic_list = 'logcoll-v2',kafka_group_name = 'ck_logcoll_ktt_test',kafka_format = 'JSONStringsEachRow',kafka_num_consumers = 6,kafka_skip_broken_messages=0,kafka_row_delimiter = '\n';

这里直接配置kafka信息即可，这里的kafka引擎表只做为一张中间表存在。

可以通过命令查看消费kafka的状态

3、创建视图

CREATE MATERIALIZED VIEW view_logcoll_ TO logcoll AS
 SELECT toDateTime(toUInt64(event_time) / 1000)  AS   
 event_time,
 event_seq,
 event_type,
...
....
 FROM kafka_to_logcoll;

该视图会实时将中间表的数据写入我们需要的埋点数据表

到此埋点数据写入clickhouse部分已经完成。
根据需求，还需要在clickhouse中关联查业务数据（mysql）

4、创建MySQL引擎表

CREATE TABLE `dtk_users` (
`id` Int32,  
`user_emall` String,  
`user_pwd` String,  
...
...
) ENGINE=MySQL('192.168.xxx.xxx','database_a','dtk_users','user','password')

这里由于是只需要部分表，所以使用的是MySQL引擎（映射关系），而没有使用MaterializedMySQL引擎（模拟从库）

但是这里需要非常小心一点！！！！！
该映射一旦建立，则允许在clickhouse中插入数据并同步回mysql。
针对该问题可以从用户权限角度解决，即只赋予研发用户查询权限。

ok，目前已经可以在clickhouse中关联查询需要的数据了。

彩蛋

clickhouse自身提供多种存储引擎适用于各类场景。
大家可以根据业务需求使用不同的引擎，以达到最优效果。
clickhouse文档说明