目录

Hive

需求描述

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

  • — 统计视频观看数Top10
  • — 统计视频类别热度Top10
  • — 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
  • — 统计视频观看数Top50所关联视频的所属类别Rank
  • — 统计每个类别中的视频热度Top10,以Music为例
  • — 统计每个类别视频观看数Top10
  • — 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频

11.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

准备工作

ETL

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

1）ETL之封装工具类


package com.cosyblogs.zuivideo.etl;

public class ETLUtils {
    /**
     * 清洗视频数据
     * <p>
     * 规则:
     * 1. 数据长度必须大于等于9
     * 2. 将视频的类别中的空格去掉
     * 3. 将关联视频通过&拼接
     *
     * @param line
     * @return 如果数据合法，返回清洗完的数据
     * 如果数据不合法， 返回null
     * <p>
     * 测试数据:
     * RX24KLBhwMI	lemonette	697	People & Blogs	512	24149	4.22	315	474	t60tW0WevkE	WZgoejVDZlo	Xa_op4MhSkg	MwynZ8qTwXA	sfG2rtAkAcg	j72VLPwzd_c	24Qfs69Al3U	EGWutOjVx4M	KVkseZR5coU	R6OaRcsfnY4	dGM3k_4cNhE	ai-cSq6APLQ	73M0y-iD9WE	3uKOSjE79YA	9BBu5N0iFBg	7f9zwx52xgA	ncEV0tSC7xM	H-J8Kbx9o68	s8xf4QX1UvA	2cKd9ERh5-8
     */
    public static String etlZuiVideoData(String line) {
        StringBuffer sbs = new StringBuffer();
        //1. 切割数据
        String[] splits = line.split("\t");
        //2. 规则一
        if (splits.length < 9) {
            return null;
        }
        //3. 规则二
        splits[3] = splits[3].replaceAll(" ", "");
        //4. 规则三
        for (int i = 0; i < splits.length; i++) {
            // 有相关视频 或者 没有相关视频
            if (i <= 8) {
                if (i == splits.length - 1) {
                    sbs.append(splits[i]);
                } else {
                    sbs.append(splits[i]).append("\t");
                }
            } else {
                if (i == splits.length - 1) {
                    sbs.append(splits[i]);
                } else {
                    sbs.append(splits[i]).append("&");
                }
            }
        }
        return sbs.toString();
    }

}

2）ETL之Mapper


package com.cosyblogs.zuivideo.etl;

import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.NullWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapreduce.Mapper;

import java.io.IOException;

public class ZuiVideoETLMapper extends Mapper<LongWritable, Text, Text, NullWritable> {
    Text outk = new Text();

    @Override
    protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {
        String line = value.toString();
        String result = ETLUtils.etlZuiVideoData(line);
        if (result == null) {
            return;
        }
        outk.set(result);
        context.write(outk, NullWritable.get());
    }

}

3）ETL之Driver


package com.cosyblogs.zuivideo.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;

import java.io.IOException;

public class ZuiVideoETLDriver {
    public static void main(String[] args) throws IOException, ClassNotFoundException, InterruptedException {
        Configuration conf = new Configuration();
        Job job = Job.getInstance(conf);
        job.setJarByClass(ZuiVideoETLMapper.class);
        job.setMapperClass(ZuiVideoETLMapper.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的 /home/hadoop/jar 目录下

5）上传原始数据到HDFS

Hive 实战-最码农

6）ETL数据


[hadoop@hadoop102 datas] hadoop jar  zuivideo-1.0-SNAPSHOT.jar  com.cosyblogs.zuivideo.etl.ZuiVideoETLDriver /zuivideo/video /zuivideo/etl-video

准备表

1）需要准备的表

创建原始数据表：zuivideo_ori，zuivideo_user_ori，

创建最终表：zuivideo_orc，zuivideo_user_orc

2）创建原始数据表：

    （1）zuivideo_ori


create table zuivideo_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）创建原始数据表: zuivideo_user_ori


create table zuivideo_user_ori(
    uploader string,
    videos int,
    friends int)
row format delimited
fields terminated by "\t"
stored as textfile;
  1. 创建orc存储格式带snappy压缩的表：

（1）zuivideo_orc


create table zuivideo_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）zuivideo_user_orc


create table zuivideo_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 "/zuivideo/etl-video" into table zuivideo_ori;
load data inpath "/zuivideo/user" into table zuivideo_user_ori;

（4）向orc表插入数据


insert into table zuivideo_orc select * from zuivideo_ori;
insert into table zuivideo_user_orc select * from zuivideo_user_ori;

业务分析

统计视频观看数Top10

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

代码：


SELECT 
     videoId,
     views 
FROM 
     zuivideo_orc
ORDER BY 
     views DESC 
LIMIT 10;
Hive 实战-最码农

统计视频类别热度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
    zuivideo_orc
lateral VIEW explode(category) zuivideo_orc_tmp AS category_name
) t1
GROUP BY
    t1.category_name
ORDER BY
    hot
DESC
LIMIT 10;
Hive 实战-最码农

统计出视频观看数最高的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
    zuivideo_orc
ORDER BY
    views
DESC
LIMIT 20
) t1
lateral VIEW explode(t1.category) t1_tmp AS category_name
) t2
GROUP BY t2.category_name;
Hive 实战-最码农

统计视频观看数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
   zuivideo_orc
ORDER BY
   views
DESC
LIMIT 50
)t1
lateral VIEW explode(t1.relatedid) t1_tmp AS relatedid_id
)t2
JOIN
   zuivideo_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;
Hive 实战-最码农

统计每个类别中的视频热度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 zuivideo_orc
lateral VIEW explode(category) zuivideo_orc_tmp AS category_name
)t1   
WHERE
    t1.category_name = "Music"
ORDER BY
    t1.views
DESC
LIMIT 10;
Hive 实战-最码农

统计每个类别视频观看数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 zuivideo_orc
lateral VIEW explode(category) zuivideo_orc_tmp AS category_name
)t1
)t2
WHERE t2.rk <=10;
Hive 实战-最码农

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

思路：

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

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

代码：


SELECT
   t2.videoId,
   t2.views,
   t2.uploader
FROM
(
SELECT
   uploader,
   videos
FROM zuivideo_user_orc
ORDER BY
   videos
DESC
LIMIT 10   
) t1
JOIN zuivideo_orc t2
ON t1.uploader = t2.uploader
ORDER BY
  t2.views
DESC
LIMIT 20;
Hive 实战-最码农