作者：韩信子 @ShowMeAI
教程地址：http://www.showmeai.tech/tuto…
本文地址：http://www.showmeai.tech/arti…
申明：版权所有，转载请分割平台与作者并注明出处

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

接下来把 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}

降级 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

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

2）装置 MYSQL

hive 的元数据服务是独立部署的，它基于 mysql 保留数据。能够应用 apt 命令在 ubuntu 环境装置 oracle mysql：

apt-get install mysql-server

如果应用 mysql -h localhost -u root -p 登录时，提醒 access denied，那须要找到和删除 mysql user 表中的一条 localhost 的非凡规定：

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

接着创立 hive 数据库：

create database hive;

接着通过 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

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>

其中外围的我的项目解释如下：

• 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

执行命令：

nohup hive –service metastore &

服务将监听在 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)

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;

（2）导入 ETL 后的数据

youtubevideo_ori：

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

youtubevideo_user_ori：

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

（3）向 ORC 表插入数据

youtubevideo_orc：

insert overwrite table youtubevideo_orc select * from youtubevideo_ori;

youtubevideo_user_orc：

insert into table youtubevideo_user_orc select * from youtubevideo_user_ori;

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;

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;

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;

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;

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; 

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;

ShowMeAI 相干文章举荐

• 图解大数据 | 导论：大数据生态与利用
• 图解大数据 | 分布式平台：Hadoop 与 Map-reduce 详解
• 图解大数据 | 实操案例：Hadoop 零碎搭建与环境配置
• 图解大数据 | 实操案例：利用 map-reduce 进行大数据统计
• 图解大数据 | 实操案例：Hive 搭建与利用案例
• 图解大数据 | 海量数据库与查问：Hive 与 HBase 详解
• 图解大数据 | 大数据分析开掘框架：Spark 初步
• 图解大数据 | Spark 操作：基于 RDD 的大数据处理剖析
• 图解大数据 | Spark 操作：基于 Dataframe 与 SQL 的大数据处理剖析
• 图解大数据 | 综合案例：应用 spark 剖析美国新冠肺炎疫情数据
• 图解大数据 | 综合案例：应用 Spark 剖析开掘批发交易数据
• 图解大数据 | 综合案例：应用 Spark 剖析开掘音乐专辑数据
• 图解大数据 | 流式数据处理：Spark Streaming
• 图解大数据 | Spark 机器学习 (上)- 工作流与特色工程
• 图解大数据 | Spark 机器学习 (下)- 建模与超参调优
• 图解大数据 | Spark GraphFrames：基于图的数据分析开掘

ShowMeAI 系列教程举荐

• 图解 Python 编程：从入门到精通系列教程
• 图解数据分析：从入门到精通系列教程
• 图解 AI 数学根底：从入门到精通系列教程
• 图解大数据技术：从入门到精通系列教程