PostgreSQLGPDB 毫秒级海量多维数据透视 案例分享

2 minute read

背景

典型的电商类数据透视业务,透视的语料可能会包含一些用户的标签数据:例如包含品牌的ID,销售区域的ID,品牌对应用户的ID,以及若干用户标签字段,时间字段等。

标签可能会按不同的维度进行归类,例如tag1 性别,tag2 年龄段, tag3 兴趣爱好, …。

业务方较多的需求可能是对自有品牌的用户进行透视,统计不同的销售区域(渠道)、时间段、标签维度下的用户数(一个非常典型的数据透视需求)。

例子

数据结构举例

每天所在区域、销售渠道的活跃用户ID

t1 (    
  uid,       -- 用户ID    
  groupid,   -- 销售渠道、区域ID    
  day        -- 日期    
)    

每个品牌的自有用户,维护增量

t2 (    
  uid,    -- 用户ID    
  pinpai  -- 品牌    
)    

用户标签,维护增量

t3 (    
  uid,    -- 用户ID    
  tag1,   -- 标签1,如兴趣    
  tag2,   -- 标签2,如性别    
  tag3,   -- 标签3,如年龄段    
  ... ,    
)    

透视举例

对某品牌、某销售区域,某标签、某日进行透视。

例如

select     
  '兴趣' as tag,     
  t3.tag1 as tag_value,     
  count(1) as cnt     
from     
  t1,     
  t2,     
  t3     
where     
  t1.uid = t3.uid     
  and t1.uid = t2.uid     
  and t2.pinpai = ?     
  and t1.groupid = ?     
  AND t1.day = '2017-06-25'     
group by t3.tag1     

这类查询的运算量较大,而且分析师可能对不同的维度进行比对分析,因此建议采用预计算的方法进行优化。

预计算优化

预计算需要得到的结果如下:

t_result (    
  day,      -- 日期    
  pinpai,   -- 品牌ID    
  groupid,  -- 渠道、地区、门店ID    
  tag1,     -- 标签类型1    
  tag2,     -- 标签类型2    
  tag3,     -- 标签类型3    
  ...       -- 标签类型n    
  cnt,      -- 用户数    
  uids,     -- 用户ID数组,这个为可选字段,如果不需要知道ID明细,则不需要保存    
  hll_uids  -- 用户HLL估值    
)    

对于GPDB,可以使用列存储,表分区则按day范围一级分区,按pinpai, groupid哈希进行二级分区,数据分布策略选择随机分布,最后针对每个tag?字段建立单独索引。 从而实现快速的检索(甭管数据量多大,单次透视请求的速度应该可以控制在100毫秒以内)。

得到这份结果后,分析师的查询简化如下(前三个条件通过分区过滤数据,最后根据tag?的索引快速得到结果):

select     
  day, pinpai, groupid, 'tag?' as tag, cnt, uids, hll_uids     
from t_result    
where    
  day =     
  and pinpai =     
  and groupid =     
  and tag? = ?      

预计算后,甚至能以非常少量的运算量,实现更加复杂的维度分析,例如分析某两天的差异用户,分析多个TAG叠加的用户等

预计算的方法

产生统计结果的SQL如下

select     
  t1.day,    
  t2.pinpai,    
  t1.groupid,    
  t3.tag1,     
  t3.tag2,    
  t3.tag3,    
  ...    
  count(1) as cnt ,    
  array_agg(uid) as uids,    
  hll_add_agg(hll_hash_integer(uid)) as hll_uids    
from     
  t1,     
  t2,     
  t3     
where     
  t1.uid = t3.uid     
  and t1.uid = t2.uid     
group by     
  t1.day,    
  t2.pinpai,    
  t1.groupid,    
  grouping sets (    
    (t3.tag1),     
    (t3.tag2),    
    (t3.tag3),    
    (...),    
    (t3.tagn)    
  )    

解释:

1、将uid聚合为数组

array_agg(uid)    

2、将UID转换为hll hash val,并聚合为HLL类型

hll_add_agg(hll_hash_integer(uid))    

3、为了按每个标签维度进行统计,可以使用多维分析语法grouping sets,不必写多条SQL来实现,数据也只会扫一遍,将按每个标签维度进行统计

  grouping sets (    
    (t3.tag1),     
    (t3.tag2),    
    (t3.tag3),    
    (...),    
    (t3.tagn)    
  )    

多维分析的语法详见

《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》

《Greenplum 最佳实践 - 多维分析的使用(CUBE, ROLLUP, GROUPING SETS in GreenPlum and Oracle)》

预计算结果透视查询

如果进行复杂透视,可以将分析结果的不同记录进行数组的逻辑运算,得到最终UID集合结果。

一、数组逻辑运算

1、在数组1但不在数组2的值

create or replace function arr_miner(anyarray, anyarray) returns anyarray as $$    
  select array(select * from (select unnest($1) except select unnest($2)) t group by 1);    
$$ language sql strict;    

2、数组1和数组2的交集

create or replace function arr_overlap(anyarray, anyarray) returns anyarray as $$    
  select array(select * from (select unnest($1) intersect select unnest($2)) t group by 1);    
$$ language sql strict;    

3、数组1和数组2的并集

create or replace function arr_merge(anyarray, anyarray) returns anyarray as $$      
  select array(select unnest(array_cat($1,$2)) group by 1);    
$$ language sql strict;    

例如在促销活动前(2017-06-24)的用户集合为UID1[],促销活动后(2017-06-25)的用户集合为UID2[],想知道促销活动得到了哪些新增用户。

arr_miner(uid2[], uid1[]) 即可得到。

二、我们使用了HLL类型,HLL本身支持数据的逻辑计算

1、计算唯一值个数

hll_cardinality(users)    

2、计算两个HLL的并集,得到一个HLL

hll_union()    

例如在促销活动前(2017-06-24)的用户集合HLL为uid1_hll,促销活动后(2017-06-25)的用户集合HLL为uid2_hll,想知道促销活动得到了多少新增用户。

hll_cardinality(uid2_hll) - hll_cardinality(uid1_hll)    

预计算调度

业务以前通过即时JOIN得到透视结果,而现在我们使用事先统计的方法得到透视结果,事先统计本身是需要调度的。

调度方法取决于数据的来源,以及数据合并的方法,流式增量或批量增量。

一、数据按天统计,历史统计数据无更新,只有增量。

定时将统计结果写入、合并至t_result结果表。

insert into t_result     
select     
  t1.day,    
  t2.pinpai,    
  t1.groupid,    
  t3.tag1,     
  t3.tag2,    
  t3.tag3,    
  ...    
  count(1) as cnt ,    
  array_agg(uid) as uids ,    
  hll_add_agg(hll_hash_integer(uid)) as hll_uids    
from     
  t1,     
  t2,     
  t3     
where     
  t1.uid = t3.uid     
  and t1.uid = t2.uid     
group by     
  t1.day,    
  t2.pinpai,    
  t1.groupid,    
  grouping sets (    
    (t3.tag1),     
    (t3.tag2),    
    (t3.tag3),    
    (...),    
    (t3.tagn)    
  )    

二、合并统计维度数据

每天的统计结果只有按天统计的结果,如果要查询按月,或者按年的统计,需要对天的数据查询并汇聚。

当然,业务也能选择异步汇聚,最终用户查询汇聚后的结果。

t_result_month (    
  month,    -- yyyy-mm    
  pinpai,   -- 品牌ID    
  groupid,  -- 渠道、地区、门店ID    
  tag1,     -- 标签类型1    
  tag2,     -- 标签类型2    
  tag3,     -- 标签类型3    
  ...       -- 标签类型n    
  cnt,      -- 用户数    
  uids,    -- 用户ID数组,这个为可选字段,如果不需要知道ID明细,则不需要保存    
  hll_uids   -- 用户HLL估值    
)    

array聚合需要自定义一个聚合函数

postgres=# create aggregate arragg (anyarray) ( sfunc=arr_merge, stype=anyarray);    
CREATE AGGREGATE    
postgres=# select arragg(c1) from (values (array[1,2,3]),(array[2,5,6])) t (c1);    
   arragg        
-------------    
 {6,3,2,1,5}    
(1 row)    

按月汇聚SQL如下

select     
  to_char(day, 'yyyy-mm'),    
  pinpai,    
  groupid,    
  tag1,    
  tag2,    
  tag3,    
  ...    
  array_length(arragg(uid),1) as cnt,    
  arragg(uid) as uids,    
  hll_union_agg() as hll_uids    
from t_result    
group by     
  to_char(day, 'yyyy-mm'),    
  pinpai,    
  groupid,    
  tag1,    
  tag2,    
  tag3,    
  ...    

按年汇聚以此类推。

三、流式调度

如果业务方有实时统计的需求,那么可以使用流式计算的方法,实时进行以上聚合统计。方法详见

《流计算风云再起 - PostgreSQL携PipelineDB力挺IoT》

《基于PostgreSQL的流式PipelineDB, 1000万/s实时统计不是梦》

《”物联网”流式处理应用 - 用PostgreSQL实时处理(万亿每天)》

如果数据量非常庞大,可以根据分区键,对数据进行分流,不同的数据落到不同的流计算节点,最后汇总流计算的结果到HybridDB(base on GPDB)中。

《ApsaraDB的左右互搏(PgSQL+HybridDB+OSS) - 解决OLTP+OLAP混合需求》

小结

1、对于透视分析需求,使用倒转的方法,将数据按查询需求进行预计算,得到统计结果,从而在透视时仅需查询计算结果,任意维度透视,都可以做到100毫秒以内的响应速度。

2、使用GROUPING SETS,对多个标签维度进行一次性统计,降低数据重复扫描和重复运算,大幅提升处理效率。

3、使用数组,记录每个透视维度的UID,从而不仅能支持透视,还能支持圈人的需求。同时支持未来更加复杂的透视需求。

4、使用HLL类型,存储估算值,在进行复杂透视时,可以使用HLL,例如多个HLL的值可以UNION,可以求唯一值个数,通常用于评估UV,新增UV等。

5、使用流计算,如果数据需要实时的统计,那么可以使用pipelineDB进行流式分析,实时计算统计结果。(pipelineDB正在插件化,将来使用会更加方便)

6、与阿里云云端组件结合,使用OSS对象存储过渡数据(原始数据),使用OSS_FDW外部表对接OSS,因此过渡数据可以不入库,仅仅用于预计算。大幅降低数据库的写入需求、空间需求。

7、使用Greenplum的一级、二级分区,将透视数据的访问需求打散到更小的单位,然后使用标签索引,再次降低数据搜索的范围,从而做到任意数据量,任意维度透视请求100毫秒以内响应。

8、使用列存储,提升压缩比,节省统计数据的空间占用。

参考

https://github.com/aggregateknowledge/postgresql-hll

Flag Counter

digoal’s 大量PostgreSQL文章入口