图解大数据 | 实操案例-Hive搭建与应用案例
文章图片
作者:韩信子@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-x2 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
hive.cli.print.header
true
Whether to print the names of the columns in query output.
javax.jdo.option.ConnectionURL
jdbc:mysql://localhost:3306/hive
javax.jdo.option.ConnectionDriverName
com.mysql.cj.jdbc.Driver
javax.jdo.option.ConnectionUserName
root
javax.jdo.option.ConnectionPassword
xxxxx
hive.metastore.uris
thrift://localhost:9083
Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.
其中核心的项目解释如下:
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服务的监听地址
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-e904d50f5242Logging 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
字段 | 备注 | 详细描述 |
---|---|---|
video id | 视频唯一id | 11位字符串 |
uploader | 视频上传者 | 上传视频的用户名String |
age | 视频年龄 | 视频在平台上的整数天 |
category | 视频类别 | 上传视频指定的视频分类 |
length | 视频长度 | 整形数字标识的视频长度 |
views | 观看次数 | 视频被浏览的次数 |
rate | 视频评分 | 满分5分 |
ratings | 流量 | 视频的流量,整型数字 |
conments | 评论数 | 一个视频的整数评论数 |
related ids | 相关视频id | 相关视频的id,最多20个 |
字段 | 备注 | 字段类型 |
---|---|---|
uploader | 上传者用户名 | string |
videos | 上传视频数 | int |
friends | 朋友数量 | int |
通过观察原始数据形式,可以发现,视频可以有多个所属分类,每个所属分类用
&
符号分割,且分割的两边有空格字符,同时相关视频也是可以有多个元素,多个相关视频又用 \t
进行分割。为了分析数据时方便对存在多个子元素的数据进行操作,首先进行数据重组清洗操作。即:将所有的类别用
&
分割,同时去掉两边空格,多个相关视频 id 也使用 &
进行分割。核心要做三件事情:
- 长度不够9的删掉
- 视频类别删掉空格
- 该相关视频的分割符
创建表:
youtubevideo_ori
,youtubevideo_user_ori
创建表:
youtubevideo_orc
,youtubevideo_user_orc
--创建: youtubevideo_ori表
create table youtubevideo_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;
--创建 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,
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 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条。
SELECTvideoid,
uploader,
age,
category,
length,
views,
rate,
ratings,
comments
FROMyoutubevideo_orc
ORDER BY views DESC limit 10;
-- 方式2SELECT *FROM(
SELECTvideoid ,
age,
category,
length,
views,
Row_number() OVER( ORDER BY views DESC) AS rn
FROMyoutubevideo_orc )t
WHEREt.rn <= 10;
2)统计视频类别热度Top10 思路:即统计每个类别有多少个视频,显示出包含视频最多的前10个类别。
- ① 需要按照类别group by聚合,然后count组内的videoId个数即可。
- ② 因为当前表结构为:一个视频对应一个或多个类别。所以如果要group by类别,需要先将类别进行列转行(展开),然后再进行count即可。
- ③ 最后按照热度排序,显示前10条。
SELECTcategory_nameAS category,
Count(t1.videoid) AS hot
FROM(
SELECT videoid,
category_name
FROMyoutubevideo_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的视频
SELECTcategory_nameAS category,
Count(t2.videoid) AS hot_with_views
FROM(
SELECT videoid,
category_name
FROM(
SELECT*
FROMyoutubevideo_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,
viewsINT,
rate FLOAT,
ratingsINT,
comments INT,
relatedid ARRAY
)
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
FROMyoutubevideo_orc lateral view explode(category) catetory AS categoryid;
--统计Music类别的Top10(也可以统计其他)
SELECTvideoid,
views
FROMyoutubevideo_category
WHEREcategoryid = "Music"
ORDER BY views DESC limit 10;
-- 方式2SELECT *
FROM(
SELECTvideoid ,
age,
categoryid,
length,
views,
Row_number() OVER( ORDER BY views DESC) AS rn
FROMyoutubevideo_category
WHEREcategoryid = "music" )t
WHEREt.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
FROMyoutubevideo_category)t
WHEREt.rn <= 10;
6)统计上传视频最多的用户Top10以及他们上传的观看次数在前10的视频 思路:
- ① 先找到上传视频最多的10个用户的用户信息
- ② 通过uploader字段与youtubevideo_orc表进行join,得到的信息按照views观看次数进行排序即可。
--第一步:
SELECT *
FROMyoutubevideo_user_orc
ORDERBY videos DESC
LIMIT10;
--第二步:
SELECT t2.videoid,
t2.uploader,
t2.views,
t2.ratings,
t1.videos,
t1.friends
FROM(SELECT *
FROMyoutubevideo_user_orc
ORDERBY videos DESC
LIMIT10) t1
JOIN youtubevideo_orc t2
ON t1.uploader = t2.uploader
ORDERBY views DESC
LIMIT20;
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:基于图的数据分析挖掘
- 图解Python编程:从入门到精通系列教程
- 图解数据分析:从入门到精通系列教程
- 图解AI数学基础:从入门到精通系列教程
- 图解大数据技术:从入门到精通系列教程
文章图片
推荐阅读
- 图解大数据|图解大数据 | 海量数据库查询-Hive与HBase详解
- 图解大数据|图解大数据 | 实操案例-MapReduce大数据统计
- 数据结构|顺序表、链表
- 超级实习生|面试总结:给应届生一些找工作的基本建议,毕竟我踩坑多
- 颜值与实用性并存的数据库建模工具Chiner教程
- 云音乐曙光埋点(还原数据理想国)
- 大学学生社团小程序开发笔记一(项目背景及技术方案的选择)
- Python|Python 十大经典排序算法实现详解
- Log4j2|Log4j2 重大漏洞编译好的log4j-2.15.0.jar包下载(替换过程)
- 重构知识的供给模式 ——《数据平台》从思考到落地