实验手册——搜狗搜索日志分析系统
文章目录
Hive简介
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-z6e4RY23-1637898881632)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122210140207.png)]
上图是来自官网的一段话,大概意思就是Hive是基于Hadoop的一种数据仓库,Hive能够对数据进行数据汇总、临时查询和分析大量的数据,它也提供了一种类似于SQL的语言可以更方便使用Hive,但是Hive并不能像流计算一样为在线交易处理而设计的, 它最好用于传统的数据仓库任务。
总而言之,直接使用Hadoop所面临的问题,人员学习成本太高,MapReduce实现复杂查询逻辑开发难度太大,而我们使用Hive具有操作接口采用类SQL语法,提供快速开发的能力的优点。
Hive的数据单元
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ynVvgKBk-1637898881636)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122212057355.png)]
- 数据库
表,视图,分区存放的地方。 - 表
就传统意义上的关系表,官网上给了一个例子,有一个page_views表,里面有timestamp(对应于页面被浏览的UNIX时间戳),userid(用于识别浏览该页面的用户),page_url(用户点击的网址的排名),referer_url(用户点击的网址的URL),IP(URL的IP地址) - 区分表
一般在Select查询中一般会扫描整个表内容,这个的时间开销很大。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partitions概念,官网上的解释是每个表可以有一个或多个分区键,决定了数据的存储方式。分区除了作为存储单元还允许用户识别满足特定标准的行。就比如,我们可以对上述的表为来自每个2009年12月23日的数据创建一个分区,这样你可以只在表的相关分区上运行该查询,从而大大加快了分析速度。需要注意的是分区只是一个虚拟的字段,只表示存在该分区,而是在加载的时候生成。 - 桶表
对于每一个表或者分区, Hive可以进一步组织成桶,Hive也是 针对某一列的哈希函数值进行桶的组织。有了桶表我们便可以获得更高的查询处理效率,比如JOIN操作,我们可以将保存相同列值的桶进行JOIN操作就可以,可以大大减少JOIN的数据量。
实验环境搭建
建议使用16.0.4的Ubuntu系统,过高的版本可能会导致MySQL无法和Hive连接
Hive
解压压缩包
sudo tar -zxvf ./apache-hive-3.1.2-bin.tar.gz -C /usr/local
cd /usr/local/
sudo mv apache-hive-3.1.2-bin hive
sudo chown -R hadoop:hadoop hive
配置环境变量
vim ~/.bashrc
在文件最前面一行添加如下内容
export HIVE_HOME=/usr/local/hive
export PATH=$PATH:$HIVE_HOME/bin
export HADOOP_HOME=/usr/local/hadoop
保存退出后,运行如下命令使配置立即生效:
source ~/.bashrc
创建配置文件
- 将hive-default.xml.template重命名为hive-default.xml
cd /usr/local/hive/conf
mv hive-default.xml.template hive-default.xml
- 使用vim编辑器新建一个配置文件hive-site.xml
cd /usr/local/hive/conf
vim hive-site.xml
- 在hive-site.xml中添加如下配置信息:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name> # 本机或远程连接hive数据库
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value> # 用户名
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hive</value> # 用户的密码
<description>password to use against metastore database</description>
</property>
</configuration>
MySQL
mysql安装
- 使用以下命令即可进行mysql安装,注意安装前先更新一下软件源以获得最新版本:
sudo apt-get update
sudo apt-get install mysql-server
- 启动mysql服务器
service mysql start
部分系统可能并不自带netstat工具,通过以下命令安装
sudo apt install net-tools
sudo netstat -tap | grep mysql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dMvTl1zb-1637898881638)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122175044877.png)]
出现上图实例,则说明已经启动成功。
配置MySQL
- 进入mysql shell界面:
mysql -u root -p
- 创建待连接的数据库
create database hive;
- 配置mysql允许hive接入:
grant all on *.* to hive@localhost identified by 'hive';
flush privileges;
注意如果采用更高版本的Mysql该语句会报错,应该先创建用户,再进行授权,如下:
CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hive';
GRANT ALL ON *.* TO 'hive'@'localhost';
启动Hive
启动Hadoop
启动hive之前,首先先启动hadoop集群,然后使用hive命令启动Hive。
cd /usr/local/hadoop
start-dfs.sh
# 启动hive
hive
常见错误
错误1
启动Hive时,报错
java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument
原因
这是因为Hive内依赖的guava.jar和hadoop内的版本不一致造成的。
解决办法
查看Hadoop安装目录下share/hadoop/common/lib内guava.jar版本,查看Hive安装目录下lib内guava.jar的版本,如果两者不一致,删除版本低的,并拷贝高版本的解决问题。
错误2
运行MySQL语句时,报错
MetaException message:Hive metastore database is not initialized.
原因
这是由于没有初始化数据库。
解决办法
cd /usr/local/hive
./bin/schematool -dbType mysql -initSchema
交互式数据处理
百度百科上的解释为:交互式处理(interactive processing)操作人员和系统之间存在交互作用的信息处理方式。操作人员通过终端设备输入输出系统输入信息和操作命令,系统接到后立即处理,并通过终端设备显示处理结果。操作人员可以根据处理结果进一步输入信息和操作命令。
简单来说就是,老师批随堂测验,同学就像是操作人员,而老师就像是一个系统,同学答完一份卷子去找老师批改,老师批改完之后,同学可以向老师一个一个提出问题,老师也逐一解答,这个一问一答过程就叫交互式。
与此相对的有另外一个概念叫做批处理,我们继续以上一例子为例,在期末考试的时候,老师把试卷分发给同学,同学此时不能和老师有交流,只能对试卷上的每一个问题进行解答,最后交卷,此时老师就像是一个操作人员,同学是一个系统,这个答题最后交卷的过程就叫做批处理。
数据预处理
查看数据
cd /home/hadoop/sogou_data/resources
less sougou.10w.utf8
# 统计行数
wc -l sougou.10w.utf8
# 统计字数
wc -w sougou.10w.utf8
# 统计字节数
wc -c sougou.10w.utf8
其中wc
命令的功能为统计指定文件的行数,字数,字节数,结果如下:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K0hS1ztM-1637898881639)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211121204036740.png)]
数据扩展
运行下载后交互式处理下的sogou-log-extend.sh文件,为sogou.10w.utf8文件扩展年,月,日,小时字段,运行后会得到一个sogou.10w.utf8.ext文件。
./sogou-log-extend.sh sogou.10w.utf8 sogou.10w.utf8.ext
# 查看新文件
less sogou.10w.utf8.ext
数据过滤
在这10万条记录里面,有的记录不是很完整,缺少了某些字段,出于实验目的,我们将第二个或第三个字段为空的行过滤掉,运行后会得到一个新的sogou.10w.utf8.flt文件
./sogou-log-filter.sh sogou.10w.utf8 sogou.10w.utf8.flt
数据上传
我们需要将数据文件提交到HDFS,首先确保Hadoop已经启动,在HDFS创建待填目录
hadoop fs -mkdir /sogou/20211121
上传数据(包括预处理后的数据)到上述目录中
hadoop fs -put /home/hadoop/sogou_data/resources/sogou.10w.utf8 /sogou/20211121
hadoop fs -mkdir /sogou_ext/20211121
hadoop fs -put /home/hadoop/sogou_data/resources/sogou.10w.utf8.flt /sogou_ext/20211121
创建数据仓库
本章的目标是在Hive中创建数据仓库,首先确保Hadoop和MySQL服务启动,在Linux终端输入hive
启动Hive客户端
数据仓库基本命令
创建数据仓库
create database sogou;
使用数据库
use sogou;
查看数据库的表
show tables;
创建表
create external table sogou.sogou_20211121(ts string,uid string,keyword string,rank int,sorder int,url string)
Row FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as TEXTFILE location '/sogou/20211121';
查看新建表的结构
show create table sogou.sougou_20211121;
describe sogou.sogou_20211121;
删除表
drop table sogou_20211121;
创建Hive分区表
上述为练习使用,下面开始正式的实验
创建带分区的表
create external table sogou.sogou_partition(ts string,uid string,keyword string,rank int,sorder int,url string) partitioned by(year int,month int,day int,hour int)
Row FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as textfile;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QRuKlLGO-1637898881641)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113310835.png)]
- 向数据库导入数据
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table sogou.sogou_partition partition(year,month,day,hour) select * from sogou.sogou_ext_20211121;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HDa41zCY-1637898881642)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113613335.png)]
- 查询导入数据
select * from sogou.sogou_ext_20211121 limit 10;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-opZuH4JT-1637898881642)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113633292.png)]
数据分析
基本统计
- 统计总记录数
select count(*) from sogou.sogou_ext_20211121;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4SNnglK5-1637898881643)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113700264.png)]
- 统计独立uid数
select count (distinct(uid)) from sogou.sogou_ext_20211121;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QhXw4Shj-1637898881644)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113728053.png)]
- 统计非空记录数
select count(*) from sogou.sogou_ext_20211121 where keyword is not null and keyword !='';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Docs12ZC-1637898881644)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122122349211.png)]
- 关键词长度统计
select avg(a.cnt) from (select size(split(keyword,'\\s+')) as cnt from sogou.sogou_ext_20211121);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T203voD9-1637898881646)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113814414.png)]
- 频度排名(即频度最高的50个词)
select keyword,count(*) as cnt from sogou.sogou_ext_20211121 group by keyword order by cnt desc limit 50;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2eIRKH3Z-1637898881646)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122113925304.png)]
结果如下:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VjIcdnX1-1637898881647)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122114019459.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PYKE4k4a-1637898881647)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122122544047.png)]
uid分析
- 查询次数分布
select SUM(IF(uids.cnt=1,1,0)),SUM(IF(uids.cnt=2,1,0)),SUM(IF(uids.cnt=3,1,0)),SUM(IF(uids.cnt>3,1,0)) from
(select uid,count(*) as cnt from sogou.sogou_ext_20211121 group by uid) uids;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CCtPpHb1-1637898881648)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122114048414.png)]
- 平均查询次数
select sum(a.cnt)/count(a.uid) from (select uid,count(*) as cnt from sogou.sogou_ext_20211121 group by uid) a;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WdUKLhGJ-1637898881648)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122114202777.png)]
- 查询次数大于2次的用户数
select count(a.uid) from (select uid,count(*) as cnt from sogou.sogou_ext_20211121 group by uid having cnt > 2) a;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KbegZmy5-1637898881649)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122114324774.png)]
- 所有用户的个数
select count(distinct (uid)) from sogou.sogou_ext_20211121;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pwUVhpTx-1637898881649)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122123326841.png)]
设上述两项操作的结果分别为A、B,则查询次数大于2次的用户占比等于A/B。
- 查询次数大于2次的数据展示
select b.* from (select uid,count(*) as cnt from sogou.sogou_ext_20111230 group by uid having cnt > 2) a
join sogou.sogou_ext_20111230 b on a.uid=b.uid
limit 50;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7T88QnV9-1637898881649)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122123616407.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jo3mjc8l-1637898881650)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122123702920.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wHT14xU5-1637898881651)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122123717386.png)]
用户行为分析
- 点击次数与rank之间的关系(计算rank在10以内的点击次数的占比)
select count(*) from sogou.sogou_ext_20211121 where rank < 11;
select count(*) from sogou.sogou_ext_20211121;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bL2lDiuK-1637898881651)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122114500398.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z4jsOhZT-1637898881651)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122123829072.png)]
以上结果显示:用户只翻看搜索引擎返回结果的前 10 个结果,即返回结果页面的第一页。这个用户行为决定了尽管搜索
引擎返回的结果数目十分庞大,但真正可能被绝大部分用户所浏览的,只有排在最前面的很小一部分而已。
- 个性化行为分析(找到搜索过百度并且次数大于3的uid)
select uid,count(*) as cnt from sogou.sogou_ext_20211121 where keyword='百度' group by uid having cnt>3;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jux9GCoN-1637898881652)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122114535084.png)]
实时数据
- 创建临时表
create table sogou.uid_cnt(uid STRING, cnt INT) COMMENT 'This is the sogou search data of one day'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yWcvtsRH-1637898881652)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122125222732.png)]
- 插入数据
INSERT OVERWRITE TABLE sogou.uid_cnt select uid,count(*) as cnt from sogou.sogou_ext_20211121 group by
uid;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rycb7ibe-1637898881653)(C:\Users\成雷诺\AppData\Roaming\Typora\typora-user-images\image-20211122125255637.png)]
配置文件汇总
Hadoop
core-site.xml
首先,hadoop.tmp.dir 是 hadoop文件系统依赖的基本配置,很多配置路径都依赖它,它的默认位置是在 **/tmp/{KaTeX parse error: Expected 'EOF', got '}' at position 5: user}̲**下面,这是个临时目录,因此…user}**下的所有东西都会丢失
<configuration>
<property>
<name>hadoop.tmp.dir</name>
<value>file:/usr/local/hadoop/tmp</value>
<description>Abase for other temporary directories.</description>
</property>
<property>
<!-- 描述集群中NameNode结点的URI(包括协议、主机名称、端口号) -->
<name>fs.defaultFS</name>
<value>hdfs://localhost:9000</value>
</property>
</configuration>
hdfs-site.xml
<configuration>
<property>
<!--hdfs数据块的复制份数,默认3,理论上份数越多跑数速度越快,但是需要的存储空间也更多。-->
<name>dfs.replication</name>
<value>1</value>
</property>
<property>
<!--作用是存放hadoop的名称节点namenode里的metadata-->
<name>dfs.namenode.name.dir</name>
<value>file:/usr/local/hadoop/tmp/dfs/name</value>
</property>
<property>
<!--作用是存放hadoop的数据节点datanode里的多个数据块。-->
<name>dfs.datanode.data.dir</name>
<value>file:/usr/local/hadoop/tmp/dfs/data</value>
</property>
</configuration>
HBase
hbase-site.xml
<configuration>
<property>
<!--这个目录是region server的共享目录,用来持久化HBase。-->
<name>hbase.rootdir</name>
<value>hdfs://localhost:9000/hbase</value>
</property>
<property>
<!--Hbase的运行模式。false是单机模式,true是分布式模式。若为false,Hbase和Zookeeper会运行在同一个JVM里面-->
<name>hbase.cluster.distributed</name>
<value>true</value>
</property>
<property>
<!--如果你打算在本地文件系统中跑hbase,请禁掉此项-->
<name>hbase.unsafe.stream.capability.enforce</name>
<value>false</value>
</property>
</configuration>
Hive
hive-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<!---本机或远程连接hive数据库---->
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&useSSL=false</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<!---JDBC驱动位置---->
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<!---用户名---->
<value>hive</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<!---用户密码---->
<value>hive</value>
<description>password to use against metastore database</description>
</property>
</configuration>