目录
1. 数据立方体
数据立方体是一种对多维的数据模型的描述方式。立方体只是三维的，但是实际数据中不仅仅是三维，可能是四维，五维的。
如上图，数据可以从三个维度查询，这样更方便解读数据。
2. OLAP的基本操作
OLAP的多维分析操作包括：钻取（Drill-down）、上卷（Roll-up）、切片（Slice）、切块（Dice）以及旋转（Pivot），下面还是以上面的数据立方体为例来逐一解释下：
1）钻取（Drill-down）：在维的不同层次间的变化，从上层降到下一层，或者说是将汇总数据拆分到更细节的数据，比如通过对2010年第二季度的总销售数据进行钻取来查看2010年第二季度4、5、6每个月的消费数据，如上图；当然也可以钻取浙江省来查看杭州市、宁波市、温州市……这些城市的销售数据。也可以说是下探
2）上卷（Roll-up）：钻取的逆操作，即从细粒度数据向高层的聚合，如将江苏省、上海市和浙江省的销售数据进行汇总来查看江浙沪地区的销售数据，如上图。
3）切片（Slice）：选择维中特定的值进行分析，比如只选择电子产品的销售数据，或者2010年第二季度的数据。
4）切块（Dice）：选择维中特定区间的数据或者某批特定值进行分析，比如选择2010年第一季度到2010年第二季度的销售数据，或者是电子产品和日用品的销售数据。
5）旋转（Pivot）：即维的位置的互换，就像是二维表的行列转换，如图中通过旋转实现产品维和地域维的互换。
3. Hive Cube
上面介绍的数据立方体Cube，基于事实和维表建立起来的。能曼珠从多角度多层级的查询和分析。
HiveCube是Hive提供的一种能快速生成多维聚合数据的方式，有三种实现方法，分别是**with cube, with rollup, grouping sets，**以上Cube语法也支持在SparkSQL引擎执行。
3.1 with cube
with cube 语法是将所有的维度进行group by的结果组合。
一个具有N维的数据模型，做完Cube操作，能产生2的N次方种聚合方式。
即3个字段的聚合，会2的3次方的组合方式。
-- with cude 的语法
select nvl(name1, 'NULL')
,nvl(name2, 'NULL')
from yiche_external.tmp_20200809001
group by name1, name2 with cube
order by name1, name2
limit 10000
-- 上面的等价于 4部分的共同
select name1, name2
from yiche_external.tmp_20200809001
group by name1, name2
union all
select name1, null
from yiche_external.tmp_20200809001
group by name1, null
union all
select null, name2
from yiche_external.tmp_20200809001
group by null, name2
union all
select null, null
from yiche_external.tmp_20200809001
group by null, null
实例：下图是原始数据
使用with cube 语句：
select nvl(name1, 'NULL')
,nvl(name2, 'NULL')
from yiche_external.tmp_20200809001
group by name1, name2 with cube
order by name1, name2
limit 10000
结果解读：
第2行：NULL NULL 是 group by null null 的结果；
第3行到第10列：是 group by null name2 的结果；
第11，14，17行：是 group by name1 null 的结果；
第15行到第16列：是 group by name1 name2的结果；
北京 NULL -- group by name1 null
北京 天坛 -- group by name1 name2
北京 故宫 -- group by name1 name2
3.2 with rollup
与with cube不同的是，该语法对groupBy子句中维度列的顺序敏感，它只返回第一个分组条件指定的列的统计行，改变groupBy列的顺序会改变聚合结果。具体使用方式见代码
-- with cude 的语法
select name1, name2
from yiche_external.tmp_20200809001
group by name1, name2 with rollup
order by name1, name2
limit 10000
-- 上面的等价于 3部分的共同
select name1, name2
from yiche_external.tmp_20200809001
group by name1, name2
union all
select name1, null
from yiche_external.tmp_20200809001
group by name1, null
union all
select null, null
from yiche_external.tmp_20200809001
group by null, null
再举个例子：
select a, b, c
from tablename
GROUP BY a, b, c, WITH ROLLUP
-- 等同于
select a, b, c
from tablename
group by a, b, c
union all
select a, b, null
from tablename
group by a, b, null
union all
select a, null, null
from tablename
group by a, null, null
一个具有N维的数据模型，做完Rollup操作，能产生N+1种聚合方式。
3.3 Grouping sets
grouping sets 是可以自由的配置需要聚合的列，通过维护聚合列的组合配置完成。比如如果只要 (name1), (name1, name2)这两种粒度的汇总，直接配置就行。其余不需要的粒度可以不配置。
select name1, name2
from yiche_external.tmp_20200809001
group by name1, name2
grouping sets ((name1), (name1, name2))
limit 10000
-- 等同于
select name1, name2
from yiche_external.tmp_20200809001
group by name1, name2
union all
select name1, null
from yiche_external.tmp_20200809001
group by name1, null
GROUP BY a, b, c WITH CUBE
is equivalent to
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( ))
GROUP BY a, b, c, WITH ROLLUP
is equivalent to
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( ))
案例：
select nvl(name1, 'NULL')
,nvl(name2, 'NULL')
,grouping__id
from yiche_external.tmp_20200809001
group by name1, name2
grouping sets ((name1, name2), (name2))
order by name1, name2
limit 10000
结果如下：
最后一列grouping_id即为多加的一列。假如说要取，(name1, name2) 粒度的汇总数据，则where grouping_id=0 即可。
但是同时也发现了，这个id居然不是连续的？ 这点是因为 grouping_id 的算法了。 在执行sql的时候 使用的 grouping sets ((name1, name2), (name2)) 。 具体原因看下一节。
3.4 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), ( ) 对应的二进制数和十进制数见表格
从上图能看出来， 在我执行的 grouping sets ((name1, name2), (name2)) ， 对应的(name1, name2) grouping__id为0 ， (name2) grouping__id为2。
hive1.0以后，grouping__id的生成算法与spark一致。在hive1.0以前，生成算法与spark不一致。在生产环境，任务的运行存在降级处理，例如spark引擎执行失败，会尝试降级到mapreduce引擎执行，如果grouping__id的生成算法不一致，对下游从Cube拿汇总结果的任务会产生严重的影响，故在实际生产环境，直接使用该方法的场景较少。
为了解决上面的问题，看下一节。
3.5 代码实现 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
处理的结果为：
这样做目的是避免了跨平台grouping__id算法不一致造成的风险，并且生成算法简洁易懂，下游使用也方便快捷。
使用的时候使用 where group_id=‘dim1:dim2’ 即可。
3.6 下游使用cube表
在生成cube的时候， 会产生多个不同的粒度的数据，汇总在一个表里面。cube一般是按天跑数据，会产生小时粒度，天粒度，周粒度数据。cube作为i底层的明细中间表。
在生产环境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
and datediff( '${DP_0_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
and datediff( '${DP_0_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
and datediff( '${DP_0_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
and datediff( '${DP_FIRST_DAY_THIS_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 uv
from
tmp_table
where
to_date(placed_order_time) >= '${DP_FIRST_DAY_PRE_MONTH_Y_m_d}'
and
to_date(placed_order_time) < '${DP_0_DAYS_AGO_Y_m_d}'
group by
......
grouping sets (
(shop_id, goods_id, is_placed_1day, placed_order_hour)
, (shop_id, goods_id, is_placed_1day)
, (shop_id, goods_id, is_placed_weekly)
, (shop_id, goods_id, is_placed_monthly)
)
对于使用这种方式处理的Cube，下游汇总表在接入的时候需要额外多加一个时间标志判断条件，比如要拿「店铺+商品+日粒度」的汇总数据 和 「店铺+商品+周粒度」的汇总数据，按照上面代码实现的group_id，现在获取汇总数据的方式见代码
-- 店铺+商品+日粒度
from
tmp_cube
where
group_id = 'shop_id:goods_id:is_placed_1day'
and
is_placed_1day = 1
-- 店铺+商品+周粒度
from
tmp_cube
where
group_id = 'shop_id:goods_id:is_placed_weekly'
and
is_placed_weekly = 1
3.7 HiveCube的拆分
假设随着业务和需求的发展，grouping sets包含的聚合维度一直在增长，到了100，200，300个，Cube还能扛得住压力吗？很明显，肯定扛不住，即使扛住了产出时间也会拖很久。如果真的遇到这种场景，也不是无计可施，可以适当对Cube进行拆分，将grouping sets的配置进行分组，从而拆分出多个Cube任务，降低单个Cube压力过大的场景。
3.8 Cube场景下的刷数
在生产环境，重刷数据是不可避免的常规操作，现实情况有很多场景需要刷近1年，近2年，近3年，甚至近4年的数据。对于Cube这种重型工具，一般是按日调度，调度一次能产出多种时间粒度的汇总结果，如果在刷数场景下，批量实例化很多批次去执行，是不合理的操作，也是不可能完成的任务。那Cube场景下的刷数怎么来完成？回答这个问题前，首先要明确Cube是一个快速生产汇总数据的工具，而不是一个方便刷数的工具。基于这个原则，我们可以拷贝线上Cube主程序代码，稍加改造，制作出刷数代码，将历史数据一次性跑出到临时表，在临时表验证数据的准确性后，动态分区批量插入到下游各粒度聚合的汇总表。Cube不需要保留很久的历史数据，它只是生产汇总数据的机器。
简而言之，是在重刷的时候，只刷某一个粒度上的数据。不进行全量的刷。
扫一扫
190
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
