打怪升级之小白的大数据之旅(七十)＜Hive旅程终点站:Hive的综合案例＞

打怪升级之小白的大数据之旅(七十)

Hive旅程终点站:Hive的综合案例

本章内容

本章是Hive的最后一章，主要是通过一个案例来对我们前面所学的知识点进行一个实操总结，大家根据案例查漏补缺，哪里知识点不会就补一下哪里的知识点

数据结构

测试数据大家自行下载：https://download.csdn.net/download/Li_G_yuan/19362051?spm=1001.2014.3001.5503

上传数据到HDFS

# 创建本地存储数据文件夹
mkdir /opt/module/hive/video
# 通过xftp/rz等工具将数据放到该文件夹下，我就不演示了
# HDFS上创建存储视频表数据的文件夹
hadoop fs -mkdir -p  /video/movie
# HDFS上创建存储用户表数据的文件夹
hadoop fs -mkdir -p  /video/user
# 上传数据到指定文件夹
hadoop fs -put video/user/user.txt   /video/user
hadoop fs -put video/movie/*.txt   /video/movie

视频表

字段	备注	详细描述
videoId	视频唯一id（String）	11位字符串
uploader	视频上传者（String）	上传视频的用户名String
age	视频年龄（int）	视频在平台上的整数天
category	视频类别（Array）	上传视频指定的视频分类
length	视频长度（Int）	整形数字标识的视频长度
views	观看次数（Int）	视频被浏览的次数
rate	视频评分（Double）	满分5分
Ratings	流量（Int）	视频的流量，整型数字
conments	评论数（Int）	一个视频的整数评论数
relatedId	相关视频id（Array）	相关视频的id，最多20个

用户表

字段	备注	字段类型
uploader	上传者用户名	string
videos	上传视频数	int
friends	朋友数量	int

需求

统计油管视频网站的常规指标，各种TopN指标（数据是很多年前的了）：

• 统计视频观看数Top10
• 统计视频类别热度Top10（类别热度：类别下的总视频数）
• 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
• 统计视频观看数Top50所关联视频的所属类别Rank
• 统计每个类别中的视频热度Top10,以Music为例（视频热度：视频观看数）
• 统计每个类别视频观看数Top10
• 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频

创建表与加载数据

因为是模拟实际开发，所以我就通过创建外部表的方式来进行

创建表并加载数据

创建外部数据表：movie_ori，movie_user_ori，

-- movie_ori表
create external table movie_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 '/video/movie';

-- movie_user_ori表
create external table movie_user_ori(
    uploader string,
    videos int,
    friends int)
row format delimited 
fields terminated by "\t" 
stored as textfile
location '/video/user';

创建最终表：movie_orc，movie_user_orc（使用orc存储）

-- movie_orc表
create table movie_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");
-- movie_user_orc表
create table movie_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 movie_orc select * from movie_ori;
insert into table movie_user_orc select * from movie_user_ori;

根据需求完成业务逻辑

前面的数据、表都准备好了，接下来就是根据需求完成我们的业务逻辑了

统计视频观看数Top10

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

SELECT 
     videoId,
     views 
FROM 
     movie_orc 
ORDER BY 
     views DESC 
LIMIT 10;

统计视频类别热度Top10（类别热度：类别下的总视频数）

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

SELECT 
	category_name,
	count(t1.videoid) hot
FROM
(
	select 
		videoid,
		category,
		category_name
	from movie_orc 
	lateral view explode(category) movie_orc_tmp as category_name
) t1
group by category_name
order by hot DESC 
limit 10;

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

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

SELECT 
	t2.category_name,
	count(t2.videoid) category_count
from
(
	select
		videoid,
		category_name
	from
	(
		SELECT 
			videoid,
			category,
			views
		from movie_orc 
		order by VIEWS DESC 
		limit 20
	) t1
	LATERAL VIEW explode(category) tmp as category_name
) t2
group by t2.category_name;

统计视频观看数Top50所关联视频的所属类别Rank

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

SELECT
	t6.category_name,
	t6.category_count,
	rank()over(order by category_count desc) rk
FROM 
(
	SELECT
		t5.category_name,
		count(t5.new_relatedid) category_count
	FROM 
	(
		SELECT
			t4.new_relatedid,
			category_name
		FROM 
		(
			SELECT
				t2.new_relatedid,
				t3.category
			FROM 
			(
				SELECT
					t1.videoid,
					new_relatedid
				FROM 
				(
					SELECT 
						videoid,
						views,
						relatedid
					FROM movie_orc
					order by views DESC 
					limit 50
				) t1
				LATERAL view explode(t1.relatedid) tmp as new_relatedid
			) t2
			join
			movie_orc t3
			on t2.new_relatedid = t3.videoid
		) t4
		LATERAL view explode(t4.category) t4_tmp as category_name
	) t5
	group by t5.category_name
) t6;

统计每个类别中的视频热度Top10,以Music为例（视频热度：视频观看数）

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

SELECT
	t1.videoid,
	t1.category_name,
	t1.VIEWS
FROM 
(
	SELECT
		videoid,
		category_name,
		VIEWS
	FROM movie_orc 
	lateral view explode(category) tmp as category_name
) t1
where t1.category_name = "Music"
order by t1.views DESC 
limit 10; 

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

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

SELECT 
	t2.category_name,
	t2.videoid,
	t2.views,
	t2.rk
FROM 
(
	SELECT 
		t1.category_name,
		t1.videoid,
		t1.views,
		rank() over(partition by t1.category_name order by t1.views desc) rk
	FROM 
	(
		SELECT
			videoid,
			category_name,
			VIEWS
		FROM movie_orc 
		lateral view explode(category) tmp as category_name
	) t1
) t2
where rk <= 10;

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

这个需求有两种思考方式，我都写下来

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

SELECT 
	t1.uploader,
	t2.videoid,
	t2.views
FROM 
(
	select 
		uploader,
		videos
	from movie_user_orc
	order by videos DESC 
	limit 10
) t1
JOIN 
movie_orc t2
on t1.uploader = t2.uploader
ORDER BY t2.views DESC
LIMIT 20;

思路二：取Top10中每个人上传的视频的前20
（1）去用户表movie_user_orc求出上传视频最多的10个用户
（2）关联movie_orc表，求出这10个用户上传的所有的视频id，视频观看次数，还要按照uploader分区，views倒序排序，求出每个uploder的上传的视频的观看排名rk
（3）在第二步的结果上，按照rk进行where过滤，求出rk<=20的数据

SELECT
	t3.uploader,
	t3.videoid,
	t3.views,
	t3.rk
FROM 
(
	SELECT
		t1.uploader,
		t2.videoid,
		t2.views,
		rank() over(partition by t1.uploader order by t2.views desc) rk
	FROM 
	(
		select 
			uploader,
			videos
		from movie_user_orc
		order by videos DESC 
		limit 10
	) t1
	JOIN 
	movie_orc t2
	on t1.uploader = t2.uploader
) t3
where t3.rk <= 20; 

总结

• Hive到此就全部结束了，大家没事的时候就去第一章中我那个Hve升级里面，多做一些题，熟能生巧，下面我来总结一下整个Hive内容

Hive总结

• hive是基于hadoop的一个工具，它的主要作用是通过Hive像操作Mysql一样操作Hadoop中存储的数据，Hive操作的语句我们通常称之为HQL
• hive可以实现类SQL主要是依据它的框架原理：元数据metastore用于建立文件、文件夹与表、数据的映射，内部的编译器和解析器可以将HQL语句转换为底层的MR程序