Hive学习笔记四之Hive实战

Hive学习笔记四之Hive实战 1.1 需求描述

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

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

1.2 数据结构

1. 视频表
   

2. 用户表
   

1.3 准备工作 1.3.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/hive/datas/video" into table gulivideo_ori;
load data local inpath "/opt/module/hive/datas/user" into table gulivideo_user_ori;

1.3.2 安装 Tez 引擎

Tez 是一个 Hive 的运行引擎，性能优于 MR。为什么优于 MR 呢？看下图

用 Hive 直接编写 MR 程序，假设有四个有依赖关系的 MR 作业，上图中，绿色是 ReduceTask，云状表示写屏蔽，需要将中间结果持久化写到 HDFS。
Tez 可以将多个有依赖的作业转换为一个作业，这样只需写一次 HDFS，且中间节点较少，从而大大提升作业的计算性能。

1. 将 tez 安装包拷贝到集群，并解压 tar 包

mkdir /opt/module/tez
 tar -zxvf /opt/software/tez-0.10.1-SNAPSHOT-minimal.tar.gz -C /opt/module/tez

2. 上传 tez 依赖到 HDFS

 hadoop fs -mkdir /tez
  hadoop fs -put /opt/software/tez-0.10.1-SNAPSHOT.tar.gz /tez

3. 新建 tez-site.xml

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 环境变量
   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 的计算引擎

vim $HIVE_HOME/conf/hive-site.xml

添加

 hive.execution.engine
 tez


 hive.tez.container.size
 1024

6. 解决日志 Jar 包冲突
   rm /opt/module/tez/lib/slf4j-log4j12-1.7.10.jar

1.4 业务分析 1.4.1 统计视频观看数 Top10

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

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

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

1.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 
(
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

1.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 
(
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

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

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

1.4.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
LIMIT 20