正文 

当前位置： 首页 > 程序 > java教程

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

GaryLea GaryLea     2023-03-21     431

关键词：

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

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程序

打怪升级之小白的大数据之旅(六十一)＜hive旅程第二站:hive安装＞(代码片段)

打怪升级之小白的大数据之旅(六十一)Hive旅程第二站:Hive安装上次回顾上一章我们学习了Hive的概念以及框架原理，本章节是对Hive的安装进行分享，因为它有些需要自己配置的点，所以我单独开了一个章节Hive安装前期... 查看详情

打怪升级之小白的大数据之旅(六十九)＜hive旅程第十站:hive的优化＞(代码片段)

打怪升级之小白的大数据之旅(六十九)Hive旅程第十站:Hive的优化上次回顾上一章介绍了Hive的压缩与存储格式，本章节是Hive的一起其他优化方法Fetch抓取Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算从hive... 查看详情

打怪升级之小白的大数据之旅(六十五)＜hive旅程第六站:hive的查询＞(代码片段)

打怪升级之小白的大数据之旅(六十五)Hive旅程第六站:Hive的查询上次回顾经过前面的学习，我们已经可以初步使用Hive对数据的一些简单操作了，本章节是Hive的一个重点内容–查询查询HQL和我们前面学习的Mysql语法是一样... 查看详情

打怪升级之小白的大数据之旅(六十八)＜hive旅程第九站:hive的压缩与存储＞(代码片段)

打怪升级之小白的大数据之旅(六十八)Hive旅程第九站:Hive的压缩与存储上次回顾上一章，我们学习完了hive的函数相关操作，到此，我们hive的大的知识点就全部介绍完毕了，当然了，还有一些细节我没有讲到... 查看详情

打怪升级之小白的大数据之旅(六十三)＜hive旅程第四站:ddl操作＞(代码片段)

打怪升级之小白的大数据之旅(六十三)Hive旅程第四站:DDL操作上次回顾上一章，我们学习了Hive的数据类型以及访问方式，本章节我们对数据库与数据表的操作进行学习DDL操作数据库操作的CURD数据库的操作和mysql相同，... 查看详情

打怪升级之小白的大数据之旅(六十四)＜hive旅程第五站:dml基本操作＞(代码片段)

打怪升级之小白的大数据之旅(六十四)Hive旅程第五站:DML基本操作上次回顾上一章，我们学习了Hive的DDL操作，学会如何操作数据库、数据表后，本章我们就要开始学习如何将数据导入到表中，如何将数据从表中导... 查看详情

打怪升级之小白的大数据之旅(六十六)＜hive旅程第七站:hive的分区表与分桶表＞(代码片段)

打怪升级之小白的大数据之旅(六十六)Hive旅程第七站:Hive的分区表与分桶表上次回顾上一章，我们学习了Hive的查询相关语法，本章节我们学习一下分区表与分桶表分区表通过前面Hive的学习，我们知道，表在HDFS中... 查看详情

打怪升级之小白的大数据之旅(七十二)＜flume进阶＞(代码片段)

打怪升级之小白的大数据之旅(七十二)Flume进阶上次回顾上一章对Flume的基础知识点进行了分享，有了上一章的铺垫，本章就深入学习一下Flume的进阶知识点Flume的使用很简单，主要就是写配置文件，至于具体怎么配... 查看详情

打怪升级之小白的大数据之旅(七十三)＜flume高级＞(代码片段)

打怪升级之小白的大数据之旅(七十三)Flume高级上次回顾上一章介绍了Flume的内部原理，本章就Flume的扩展知识进行讲解，本章的重点就是了解并学会使用Flume的自定义组件自定义组件在上一章介绍了内部原理，所以下... 查看详情

打怪升级之小白的大数据之旅(七十四)＜初识kafka＞(代码片段)

打怪升级之小白的大数据之旅(七十四)初识Kafka引言学完Flume之后，接下来将为大家带来Kafka相关的知识点，在工作中，Kafka和Flume经常会搭配使用，那么Kafka究竟是什么呢？让我们开始今天的内容吧Kafka地图惯例&... 查看详情

打怪升级之小白的大数据之旅(六十)＜hive旅程中的始发站＞(代码片段)

打怪升级之小白的大数据之旅(六十)Hive旅程中的始发站引言经过了前面Hadoop、MR、Java、MySQL以及Linux的洗礼，接下来我们就要进入到大数据中特别重要的一个知识点学习–Hive,Hive是我们大数据日常工作中必不可少的一个技能&#x... 查看详情

打怪升级之小白的大数据之旅(七十一)＜hadoop生态：初识flume＞(代码片段)

打怪升级之小白的大数据之旅(七十一)Hadoop生态：初识Flume上次回顾上一章，我们学习完了hive的内容，本章开始是Hadoop中经常使用的另外一个框架Flume初识Flume下面这个是flume的标志flume的中文是水槽，但我觉得将它... 查看详情

打怪升级之小白的大数据之旅(七十二)＜flume进阶＞(代码片段)

打怪升级之小白的大数据之旅(七十二)Flume进阶上次回顾上一章对Flume的基础知识点进行了分享，有了上一章的铺垫，本章就深入学习一下Flume的进阶知识点Flume的使用很简单，主要就是写配置文件，至于具体怎么配... 查看详情

打怪升级之小白的大数据之旅(七十三)＜flume高级＞(代码片段)

打怪升级之小白的大数据之旅(七十三)Flume高级上次回顾上一章介绍了Flume的内部原理，本章就Flume的扩展知识进行讲解，本章的重点就是了解并学会使用Flume的自定义组件自定义组件在上一章介绍了内部原理，所以下... 查看详情

打怪升级之小白的大数据之旅(七十四)＜初识kafka＞(代码片段)

打怪升级之小白的大数据之旅(七十四)初识Kafka引言学完Flume之后，接下来将为大家带来Kafka相关的知识点，在工作中，Kafka和Flume经常会搭配使用，那么Kafka究竟是什么呢？让我们开始今天的内容吧Kafka地图惯例&... 查看详情

flask连接数据库打怪升级之旅

...一个学习经验，也就是我们今天分享的连接数据库部分的打怪升级之旅。希望可以为大家在学习Python的路上提供一些参考。初级阶段首先安装Mysql扩展包650)this.width=650; 查看详情

flask连接数据库打怪升级之旅

...、初级阶段 1.Mysql扩展包 2.建立数据库连接3.开启打怪升级之路 在日常开发中，连 查看详情

03注解是什么？——《android打怪升级之旅》(代码片段)

感谢大家和我一起，在Android世界打怪升级！注解非常的简单，但又大量的出现在源码中。希望通过该文章，能让大家看到注解不打怵，明白如何自定义注解，以及注解的作用，一眼就能粗略的理解该注... 查看详情