大数据之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<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