Apache Kylin的精确去重Measure的使用和探索

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内到重合用户数，步骤如下：

1. 统计产品A在时间范围t1和t2的去重用户数UserQuantityA

2. 统计产品B在时间范围t1和t2的去重用户数UserQuantityB

3. 统计产品A和B在时间范围t1和t2的总的去重用户数UserQuantityAORB

4. 计算产品A和B在时间范围t1和t2内的重合用户数UserQuantityAAndB

UserQuantityAAndB =UserQuantityAORB -UserQuantityA -UserQuantityB