Kylin版本:apache-kylin-3.0.0-alpha2-bin-cdh60
Apache Kylin在构建Cube的时候,提供的Count Distinct功能,有近似去重和精确去重。
近似去重是基于HyperLogLog算法实现的,可以指定不同的精度。精度越高,使用的存储也越多。
精确去重是基于bitmap实现的。
由于我们的DMP广告平台,是提供给第三方使用,需要精确统计。
下面先简单介绍下我们用到的广告表和相关字段的基数。
表SENT_REPORT_LOG |
| |
字段名 | 备注 | 基数 |
S_IDENTITY | 发送用户ID | 66758094 |
R_IDENTITY | 发送成功ID,为空表示发送失败 | 64235809 |
V_IDENTITY | 点击成功用户ID,为空表示未点击 | 6900151 |
IS_SENT | 是否发送成功,0表示发送失败,1表示发送成功 | 2 |
IS_CLICK | 是否点击,0表示未点击,1表示有点击 | 2 |
PRODUCT_ID | 产品ID | 10 |
CALCULATE_DATE | 发送日期 | 40 |
|
|
|
我们统计的是1个的数据,预计有1亿条数据左右
E_CUSTOMER_LABEL为用户维表,数据量约3亿左右
要解决的问题是:
统计不同的发送状态下,两个不同的产品之间广告发送用户的重合数量
解决方案1:
DIMENSIONS:CALCULATE_DATE
MEASURES:COUNT_DISTINCT(S_IDENTITY),COUNT_DISTINCT(R_IDENTITY),COUNT_DISTINCT(V_IDENTITY)
查询sql1如下:
select count(distinct S_IDENTITY)
from SENT_REPORT_LOG
where SENT_REPORT_LOG.S_DATE >= '2019-10-01' and SENT_REPORT_LOG.S_DATE <= '2019-10-01’;
耗时:35s
查询一周数据:
报错:coprocessor timeout after scanning 7723200 rows while executing SQL
百度了下,美团有类似问题,居然需要基于Kylin二次开发,hold不住啊
https://blog.csdn.net/u010670689/article/details/82110967
又重新思考了下整个cube的建立过程,发现由于需要精确count_distinct,没有对S_IDENTITY建立纬度。尝试建立纬度,看下有没有效果
解决方案2:
DIMENSIONS:CALCULATE_DATE,S_IDENTITY,R_IDENTITY,V_IDENTITY
MEASURES:COUNT_DISTINCT(S_IDENTITY),COUNT_DISTINCT(R_IDENTITY),COUNT_DISTINCT(V_IDENTITY)
Sql1:1001
用时0.2s
Sql1:1001-1007
用时0.72s
Sql1:1001-1031
用时1.12s
缺点:ExpansionRate太高
问题3:添加了R_IDENTITY作为条件后,查询时候又到30S左右了
select count(distinct S_IDENTITY)
from SENT_REPORT_LOG
where SENT_REPORT_LOG.S_DATE >= '2019-10-01' and SENT_REPORT_LOG.S_DATE <= '2019-10-01’
And R_IDENTITY != ‘’;
查询时间:26s,太长了
尝试解决:使用IS_SENT来作为替代,IS_SENT只有两个基数
DIMENSIONS:CALCULATE_DATE,S_IDENTITY,R_IDENTITY,V_IDENTITY,IS_SENT
MEASURES:COUNT_DISTINCT(S_IDENTITY),COUNT_DISTINCT(R_IDENTITY),COUNT_DISTINCT(V_IDENTITY)
select count(distinct S_IDENTITY)
from SENT_REPORT_LOG left join E_CUSTOMER_LABEL
on SENT_REPORT_LOG.S_IDENTITY = E_CUSTOMER_LABEL.UID
where SENT_REPORT_LOG.S_DATE >= '2019-10-01' and SENT_REPORT_LOG.S_DATE <= '2019-10-02'
and is_sent = 1;
查询时间变为来0.3s
select count(distinct S_IDENTITY)
from SENT_REPORT_LOG left join E_CUSTOMER_LABEL
on SENT_REPORT_LOG.S_IDENTITY = E_CUSTOMER_LABEL.UID
where SENT_REPORT_LOG.S_DATE >= '2019-10-01' and SENT_REPORT_LOG.S_DATE <= '2019-10-31'
and is_sent = 1;
耗时0.6s看来也是可以接受的
最后,调整思路
总的发送用户去重数all_sent_quantity:
select count(distinct S_IDENTITY)
from SENT_REPORT_LOG
where S_DATE >= '2019-10-01'
and S_DATE <= '2019-10-07'
andPRODUCT_ID =A;
发送成功状态去重用户数suc_sent_quantity:
select count(distinctR_IDENTITY)
from SENT_REPORT_LOG
where S_DATE >= '2019-10-01'
and S_DATE <= '2019-10-07'
andPRODUCT_ID =A;
发送失败状态去重用户数fail_sent_quantity:
fail_sent_quantity =all_sent_quantity-suc_sent_quantity
下面,直面去重统计这个问题:
首先想到的是用一个包含子查询的sql
select count (1)
from
(select distinct S_IDENTITY
from SENT_REPORT_LOG left join E_CUSTOMER_LABEL
on SENT_REPORT_LOG.S_IDENTITY = E_CUSTOMER_LABEL.UID
where SENT_REPORT_LOG.S_DATE >= '2019-10-01' and SENT_REPORT_LOG.S_DATE <= '2019-10-01'
and BRAND_NAME = 'VIVO-维沃') tmp1,
(select distinct S_IDENTITY
from SENT_REPORT_LOG left join E_CUSTOMER_LABEL
on SENT_REPORT_LOG.S_IDENTITY = E_CUSTOMER_LABEL.UID
where SENT_REPORT_LOG.S_DATE >= '2019-10-02' and SENT_REPORT_LOG.S_DATE <= '2019-10-02'
and BRAND_NAME = 'VIVO-维沃') tmp2
where tmp1.S_IDENTITY = tmp2.S_IDENTITY;
但是报错啊
调整思路:
统计产品A和B在时间范围t1和t2内到重合用户数,步骤如下:
-
统计产品A在时间范围t1和t2的去重用户数UserQuantityA
-
统计产品B在时间范围t1和t2的去重用户数UserQuantityB
-
统计产品A和B在时间范围t1和t2的总的去重用户数UserQuantityAORB
-
计算产品A和B在时间范围t1和t2内的重合用户数UserQuantityAAndB
UserQuantityAAndB =UserQuantityAORB -UserQuantityA -UserQuantityB