第一章.Hive实战

1.需求描述

需求描述

统计硅谷影音视频网站的常规指标,各种TopN指标

• 统计视频观看数top10
• 统计视频类别热度top10(类别热度:类别下的总视频数)
• 统计出视频观看数最高的20个视频的所属类别以及类别包含top20视频的个数
• 统计视频观看数top50所关联视频的所属类别Rank
• 统计每个类别中的视频热度top10,以Music为例
• 统计每个类别视频观看数top10
• 统计上传视频最多的用户top10以及他们上传视频观看次数在前20的视频

2.数据结构

1.视频表

2.用户表

3.准备工作

需要准备的表

• 创建外部数据表 : gulivideo_ori, gulivideo_user_ori
• 创建最终表: gulivideo_orc,gulivideo_user_ori

# 创建外部数据表
1. 上传原始数据到HDFS
hadoop fs -mkdir -p /gulivideo/video
hadoop fs -mkdir -p /gulivideo/user
hadoop fs -put /home/atguigu/data/user/user.txt /gulivideo/user
hadoop fs -put /home/atguigu/data/video/*.txt /gulivideo/video
2. 创建外部数据表:gulivideo_ori
create external table gulivideo_ori(
videoId string,
uploader string,
age int,
category array<String>,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array<String>
)
row format delimited fields terminated by '\t'
collection items terminated by '&'
stored as textfile
location '/gulivideo/video';
3. 创建外部数据表: gulivideo_user_ori
create external table gulivideo_user_ori(
uploader string,
Videos int,
friends int
)
row format delimited fields terminated by '\t'
stored as textfile
location '/gulivideo/user';

# 创建orc存储格式带snappy压缩的管理表
1. gulivideo_orc
create table gulivideo_orc(
    videoId string, 
    uploader string, 
    age int, 
    category array<string>, 
    length int, 
    views int, 
    rate float, 
    ratings int, 
    comments int,
    relatedId array<string>)
stored as orc
tblproperties("orc.compress"="SNAPPY");
2. gulivideo_user_orc
create table gulivideo_user_orc(
    uploader string,
    videos int,
    friends int)
row format delimited 
fields terminated by "\t" 
stored as orc
tblproperties("orc.compress"="SNAPPY");

# 向表中插入数据
insert into table gulivideo_orc select * from gulivideo_ori;
insert into table gulivideo_user_orc select * from gulivideo_user_ori;

4.业务分析

需求一: 统计视频观看数Top10

思路 : 使用order by 按照views字段做一个全局排序即可,同时我们设置只显示前10条

select videoId,`views`
from gulivideo_orc
order by `views` desc
limit 10;

需求二:统计视频类别热度Top10(类别热度:类别下的总视频数)

思路:

1. 即统计每个类别有多少个视频,显示出包含视频最多的前10个类别
2. 需要按照类别group by聚合,然后count组内的videoId个数即可
3. 因为当前表结构为: 一个视频对应一个或多个类别,所以如果要group by类别,需要先将类别进行列转行(炸开),然后再进行count 即可
4. 最终按照热度排序,显示前10条

select t1.category_name,count(t1.videoId) cv
from
(select videoId,category_name
from gulivideo_orc
lateral view explode(category) lv as category_name) t1
group by t1.category_name
order by cv desc
limit 10;

需求三: 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数

思路:

1. 求出观看数前20的视频信息(主要是类别)
2. 在第一步的结果下,求出观看数前20的视频的类别,需要炸开(列转行),形成新字段category_name
3. 在第二步的结果下,按照炸开的视频类别category_name 分组,然后统计组内的个数category_count

select t2.category_name,count(t2.videoId)
from
(select t1.videoId,category_name 
 from
(select videoId, `views` ,category
from gulivideo_orc
order by `views` desc
limit 20) t1
lateral view explode (t1.category) lv as category_name) t2
group by t2.category_name;

需求四: 统计视频观看数top50的视频所关联视频的所属类别排序

思路:

1. 先找出观看数前50的视频信息(主要是求出关联视频)
2. 炸开第一步求出的关联视频array,形成一个新字段new_relatedid
3. 用第二步求出的结果集的new_relatedid和gulivideo_orc表进行join,求出new_relatedid的类别
4. 炸开第三部结果中的category,形成新字段category_name
5. 在第四部的结果上,按照category_name分组,然后求出每组的个数 category_count
6. 在第五步的基础上,对category_count进行排序,利用开窗函数

-- 6.使用rank函数对count_number进行排序（要的是排名）
select t6.category_name,t6.count_number,rank()over(order by t6.count_number desc)
from (
-- 5.将类别分组并统计出其数量
         select t5.category_name, count(t5.category_name) count_number
         from (
                  -- 4.将关联视频的类别炸开
                  select category_name
                  from (
                           -- 3.将相关视频的表和视频表进行关联(relatedid_name和videoid)
                           select t2.relatedid_name, t3.videoid, t3.category
                           from (
                                    --2.将关联的视频炸开
                                    select t1.relatedid, relatedid_name
                                    from (
                                             --1.获取top50的视频
                                             select videoid, `views`, relatedid
                                             from gulivideo_orc
                                             order by `views` desc
                                             limit 50
                                         ) t1
                                             lateral view explode(t1.relatedid) lv as relatedid_name
                                ) t2
                                    join gulivideo_orc t3
                                         on t2.relatedid_name = t3.videoid
                       ) t4
                           lateral view explode(t4.category) lv as category_name
              ) t5
         group by t5.category_name
     ) t6;

需求五: 统计每个类别中的视频热度(视频观看数) Top10,以Music为例

思路 :

1. 要想统计Music类别中的视频热度Top10,需要先找到Music类别,那么就需要将category炸开形成新的字段category_name
2. 然后通过category_name过滤”Music”分类的所有视频信息,按照视频观看数倒序排序

select t1.category_name,t1.`views`
from
(select `views`,category_name
from
gulivideo_orc
lateral view explode(category) lv as category_name) t1
where t1.category_name = 'Music'
order by t1.`views` desc
limit 10;

需求六: 统计每个类别视频观看数Top10

思路:

1. 把原始表中的类别炸开,形成新字段category_name
2. 按照炸开的类别字段category_name分区,按照视频观看数views倒序排序进行开窗,求出每个类别下的所有视频的观看次数排名
3. 按照rk字段对全表进行where过滤,求出每个类别观看数Top10

select t2.videoId,t2.`views`,t2.category_name,t2.r_number
from
(select t1.videoId,t1.`views`,t1.category_name,rank() over(partition by t1.category_name order by t1.`views` desc ) r_number
from
(select videoid,category_name,`views`
from
gulivideo_orc
lateral view explode(category) lv as category_name) t1)t2
where t2.r_number <= 10;

需求七: 统计上传视频最多的用户Top10,以及他们上传的视频观看次数在前20 的视频

此需求的后半段”他们上传的视频观看次数在前20的视频”说的含糊不清,有以下两种约定

约定一: 取Top10中所有人上传的视频的前20

思路:

1. 去用户表gulivideo_user_orc 求出上传视频最多的10个用户
2. 关联gulivideo_orc表,求出这10个用户上传的所有视频,按照观看数求前20

# 上传视频最多的用户Top10
select uploader,videos
from
gulivideo_user_orc
order by videos desc
limit 10;
# 他们上传的视频观看次数在前20的视频
select t1.uploader,t2.`views`
from
(select uploader,videos
from
gulivideo_user_orc
order by videos desc
limit 10) t1 join gulivideo_orc t2
on t1.uploader = t2.uploader
order by `views` desc
limit 20;

约定二: 取Top10中每个人上传视频的前20

思路:

1. 去用户表gulivideo_user_orc求出上传视频最多的10个用户
2. 关联gulivideo_orc表,求出这10个用户上传的所有视频的id,视频观看次数,还要按照uploader分区,views倒序排序,求出每个uploader的上传的视频的观看排名r_number
3. 在第二步的基础上,按照rk进行where过滤,求出r_number< =20的数据

select t4.uploader,t4.`views`,t4.r_number
from
(select t3.`views`,t3.uploader,rank() over(partition by t3.uploader order by `views` desc) r_number
from
(select t1.uploader,t2.`views`
from
(select uploader,videos
from gulivideo_user_orc
order by videos desc
limit 10) t1
join gulivideo_orc t2
on t1.uploader = t2.uploader) t3) t4
where t4.r_number <= 20;

第二章.企业级优化

详情见avi视频