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

青旅半醒 2022-09-14 11:23 147阅读 0赞

### 大数据之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）视频表 ##

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5Z2P6K6y5b6u5pyN5Yqh_size_20_color_FFFFFF_t_70_g_se_x_16]

## 2）用户表 ##

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5Z2P6K6y5b6u5pyN5Yqh_size_20_color_FFFFFF_t_70_g_se_x_16 1]

# 三、准备表 #

## 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<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;

（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<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");

（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

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5Z2P6K6y5b6u5pyN5Yqh_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/248e0e5bcfb54edfa90a96e809d3ab61.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5bCP5Z2P6K6y5b6u5pyN5Yqh_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/5aaae8f5aa7245c5b846987c1d93b4e9.png