大数据Hive学习之旅第七篇 一、Hive 实战 二、常见错

发表于

「这是我参与11月更文挑战的第28天，活动详情查看：2021最后一次更文挑战」。

一、Hive 实战

1、需求描述

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

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

2、数据结构

  1. 视频表

image.png
2. 用户表

image.png

3、准备工作

  1. 准备表
* 需要准备的表


    + 创建原始数据表：gulivideo\_ori，gulivideo\_user\_ori
    + 创建最终表：gulivideo\_orc，gulivideo\_user\_orc
* 创建原始数据表


    + gulivideo\_ori



    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
hive复制代码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 "&"
stored as textfile;


    + gulivideo\_user\_ori



    
1
2
3
4
5
6
hive复制代码create table gulivideo_user_ori(
 uploader string,
 videos int,
 friends int)
row format delimited fields terminated by "\t" 
stored as textfile;


* 创建 orc 存储格式带 snappy 压缩的表


    + gulivideo\_orc



    
1
2
3
4
5
6
7
8
9
10
11
12
13
hive复制代码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");


    + gulivideo\_user\_orc



    
1
2
3
4
5
6
7
8
hive复制代码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");


    + 向 ori 表插入数据



    
1
2
hive复制代码load data local inpath "/opt/module/data/video" into table gulivideo_ori;
load data local inpath "/opt/module/data/user" into table gulivideo_user_ori;


    + 向 orc 表插入数据



    
1
2
hive复制代码insert into table gulivideo_orc select * from gulivideo_ori;
insert into table gulivideo_user_orc select * from gulivideo_user_ori;
  1. 安装 Tez 引擎（了解）

tez.apache.org/

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

image.png

用 Hive 直接编写 MR 程序，假设有四个有依赖关系的 MR 作业，上图中，绿色是 ReduceTask，云状表示写屏蔽，需要将中间结果持久化写到 HDFS。

Tez 可以将多个有依赖的作业转换为一个作业，这样只需写一次 HDFS，且中间节点较少，从而大大提升作业的计算性能。

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




1
2
3
shell复制代码[moe@hadoop102 ~]$ mkdir /opt/module/tez

[moe@hadoop102 ~]$ tar -zxvf /opt/software/tez-0.10.1-SNAPSHOT-minimal.tar.gz -C /opt/module/tez/


* 上传 tez 依赖到 HDFS




1
2
3
shell复制代码[moe@hadoop102 ~]$ hadoop fs -mkdir /tez

[moe@hadoop102 ~]$ hadoop fs -put /opt/software/tez-0.10.1-SNAPSHOT.tar.gz /tez


* 新建 tez-site.xml




1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
xml复制代码<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>

        <property>
                <name>tez.lib.uris</name>
                <value>${fs.defaultFS}/tez/tez-0.10.1-SNAPSHOT.tar.gz</value>
        </property>

        <property>
                <name>tez.use.cluster.hadoop-libs</name>
                <value>true</value>
        </property>

        <property>
                <name>tez.am.resource.memory.mb</name>
                <value>1024</value>
        </property>

        <property>
                <name>tez.am.resource.cpu.vcores</name>
                <value>1</value>
        </property>

        <property>
                <name>tez.container.max.java.heap.fraction</name>
                <value>0.4</value>
        </property>

        <property>
                <name>tez.task.resource.memory.mb</name>
                <value>1024</value>
        </property>

        <property>
                <name>tez.task.resource.cpu.vcores</name>
                <value>1</value>
        </property>

</configuration>


* 修改 Hadoop 环境变量




1
shell复制代码[moe@hadoop102 ~]$ vim $HADOOP_HOME/etc/hadoop/shellprofile.d/tez.sh



添加 Tez 的 Jar 包相关信息




1
2
3
4
5
6
7
sh复制代码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
}


* 修改 Hive 的计算引擎




1
shell复制代码[moe@hadoop102 ~]$ vim $HIVE_HOME/conf/hive-site.xml



添加




1
2
3
4
5
6
7
8
9
xml复制代码<property>
        <name>hive.execution.engine</name>
        <value>tez</value>
</property>

<property>
        <name>hive.tez.container.size</name>
        <value>1024</value>
</property>


* 解决日志 Jar 包冲突




1
shell复制代码[moe@hadoop102 ~]$ rm /opt/module/tez/lib/slf4j-log4j12-1.7.10.jar

4、业务分析

4.1、统计视频观看数 Top10

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

最终SQL：

1
2
3
4
5
6
7
8
hive复制代码SELECT 
 videoId,
 views
FROM 
 gulivideo_orc
ORDER BY 
 views DESC
LIMIT 10;

image.png

4.2、统计视频类别热度 Top10

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

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

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

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

最终SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
hive复制代码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;

image.png

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

思路：
（1）先找到观看数最高的 20 个视频所属条目的所有信息，降序排列

（2）把这 20 条信息中的 category 分裂出来(列转行)

（3）最后查询视频分类名称和该分类下有多少个 Top20 的视频

最终SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
hive复制代码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;

image.png

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

最终SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
hive复制代码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;

image.png

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

思路：

（1）要想统计 Music 类别中的视频热度 Top10，需要先找到 Music 类别，那么就需要将category 展开，所以可以创建一张表用于存放 categoryId 展开的数据。

（2）向 category 展开的表中插入数据。

（3）统计对应类别（Music）中的视频热度。统计 Music 类别的 Top10（也可以统计其他）

最终SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
hive复制代码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;

image.png

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

最终SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
hive复制代码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;

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

思路：

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

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

最终SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
hive复制代码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;

image.png

二、常见错误及解决方案

  1. 如果更换 Tez 引擎后，执行任务卡住，可以尝试调节容量调度器的资源调度策略

将$HADOOP_HOME/etc/hadoop/capacity-scheduler.xml 文件中的

1
2
3
4
5
6
7
8
9
xml复制代码<property>
 <name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
 <value>0.1</value>
 <description>
 Maximum percent of resources in the cluster which can be used to run 
 application masters i.e. controls number of concurrent running
 applications.
 </description>
</property>

改成

1
2
3
4
5
6
7
8
9
xml复制代码<property>
 <name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
 <value>1</value>
 <description>
 Maximum percent of resources in the cluster which can be used to run 
 application masters i.e. controls number of concurrent running
 applications.
 </description>
</property>
  1. JVM 堆内存溢出

描述：java.lang.OutOfMemoryError: Java heap space

解决：在 yarn-site.xml 中加入如下代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
xml复制代码<property>
        <name>yarn.scheduler.maximum-allocation-mb</name>
        <value>2048</value>
</property>

<property>
        <name>yarn.scheduler.minimum-allocation-mb</name>
        <value>2048</value>
</property>

<property>
        <name>yarn.nodemanager.vmem-pmem-ratio</name>
        <value>2.1</value>
</property>

<property>
        <name>mapred.child.java.opts</name>
        <value>-Xmx1024m</value>
</property>
  1. 虚拟内存限制

在 yarn-site.xml 中添加如下配置:

1
2
3
4
xml复制代码<property>
        <name>yarn.nodemanager.vmem-check-enabled</name>
        <value>false</value>
</property>

三、友情链接

大数据Hive学习之旅第六篇

大数据Hive学习之旅第五篇

大数据Hive学习之旅第四篇

大数据Hive学习之旅第三篇

大数据Hive学习之旅第二篇

大数据Hive学习之旅第一篇

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%