Hive实战

一、需求分析
统计抖音视频网站的常规指标，各种TopN指标：
– 统计视频观看数Top10
– 统计视频类别热度Top10
– 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
– 统计视频观看数Top50所关联视频的所属类别排序
– 统计每个类别中的视频热度Top10,以Music为例
– 统计每个类别视频观看数Top10
– 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频

二、数据准备
1、video_info(视频表)

2、user_info(用户表)

3、建表及导入数据
video_info表：

create table video_info(
 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;

user_info表：

create table user_info(  
 uploader string,  
 videos int,  
 friends int) 
 row format delimited 
 fields terminated by "\t" 
 stored as textfile;

video_info_orc表：

create table video_info_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");

user_info_orc表：

create table user_info_orc(  
 uploader string,  
 videos int,  
 friends int) 
 row format delimited fields terminated by "\t" 
 stored as orc 
 tblproperties("orc.compress"="SNAPPY"); 

三、导入数据

load data local inpath '/opt/module/hive/datas/video' into table video_info;
load data local inpath '/opt/module/hive/datas/user' into table user_info;

转到orc表中：

insert into table video_info_orc select * from video_info;
insert into table user_info_orc select * from user_info;

需求一、统计视频观看数Top10
思路：用order by 按照views字段做一个全局排序即可，同时我们设置只显示前10条。
最终SQL：

SELECT videoId,views from video_info_orc order by views desc limit 10;

需求二、统计视频类别热度Top10
思路：（1）即统计每个类别有多少个视频，显示出包含视频最多的前10个类别。
（2）我们需要按照类别group by聚合，然后count组内的videoId个数即可。
（3）因为当前表结构为：一个视频对应一个或多个类别，所以如果要group by 类别的话，需要先将类别进行列转行（展开），然后再进行count即可。
（4）最后按照热度排序，显示前10条。
最终SQL：

SELECT t1.category_name,COUNT(t1.videoId) hot 
FROM 
(SELECT videoId,category_name FROM video_info_orc lateral VIEW explode(category) video_info_orc_tmp AS category_name) t1 
group by 
t1.category_name 
order by hot desc limit 10; 

需求三、统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数。
思路：（1）先找到观看数最高的20个视频所属条目的所有信息，降序排列。
（2）把这20条信息中的category分裂出来（列转行）
（3）最后查询视频分类名称和该分类下有多少个Top20的视频。
最终SQL：

SELECT t2.category_name,COUNT(t2.videoId) AS video_sum 
FROM 
(SELECT t1.videoId,category_name 
FROM 
(SELECT videoId,views,category FROM video_info_orc order by views desc limit 20)t1 
lateral VIEW explode(t1.category) t1_tmp AS category_name) t2 GROUP BY t2.category_name

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

需求五、统计每个类别中的视频热度Top10，以Music为例

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

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