大数据之Hive 实战完整使用（第十一章）

大数据之Hive 实战完整使用

一、需求描述
二、数据结构
- 1）视频表
- 2）用户表
三、准备表
- 1）需要准备的表
- 2）创建原始数据表
- 2）创建 orc 存储格式带 snappy 压缩的表
四、业务分析、
- 1、统计视频观看数 Top10
- 2、统计视频类别热度 Top10
- 3、统计出视频观看数最高的 20 个视频的所属类别以及类别包含Top20 视频的个数
- 4、统计视频观看数 Top50 所关联视频的所属类别排序
- 5、统计每个类别中的视频热度 Top10，以 Music 为例
- 6、统计每个类别视频观看数 Top10
- 7、统计上传视频最多的用户 Top10 以及他们上传的视频观看次数在前 20 的视频

一、需求描述

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

二、数据结构 1）视频表

2）用户表

三、准备表 1）需要准备的表

创建原始数据表：gulivideo_ori，gulivideo_user_ori，
创建最终表：gulivideo_orc，gulivideo_user_orc

2）创建原始数据表

（1）gulivideo_ori

create table gulivideo_ori(
videoId string, 
 uploader string, 
 age int, 
 category array, 
 length int, 
 views int, 
 rate float, 
 ratings int, 
 comments int,
 relatedId array)
row format delimited fields terminated by "t"
collection items terminated by "&"
stored as textfile;

（2）创建原始数据表: gulivideo_user_ori

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

2）创建 orc 存储格式带 snappy 压缩的表

（1）gulivideo_orc

create table gulivideo_orc(
 videoId string, 
 uploader string, 
 age int, 
 category array, 
 length int, 
 views int, 
 rate float, 
 ratings int, 
 comments int,
 relatedId array)
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");

（3）向 ori 表插入数据

load data local inpath "/opt/module/data/video" into table gulivideo_ori;
load data local inpath "/opt/module/user" into table gulivideo_user_ori;

（4）向 orc 表插入数据

insert into table gulivideo_orc select * from gulivideo_ori;
insert into table gulivideo_user_orc select * from gulivideo_user_ori;

四、业务分析、 1、统计视频观看数 Top10

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

SELECt
videoId, views
FROM
gulivideo_orc ORDER BY
views DESC LIMIT 10;

2、统计视频类别热度 Top10

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

SELECt
t1.category_name , COUNT(t1.videoId) hot
FROM  ( SELECt
videoId, category_name
FROM
gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
) t1 GROUP BY
t1.category_name ORDER BY
hot DESC LIMIT 10

3、统计出视频观看数最高的 20 个视频的所属类别以及类别包含Top20 视频的个数

思路：
（1）先找到观看数最高的 20 个视频所属条目的所有信息，降序排列
（2）把这 20 条信息中的 category 分裂出来(列转行)
（3）最后查询视频分类名称和该分类下有多少个 Top20 的视频

SELECt
	t2.category_name,
	COUNT( t2.videoId ) video_sum 
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 ) t1_tmp AS category_name 
	) t2 
GROUP BY
	t2.category_name

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

SELECt
	t6.category_name,
	t6.video_sum,
	rank() over ( ORDER BY t6.video_sum DESC ) rk 
FROM
	(
	SELECt
		t5.category_name,
		COUNT( t5.relatedid_id ) video_sum 
	FROM
		(
		SELECt
			t4.relatedid_id,
			category_name 
		FROM
			(
			SELECt
				t2.relatedid_id,
				t3.category 
			FROM
				(
				SELECt
					relatedid_id 
				FROM
					( SELECt videoId, views, relatedid FROM gulivideo_orc ORDER BY views DESC LIMIT 50 ) t1 lateral VIEW explode ( t1.relatedid ) t1_tmp AS relatedid_id 
				) t2
				JOIN gulivideo_orc t3 ON t2.relatedid_id = t3.videoId 
			) t4 lateral VIEW explode ( t4.category ) t4_tmp AS category_name 
		) t5 
	GROUP BY
		t5.category_name 
	ORDER BY
	video_sum DESC 
	) t6

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

思路：
（1）要想统计 Music 类别中的视频热度 Top10，需要先找到 Music 类别，那么就需要将
category 展开，所以可以创建一张表用于存放 categoryId 展开的数据。
（2）向 category 展开的表中插入数据。
（3）统计对应类别（Music）中的视频热度。统计 Music 类别的 Top10（也可以统计其他）

SELECt
	t1.videoId,
	t1.views,
	t1.category_name 
FROM
	( SELECt videoId, views, category_name FROM gulivideo_orc lateral VIEW explode ( category ) gulivideo_orc_tmp AS category_name ) t1 
WHERe
	t1.category_name = "Music" 
ORDER BY
	t1.views DESC 
	LIMIT 10

6、统计每个类别视频观看数 Top10

SELECt
	t2.videoId,
	t2.views,
	t2.category_name,
	t2.rk 
FROM
	(
	SELECt
		t1.videoId,
		t1.views,
		t1.category_name,
		rank() over ( PARTITION BY t1.category_name ORDER BY t1.views DESC ) rk 
	FROM
		( SELECt videoId, views, category_name FROM gulivideo_orc lateral VIEW explode ( category ) gulivideo_orc_tmp AS category_name ) t1 
	) t2 
WHERe
	t2.rk <= 10

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

思路：
（1）求出上传视频最多的 10 个用户
（2）关联 gulivideo_orc 表，求出这 10 个用户上传的所有的视频，按照观看数取前 20

SELECt
	t2.videoId,
	t2.views,
	t2.uploader 
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
	t2.views DESC

大数据之Hive 实战 完整使用（第十一章）

大数据系统相关栏目本月热门文章

大数据之Hive 实战完整使用（第十一章）