作为一个大数据工程师, 细粒度的数据分析是避免不了的事情;
通常情况下,使用2-4个维度进行数据分析能对付绝大多数数据分析的需求;
但是在面对特殊需求的时候, 对数据进行10个维度的分组分析,通常用的union all, jion等低维查询再组合的方法明显不能用了, 这个时候就是with cube的show time;

例:对表temp.temp_test_v1中10个字段进行组合维度查询统计

create table temp.temp_test_v2 as
select column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8,  column_9,  column_10,  count(id),  count(distinct user_id)
from temp.temp_test_v1
group by column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8,  column_9,  column_10
with cube;

sql好写, 但是需要考虑sql是否能运行, 是否效率, 统计结果是否准确;
下面开始验证
1: 验证sql是否能运行
直接运行上面的sql,直接报错, 报错如下:

FAILED: SemanticException [Error 10226]: An additional MR job is introduced since the cardinality of grouping sets is more than hive.new.job.grouping.set.cardinality. This functionality is not supported with distincts. Either set hive.new.job.grouping.set.cardinality to a high number (higher than the number of rows per input row due to grouping sets in the query), or rewrite the query to not use distincts. The number of rows per input row due to grouping sets is 128

这是因为我在hive里跑的这个hql, 默认设置的grouping sets的值是128, 需要将grouping sets改大, 至少需要528才能运行

set hive.new.job.grouping.set.cardinality = 528;

grouping sets不是设置的越大越好, 最大不要超过1024, 再大的话不仅占资源, sql执行速度也不会有所提高;

2: 验证运行效率
说实话, 我不想直接跑上面的sql, 不是我不想跑, 而是我不想等它跑完, 等它跑完的时间够我逛个街, 洗个澡, 喝喝茶, 再慢悠悠的回来, 发现它有可能还没跑完, 依旧卡着reduce 99%, 你要不信邪, 可以自己去试试;

下面是优化过的SQL

create table temp.temp_test_v2 as
select a.column_1,  a.column_2,  a.column_3,  a.column_4,  a.column_5,  a.column_6,  a.column_7,  a.column_8,  a.column_9,  a.column_10, sum(a.vv), count(distinct a.user_id)
from (
	select column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8, column_9,  column_10, user_id, count(id) vv
	from temp.temp_test_v1
	group by column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8, column_9,  column_10, user_id
)  a
group by a.column_1,  a.column_2,  a.column_3,  a.column_4,  a.column_5,  a.column_6,  a.column_7,  a.column_8,  a.column_9,  a.column_10
with cube;

在执行这个SQL, 你会发现和最开始SQL的效率完全不在一个等级上, 自己体会, 多说无益;

3: 验证数据准确
多维数据统计, 最吃力的地方就是数据校验;
如果 column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10 这10个字段的数据存在错误数据还好说, 如果存在null就是导致最后的统计数据出现重复;

校验sql

select column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8, column_9,  column_10, count(*)
from temp.temp_test_v2
group by column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8, column_9,  column_10
having count(*) > 1;

如果上面的sql查询出重复数据, 恭喜你, 你中奖了, 你需要排查column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10这10个字段里的是否存在数据为Null;

第三次优化sql

create table temp.temp_test_v2 as
select a.column_1,  a.column_2,  a.column_3,  a.column_4,  a.column_5,  a.column_6,  a.column_7,  a.column_8,  a.column_9,  a.column_10, sum(a.vv), count(distinct a.user_id)
from (
	select column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8, column_9,  column_10, user_id, count(id) vv
	from (
		select id, user_id, nvl(column_1, '未知') column_1, nvl(column_2, '未知') column_2, nvl(column_3, '未知') column_3, nvl(column_4, '未知') column_4, nvl(column_5, '未知') column_5, nvl(column_6, '未知') column_6, nvl(column_7, '未知') column_7, nvl(column_8, '未知') column_8, nvl(column_9, '未知') column_9, nvl(column_10, '未知') column_10
		from temp.temp_test_v1
	) b
	group by column_1,  column_2,  column_3,  column_4,  column_5,  column_6,  column_7,  column_8, column_9,  column_10, user_id
)  a
group by a.column_1,  a.column_2,  a.column_3,  a.column_4,  a.column_5,  a.column_6,  a.column_7,  a.column_8,  a.column_9,  a.column_10
with cube;

这样写的好处就是提前将null数据给清洗处理出来, 不做重复无用功;

你学废了么?