目录
0 需求描述
1 数据结构
2 准备工作
2.1 准备表
2.2 安装TEZ计算引擎
3 业务分析
3.1 统计视频观看数Top10
3.2 统计视频类别热度Top10
3.3 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
3.4 统计视频观看数Top50所关联视频的所属类别排序
3.5 统计每个类别中的视频热度Top10，以Music为例
3.6 统计每个类别视频观看数Top10
3.7 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频
0 需求描述
统计爱奇艺视频网站的常规指标，各种TopN指标：（多维分析）
-- 统计视频观看数Top10
-- 统计视频类别热度Top10
-- 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
-- 统计视频观看数Top50所关联视频的所属类别排序
-- 统计每个类别中的视频热度Top10,以Music为例
-- 统计每个类别视频观看数Top10
-- 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频
1 数据结构
（1）视频表
视频表
| 字段 | 备注 | 详细描述 |
| videoId | 视频唯一id（String） | 11位字符串 |
| uploader | 视频上传者（String） | 上传视频的用户名String |
| age | 视频年龄（int） | 视频在平台上的整数天 |
| category | 视频类别（Array | 上传视频指定的视频分类 |
| length | 视频长度（Int） | 整形数字标识的视频长度 |
| views | 观看次数（Int） | 视频被浏览的次数 |
| rate | 视频评分（Double） | 满分5分 |
| Ratings | 流量（Int） | 视频的流量，整型数字 |
| conments | 评论数（Int） | 一个视频的整数评论数 |
| relatedId | 相关视频id（Array | 相关视频的id，最多20个 |
（2）用户表
用户表
| 字段 | 备注 | 字段类型 |
| uploader | 上传者用户名 | string |
| videos | 上传视频数 | int |
| friends | 朋友数量 | int |
2 准备工作
2.1 准备表
（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,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
row format delimited fields terminated by "t"
collection items terminated by "&"
stored as textfile;
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;
（3）创建orc存储格式带snappy压缩的表：
（1）gulivideo_orc
create table gulivideo_orc(
videoId string,
uploader string,
age int,
category array,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array)
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 local inpath "/opt/module/data/video" into table gulivideo_ori; load data local inpath "/opt/module/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;
2.2 安装TEZ计算引擎
Tez是一个Hive的运行引擎，性能优于MR。为什么优于MR呢？看下。
用Hive直接编写MR程序，假设有四个有依赖关系的MR作业，上图中，绿色是Reduce Task，云状表示写屏蔽，需要将中间结果持久化写到HDFS。
Tez可以将多个有依赖的作业转换为一个作业，这样只需写一次HDFS，且中间节点较少，从而大大提升作业的计算性能。
1）将tez安装包拷贝到集群，并解压tar包
[root@hadoop102 software]$ mkdir /opt/module/tez [root@hadoop102 software]$ tar -zxvf /opt/software/tez-0.10.1-SNAPSHOT-minimal.tar.gz -C /opt/module/tez
2）上传tez依赖到HDFS
[root@hadoop102 software]$ hadoop fs -mkdir /tez [root@hadoop102 software]$ hadoop fs -put /opt/software/tez-0.10.1-SNAPSHOT.tar.gz /tez
3）新建tez-site.xml
[root@hadoop102 software]$ vim $HADOOP_HOME/etc/hadoop/tez-site.xml
添加如下内容：
tez.lib.uris ${fs.defaultFS}/tez/tez-0.10.1-SNAPSHOT.tar.gz tez.use.cluster.hadoop-libs true tez.am.resource.memory.mb 1024 tez.am.resource.cpu.vcores 1 tez.container.max.java.heap.fraction 0.4 tez.task.resource.memory.mb 1024 tez.task.resource.cpu.vcores 1
4）修改Hadoop环境变量
[root@hadoop102 software]$ vim $HADOOP_HOME/etc/hadoop/shellprofile.d/tez.sh
添加Tez的Jar包相关信息
hadoop_add_profile tez
function _tez_hadoop_classpath
{
hadoop_add_classpath "$HADOOP_HOME/etc/hadoop" after
hadoop_add_classpath "/opt/module/tez/*" after
hadoop_add_classpath "/opt/module/tez/lib/*" after
}
5）修改Hive的计算引擎
[root@hadoop102 software]$ vim $HIVE_HOME/conf/hive-site.xml
添加
hive.execution.engine tez hive.tez.container.size 1024
6）解决日志Jar包冲突
[root@hadoop102 software]$ rm /opt/module/tez/lib/slf4j-log4j12-1.7.10.jar
3 业务分析
3.1 统计视频观看数Top10
思路：使用order by按照views字段做一个全局排序即可，同时我们设置只显示前10条。
最终代码：
SELECt
videoId,
views
FROM
gulivideo_orc
ORDER BY
views DESC
LIMIT 10;
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_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
) t1
GROUP BY
t1.category_name
ORDER BY
hot
DESC
LIMIT 10
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_orc
ORDER BY
views
DESC
LIMIT 20
) t1
lateral VIEW explode(t1.category) t1_tmp AS category_name
) t2
GROUP BY t2.category_name
3.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 ( SELECt videoId, views, relatedid FROM gulivideo_orc ORDER BY views DESC LIMIT 50 )t1 lateral VIEW explode(t1.relatedid) t1_tmp AS relatedid_id )t2 JOIN gulivideo_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
3.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
3.6 统计每个类别视频观看数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_orc
lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
)t1
)t2
WHERe t2.rk <=10
3.7 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频
思路：
- （1）求出上传视频最多的10个用户
- （2）关联gulivideo_orc表，求出这10个用户上传的所有的视频，按照观看数取前20
最终代码:
SELECt t2.videoId, t2.views, t2.uploader FROM ( SELECt uploader, videos FROM gulivideo_user_orc ORDER BY videos DESC LIMIT 10 ) t1 JOIN gulivideo_orc t2 ON t1.uploader = t2.uploader ORDER BY t2.views DESC
