图解大数据 | Hive搭建与应用@实操案例_hive项目案例

作者：韩信子@ShowMeAI
教程地址：http://www.showmeai.tech/tutorials/84
本文地址：http://www.showmeai.tech/article-detail/171
声明：版权所有，转载请联系平台与作者并注明出处

收藏ShowMeAI查看更多精彩内容

---

1.Hive 搭建与配置

大数据生态中最重要的工具平台之一是Hive，它是离线计算的关键组件，常用于数仓建设，在公司内会通过SQL实现大数据的统计与报表。下面来看一下Hive的搭建和配置使用方法。

1）下载Hive

安装Hive过程可以参考官方文档：https://cwiki.apache.org/confluence/display/Hive/GettingStarted。

按照文件建议在 http://www.apache.org/dyn/closer.cgi/hive/ 下载最新的release，这里以Hive3.1.3为例讲解。

把安装文件解压到安装有hadoop环境的机器上：

root@ubuntu:~/bigdata# ll
total 20
drwxr-xr-x 27 root root 4096 Sep 30 07:24 azkaban/
drwxr-xr-x  2 root root 4096 Oct 13 08:36 demo/
drwxr-xr-x 12 1001 1001 4096 Sep 30 09:43 hadoop-3.3.0/
drwxr-xr-x 11 root root 4096 Oct 13 07:58 hive-3.1.3/
drwxr-xr-x 32 work work 4096 Aug 28 07:28 spark-3.0.1/
root@ubuntu:~/bigdata# pwd
/root/bigdata
1
2
3
4
5
6
7
8
9

接下来把Hive的bin目录导出PATH：

root@ubuntu:~/bigdata# cat /etc/profile.d/java.sh 
export PATH=/usr/local/jdk/bin:/root/bigdata/hadoop-3.3.0/bin:/root/bigdata/spark-3.0.1/bin:/root/bigdata/hive-3.1.3/bin:${PATH}
1
2

升级guava依赖为hadoop版本：

mv lib/guava-19.0.jar lib/guava-19.0.jar.bk 
ln -s  /root/bigdata/hadoop-3.3.0/share/hadoop/hdfs/lib/guava-27.0-jre.jar  /root/bigdata/hive-3.1.3/lib/guava-27.0-jre.jar
1
2

上述第一条命令先备份了Hive自带的guava依赖包，然后将hadoop自带的更高版本软链过来，这个版本一致性是Hive正常运行的关键之一。

2）安装MYSQL

hive的元数据服务是独立部署的，它基于mysql保存数据。可以使用apt命令在ubuntu环境安装oracle mysql：

apt-get install mysql-server
1

如果使用 mysql -h localhost -u root -p 登录时，提示 access denied，那需要找到和删除 mysql user 表中的一条 localhost 的特殊规则：

delete from user where User=’root’ and Host=’localhost’;
FLUSH PRIVILEGES;
1
2

接着创建hive数据库：

create database hive;
1

接着通过wget命令下载 JDBC mysql，并把该Jar放到hive的lib目录下（hive metastore服务将用该JDBC驱动连接mysql读写元数据）：

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.21/mysql-connector-java-8.0.21.jar
mv mysql-connector-java-8.0.21.jar lib
1
2

3）配置Hive

Hive会自动加载 conf/hive-site.xml 配置文件，官方在 conf/hive-default.xml.template 提供了一个模板文件，里面是 Hive 加载不到 hive-site.xml 时使用的默认值，可以参考 conf/hive-default.xml.template来填写 hive-site.xml，下面是一个配置好的样本（只配置了必要项）：

root@ubuntu:~/bigdata/hive-3.1.3# cat conf/hive-site.xml 
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
        <property>
                <name>hive.cli.print.header</name>
                <value>true</value>
                <description>Whether to print the names of the columns in query output.</description>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionURL</name>
                <value>jdbc:mysql://localhost:3306/hive</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionDriverName</name>
                <value>com.mysql.cj.jdbc.Driver</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionUserName</name>
                <value>root</value>
        </property>
        <property>
                <name>javax.jdo.option.ConnectionPassword</name>
                <value>xxxxx</value>
        </property>
        <property>
                <name>hive.metastore.uris</name>
                <value>thrift://localhost:9083</value>
                <description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>
        </property>
</configuration>
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

其中核心的项目解释如下：

• hive.cli.print.header：Hive命令行工具将打印table的表头，方便阅读结果
• javax.jdo.option.ConnectionURL：数据库JDBC URL，这里就是mysql的Hive数据库
• javax.jdo.option.ConnectionDriverName：JDBC类名，8.x版本Mysql jar的类名有所变化，需要注意
• javax.jdo.option.ConnectionUserName：mysql用户名
• javax.jdo.option.ConnectionPassword：mysql密码
• hive.metastore.uris：启动metastore服务的监听地址

4）启动metastore服务

先执行Hive建表命令，完成mysql元数据建表：

bin/schematool -dbType mysql -initSchema
1

执行命令：

nohup hive –service metastore &
1

服务将监听在 localhost:9083 端口，生产环境需要让host是可以被其他服务器访问到的，因为访问metastore服务的客户端不一定在本机。

现在命令行使用Hive命令，将会自动根据 hive-site.xml 连接到 metastore 服务，运行Hive命令做一下测试：

root@ubuntu:~/bigdata/hive-3.1.3# hive
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/root/bigdata/hive-3.1.3/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/root/bigdata/hadoop-3.3.0/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Hive Session ID = f0d4bf60-d85f-456a-98fb-e904d50f5242

Logging initialized using configuration in jar:file:/root/bigdata/hive-3.1.3/lib/hive-common-3.1.3.jar!/hive-log4j2.properties Async: true
Hive Session ID = 959e0cda-f8eb-4fc1-b798-cb5175e735d2
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.

hive> show databases;
OK
database_name
default
Time taken: 0.516 seconds, Fetched: 2 row(s)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Hive命令过后进入，可以看到自带的default数据库，并且还提示Hive目前使用MR作为计算引擎，实际Hive建议开始使用spark或者tez作为SQL的底层计算引擎，未来最终会彻底取消MR。

目前继续使用MR作为计算引擎即可，Hive会根据hadoop命令自动找到hadoop和yarn配置文件，最终SQL是通过MR运行在yarn上完成计算的。

以上就是Hive的完整搭建过程，小伙伴们就可以欢快地开始使用Hive了。

2.Hive应用案例

本案例对视频网站的数据进行各种指标分析，为管理者提供决策支持。

1）需求描述

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

• 统计视频观看数Top10
• 统计视频类别热度Top10
• 统计视频观看数Top20所属类别
• 统计视频观看数Top50所关联视频的所属类别Rank
• 统计每个类别中的视频热度Top10
• 统计每个类别中视频流量Top10
• 统计上传视频最多的用户Top10以及他们上传的视频
• 统计每个类别视频观看数Top10

2）项目表字段

视频表

用户表

ETL原始数据

通过观察原始数据形式，可以发现，视频可以有多个所属分类，每个所属分类用 & 符号分割，且分割的两边有空格字符，同时相关视频也是可以有多个元素，多个相关视频又用 \t 进行分割。

为了分析数据时方便对存在多个子元素的数据进行操作，首先进行数据重组清洗操作。即：将所有的类别用 & 分割，同时去掉两边空格，多个相关视频 id 也使用 & 进行分割。

核心要做三件事情：

• 长度不够9的删掉
• 视频类别删掉空格
• 该相关视频的分割符

3）准备工作

（1）创建 Hive 表

创建表：youtubevideo_ori，youtubevideo_user_ori
创建表：youtubevideo_orc，youtubevideo_user_orc

--创建: youtubevideo_ori表
create table youtubevideo_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;

--创建 youtubevideo_user_ori表:
create table youtubevideo_user_ori(
    uploader string,
    videos int,
    friends int)
row format delimited
fields terminated by "\t" 
stored as textfile;

--创建 youtubevideo_orc表:
create table youtubevideo_orc(
    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 orc;

--创建 youtubevideo_user_orc表:
create table youtubevideo_user_orc(
    uploader string,
    videos int,
    friends int)
row format delimited
fields terminated by "\t" 
stored as orc;
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

（2）导入ETL后的数据

youtubevideo_ori：

load data inpath "/output/youtube_video" into table youtubevideo_ori;
1

youtubevideo_user_ori：

load data inpath "/youtube_video/user" into table youtubevideo_user_ori;
1

（3）向ORC表插入数据

youtubevideo_orc：

insert overwrite table youtubevideo_orc select * from youtubevideo_ori;
1

youtubevideo_user_orc：

insert into table youtubevideo_user_orc select * from youtubevideo_user_ori;
1

3.业务分析

1）统计视频观看数 top10

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

SELECT   videoid,
         uploader,
         age,
         category,
         length,
         views,
         rate,
         ratings,
         comments
FROM     youtubevideo_orc
ORDER BY views DESC limit 10;

-- 方式2SELECT *

FROM  (
                SELECT   videoid ,
                         age,
                         category,
                         length,
                         views,
                         Row_number() OVER( ORDER BY views DESC) AS rn
                FROM     youtubevideo_orc )t
WHERE  t.rn <= 10;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2）统计视频类别热度Top10

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

• ① 需要按照类别group by聚合，然后count组内的videoId个数即可。
• ② 因为当前表结构为：一个视频对应一个或多个类别。所以如果要group by类别，需要先将类别进行列转行(展开)，然后再进行count即可。
• ③ 最后按照热度排序，显示前10条。

SELECT   category_name     AS category,
         Count(t1.videoid) AS hot
FROM     (
                SELECT videoid,
                       category_name
                FROM   youtubevideo_orc lateral view explode(category) t_catetory as category_name) t1
GROUP BY t1.category_name
ORDER BY hot DESC limit 10;
1
2
3
4
5
6
7
8

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

思路:

• ① 先找到观看数最高的20个视频所属条目的所有信息，降序排列
• ② 把这20条信息中的category分裂出来(列转行)
• ③ 最后查询视频分类名称和该分类下有多少个Top20的视频

SELECT   category_name     AS category,
         Count(t2.videoid) AS hot_with_views
FROM     (
                SELECT videoid,
                       category_name
                FROM   (
                                SELECT   *
                                FROM     youtubevideo_orc
                                ORDER BY views DESC limit 20) t1 lateral VIEW explode(category) t_catetory as category_name) t2
GROUP BY category_name
ORDER BY hot_with_views DESC;
1
2
3
4
5
6
7
8
9
10
11

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

思路：

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

--创建表类别表：
CREATE TABLE youtubevideo_category
             (
                          videoid STRING,
                          uploader STRING,
                          age INT,
                          categoryid 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 orc;

--向类别表中插入数据：
INSERT INTO table youtubevideo_category
SELECT videoid,
       uploader,
       age,
       categoryid,
       length,
       views,
       rate,
       ratings,
       comments,
       relatedid
FROM   youtubevideo_orc lateral view explode(category) catetory AS categoryid;

--统计Music类别的Top10（也可以统计其他）
SELECT   videoid,
         views
FROM     youtubevideo_category
WHERE    categoryid = "Music"
ORDER BY views DESC limit 10;

-- 方式2SELECT *
FROM  (
                SELECT   videoid ,
                         age,
                         categoryid,
                         length,
                         views,
                         Row_number() OVER( ORDER BY views DESC) AS rn
                FROM     youtubevideo_category
                WHERE    categoryid = "music" )t
WHERE  t.rn <= 10;
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

5）统计每个类别中视频流量Top10

思路：

• ① 创建视频类别展开表（categoryId列转行后的表）
• ② 按照ratings排序即可

SELECT *
FROM  (SELECT videoid,
              age,
              categoryid,
              length,
              ratings,
              Row_number()
                OVER(
                  partition BY categoryid
                  ORDER BY ratings DESC) AS rn
       FROM   youtubevideo_category)t
WHERE  t.rn <= 10; 
1
2
3
4
5
6
7
8
9
10
11
12

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

思路:

• ① 先找到上传视频最多的10个用户的用户信息
• ② 通过uploader字段与youtubevideo_orc表进行join，得到的信息按照views观看次数进行排序即可。

--第一步:
SELECT *
FROM   youtubevideo_user_orc
ORDER  BY videos DESC
LIMIT  10;

--第二步:
SELECT t2.videoid,
       t2.uploader,
       t2.views,
       t2.ratings,
       t1.videos,
       t1.friends
FROM   (SELECT *
        FROM   youtubevideo_user_orc
        ORDER  BY videos DESC
        LIMIT  10) t1
       JOIN youtubevideo_orc t2
         ON t1.uploader = t2.uploader
ORDER  BY views DESC
LIMIT  20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

【大数据技术与处理】推荐阅读

• 图解大数据 | 大数据生态与应用导论
• 图解大数据 | 分布式平台Hadoop与Map-Reduce详解
• 图解大数据 | Hadoop系统搭建与环境配置@实操案例
• 图解大数据 | 应用Map-Reduce进行大数据统计@实操案例
• 图解大数据 | Hive搭建与应用@实操案例
• 图解大数据 | Hive与HBase详解@海量数据库查询
• 图解大数据 | 大数据分析挖掘框架@Spark初步
• 图解大数据 | 基于RDD大数据处理分析@Spark操作
• 图解大数据 | 基于Dataframe / SQL大数据处理分析@Spark操作
• 图解大数据 | 使用Spark分析新冠肺炎疫情数据@综合案例
• 图解大数据 | 使用Spark分析挖掘零售交易数据@综合案例
• 图解大数据 | 使用Spark分析挖掘音乐专辑数据@综合案例
• 图解大数据 | Spark Streaming @流式数据处理
• 图解大数据 | 工作流与特征工程@Spark机器学习
• 图解大数据 | 建模与超参调优@Spark机器学习
• 图解大数据 | GraphFrames @基于图的数据分析挖掘

ShowMeAI 系列教程推荐

• 大厂技术实现：推荐与广告计算解决方案
• 大厂技术实现：计算机视觉解决方案
• 大厂技术实现：自然语言处理行业解决方案
• 图解Python编程：从入门到精通系列教程
• 图解数据分析：从入门到精通系列教程
• 图解AI数学基础：从入门到精通系列教程
• 图解大数据技术：从入门到精通系列教程
• 图解机器学习算法：从入门到精通系列教程
• 机器学习实战：手把手教你玩转机器学习系列
• 深度学习教程：吴恩达专项课程 · 全套笔记解读
• 自然语言处理教程：斯坦福CS224n课程 · 课程带学与全套笔记解读