13：Hive从0到1系列学习：几个Hive小案例，连接Hive知识点，加深巩固

矫情吗；* 2023-10-04 17:22 5阅读 0赞

#### 内容目录 ####

*   *  Hive实战小项目
     *   *  1、数据准备
         *  2、业务分析
         *   *  ①统计视频观看数Top10
             *  ②统计视频类别热度Top10
             *  ③统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
             *  ④统计视频观看数Top50所关联视频的所属类别排序
             *  ⑤统计类别视频观看数Top10
             *  ⑥统计每个类别视频观看数Top10
             *  ⑦统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频

### Hive实战小项目 ###

需求分析：

统计硅谷影音视频网站的常规指标，各种TopN指标：

– 统计视频观看数Top10

– 统计视频类别热度Top10

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

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

– 统计每个类别中的视频热度Top10,以Music为例

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

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

数据结构：

1、视频表

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

2、用户表

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>备注</th> 
   <th>字段类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>uploader</td> 
   <td>上传者用户名</td> 
   <td>string</td> 
  </tr> 
  <tr> 
   <td>videos</td> 
   <td>上传视频数</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>friends</td> 
   <td>朋友数量</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

#### 1、数据准备 ####

①创建原始表

视频表

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;

用户表

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

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

视频表

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

用户表

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

③向ori表中插入数据

load data local inpath "/opt/module/hive/datas/video" into table gulivideo_ori;
    load data local inpath "/opt/module/hive/datas/guliVideo/user/user.txt" into table gulivideo_user_ori;

④向orc表中插入数据

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

#### 2、业务分析 ####

##### ①统计视频观看数Top10 #####

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

select
        videoId,
        `views`
    from gulivideo_orc
    order by `views` desc 
    limit 10;

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

##### ②统计视频类别热度Top10 #####

思路：就是统计出每个视频类别中包含的视频数，将数量最多的十个输出

因为一个视频对应一个或者多个类别，所以还要来一个列转行

1）首先进行列转行

select videoId, category_col from gulivideo_ori
    lateral view explode(category) t as category_col
    limit 10;

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

2）然后按照类别进行分组，然后计算count()即可，排序输出前10

select 
        tmp01.category_col,
        count(tmp01.videoId) nums
    from
    (
    select videoId, category_col from gulivideo_ori
    lateral view explode(category) t as category_col
    ) tmp01
    group by tmp01.category_col
    order by nums desc
    limit 10;

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

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

1）首先，视频观看数最高的20个视频所属的类别

select 
        videoId,
        views,
        category
    from gulivideo_ori
    order by views desc
    limit 20;

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

2）通过上述的结果，将其列转行

select
    category_col,
    count(tmp01.videoId) nums
    from(
        select 
        videoId,
        views,
        category
        from gulivideo_ori
        order by views desc
        limit 20
    ) tmp01
    lateral view
    explode(tmp01.category) t as category_col
    group by category_col
    order by nums
    limit 10;

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

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

1）首先查看视频观看数Top50的视频

select
        videoId,
        relatedId,
        `views`
    from gulivideo_orc
    order by `views` desc
    limit 50

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

2）将查询结果的列转为行

select
    	realte_id
    from (
    select
        videoId,
        relatedId,
        `views`
    from gulivideo_orc
    order by `views` desc
    limit 50
    ) t1
    lateral view
    explode(t1.relatedId) tmp as realte_id;

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

3）上述就得到了所有的视频类型结果，然后找到这些视频的关联类别

select
        t2.realte_id,
        t2.cate
    from (
        select
        realte_id,
        t1.category cate
        from (
            select
                videoId,
                relatedId,
                category,
                `views`
            from gulivideo_orc
            order by `views` desc
            limit 50
        ) t1
        lateral view
        explode(t1.relatedId) tmp as realte_id
    ) t2 join gulivideo_orc g on t2.realte_id = g.videoId;

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

4）找到了视频对应的类型，还要将相应的视频类型列转行

select
        t3.realte_id,
        category_name
    from (
    select
        t2.realte_id,
        g.category
    from (
    select
    	realte_id
    from (
    select
        videoId,
        relatedId,
        `views`
    from gulivideo_orc
    order by `views` desc
    limit 50
    ) t1
    lateral view
    explode(t1.relatedId) tmp as realte_id
    ) t2 join gulivideo_orc g on t2.realte_id = g.videoId
    ) t3
    lateral view
    explode(t3.category) tmp as category_name;

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

5）按照类别名进行分组，统计每组的数量，进行排序

select 
        t5.category_name,
        t5.num,
        rank() over(order by t5.num desc ) rk
    from (
         select
             t4.category_name,
             count(t4.realte_id) num
         from (
                  select
                      t3.realte_id,
                      category_name
                  from (
                           select
                               t2.realte_id,
                               g.category
                           from (
                                    select
                                        realte_id
                                    from (
                                             select
                                                 videoId,
                                                 relatedId,
                                                 `views`
                                             from gulivideo_orc
                                             order by `views` desc
                                             limit 50
                                         ) t1
                                             lateral view
                                                 explode(t1.relatedId) tmp as realte_id
                                ) t2 join gulivideo_orc g on t2.realte_id = g.videoId
                       ) t3
                           lateral view
                               explode(t3.category) tmp as category_name
              ) t4
         group by t4.category_name        
    ) t5 ;

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

##### ⑤统计类别视频观看数Top10 #####

1）查找出视频的观看数以及所属于的类别

select 
    	videoId, 
    	views, 
    	category 
    from gulivideo_orc;

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

2）将类别进行分行

select 
    	videoId, 
    	views, 
    	category_name 
    from gulivideo_orc
    lateral view 
    explode(category) tmp as category_name;

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

3）按照类别分组，求和观看数

select 
    	sum(t1.views)
        sum_views, 
        t1.category_name 
    from (
        select 
        	views, 
        	category_name
        from gulivideo_orc 
        lateral view explode(category) tmp as category_name) t1
    group by t1.category_name limit 10;

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

4）排序得到Top10

select 
        t2.sum_views, 
        t2.category_name
    from(
    	select 
        	sum(t1.views) sum_views,
        	t1.category_name 
       	from (
            select 
            	views, 
            	category_name 
            from gulivideo_orc 
            lateral view explode(category) tmp as category_name) t1
    	group by t1.category_name
        ) t2
    order by t2.sum_views desc limit 10;

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

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

注意，这个和上题的区别是统计每个类别中视频观看数的Top10

使用窗口函数，按照视频类别进行开窗，每个分区内按照视频播放量进行排序，取Top10

1）将视频类型的列转为行

select 
    	views, 
    	category_name
    from gulivideo_orc 
    lateral view explode(category) tmp as category_name;

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

2）开窗，按照视频类别进行分区，视频播放量进行排序，取Top10

select 
    	t2.views,
    	t2.category_name,
    	t2.rk
    from( 
        select 
            t1.views,
            t1.category_name,
            rank() over(partition by t1.category_name order by t1.views desc) rk
        from( 
            select
                views,
                category_name
            from gulivideo_orc
            lateral view explode(category) tmp as category_name
            ) t1
        ) t2
    where rk<=10;

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

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

1）首先需要查找上传视频最多的用户Top10

select 
    	uploader
    from gulivideo_user_orc
    order by videos desc
    limit 10;

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

2）查找出其上传的视频

select
        t1.uploader,
        g.videoId,
        g.`views`
    from (
        select
        	uploader
        from gulivideo_user_orc
        order by videos desc
        limit 10
        ) t1 
        join gulivideo_orc g on t1.uploader = g.uploader

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

3）开窗按照uploader分组，观看量进行排序

select
        t3.uploader,
        t3.videoId,
        t3.views,
        t3.rk
    from(
        select
            t2.uploader,
            t2.videoId,
            t2.views,
            rank() over(partition by t2.uploader order by t2.views desc) rk
        from(
            select
                t1.uploader,
                g.videoId,
                g.`views`
            from (
                select
                	uploader
                from gulivideo_user_orc
                order by videos desc
                limit 10
            ) t1 
            join gulivideo_orc g on t1.uploader = g.uploader
        ) t2
    ) t3
    where rk<=20;

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

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/7b3f00b1d78743c3b853b2f7fdcd9a3b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/92cfae2fb2fd48ee8cd03d8f9544e775.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/e50d3361bace444299b90c82907900f6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/8ca09bc48e194a5a850d4a25b4f43bf5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/0587e40c989e4e6ba31d0aa93d4e0152.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/5a55934fdd2445a5b26b356914a33087.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 6]: https://img-blog.csdnimg.cn/0d92aa68adbd44efaf44d50e8e08e785.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 7]: https://img-blog.csdnimg.cn/5882db2d58614429af246893781be8a8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 8]: https://img-blog.csdnimg.cn/35130d4e833740e88e6e7c726aa7b0ff.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 9]: https://img-blog.csdnimg.cn/0d6b225cbafa4959b493ed6992019aa1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 10]: https://img-blog.csdnimg.cn/a3bbcc7e2c2c439ca2df3fe19ba05bd8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 11]: https://img-blog.csdnimg.cn/0132bd9a840a420d9c03d68df8389c71.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 12]: https://img-blog.csdnimg.cn/22082a64e98e4f4cb0abfd299ecd18bf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 13]: https://img-blog.csdnimg.cn/7fa0b214abf8457cbd1cb110995e6ae4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 14]: https://img-blog.csdnimg.cn/1f96842b278c4c1593579509b39c4d9c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 15]: https://img-blog.csdnimg.cn/5d406f070be24c2ea4af28dfa199f3c0.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 16]: https://img-blog.csdnimg.cn/5a41ee3a83e147358b9770690ecf835b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 17]: https://img-blog.csdnimg.cn/37e3f9b53a014859b9b9503626cb9ad2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA54mn56CB5paH_size_20_color_FFFFFF_t_70_g_se_x_16 18]: https://img-blog.csdnimg.cn/14555af63bc145c5a3de30aeac57153b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54mn56CB5paH,size_20,color_FFFFFF,t_70,g_se,x_16