Hive学习视频心得（六）Hive实战之某视频网站分析

最新推荐文章于 2022-04-27 14:36:13 发布

友培

最新推荐文章于 2022-04-27 14:36:13 发布

阅读量1.9k

点赞数 1

分类专栏：大数据——数仓工具Hive 文章标签：大数据 hive hadoop 数据仓库 etl

本文链接：https://blog.csdn.net/xiaoyoupei/article/details/112854880

版权

大数据——数仓工具Hive 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

Hive实战之某视频网站分析

Hive实战之某视频网站分析

一、项目需求简介

统计某视频网站的常规指标，各种TopN指标：

–统计视频观看数Top10

–统计视频类别热度Top10

–统计视频观看数Top20所属类别

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

–统计每个类别中的视频热度Top10

–统计每个类别中视频流量Top10

–统计上传视频最多的用户Top10以及他们上传的视频

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

二、项目具体步骤

1）、数据结构

①、视频表

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

②、用户表

字段	备注	字段类型
uploader	上传者用户名	string
videos	上传视频数	int
friends	朋友数量	int

2）、项目准备

将数据存放在虚拟机的目录下（假设该数据已经拉取好，并且存放在文本文件内），并启动hadoop集群、启动hiveserver2后台服务

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QjvHCssw-1611070048907)(C:\Users\xiaoyoupei\AppData\Roaming\Typora\typora-user-images\image-20210118210125248.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e8NVNX54-1611070048909)(C:\Users\xiaoyoupei\AppData\Roaming\Typora\typora-user-images\image-20210118210306370.png)]$

3）、数据准备

在hdfs上创建project目录，并在该目录下创建user、video目录分别存放事先准备好的user数据和video的数据

#创建project目录
hdfs dfs -mkdir /project

#创建user、video目录
hdfs dfs -mkdir /project/user
hdfs dfs -mkdir /project/video
    
#上传数据，数据已经放在了虚拟机目录下
hdfs dfs -put user.txt  /project/user
hdfs dfs -put ./*  /project/video

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AwZPHIT8-1611070048911)(C:\Users\xiaoyoupei\AppData\Roaming\Typora\typora-user-images\image-20210118205730409.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ot3pWM4m-1611070048913)(C:\Users\xiaoyoupei\AppData\Roaming\Typora\typora-user-images\image-20210118205748507.png)]$

4）、数据处理（ETL）

我们观察数据可得：视频可以有多个所属分类，每个所属分类用&符号分割，且分割的两边有空格字符，同时相关视频也是可以有多个元素，多个相关视频又用“\t”进行分割。为了分析数据时方便对存在多个子元素的数据进行操作，我们首先进行数据重组清洗操作。去掉视频类别的&符号两边的空格，再用&将相关视频联合，具体代码如下：

①、ETLMapper.java

/**
 * ETL处理Mapper
 */
public class ETLMapper extends Mapper<LongWritable, Text, Text, NullWritable> {

    private Counter pass;
    private Counter fail;

    private StringBuilder sb = new StringBuilder();
    private Text result = new Text();

    /**
     * 计数器
     *
     * @param context
     * @throws IOException
     * @throws InterruptedException
     */
    @Override
    protected void setup(Context context) throws IOException, InterruptedException {
        pass = context.getCounter("ETL", "Pass");
        fail = context.getCounter("ETL", "fail");
    }

    /**
     * 将一行日志进行处理，第四个字段中的空格去掉，将最后相关视频的分隔符改成'&'
     *
     * @param key
     * @param value
     * @param context
     * @throws IOException
     * @throws InterruptedException
     */
    @Override
    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
        //一行数据
        String line = value.toString();

        //将数据切分
        String[] fields = line.split("\t");

        //判断字数个数够不够(判断related ids)
        if (fields.length >= 9) {
            //去掉第四个字段的空格(将category去掉空格)
            fields[3] = fields[3].replace(" ", "");

            //拼接字段成一行，并注意最后几个字段的分隔符
            //拼接之间把旧的字符串清零
            sb.setLength(0);
            for (int i = 0; i < fields.length; i++) {
                if (i == fields.length - 1) {
                    //如果当前正在拼接的字段是这一行的最后的一个字段
                    sb.append(fields[i]);
                } else if (i <= 8) {
                    //如果拼接的是前九个字段，分隔符\t
                    sb.append(fields[i]).append("\t");
                } else {
                    //剩下的字段
                    sb.append(fields[i]).append("&");
                }
            }
            result.set(sb.toString());

            context.write(result, NullWritable.get());

            pass.increment(1);
        } else {
            //数据丢弃
            fail.increment(1);
        }
    }
}

②、ETLDriver.java

public class ETLDriver {
    public static void main(String[] args) throws IOException, ClassNotFoundException, InterruptedException {
        Job job = Job.getInstance(new Configuration());

        job.setJarByClass(ETLDriver.class);
        job.setMapperClass(ETLMapper.class);
        job.setNumReduceTasks(0);

        job.setMapOutputKeyClass(Text.class);
        job.setMapOutputValueClass(NullWritable.class);

        FileInputFormat.setInputPaths(job, new Path(args[0]));
        FileOutputFormat.setOutputPath(job, new Path(args[1]));

        boolean b = job.waitForCompletion(true);
        System.exit(b ? 0 : 1);
    }
}

③、打包提交集群执行

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-axbXd5PJ-1611070048914)(C:\Users\xiaoyoupei\AppData\Roaming\Typora\typora-user-images\image-20210118223508203.png)]$

#提交集群执行
yarn jar hive-1.0-SNAPSHOT.jar project.ETL.ETLDriver /project/video /project/video_ETL

补充：hive-1.0-SNAPSHOT.jar是打包名，project.ETL.ETLDriver是代码的reference，/project/video是hdfs上存储数据的路径，/project/video_ETL是设置输出结果存储的位置。

④、ETL执行完成后截图

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rSaJmE4G-1611070048915)(C:\Users\xiaoyoupei\AppData\Roaming\Typora\typora-user-images\image-20210118223759279.png)]$

5）、Hive端导入数据

①、项目创库

--创项目库
create database project;

--使用项目库
use project;

②、项目建表

a、外部表

--video表
create external table video_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 "&"
location '/project/video_ETL';

--user表
create external table user_ori(
    uploader string,
    videos int,
    friends int)
row format delimited fields terminated by "\t" 
location '/project/user';

b、内部表

--video_orc表
create table video_orc(
    videoId string, 
    uploader string, 
    age int, 
    category array<string>, 
    length int, 
    views int, 
    rate float, 
    ratings int, 
    comments int,
    relatedId array<string>);

--user_orc表
create table user_orc(
    uploader string,
    videos int,
    friends int);

c、外部向内部数据导入

--从外部表中插入数据
insert into table video_orc select * from video_ori;
insert into table user_orc select * from user_ori;

三、处理项目需求

1）、统计视频观看数Top10

①、sql

SELECT
    videoid,
    views
FROM
    video_orc
ORDER BY
    views DESC
LIMIT 10;

②、结果

+--------------+-----------+--+
|   videoid    |   views   |
+--------------+-----------+--+
| dMH0bHeiRNg  | 42513417  |
| 0XxI-hvPRRA  | 20282464  |
| 1dmVU08zVpA  | 16087899  |
| RB-wUgnyGv0  | 15712924  |
| QjA5faZF1A8  | 15256922  |
| -_CSo1gOd48  | 13199833  |
| 49IDp76kjPw  | 11970018  |
| tYnn51C3X_w  | 11823701  |
| pv5zWaTEVkI  | 11672017  |
| D2kJZOfq7zk  | 11184051  |
+--------------+-----------+--+

2）、统计视频类别热度Top10

①、定义视频类别热度（假设按照类别下视频的个数来决定）

②、将类别炸开

select
    videoid,
    cate
from 
    video_orc LATERAL view explode(category) tb1 as cate;

③、统计各类别有多少的视频并排序

select 
    cate,
    count(videoid) n
from
   (select
        videoid,
        cate
    from 
        video_orc LATERAL view explode(category) tb1 as cate) t1
group by
    cate
order by
    n desc
limit 10;

④、结果

+----------------+---------+--+
|      cate      |    n    |
+----------------+---------+--+
| Music          | 179049  |
| Entertainment  | 127674  |
| Comedy         | 87818   |
| Animation      | 73293   |
| Film           | 73293   |
| Sports         | 67329   |
| Gadgets        | 59817   |
| Games          | 59817   |
| Blogs          | 48890   |
| People         | 48890   |
+----------------+---------+--+

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

①、统计前20的视频和其类别

select
    videoid,
	views,
	category
from 
    video_orc
order by
    views
limit 20;

②、打散类别

select
    videoid,
    cate
from
    t1
Lateral view explode(category) tb1 as cate;

③、按照类别统计个数

select
    cate
    count(videoid) n
from
    t2
group by
    cate
order by
    n desc;

④、整合

select
    cate,
    count(videoid) n
from
    (select
         videoid,
         cate
     from
         (select
             videoid,
             views,
             category
          from 
             video_orc
          order by
             views
          limit 20
         ) t1
     Lateral view explode(category) tb1 as cate) t2
group by
    cate
order by
    n desc;

⑤、结果

+----------------+----+--+
|      cate      | n  |
+----------------+----+--+
| Entertainment  | 8  |
| People         | 4  |
| Blogs          | 4  |
| Sports         | 3  |
| Music          | 3  |
| Games          | 1  |
| Gadgets        | 1  |
| Comedy         | 1  |
+----------------+----+--+

4）、统计视频观看数Top50所关联视频的所属类别排序

①、统计观看数前50的视频的关联视频

select
    videoid,
	views,
	relatedid
from
    video_orc
order by
    views
limit 50;

②、炸开关联视频

select
    explode(relatedid) videoid
from
    t1;

③、和原表join获取关联视频类别

select
    distinct t2.videoid,
	v.category
from
    t2
join
    video_orc v
on  t2.videoid=v.videoid;

④、炸开类别

select
    explode(category) cate
from 
    t3;

⑤、和类别热度表join，排序

--t5
select 
    cate,
    count(videoid) n
from
   (select
        videoid,
        cate
    from 
        video_orc LATERAL view explode(category) tb1 as cate) t5
group by
    cate;

select
    distinct t4.cate,
    t5.n
from
    t4
join
    t5
on
    t4.cate=t5.cate
order by
    t5.n desc;

⑥、整合

SELECT DISTINCT
    t4.cate,
    t5.n 
FROM
    (
    SELECT
        explode ( category ) cate 
    FROM
        (
        SELECT DISTINCT
            t2.videoid,
            v.category 
        FROM
            ( SELECT explode ( relatedid ) videoid FROM ( SELECT videoid, views, relatedid FROM video_orc ORDER BY views LIMIT 50 ) t1 ) t2
            JOIN video_orc v ON t2.videoid = v.videoid 
        ) t3 
    ) t4
    JOIN (
    SELECT
        cate,
        count( videoid ) n 
    FROM
        ( SELECT videoid, cate FROM video_orc LATERAL VIEW explode ( category ) tb1 AS cate ) t5 
    GROUP BY
        cate 
    ) t5 ON t4.cate = t5.cate 
ORDER BY
    t5.n DESC;

⑦、结果

+----------------+---------+--+
|    t4.cate     |  t5.n   |
+----------------+---------+--+
| Music          | 179049  |
| Entertainment  | 127674  |
| Comedy         | 87818   |
| Animation      | 73293   |
| Film           | 73293   |
| Sports         | 67329   |
| Gadgets        | 59817   |
| Games          | 59817   |
| Blogs          | 48890   |
| People         | 48890   |
| News           | 35925   |
| Politics       | 35925   |
| Howto          | 18257   |
| DIY            | 18257   |
| Places         | 14675   |
| Travel         | 14675   |
| Vehicles       | 14284   |
| Autos          | 14284   |
| Animals        | 10496   |
| Pets           | 10496   |
| UNA            | 6062    |
+----------------+---------+--+

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

①、把视频表的类别炸开生成中间表格

create table video_category
as select
    videoid,
    uploader,
    age,
    cate,
    length,
    views,
    rate,
    ratings,
    comments,
    relatedid,
from
    video_orc
Lateral view explode(category) tb1 as cate;

②、从video_category直接查询Music类的观看次数前10视频

select
    videoid,
    views
from
    video_category
where
    cate="Music"
order by 
    views desc
limit 10;

③、结果

+--------------+-----------+--+
|   videoid    |   views   |
+--------------+-----------+--+
| QjA5faZF1A8  | 15256922  |
| tYnn51C3X_w  | 11823701  |
| pv5zWaTEVkI  | 11672017  |
| 8bbTtPL1jRs  | 9579911   |
| UMf40daefsI  | 7533070   |
| -xEzGIuY7kw  | 6946033   |
| d6C0bNDqf3Y  | 6935578   |
| HSoVKUVOnfQ  | 6193057   |
| 3URfWTEPmtE  | 5581171   |
| thtmaZnxk_0  | 5142238   |
+--------------+-----------+--+

6）、统计每个类别中视频流量Top10，以Music为例

①、从video_category直接查询Music类的流量前10视频

select
    videoid,
    ratings
from
    video_category
where
    cate="Music"
order by 
    ratings desc
limit 10;

②、结果

+--------------+----------+--+
|   videoid    | ratings  |
+--------------+----------+--+
| QjA5faZF1A8  | 120506   |
| pv5zWaTEVkI  | 42386    |
| UMf40daefsI  | 31886    |
| tYnn51C3X_w  | 29479    |
| 59ZX5qdIEB0  | 21481    |
| FLn45-7Pn2Y  | 21249    |
| -xEzGIuY7kw  | 20828    |
| HSoVKUVOnfQ  | 19803    |
| ARHyRI9_NB4  | 19243    |
| gg5_mlQOsUQ  | 19190    |
+--------------+----------+--+

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

答案一：前十用户每人前20

①、统计上传视频最多的用户Top10

select
    uploader,
	videos
from
    user_orc
order by 
    videos desc
limit 10;

②、和video_orc联立，找出这些用户上传的视频以及排名

select
    t1.uploader,
    v.videoid,
    rank() over(partition by t1.uploader order by v.views desc) hot
from
    t1
join
    video_orc v
on v.uploader=t1.uploader;

③、求前20

select
    t2.uploader,
	t2.videoid,
	t2.hot
from 
    t2
where hot<=20;

④、整合

SELECT
    t2.uploader,
    t2.videoid,
    t2.hot 
FROM
    (
    SELECT
        t1.uploader,
        v.videoid,
        rank() over ( PARTITION BY t1.uploader ORDER BY v.views DESC ) hot 
    FROM
        ( SELECT uploader, videos FROM user_orc ORDER BY videos DESC LIMIT 10 ) t1
        JOIN video_orc v ON v.uploader = t1.uploader 
    ) t2 
WHERE
    hot <= 20;

⑤、结果

+----------------+--------------+---------+--+
|  t2.uploader   |  t2.videoid  | t2.hot  |
+----------------+--------------+---------+--+
| Ruchaneewan    | 5_T5Inddsuo  | 1       |
| Ruchaneewan    | wje4lUtbYNU  | 2       |
| Ruchaneewan    | i8rLbOUhAlM  | 3       |
| Ruchaneewan    | OwnEtde9_Co  | 4       |
| Ruchaneewan    | 5Zf0lbAdJP0  | 5       |
| Ruchaneewan    | wenI5MrYT20  | 6       |
| Ruchaneewan    | Iq4e3SopjxQ  | 7       |
| Ruchaneewan    | 3hzOiFP-5so  | 7       |
| Ruchaneewan    | JgyOlXjjuw0  | 9       |
| Ruchaneewan    | fGBVShTsuyo  | 10      |
| Ruchaneewan    | O3aoL70DlVc  | 11      |
| Ruchaneewan    | q4y2ZS5OQ88  | 12      |
| Ruchaneewan    | lyUJB2eMVVg  | 13      |
| Ruchaneewan    | _RF_3VhaQpw  | 14      |
| Ruchaneewan    | DDl2cjI-aJs  | 15      |
| Ruchaneewan    | xbYyjUdhtJw  | 16      |
| Ruchaneewan    | 4dkKeIUkN7E  | 17      |
| Ruchaneewan    | qCfuQA6N4K0  | 18      |
| Ruchaneewan    | TmYbGQaRcNM  | 19      |
| Ruchaneewan    | dOlfPsFSjw0  | 20      |
| expertvillage  | -IxHBW0YpZw  | 1       |
| expertvillage  | BU-fT5XI_8I  | 2       |
| expertvillage  | ADOcaBYbMl0  | 3       |
| expertvillage  | yAqsULIDJFE  | 4       |
| expertvillage  | vcm-t0TJXNg  | 5       |
| expertvillage  | 0KYGFawp14c  | 6       |
| expertvillage  | j4DpuPvMLF4  | 7       |
| expertvillage  | Msu4lZb2oeQ  | 8       |
| expertvillage  | ZHZVj44rpjE  | 9       |
| expertvillage  | foATQY3wovI  | 10      |
| expertvillage  | -UnQ8rcBOQs  | 11      |
| expertvillage  | crtNd46CDks  | 12      |
| expertvillage  | D1leA0JKHhE  | 13      |
| expertvillage  | NJu2oG1Wm98  | 14      |
| expertvillage  | CapbXdyv4j4  | 15      |
| expertvillage  | epr5erraEp4  | 16      |
| expertvillage  | IyQoDgaLM7U  | 17      |
| expertvillage  | tbZibBnusLQ  | 18      |
| expertvillage  | _GnCHodc7mk  | 19      |
| expertvillage  | hvEYlSlRitU  | 20      |
+----------------+--------------+---------+--+

答案二：前十用户总榜前20

①、统计视频上传最多的用户Top10

SELECT
    uploader,
    videos
FROM
    user_orc
ORDER BY
    videos DESC
LIMIT 10;

②、观看数前20的视频

SELECT
    videoid,
    uploader,
    views
FROM
    video_orc
ORDER BY
    views DESC
LIMIT 20;

③、二表联列，求出匹配

SELECT
    t1.uploader,
    t2.videoid
FROM
    t1
LEFT JOIN t2 ON
    t1.uploader = t2.uploader;

④、整合

SELECT
    t1.uploader,
    t2.videoid 
FROM
    ( SELECT 
          uploader, videos 
      FROM 
          user_orc 
      ORDER BY 
          videos DESC 
      LIMIT 10 ) t1
    LEFT JOIN 
    ( SELECT 
          videoid, uploader, views 
      FROM 
          video_orc 
      ORDER BY 
          views DESC 
      LIMIT 20 ) t2 
ON t1.uploader = t2.uploader;

⑤、结果

+---------------------+-------------+--+
|     t1.uploader     | t2.videoid  |
+---------------------+-------------+--+
| expertvillage       | NULL        |
| TourFactory         | NULL        |
| myHotelVideo        | NULL        |
| AlexanderRodchenko  | NULL        |
| VHTStudios          | NULL        |
| ephemeral8          | NULL        |
| HSN                 | NULL        |
| rattanakorn         | NULL        |
| Ruchaneewan         | NULL        |
| futifu              | NULL        |
+---------------------+-------------+--+

8）、统计每个类别视频观看数Top10

①、从video_category表查出每个类别视频观看数排名

SELECT
    cate,
    videoid,
    views,
    RANK() OVER(PARTITION BY cate ORDER BY views DESC) hot
FROM
    video_category;

②、取每个类别的Top10

SELECT
    cate,
    videoid,
    views
FROM
    t1
WHERE
    hot <= 10;

③、整合

SELECT
    cate,
    videoid,
    views
FROM
    (SELECT
         cate,
         videoid,
         views,
    RANK() OVER(PARTITION BY cate 
    ORDER BY views DESC) hot
FROM
    video_category)t1
WHERE
    hot <= 10;

④、结果

+----------------+--------------+-----------+--+
|      cate      |   videoid    |   views   |
+----------------+--------------+-----------+--+
| Animation      | sdUUx5FdySs  | 5840839   |
| Animation      | 6B26asyGKDo  | 5147533   |
| Animation      | H20dhY01Xjk  | 3772116   |
| Animation      | 55YYaJIrmzo  | 3356163   |
| Animation      | JzqumbhfxRo  | 3230774   |
| Animation      | eAhfZUZiwSE  | 3114215   |
| Animation      | h7svw0m-wO0  | 2866490   |
| Animation      | tAq3hWBlalU  | 2830024   |
| Animation      | AJzU3NjDikY  | 2569611   |
| Animation      | ElrldD02if0  | 2337238   |
| Autos          | RjrEQaG5jPM  | 2803140   |
| Autos          | cv157ZIInUk  | 2773979   |
| Autos          | Gyg9U1YaVk8  | 1832224   |
| Autos          | 6GNB7xT3rNE  | 1412497   |
| Autos          | tth9krDtxII  | 1347317   |
| Autos          | 46LQd9dXFRU  | 1262173   |
| Autos          | pdiuDXwgrjQ  | 1013697   |
| Autos          | kY_cDpENQLE  | 956665    |
| Autos          | YtxfbxGz1u4  | 942604    |
| Autos          | aCamHfJwSGU  | 847442    |
| DIY            | hut3VRL5XRE  | 2684989   |
| DIY            | YYTpb-QXV0k  | 2492153   |
| DIY            | Pf3z935R37E  | 2096661   |
| DIY            | Yd99gyE4jCk  | 1918946   |
| DIY            | koQFjKwVFB0  | 1757071   |
| DIY            | f5Fg6KFcOsU  | 1751817   |
| DIY            | STQ3nhXuuEM  | 1713974   |
| DIY            | FtKuBKIaVvs  | 1520774   |
| DIY            | M0ODskdEPnQ  | 1503351   |
| DIY            | uFwCk4UPtlM  | 1500110   |
| Entertainment  | 1dmVU08zVpA  | 16087899  |
| Entertainment  | RB-wUgnyGv0  | 15712924  |
| Entertainment  | vr3x_RRJdd4  | 10786529  |
| Entertainment  | lsO6D1rwrKc  | 10334975  |
| Entertainment  | ixsZy2425eY  | 7456875   |
| Entertainment  | RUCZJVJ_M8o  | 6952767   |
| Entertainment  | tFXLbXyXy6M  | 5810013   |
| Entertainment  | 7uwCEnDgd5o  | 5280504   |
| Entertainment  | 2KrdBUFeFtY  | 4676195   |
| Entertainment  | vD4OnHCRd_4  | 4230610   |
| Film           | sdUUx5FdySs  | 5840839   |
| Film           | 6B26asyGKDo  | 5147533   |
| Film           | H20dhY01Xjk  | 3772116   |
| Film           | 55YYaJIrmzo  | 3356163   |
| Film           | JzqumbhfxRo  | 3230774   |
| Film           | eAhfZUZiwSE  | 3114215   |
| Film           | h7svw0m-wO0  | 2866490   |
| Film           | tAq3hWBlalU  | 2830024   |
| Film           | AJzU3NjDikY  | 2569611   |
| Film           | ElrldD02if0  | 2337238   |
| Pets           | 2GWPOPSXGYI  | 3660009   |
| Pets           | xmsV9R8FsDA  | 3164582   |
| Pets           | 12PsUW-8ge4  | 3133523   |
| Pets           | OeNggIGSKH8  | 2457750   |
| Pets           | WofFb_eOxxA  | 2075728   |
| Pets           | AgEmZ39EtFk  | 1999469   |
| Pets           | a-gW3RbJd8U  | 1836870   |
| Pets           | 8CL2hetqpfg  | 1646808   |
| Pets           | QmroaYVD_so  | 1645984   |
| Pets           | Sg9x5mUjbH8  | 1527238   |
| Places         | bNF_P281Uu4  | 5231539   |
| Places         | s5ipz_0uC_U  | 1198840   |
| Places         | 6jJW7aSNCzU  | 1143287   |
| Places         | dVRUBIyRAYk  | 1000309   |
| Places         | lqbt6X4ZgEI  | 921593    |
| Places         | RIH1I1doUI4  | 879577    |
| Places         | AlPqL7IUT6M  | 845180    |
| Places         | _5QUdvUhCZc  | 819974    |
| Places         | m9A_vxIOB-I  | 677876    |
| Places         | CL6f3Cyh85w  | 611786    |
| Travel         | bNF_P281Uu4  | 5231539   |
| Travel         | s5ipz_0uC_U  | 1198840   |
| Travel         | 6jJW7aSNCzU  | 1143287   |
| Travel         | dVRUBIyRAYk  | 1000309   |
| Travel         | lqbt6X4ZgEI  | 921593    |
| Travel         | RIH1I1doUI4  | 879577    |
| Travel         | AlPqL7IUT6M  | 845180    |
| Travel         | _5QUdvUhCZc  | 819974    |
| Travel         | m9A_vxIOB-I  | 677876    |
| Travel         | CL6f3Cyh85w  | 611786    |
| UNA            | aRNzWyD7C9o  | 8825788   |
| UNA            | jtExxsiLgPM  | 5320895   |
| UNA            | PxNNR4symuE  | 4033376   |
| UNA            | 8cjTSvvoddc  | 3486368   |
| UNA            | LIhbap3FlGc  | 2849832   |
| UNA            | lCSTULqmmYE  | 2179562   |
| UNA            | UyTxWvp8upM  | 2106933   |
| UNA            | y6oXEWowirI  | 1666084   |
| UNA            | _x2-AmY8FI8  | 1403113   |
| UNA            | ICoDFooBXpU  | 1376215   |
| Animals        | 2GWPOPSXGYI  | 3660009   |
| Animals        | xmsV9R8FsDA  | 3164582   |
| Animals        | 12PsUW-8ge4  | 3133523   |
| Animals        | OeNggIGSKH8  | 2457750   |
| Animals        | WofFb_eOxxA  | 2075728   |
| Animals        | AgEmZ39EtFk  | 1999469   |
| Animals        | a-gW3RbJd8U  | 1836870   |
| Animals        | 8CL2hetqpfg  | 1646808   |
| Animals        | QmroaYVD_so  | 1645984   |
| Animals        | Sg9x5mUjbH8  | 1527238   |
+----------------+--------------+-----------+--+
|      cate      |   videoid    |   views   |
+----------------+--------------+-----------+--+
| Blogs          | -_CSo1gOd48  | 13199833  |
| Blogs          | D2kJZOfq7zk  | 11184051  |
| Blogs          | pa_7P5AbUww  | 5705136   |
| Blogs          | f4B-r8KJhlE  | 4937616   |
| Blogs          | LB84A3zcmVo  | 4866739   |
| Blogs          | tXNquTYnyg0  | 3613323   |
| Blogs          | EYppbbbSxjc  | 2896562   |
| Blogs          | LH7vrLlDZ6U  | 2615359   |
| Blogs          | bTV85fQhj0E  | 2192656   |
| Blogs          | eVFF98kNg8Q  | 1813803   |
| Comedy         | dMH0bHeiRNg  | 42513417  |
| Comedy         | 0XxI-hvPRRA  | 20282464  |
| Comedy         | 49IDp76kjPw  | 11970018  |
| Comedy         | 5P6UU6m3cqk  | 10107491  |
| Comedy         | _BuRwH59oAo  | 9566609   |
| Comedy         | MNxwAU_xAMk  | 7066676   |
| Comedy         | pYak2F1hUYA  | 6322117   |
| Comedy         | h0zAlXr1UOs  | 5826923   |
| Comedy         | C8rjr4jmWd0  | 5587299   |
| Comedy         | R4cQ3BoHFas  | 5508079   |
| Gadgets        | pFlcqWQVVuU  | 3651600   |
| Gadgets        | bcu8ZdJ2dQo  | 2617568   |
| Gadgets        | -G7h626wJwM  | 2565170   |
| Gadgets        | oMaTZFCLbq0  | 2554620   |
| Gadgets        | GxSdKF5Fd38  | 2468395   |
| Gadgets        | z1lj87UyvfY  | 2373875   |
| Gadgets        | KhCmfX_PQ7E  | 1967929   |
| Gadgets        | 2SVMFCZgvNM  | 1813794   |
| Gadgets        | gPutYwiiE0o  | 1633482   |
| Gadgets        | 7wt5FiZQrgM  | 1399531   |
| Games          | pFlcqWQVVuU  | 3651600   |
| Games          | bcu8ZdJ2dQo  | 2617568   |
| Games          | -G7h626wJwM  | 2565170   |
| Games          | oMaTZFCLbq0  | 2554620   |
| Games          | GxSdKF5Fd38  | 2468395   |
| Games          | z1lj87UyvfY  | 2373875   |
| Games          | KhCmfX_PQ7E  | 1967929   |
| Games          | 2SVMFCZgvNM  | 1813794   |
| Games          | gPutYwiiE0o  | 1633482   |
| Games          | 7wt5FiZQrgM  | 1399531   |
| Howto          | hut3VRL5XRE  | 2684989   |
| Howto          | YYTpb-QXV0k  | 2492153   |
| Howto          | Pf3z935R37E  | 2096661   |
| Howto          | Yd99gyE4jCk  | 1918946   |
| Howto          | koQFjKwVFB0  | 1757071   |
| Howto          | f5Fg6KFcOsU  | 1751817   |
| Howto          | STQ3nhXuuEM  | 1713974   |
| Howto          | FtKuBKIaVvs  | 1520774   |
| Howto          | M0ODskdEPnQ  | 1503351   |
| Howto          | uFwCk4UPtlM  | 1500110   |
| Music          | QjA5faZF1A8  | 15256922  |
| Music          | tYnn51C3X_w  | 11823701  |
| Music          | pv5zWaTEVkI  | 11672017  |
| Music          | 8bbTtPL1jRs  | 9579911   |
| Music          | UMf40daefsI  | 7533070   |
| Music          | -xEzGIuY7kw  | 6946033   |
| Music          | d6C0bNDqf3Y  | 6935578   |
| Music          | HSoVKUVOnfQ  | 6193057   |
| Music          | 3URfWTEPmtE  | 5581171   |
| Music          | thtmaZnxk_0  | 5142238   |
| News           | hr23tpWX8lM  | 4706030   |
| News           | YgW7or1TuFk  | 2899397   |
| News           | nda_OSWeyn8  | 2817078   |
| News           | 7SV2sfoPAY8  | 2803520   |
| News           | HBa9wdOANHw  | 2348709   |
| News           | xDh_pvv1tUM  | 2335060   |
| News           | p_YMigZmUuk  | 2326680   |
| News           | QCVxQ_3Ejkg  | 2318782   |
| News           | a9WB_PXjTBo  | 2310583   |
| News           | qSM_3fyiaxM  | 2291369   |
| People         | -_CSo1gOd48  | 13199833  |
| People         | D2kJZOfq7zk  | 11184051  |
| People         | pa_7P5AbUww  | 5705136   |
| People         | f4B-r8KJhlE  | 4937616   |
| People         | LB84A3zcmVo  | 4866739   |
| People         | tXNquTYnyg0  | 3613323   |
| People         | EYppbbbSxjc  | 2896562   |
| People         | LH7vrLlDZ6U  | 2615359   |
| People         | bTV85fQhj0E  | 2192656   |
| People         | eVFF98kNg8Q  | 1813803   |
| Politics       | hr23tpWX8lM  | 4706030   |
| Politics       | YgW7or1TuFk  | 2899397   |
| Politics       | nda_OSWeyn8  | 2817078   |
| Politics       | 7SV2sfoPAY8  | 2803520   |
| Politics       | HBa9wdOANHw  | 2348709   |
| Politics       | xDh_pvv1tUM  | 2335060   |
| Politics       | p_YMigZmUuk  | 2326680   |
| Politics       | QCVxQ_3Ejkg  | 2318782   |
| Politics       | a9WB_PXjTBo  | 2310583   |
| Politics       | qSM_3fyiaxM  | 2291369   |
| Sports         | Ugrlzm7fySE  | 2867888   |
| Sports         | q8t7iSGAKik  | 2735003   |
| Sports         | 7vL19q8yL54  | 2527713   |
| Sports         | g3dXfFZ6SH0  | 2295871   |
| Sports         | P-bWsOK-h98  | 2268107   |
| Sports         | HD8f_Qgwc50  | 2165475   |
| Sports         | qjWQNwv-GJ4  | 2132591   |
| Sports         | eN0V-rJQSHE  | 2124653   |
| Sports         | fM38G1450Ew  | 2052778   |
| Sports         | 3PGzrfE8rJg  | 2013466   |
+----------------+--------------+-----------+--+
|      cate      |   videoid    |   views   |
+----------------+--------------+-----------+--+
| Vehicles       | RjrEQaG5jPM  | 2803140   |
| Vehicles       | cv157ZIInUk  | 2773979   |
| Vehicles       | Gyg9U1YaVk8  | 1832224   |
| Vehicles       | 6GNB7xT3rNE  | 1412497   |
| Vehicles       | tth9krDtxII  | 1347317   |
| Vehicles       | 46LQd9dXFRU  | 1262173   |
| Vehicles       | pdiuDXwgrjQ  | 1013697   |
| Vehicles       | kY_cDpENQLE  | 956665    |
| Vehicles       | YtxfbxGz1u4  | 942604    |
| Vehicles       | aCamHfJwSGU  | 847442    |
+----------------+--------------+-----------+--+

友培

关注

1
点赞
踩
24

收藏

觉得还不错? 一键收藏
打赏
0
评论
Hive学习视频心得（六）Hive实战之某视频网站分析

文章目录Hive实战之某视频网站分析一、项目需求简介二、项目具体步骤1）、数据结构①、视频表②、用户表2）、项目准备3）、数据准备4）、数据处理（ETL）①、ETLMapper.java②、ETLDriver.java③、打包提交集群执行④、ETL执行完成后截图5）、Hive端导入数据①、项目创库②、项目建表a、外部表b、内部表c、外部向内部数据导入三、处理项目需求1）、统计视频观看数Top102）、统计视频类别热度Top103）、统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数4
复制链接

扫一扫