Hive实战

1 需求描述

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

• 统计视频观看数Top10

• 统计视频类别热度Top10

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

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

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

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

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

2 数据结构

1）视频表

视频表

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

2）用户表

用户表

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

3 准备工作

3.1 ETL

通过观察原始数据形式，可以发现，视频可以有多个所属分类，每个所属分类用&符号分割，且分割的两边有空格字符，同时相关视频也是可以有多个元素，多个相关视频又用“\t”进行分割。为了分析数据时方便对存在多个子元素的数据进行操作，我们首先进行数据重组清洗操作。即：将所有的类别用“&”分割，同时去掉两边空格，多个相关视频id也使用“&”进行分割。

1）ETL之封装工具类

public class ETLUtil {
    /**
 	* 数据清洗方法
 	*/
   public static  String  etlData(String srcData){
        StringBuffer resultData = new StringBuffer();
        //1. 先将数据通过\t 切割
        String[] datas = srcData.split("\t");
        //2. 判断长度是否小于9
        if(datas.length <9){
            return null ;
        }
        //3. 将数据中的视频类别的空格去掉
        datas[3]=datas[3].replaceAll(" ","");
        //4. 将数据中的关联视频id通过&拼接
        for (int i = 0; i < datas.length; i++) {
            if(i < 9){
                //4.1 没有关联视频的情况
                if(i == datas.length-1){
                    resultData.append(datas[i]);
                }else{
                    resultData.append(datas[i]).append("\t");
                }
            }else{
                //4.2 有关联视频的情况
                if(i == datas.length-1){
                    resultData.append(datas[i]);
                }else{
                    resultData.append(datas[i]).append("&");
                }
            }
        }
        return resultData.toString();
    }
}  

2）ETL之Mapper

  /**
 * 清洗谷粒影音的原始数据
 * 清洗规则
 *  1. 将数据长度小于9的清洗掉
 *  2. 将数据中的视频类别中间的空格去掉   People & Blogs
 *  3. 将数据中的关联视频id通过&符号拼接
 */
public class EtlMapper extends Mapper<LongWritable, Text,Text, NullWritable> {
    private Text k = new Text();
    @Override
    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
       //获取一行
        String line = value.toString();
        //清洗
        String resultData = ETLUtil.etlData(line);

        if(resultData != null) {
            //写出
            k.set(resultData);
            context.write(k,NullWritable.get());
        }
    }
}

3）ETL之Driver

package com.atguigu.gulivideo.etl;

import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.Path;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Job;
import org.apache.hadoop.mapreduce.lib.input.FileInputFormat;
import org.apache.hadoop.mapreduce.lib.output.FileOutputFormat;

public class EtlDriver {
    public static void main(String[] args) throws Exception {
        Configuration conf = new Configuration();
        Job job  = Job.getInstance(conf);
        job.setJarByClass(EtlDriver.class);
        job.setMapperClass(EtlMapper.class);
        job.setMapOutputKeyClass(Text.class);
        job.setMapOutputValueClass(NullWritable.class);
        job.setOutputKeyClass(Text.class);
        job.setOutputValueClass(NullWritable.class);
        job.setNumReduceTasks(0);
        FileInputFormat.setInputPaths(job,new Path(args[0]));
        FileOutputFormat.setOutputPath(job,new Path(args[1]));
        job.waitForCompletion(true);
    }
}  

4）将ETL程序打包为etl.jar并上传到Linux的/opt/module/hive/datas目录下

5）上传原始数据到HDFS

[atguigu@hadoop102 datas] pwd
/opt/module/hive/datas

[atguigu@hadoop102 datas] hadoop fs -mkdir -p  /gulivideo/video

[atguigu@hadoop102 datas] hadoop fs -mkdir -p  /gulivideo/user

[atguigu@hadoop102 datas] hadoop fs -put gulivideo/user/user.txt  /gulivideo/user

[atguigu@hadoop102 datas] hadoop fs -put gulivideo/video/*.txt  /gulivideo/video

6）ETL数据

[atguigu@hadoop102 datas] hadoop jar  etl.jar  com.atguigu.hive.etl.EtlDriver /gulivideo/video /gulivideo/video/output

3.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 "&";

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

1） 创建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 inpath "/gulivideo/video/output" into table gulivideo_ori;
load data inpath "/gulivideo/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;

4 统计

4.1 统计视频观看数Top10

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

用来测试集群环境

最终代码：

SELECT videoId,views 
FROM gulivideo_orc
ORDER BY views DESC 
LIMIT 10;

4.2 统计视频类别热度Top10

思路：

（1）即统计每个类别有多少个视频，显示出包含视频最多的前10个类别。

（2）我们需要按照类别group by聚合，然后count组内的videoId个数即可。

（3）因为当前表结构为：一个视频对应一个或多个类别。所以如果要group by类别，需要先将类别进行列转行(展开)，然后再进行count即可。

（4）最后按照热度排序，显示前10条。

最终代码：

SELECT
    t1.category_name ,
    COUNT(t1.videoId) hot
FROM
(
-- 1.将类别列炸开
SELECT
    videoId,
    category_name
FROM
    gulivideo_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
) t1
-- 2. 按照类别分组 求count，并按count排序，取前10
GROUP BY
    t1.category_name
ORDER BY
    hot
DESC
LIMIT 10;

4.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
(
-- 1. 最高的前20个视频
SELECT
    videoId,
    views,
    category
FROM
    gulivideo_orc
ORDER BY
    views
DESC
LIMIT 20
) t1
-- 2. 将类别列炸开
lateral VIEW explode(t1.category) t1_tmp AS category_name
) t2
-- 3. 分类统计
GROUP BY t2.category_name;

4.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 
(
-- 1.统计视频观看数Top50所关联视频
SELECT 
   videoId, 
   views,
   relatedid 
FROM 
   gulivideo_orc
ORDER BY
   views 
DESC 
LIMIT 50
)t1
-- 2. 将关联列炸开
lateral VIEW explode(t1.relatedid) t1_tmp AS relatedid_id
)t2 
-- 3.与原表 join
JOIN 
   gulivideo_orc t3 
ON 
 t2.relatedid_id = t3.videoId 
) t4 
-- 4. 将类别列炸开
lateral VIEW explode(t4.category) t4_tmp AS category_name
) t5
-- 5. 分类统计
GROUP BY
  t5.category_name
ORDER BY 
  video_sum
DESC 
) t6;

窗口排序函数用于给每个分组内的数据打上排序的标号。注意窗口排序函数不支持窗口表达式。

• row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
• rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
• dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；

上述这三个函数用于分组TopN的场景非常适合。

4.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;

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

分组TopN问题

最终代码：

SELECT 
  t2.videoId,
  t2.views,
  t2.category_name,
  t2.rk
FROM 
(
SELECT 
   t1.videoId,
   t1.views,
   t1.category_name,
   -- 组内排序 over开窗
   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;

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

思路：

（1）求出上传视频最多的10个用户

（2）关联gulivideo_orc表，求出这10个用户上传的所有的视频，按照观看数取前20

最终代码:

SELECT
   t2.videoId,
   t2.views,
   t2.uploader
FROM
(
-- 1.上传视频最多的10个用户
SELECT
   uploader,
   videos
FROM gulivideo_user_orc
ORDER BY
   videos
DESC
LIMIT 10
) t1
-- 2.关联视频表
JOIN gulivideo_orc t2
ON t1.uploader = t2.uploader
-- 3.根据观看次数，对用户所上传的视频进行排名
ORDER BY
  t2.views
DESC
LIMIT 20;