做数据分析的同学总有会吐槽的
1:为什么我写个sql放到线上去执行就这么慢呢?能不能快点出结果?
2:能不能把这几个表组合成一个大宽表让我只做一个表的查询,不用手动连那么多表?
.....
相信很多做数据开发的同学总会听到这样的吐槽,哈哈,怎么解决这种问题是数据开发同学头痛问题。
先我们来讲一个案例,在我们的业务中,注册是不需要手机号,注册之后会填写个人信息,然后呢,我们要统计某个年龄段绑定手机的留存。这样我们上报会产生三个事件,然后这样的话我们在数据表里面就会有三张表注册表(user),绑定手机表(bind),用户信息表(user_info),这样分析师如果要计算某个年龄段绑定手机的留存,需要连三个表,像我们的用户都是亿级,join查询起来三个表都是比较好资源,然后我们就想把三个表通过join方法组合成一个大宽表。下面我之前想到的是有两种方案:
方案一:通过脚本定时查询数据把数据汇总到一个表里面
方案二:利用物化视图来解决,但是发现好像还是得配合脚本处理,因为物化视图有个问题就是左边驱动,如果其他表变化是不会更新物化视图的数据。
我这里就细化物化视图这种解决方案。
物化视图概念:
我们都知道,数据库中的视图(view)是从一张或多张数据库表查询导出的虚拟表,反映基础表中数据的变化,且本身不存储数据。然而物化视图是查询结果集的一份持久化存储,所以它与普通视图完全不同,而非常趋近于表。“查询结果集”的范围很宽泛,可以是基础表中部分数据的一份简单拷贝,也可以是多表join之后产生的结果或其子集,或者原始数据的聚合指标等等。所以,物化视图不会随着基础表的变化而变化,所以它也称为快照(snapshot)。如果要更新数据的话,需要用户手动进行,如周期性执行SQL,或利用触发器等机制。
下面我来个例子看看物化视图使用的例子:
建立三个表:
CREATE TABLE user( user_id UInt16,phone String,name String,create_time DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (user_id) SETTINGS index_granularity = 819;
CREATE TABLE bind( user_id UInt16,phone String,create_time DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (user_id) SETTINGS index_granularity = 819;
CREATE TABLE user_info( user_id UInt16,sig String,node String,create_time DateTime ) ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) ORDER BY (user_id) SETTINGS index_granularity = 819;
插入一些数据:
insert into user values(1,'18475484454','张三',now()),(2,'18465484454','李四',now()),(3,'','王五',now()),(4,'','小狗',now())
insert into bind values(1,'18475484454',now()),(3,'18475484457',now())
insert into user_info values(1,'我的地盘我做主','我的签名',now()),(3,'我的地盘我做主2','我的签名2',now())
然后我建个物化视图,我发现建物化视图的坑还是挺多,我这里贴一下我的错误写法和正确写法,我用的clickhouse的版本是21.2.5.5。
错误写法:不能加别名,不然识别不
1:CREATE MATERIALIZED VIEW big_user ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) POPULATE AS select a.user_id user_id,a.name name,a.create_time create_time,b.phone phone,c.sig sig,c.node node from user a left join bind b on a.user_id = b.user_id left join user_info c on a.user_id = c.user_id
2:CREATE MATERIALIZED VIEW big_user ENGINE = MergeTree() PARTITION BY toYYYYMM(create_time) POPULATE AS select a.user_id user_id,a.name name,a.create_time create_time,b.phone phone,c.sig sig,c.node node from user as a left join bind as b on a.user_id = b.user_id left join user_info as c on a.user_id = c.user_id
正确写法:
CREATE MATERIALIZED VIEW big_user ENGINE = MergeTree() PARTITION BY toYYYYMM(user.create_time) ORDER BY (user.user_id) POPULATE AS select user.user_id,user.name,user.create_time,bind.phone,user_info.sig,user_info.node from user left join bind on user.user_id = bind.user_id left join user_info on user.user_id = user_info.user_id
到这里我的物化视图建立成功了,我们来看下数据执行:
select * from big_user;
刚开始是没有user_id为6的,然后我执行了
insert into user values(6,'18475484454','张三',now())
这个sql之后,就出现下面的图了
后面我又执行了一条插入的数据,结果还是没变,这个是因为物化视图的驱动是目前是单标驱动。
insert into bind values(6,'19975484454',now())
好了,上面说了使用clickhouse的物化视图的使用,还讲了处理成大宽表的两种方案,如果有更好的方案的同学也欢迎留言一起交流。
总结:
1:如果物化视图是由两表join产生的,那么物化视图仅有在左表插入数据时才更新。如果只有右表插入数据,则不更新。
2:建立物化视图的时候,POPULATE字段的使用得注意,如果线上很多数据,插入比较多,正在插入的时候表的数据是不会更新到物化视图表里面的,会存在丢数据。