三、利用HiveSQL离线分析评论数据
1.基础概述
Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行。
其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。
Hive构建在基于静态批处理的Hadoop之上，由于Hadoop通常都有较高的延迟并且在作业提交和调度的时候需要大量的开销。因此，Hive并不适合那些需要低延迟的应用，它最适合应用在基于大量不可变数据的批处理作业，例如，网络日志分析。
Hive的特点是：可伸缩（在Hadoop集群上动态的添加设备）、可扩展、容错、输出格式的松散耦合。
Hive将元数据存储在关系型数据库（RDBMS）中，比如MySQL、Derby中。
2.需求概述
在本节中，我们将使用Hive对以下指标进行统计并演示：
1.移动端和PC端，用户比例
2.用户评论周期（收到货后，一般多久进行评论）
3.会员级别统计（判断购买此商品的用户级别）
4.每天评论量（大体能反映出下单时间）
5.自定义UDF，功能为：去掉评论时间的时分秒，只保留年月日
3.初始化操作
1.首先，使用jps查看hadoop相关进程是否已经启动
- jps
若未启动，则启动hadoop：
- cd /apps/hadoop/sbin
- ./start-all.sh
再次输入JPS查看进程：
- jps
2.启动MySQL服务(数据库密码为：zhangyu)
- sudo service mysql start
3.切换到/data目录，并创建名为edu3的目录
- cd /data
- mkdir edu3
再切换到/data/edu3目录下，并使用wget命令下载本次实验使用的数据：
- cd /data/edu3
- wget http://59.64.78.41:60000/allfiles/edu3/pinglun
4.执行命令，启动Hive
- hive
在Hive中创建edu3数据仓库，并切换到edu3下。
- create database edu3;
- use edu3;
5.在Hive创建一张表，用于存放清洗后的数据，表名为pinglun，字段名、字符类型、字段解释如下：
- productid string 产品ID
- commentcount int 评论数
- goodcount int 好评数
- generalcount int 中评数
- poorcount int 差评数
- goodrateshow float 好评率
- generalrateshow float 中评率
- poorrateshow float 差评率
- guid string 随机生成ID
- content string 评论内容
- creationtime string 写评论的时间
- score int 打分
- nickname string 昵称
- userlevelname string 会员级别
- userclientshow string 评论设备
- ismobile string 是否移动端
- days int 评论时间距【收货/下单】时间多长时间
6.在Hive中创建内部表：
- create table pinglun (
- productid string,
- commentcount int,
- goodcount int,
- generalcount int,
- poorcount int,
- goodrateshow float,
- generalrateshow float,
- poorrateshow float,
- guid string,
- content string,
- creationtime string,
- score int,
- nickname string,
- userlevelname string,
- userclientshow string,
- ismobile string,
- days int
- ) row format delimited
- fields terminated by '\t';
创建成功后，查看pinglun表的表结构：
- desc pinglun;
当然，也可以创建外部表：
- create external table if not exists pinglunwb (
- productid string,
- commentcount int,
- goodcount int,
- generalcount int,
- poorcount int,
- goodrateshow float,
- generalrateshow float,
- poorrateshow float,
- guid string,
- content string,
- creationtime string,
- score int,
- nickname string,
- userlevelname string,
- userclientshow string,
- ismobile string,
- days int
- ) row format delimited
- fields terminated by '\t'
- location '/myedu2/out/1';
外部表的创建方法比内部表多了一个external，同时还加上了if判断，判断创建表之前，是否存在同样名称表。
Hive创建内部表时，会将数据移动到数据仓库指向的路径；创建外部表时，仅记录数据所在的路径， 不对数据的位置做任何改变。
在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据，生产中常使用外部表。
7.表设计好以后，在Hive端使用load命令，将/data/edu3下的pinglun导入Hive表中。
- load data local inpath '/data/edu3/pinglun' into table pinglun;
在hive中，执行查询操作，验证数据是否导入成功。
- select * from pinglun limit 10;
查看数据条数
- select count(1) as num from pinglun;
4.需求1：分析用户使用移动端购买还是PC端购买，及移动端和PC端的用户比例
SQL语句：
- select
- case
- when ismobile='true' then 1
- when ismobile='false' then 0
- end as ismobile,
- count(1) as num
- from pinglun
- group by ismobile;
其中1代表移动端购买，共816人，0代表PC端购买，共174人。
5.需求2：分析用户评论周期（收到货后，一般多久进行评论）
SQL语句：
- select
- days,
- count(1) as num
- from pinglun
- group by days
- order by num desc;
通过分析结果，我们可以清楚地了解到收到货后第一天，评论119人，第二天2天评论114人，3天评论的有90人，等等...
6.需求3：分析会员级别（判断购买此商品的用户级别）
SQL语句：
- select
- userlevelname,
- count(1) as num
- from pinglun
- group by userlevelname
- order by num desc;
通过分析结果，我们可以看到购买用户会员级别为银牌的最多，有419人，金牌会员有223人，钻石会员125人等。
具体数值，会根据采集来的数据的变化而变化。时间和商品不同，会导致数值有波动。
7.需求4：分析每天评论量
SQL语句：
- select
- substr(creationtime, 0, 10) as dt,
- count(1) as num
- from pinglun
- group by substr(creationtime, 0, 10)
- order by num desc;
通过分析，我们可以看到每天评论数
8.使用UDF
1.在需求4中，对日期的处理，我们使用了Hive中自带的，截取字符串的函数substr。有时这些函数功能较弱，需要增强。所以我们可以进行自定义。下面编写自定义函数，执行数据处理。这种函数叫UDF（User Defined Function）
下面，使用另一种方式，来处理需求4中的日期。
2.打开eclipse，创建Java项目
将项目命名为myudf3。
选中项目名myudf3，右键，依次点击New=》Package，创建包，
将包命名为my.udf
选中包my.udf，右键依次点击New=》Class创建类
将类命名为ParseDate
选中项目名myudf3，右键依次点击New=>Folder，创建目录，并将目录命名为libs。用于存放项目所依赖的jar文件
最终项目框架，如下：
3.打开桌面终端模拟器，进入命令行，切换目录到/data/edu3目录下，使用wget命令下载实验所需的jar包
- cd /data/edu3
- wget http://59.64.78.41:60000/allfiles/edu3/hive-udf-libs.tar.gz
将hive-udf-libs.tar.gz进行解压，并查看解压后的/data/edu3目录：
- tar -zxvf hive-udf-libs.tar.gz
- ls /data/edu3
4.将/data/edu3/hive-udf-libs目录下所有jar包，导入拷贝到myudf3项目的libs目录下
点击OK
导入后，选中lib中的所有文件，单击右键并依次选择Build Path=>Add to Build Path。
5.编写ParseData类中编写代码，实现UDF。要想自定义函数，需要使ParseData类继承UDF类，并重构evaluate函数即可。
- package my.udf;
- import org.apache.hadoop.hive.ql.exec.UDF;
- public class ParseDate extends UDF {
- public String evaluate(String createiontime){
- return null;
- }
- }
在这里evaluate函数，要实现的功能，就是对“2017-06-20 17:09:37”格式的日期数据，进行处理最终只保留日期部分“2017-06-20”。
- public String evaluate(String createiontime) throws ParseException{
- DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
- Date dt = dateFormat.parse( createiontime );
- return dateFormat.format(dt);
- }
上面这段日期转换的代码很简单，我们也可以放到main函数中，进行测试
- public static void main(String[] args) throws ParseException {
- String dtString = "2017-1-1 12:10:10";
- DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
- Date dt = dateFormat.parse(dtString);
- String result = dateFormat.format(dt);
- System.out.println(result);
- }
执行结果，最终会得到2017-01-01这样的结果
UDF的完整代码如下：
- package my.udf;
- import java.text.DateFormat;
- import java.text.ParseException;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import org.apache.hadoop.hive.ql.exec.UDF;
- public class ParseDate extends UDF {
- public String evaluate(String createiontime) throws ParseException{
- DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
- Date dt = dateFormat.parse( createiontime );
- return dateFormat.format(dt);
- }
- }
6.将我们自定义的UDF打包成Jar文件。右键类文件，点击Export
在弹出框中输入jar，在列出的可选项中，选择JAR file
在弹出的窗口中，点击Browser，设置要导出jar文件的位置
将Jar文件导出到/data/edu3
7.执行测试。
重启hive命令行终端，在命令行下，直接输入命令
- add jar /data/edu3/udf.jar;
这句话的意思是，将编写的自定义函数分发到集群中去。这种方式不用改变集群环境。
输入命令，创建临时方法
- create temporary function udf as 'my.udf.ParseDate';
再执行需求4中查询命令
- use edu3;
- select
- udf(creationtime) as dt,
- count(1) as num
- from pinglun
- group by udf(creationtime)
- order by num desc;
执行测试后，可在窗口看到输出结果。至此实验完毕。
414
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
