HiveCube在有赞的实践

见贤思齐

????点击关注“有赞coder”获取更多技术干货哦～作者：小君部门：数据中台一、前言多维分析是数据仓库系统下游常见的基础应用，底层数据是包含多种粒度汇总结果的Cube，用于提供上卷，下钻等操作的数据支持。创建Cube的工具有很多，本文重点介绍在多维汇总场景下，由传统开发模式替换为HiveCube开发模式过程中碰到的问题以及处理经验，主要包括以下方面的内容：背景理论实践二、背景在今年上半年，我们接到公司一个项目，项目的大致内容是给有赞商家提供自助取数功能。自助取数功能是给有赞商家提供不同维度下不同主题域指标的预览和下载服务。比如商家可以查看交易域的下单金额，客户域的客户数，流量域的访问数等，商家可以按预置的维度，导出任意聚合粒度的汇总数据，但商家查看指标所选的粒度组合是不确定的，可能是分店粒度，可能是总店粒度，可能是商品粒度，也可能是商品规格粒度，在来源上需要区分下单渠道，在时间上需区分小时粒度、天粒度、周粒度、月粒度，整个维度的排列组合非常多。为了实现这套多维聚合的自助取数服务，最开始很自然想到采用Kylin，因为Kylin天生就是做多维聚合的事儿。后面这个想法很快被否决，主要因为当时Kylin机器资源紧张，有限的机器资源必须确保线上正在运行应用的稳定。为了更优雅的基于数仓各主题域的星型模型去产出混合粒度的多维数据立方体，降低代码开发成本和维护成本，我们开始尝试去使用HiveCube，下文主要介绍HiveCube在有赞的使用以及实践。三、理论Cube又叫数据立方体，是基于事实和维度而建立起来的多维数据模型，主要为了满足用户从多角度多层次进行数据查询和分析的需要。HiveCube是Hive提供的一种能快速生成多维聚合数据的方式，有三种实现方法，分别是with cube, with rollup, grouping sets，以上Cube语法也支持在SparkSQL引擎执行。3.1 with Cube该语法生成的结果集展示groupBy所列举维度的所有组合方式的聚合。具体使用方式见代码： -- with cube语法 --select dim1, dim2, count(*)from t1group by dim1, dim2 with cube; -- 常规语法 --select dim1, dim2, count(*) from t1 group by dim1, dim2 union allselect dim1, null, count(*) from t1 group by dim1, null union allselect null, dim2, count(*) from t1 group by null, dim2 union allselect null, null, count(*) from t1;一个具有N维的数据模型，做完Cube操作，能产生2N种聚合方式。3.2 with Rollup与 withcube不同的是，该语法对groupBy子句中维度列的顺序敏感，它只返回第一个分组条件指定的列的统计行，改变groupBy列的顺序会改变聚合结果。具体使用方式见代码： -- with rollup语法 --select dim1, dim2, count(*)from t1group by dim1, dim2 with rollup; -- 常规语法 --select dim1, dim2, count(*) from t1 group by dim1, dim2 union allselect dim1, null, count(*) from t1 group by dim1, null union allselect null, null, count(*) from t1;一个具有N维的数据模型，做完Rollup操作，能产生N+1种聚合方式。3.3 Grouping sets该语法最为灵活，可以自由配置需要聚合的列，通过维护聚合列组合的配置来完成，强烈推荐使用该方法。比如只需要(dim1), (dim1, dim2) 这两种粒度的汇总，直接配置即可，不需要的聚合粒度无需配置，具体使用方式见代码：select dim1, dim2, count(*)from t1group by dim1, dim2 grouping sets ( (dim1), (dim1, dim2) )所以，不管是从性能的角度，还是从使用的灵活度角度，grouping sets都是最优方案，?groupings sets也能等价去替代上面2种语法的实现，具体见代码：GROUP BY a, b, c WITH CUBEis equivalent toGROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))GROUP BY a, b, c, WITH ROLLUPis equivalent toGROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))3.4 Grouping__id FunctionCube语法能迅速生产各种粒度的汇总结果，但不同粒度的汇总数据放在一个Cube，如何便捷拿到指定粒度的汇总数据是一个必须解决的问题。对此问题官方提供了原生的实现方法，通过grouping__id函数，在生成Cube的时候给每种聚合粒度打标，后续从Cube拿指定聚合粒度的汇总数据，只需通过grouping__id生成的标过滤即可。以下模拟了一份二维数据集，基于这份数据集，我们需要做 (dim1, dim2), (dim1), (dim2), ( ) 这四种粒度的汇总，分别计算出对应聚合粒度的记录数。data:code:select dim1, dim2, count(*), grouping__idfrom t1group by dim1, dim2 grouping sets ( (dim1, dim2), (dim1), (dim2), () );result:从结果可以看出 (dim1, dim2), (dim1), (dim2), ( ) 四种粒度的汇总分别被标记上 0, 1, 2, 3 四种grouping__id值，如果需要获取(dim1, dim2)这种粒度的汇总数据，可直接从Cube里面限定 WHERE group_id = 0 即可。3.5 Grouping__id 算法This function returns a bitvector corresponding to whether each column is present or not. For each column, a value of "1" is produced for a row in the result set if that column has been aggregated in that row, otherwise the value is "0". This can be used to differentiate when there are nulls in the data.grouping__id的值是根据groupBy的列是否使用和列的顺序来决定。靠近groupBy的列为高位，远离groupBy的列为低位；列被使用则为'0'，列没有被使用则为'1'。按照此规则，对每种粒度的组合生成一组二进制数，然后将二进制数转成十进制数。(dim1, dim2), (dim1), (dim2), ( ) 对应的二进制数和十进制数见表格hive1.0以后，grouping__id的生成算法与spark一致。在hive1.0以前，生成算法与spark不一致。在生产环境，任务的运行存在降级处理，例如spark引擎执行失败，会尝试降级到mapreduce引擎执行，如果grouping__id的生成算法不一致，对下游从Cube拿汇总结果的任务会产生严重的影响，故在实际生产环境，直接使用该方法的场景较少。四、实践该部分内容重点介绍HiveCube在生产环境使用过程中碰到的问题以及处理经验4.1 代码实现grouping__id因为grouping__id的实现算法在Hive与Spark可能存在差异，相同的代码在不同平台执行会产生不同的group_id标。为了避免这种风险，可以借助gruoping__id的实现思想，用代码给不同粒度的聚合组合打标，打标的实现也非常简单，见以下代码。我们可以根据列的值是否为NULL来判断该聚合组合方式是否使用到该列。如果该列的值IS NOT NULL则赋值该列字段名，如果IS NULL则赋值NULL，最终将各列处理的结果用特殊字符拼装成一个字符串，后续通过该字符串来识别具体是哪种粒度的汇总。concat_ws ( ':' , case when dim1 is not null then 'dim1' else null end , case when dim2 is not null then 'dim2' else null end ) as group_id处理效果：如果需要获取(dim1, dim2)这种粒度的汇总数据，可直接从Cube里面限定 WHERE group_id = 'dim1:dim2' 即可。这种处理方式避免了跨平台grouping__id算法不一致造成的风险，并且生成算法简洁易懂，下游使用也方便快捷。1.在使用Cube基于各主题域明细中间层做多维运算之前，务必，务必，务必确保每个维度都做了空处理操作，否则会导致通过上面代码实现的group_id无法区分是数据NULL还是非聚合列产生的NULL，从而导致 无法准确拿到指定聚合粒度的汇总数据。2.增减维度需要注意维护生成group_id的代码！当新增维度，不能随便位置添加，需要在尾部追加，不能影响已生成的group_id；当减维度，注意要下线使用相关汇总数据的表。4.2 汇总表接入Cube如果我们把Cube比作制造汇总数据的工厂，那么各粒度汇总的结果表就是工厂生产的具体产品。假设现在有以下粒度的汇总：grouping sets ( (shop_id, goods_id, placed_order_date) , (shop_id, goods_id, log_src_channel, placed_order_date) )现在需要分别拿到 「店铺+商品+日粒度」和「店铺+商品+来源渠道+日粒度」的汇总数据，按照上面代码实现的group_id，现在获取汇总数据的方式见代码：-- 店铺+商品+日粒度from tmp_cubewhere group_id = 'shop_id:goods_id:placed_order_date'-- 店铺+商品+来源渠道+日粒度from tmp_cubewhere group_id = 'shop_id:goods_id:log_src_channel:placed_order_date'4.3 Cube数据量的压缩在生产环境Cube一般采用按天跑的形式，使用日分区表的方式进行存储，原则上Cube一次性可以产出小时粒度，天粒度，周粒度，月粒度等不同时间粒度的汇总，对于构建Cube底层使用的明细中间层的时间限制需要优先满足长时间跨度的指标。例如在计算月粒度指标的时候，在日粒度汇总层面会产出近30天的日粒度汇总，但现实情况下游一般只会使用最新一天的日粒度汇总数据，即昨日的汇总数据，但按以上方式的处理就会每天产生29个不会被使用到的日粒度汇总，在聚合维度比较多的时候，数据量膨胀会非常厉害，对于这种情况，可以适当对时间维度做去明细化处理，时间可以处理成是否当日，是否当周，是否当月，是否近30天等标志。这样再计算日粒度汇总的时候，如果是昨日我们把它放在1这个维度值里面；如果不是昨日，也就是其他29天，我们把它放在0这个维度值里面，这样29条记录就被压缩为1条记录，对于一个具体的聚合组合方式数据量能压缩到只有2条，我们只取关注的有效汇总数据即可。具体处理方式见代码：select , shop_id as shop_id , goods_id as goods_id , buyer_id as buyer_id , log_src_channel as log_src_channel , substr(placed_order_time, 1, 10) as placed_order_date , substr(placed_order_time, 12, 2) as placed_order_hour , case when datediff( '${DP_1_DAYS_AGO_Y_m_d}', substr(placed_order_time,1,10) ) 0 then 1 else 0 end as is_placed_1day -- 是否当日 , case when datediff( '${DP_30_DAYS_AGO_Y_m_d}', substr(placed_order_time,1,10) ) 0 then 1 else 0 end as is_placed_30day -- 是否近30天 , case when datediff( '${DP_1_WEEKS_AGO_MONDAY_Y_m_d}', substr(placed_order_time,1,10) ) 0 then 1 else 0 end as is_placed_weekly -- 是否当周 , case when datediff( '${DP_FIRST_DAY_PRE_MONTH_Y_m_d}', substr(placed_order_time,1,10) ) 0 then 1 else 0 end as is_placed_monthly -- 是否当月 , sum( sku_real_pay ) as amt , count( distinct buyer_id ) as uvfrom tmp_tablewhere to_date(placed_order_time) >= '${DP_FIRST_DAY_PRE_MONTH_Y_m_d}' and to_date(placed_order_time)