Hive实战练习（包含数据集）

数据集直链下载（100MB）： https://www.123pan.com/s/T1n0Vv-mTc3d

1.0数据说明

2.0建表

1.创建数据库

create database db_exercise;
1

2.切换到数据库

use db_exercise;
1

3.创建原始数据表：gulivideo，gulivideo_user

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 "&";
1
2
3
4
5
6
7
8
9
10
11
12
13

create table gulivideo_user(
 uploader string,
 videos int,
 friends int)
row format delimited
fields terminated by "\t";
1
2
3
4
5
6

4.上传数据到hdfs

hdfs dfs -put data/user/user.txt   /user/hive/warehouse/db_exercise.db/gulivideo_ori
hdfs dfs -put data/video/*   /user/hive/warehouse/db_exercise.db/gulivideo_ori
1
2

3.0实战练习

3.1统计视频观看数 Top10

select videoid,views
from gulivideo_ori
order by views desc
limit 10;
1
2
3
4

3.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_ori lateral view explode(category)  gulivideo_orc_tmp AS category_name
    )t1
group by t1.category_name
order by hot desc
limit 10;
1
2
3
4
5
6
7
8

3.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_ori
             order by views desc
             limit 20
             )t1
        lateral VIEW explode(t1.category) t1_tmp AS category_name
        )t2
GROUP BY t2.category_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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

（1）要想统计 Music 类别中的视频热度 Top10，需要先找到 Music 类别，那么就需要将category 展开，所以可以创建一张表用于存放 categoryId 展开的数据。

（2）向 category 展开的表中插入数据。

（3）统计对应类别（Music）中的视频热度。

SELECT
 t1.videoid,
 t1.views,
 t1.category_name
FROM
    (
    SELECT
     videoId,
     views,
     category_name
    FROM gulivideo_ori
    lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
    )t1
WHERE
 t1.category_name = "Music"
ORDER BY
 t1.views
DESC
LIMIT 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

3.5统计每个类别视频观看数 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_ori
        lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
        )t1
    )t2
WHERE t2.rk <=10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

SELECT
 t2.videoid,
 t2.views,
 t2.uploader
FROM
    (
        SELECT
         uploader,
         videos
        FROM gulivideo_user
        ORDER BY videos DESC
        LIMIT 10
    ) t1
JOIN gulivideo_ori t2
ON t1.uploader = t2.uploader
ORDER BY t2.views DESC
LIMIT 20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17