写在前面:我是「云祁」,一枚热爱技术、会写诗的大数据开发猿。昵称来源于王安石诗中一句
[ 云之祁祁,或雨于渊 ]
,甚是喜欢。
写博客一方面是对自己学习的一点点总结及记录,另一方面则是希望能够帮助更多对大数据感兴趣的朋友。如果你也对数据中台、数据建模、数据分析以及Flink/Spark/Hadoop/数仓开发
感兴趣,可以关注我的动态 https://blog.csdn.net/BeiisBei ,让我们一起挖掘大数据的价值~
每天都要进步一点点,生命不是要超越别人,而是要超越自己! (ง •_•)ง
一、背景
在用HQL进行数据分析时,我们不仅想要聚集前的数据,又想要聚集后的数据,这时候往往可以想到用窗口函数,具体窗口函数的使用,可以参考的这篇博文 hive 窗口函数总结 。
几道开胃菜,每一道笔试题,我都尽可能从多个角度来分析,大家可以参考一下,既可以通过自连接来实现,也可以通过窗口函数来解决,欢迎大家讨论 🙆♂️:
二、笔试题实战
Hive 的五个笔试题 |
---|
【Hive】笔试题 01 |
【Hive】笔试题 02 |
【Hive】笔试题 03 |
【Hive】笔试题 04 |
【Hive】笔试题 05 |
【Hive】大厂热门数据分析题 |
未持完续… |
三、数据分析题(案例)
场景举例:北京市学生成绩分析 💥
成绩的数据格式:时间,学校,年纪,姓名,科目,成绩
样例数据如下:
2013,北大,1,裘容絮,语文,97
2013,北大,1,庆眠拔,语文,52
2013,北大,1,乌洒筹,语文,85
2012,清华,0,钦尧,英语,61
2015,北理工,3,冼殿,物理,81
2016,北科,4,况飘索,化学,92
2014,北航,2,孔须,数学,70
2012,清华,0,王脊,英语,59
2014,北航,2,方部盾,数学,49
2014,北航,2,东门雹,数学,77
create table t_score (
time int,
school string,
class int,
name string,
subjects string,
score int
)
row format delimited fields terminated by ',';
load data local inpath "/opt/data/a.csv" into table t_score
问题:
分组 TopN,选出2014年每个学校、每个年级、分数前三的科目
select t.*
from
(
select
time,
school,
class,
score,
row_number() over (partition by school, class, subjects order by score desc) rank_code
from t_score
where time = "2014"
) t
where t.rank_code <= 3;
详解如下:
row_number函数:row_number() 按指定的列进行分组生成行序列,从 1 开始,如果两行记录的分组列相同,则行序列 +1。
over 函数:是一个窗口函数。
over (order by score) 按照 score 排序进行累计,order by 是个默认的开窗函数。
over (partition by class) 按照班级分区。
over (partition by class order by score) 按照班级分区,并按着分数排序。
over (order by score range between 2 preceding and 2 following) 窗口范围为当前行的数据幅度减2加2后的范围内的数据求和。
优化:
row_number() over (distribute by school, class, subjects sort by score desc) rank_code
2014年,北航,每个班级,每科的分数,及分数上下浮动 2 分的总和
select time,school, class, subjects, score,
sum(score) over (order by score range between 2 preceding and 2 following) sscore
from t_score
where time = "2014" and school="北航";
over (order by score rows between 2 preceding and 2 following):窗口范围为当前行前后各移动2行。
where 与 having:2012年,清华 0 年级,总成绩大于 200 分的学生以及学生数
select *,sum(score) as total_score,
count(1) over (partition by school, class)
from t_score where school="清华" and class = 0 and time=2012
group by school, class, name,time,subjects,score having total_score > 50;
having 是分组(group by)后的筛选条件,分组后的数据组内再筛选,也就是说 HAVING 子句可以让我们筛选成组后的各组数据。
where 则是在分组,聚合前先筛选记录。也就是说作用在 GROUP BY 子句和 HAVING 子句前。
四、情景分析题
今年加入进来了 10 个学校,学校数据差异很大计算每个学校的平均分。
该题主要是考察数据倾斜的处理方式。
group by 方式很容易产生数据倾斜 ❗,需要注意一下几点:
Map 端部分聚合
hive.map.aggr=true(用于设定是否在 map 端进行聚合,默认值为真,相当于 combine)
hive.groupby.mapaggr.checkinterval=100000(用于设定 map 端进行聚合操作的条数)
有数据倾斜时进行负载均衡
设定 hive.groupby.skewindata,当选项设定为 true 是,生成的查询计划有两个 MapReduce 任务。
(先打散数据)
第一个 MapReduce 中,map 的输出结果集合会随机分布到 reduce 中, 每个 reduce 做部分聚合操作,并输出结果。
这样处理的结果是,相同的 group by key 有可能分发到不同的 reduce 中,从而达到负载均衡的目的;
第二个 MapReduce 任务再根据预处理的数据结果按照 group by key 分布到 reduce 中
(这个过程可以保证相同的 group by key 分布到同一个 reduce 中),最后完成最终的聚合操作。
🚀
假设我创建了一张表,其中包含了 2016 年客户完成的所有交易的详细信息:
CREATE TABLE transaction_details
(cust_id INT, amount FLOAT, month STRING, country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;
现在我插入了 100 万条数据,我想知道每个月的总收入。
问:如何高效的统计出结果,写出步骤即可。 (提示:动态分区)
1.首先分析这个需求,其实并不难,但是由于题目说了,要高效.而且数据量也不小,直接写sql查询估计肯定会挂.
2.分析:
a.我们可以通过根据每个月对表进行分区来解决查询慢的问题。 因此,对于每个月我们将只扫描分区的数据,而不是整个数据集。
b.但是我们不能直接对现有的非分区表进行分区。所以我们会采取以下步骤来解决这个问题:
c.创建一个分区表,partitioned_transaction:
i.create table partitioned_transaction
(cust_id int, amount float, country string) partitioned by (month string)
row format delimited fields terminated by ‘,’ ;
d.在 Hive 中启用动态分区:
i.SET hive.exec.dynamic.partition=true;
ii.SET hive.exec.dynamic.partition.mode=nonstrict;
e.将数据从非分区表导入到新创建的分区表中:
i.insert overwrite table partitioned_transaction partition (month) select cust_id, amount, country, month from transaction_details;
f.使用新建的分区表实现需求。
欢迎扫码关注我的公众号,在这里我将专注分享数据仓库、数据建模与大数据技术的相关内容。