最近有一个需求,统计每天的新老用户,日活,周活,月活。
我们每天的增量数据会加入到hive历史数据表中,包含用户访问网站的一些信息,字段有很多,包括用户唯一标识guid。
当然了日活,周活,月活就是一个count(distinct(guid))语句,非常常用的sql。
但是这里的问题是:
A:每天的新老用户应该怎么统计呢?
B:这还不简单,判断用户guid是否存在与历史库guid中嘛?
A:历史数据几十个T,大概一百亿行,你要每天将当日数据(2~3亿行)与历史数据几亿行进行join判断?
B:额,这个,这个,好像不行哦!
是的,历史数据里面是用户网站访问行为,同一个用户在同一天,不同的天都有可能出现,guid在历史表中会有多次。如果直接join,性能很差,实际上是做了很多不必要的工作。
解决方案:
维护一张用户表,里面有4列:guid, starttime, endtime, num,分别是用户的guid,第一次访问时间,最后一次访问时间,访问天数;
从某个状态开始,历史表中guid是唯一的;
当天数据去重后,与历史库join,如果guid在历史库出现过,则将endtime更新为当天时间,num加一;
否则,这是一个新用户,插入历史库,starttime, endtime都为当天时间,num初始值为1。
维护了这么一张用户表后,接下来就可以写hql统计业务了,计算当天新老用户时,只需要与这个历史库进行join就行了(目前为止4千万),当日guid去重后是1千多万,这样就是4千万~1千万的join了,与开始4千万~100亿的join,性能会有巨大提升。
hive历史表的设计与hive相关配置
可以看到这里hive历史表history_helper需要频繁修改,hive表支持数据修改需要在${HIVE_HOME}/conf/hive-site.xml中添加事务支持:
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
</property>
<property>
<name>hive.compactor.worker