三、利用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相关进程是否已经启动

view plain "copy

  1. jps  

若未启动，则启动hadoop：

view plain "copy

  1. cd /apps/hadoop/sbin  

view plain "copy

  1. ./start-all.sh  

再次输入JPS查看进程：

view plain "copy

  1. jps  

2.启动MySQL服务(数据库密码为：zhangyu)

view plain "copy

  1. sudo service mysql start  

3.切换到/data目录，并创建名为edu3的目录

view plain "copy

  1. cd /data  

view plain "copy

  1. mkdir edu3  

再切换到/data/edu3目录下，并使用wget命令下载本次实验使用的数据：

view plain "copy

  1. cd /data/edu3  

view plain "copy

  1. wget http://59.64.78.41:60000/allfiles/edu3/pinglun  

4.执行命令，启动Hive

view plain "copy

  1. hive  

在Hive中创建edu3数据仓库，并切换到edu3下。

view plain "copy

  1. create database edu3;  
  2. use edu3;  

5.在Hive创建一张表，用于存放清洗后的数据，表名为pinglun，字段名、字符类型、字段解释如下：

view plain "copy

  1. productid        string  产品ID  
  2. commentcount     int     评论数  
  3. goodcount        int     好评数  
  4. generalcount     int     中评数  
  5. poorcount        int     差评数  
  6. goodrateshow     float   好评率  
  7. generalrateshow  float   中评率  
  8. poorrateshow     float   差评率  
  9. guid             string  随机生成ID  
  10. content          string  评论内容  
  11. creationtime     string  写评论的时间  
  12. score            int     打分  
  13. nickname         string  昵称  
  14. userlevelname    string  会员级别  
  15. userclientshow   string  评论设备  
  16. ismobile         string  是否移动端  
  17. days             int     评论时间距【收货/下单】时间多长时间  

6.在Hive中创建内部表：

view plain "copy

  1. create table pinglun (  
  2. productid       string,  
  3. commentcount    int,  
  4. goodcount       int,  
  5. generalcount    int,  
  6. poorcount       int,  
  7. goodrateshow    float,  
  8. generalrateshow float,  
  9. poorrateshow    float,  
  10. guid            string,  
  11. content         string,  
  12. creationtime    string,  
  13. score           int,  
  14. nickname        string,  
  15. userlevelname   string,  
  16. userclientshow  string,  
  17. ismobile        string,  
  18. days            int  
  19. )  row format delimited  
  20. fields terminated by '\t';  

创建成功后，查看pinglun表的表结构：

view plain "copy

  1. desc pinglun;  

当然，也可以创建外部表：

view plain "copy

  1. create external table if not exists pinglunwb (  
  2. productid       string,  
  3. commentcount    int,  
  4. goodcount       int,  
  5. generalcount    int,  
  6. poorcount       int,  
  7. goodrateshow    float,  
  8. generalrateshow float,  
  9. poorrateshow    float,  
  10. guid            string,  
  11. content         string,  
  12. creationtime    string,  
  13. score           int,  
  14. nickname        string,  
  15. userlevelname   string,  
  16. userclientshow  string,  
  17. ismobile        string,  
  18. days            int  
  19. )  row format delimited  
  20. fields terminated by '\t'  
  21. location '/myedu2/out/1';  

外部表的创建方法比内部表多了一个external，同时还加上了if判断，判断创建表之前，是否存在同样名称表。

Hive创建内部表时，会将数据移动到数据仓库指向的路径；创建外部表时，仅记录数据所在的路径， 不对数据的位置做任何改变。

在删除表的时候，内部表的元数据和数据会被一起删除， 而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据，生产中常使用外部表。

7.表设计好以后，在Hive端使用load命令，将/data/edu3下的pinglun导入Hive表中。

view plain "copy

  1. load data local inpath '/data/edu3/pinglun' into table pinglun;  

在hive中，执行查询操作，验证数据是否导入成功。

view plain "copy

  1. select * from pinglun limit 10;  

查看数据条数

view plain "copy

  1. select count(1) as num from pinglun;  

4.需求1：分析用户使用移动端购买还是PC端购买，及移动端和PC端的用户比例

SQL语句：

view plain "copy

  1. select  
  2.   case  
  3.     when ismobile='true' then 1  
  4.     when  ismobile='false' then 0  
  5.   end as ismobile,  
  6.   count(1) as num  
  7. from pinglun  
  8. group by ismobile;  

其中1代表移动端购买，共816人，0代表PC端购买，共174人。

5.需求2：分析用户评论周期（收到货后，一般多久进行评论）

SQL语句：

view plain "copy

  1. select  
  2.  days,  
  3.  count(1) as num  
  4. from pinglun  
  5. group by days  
  6. order by num desc;  

通过分析结果，我们可以清楚地了解到收到货后第一天，评论119人，第二天2天评论114人，3天评论的有90人，等等...

6.需求3：分析会员级别（判断购买此商品的用户级别）

SQL语句：

view plain "copy

  1. select  
  2.   userlevelname,  
  3.   count(1) as num  
  4. from pinglun  
  5. group by userlevelname  
  6. order by num desc;  

通过分析结果，我们可以看到购买用户会员级别为银牌的最多，有419人，金牌会员有223人，钻石会员125人等。

具体数值，会根据采集来的数据的变化而变化。时间和商品不同，会导致数值有波动。

7.需求4：分析每天评论量

SQL语句：

view plain "copy

  1. select  
  2.   substr(creationtime, 0, 10) as dt,  
  3.   count(1) as num  
  4. from pinglun  
  5. group by substr(creationtime, 0, 10)  
  6. 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包

view plain "copy

  1. cd /data/edu3  

view plain "copy

  1. wget http://59.64.78.41:60000/allfiles/edu3/hive-udf-libs.tar.gz  

将hive-udf-libs.tar.gz进行解压，并查看解压后的/data/edu3目录：

view plain "copy

  1. tar -zxvf hive-udf-libs.tar.gz  
  2. 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函数即可。

view plain "copy

  1. package my.udf;  
  2. import org.apache.hadoop.hive.ql.exec.UDF;  
  3. public class ParseDate extends UDF {  
  4.     public String evaluate(String createiontime){  
  5.         return null;  
  6.     }  
  7.   
  8. }  

在这里evaluate函数，要实现的功能，就是对“2017-06-20 17:09:37”格式的日期数据，进行处理最终只保留日期部分“2017-06-20”。

view plain "copy

  1. public String evaluate(String createiontime) throws ParseException{  
  2.     DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");  
  3.     Date dt = dateFormat.parse( createiontime );  
  4.     return dateFormat.format(dt);  
  5.   
  6. }  

上面这段日期转换的代码很简单，我们也可以放到main函数中，进行测试

view plain "copy

  1. public static void main(String[] args) throws ParseException {  
  2.     String dtString = "2017-1-1 12:10:10";  
  3.     DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");  
  4.     Date dt = dateFormat.parse(dtString);  
  5.     String result = dateFormat.format(dt);  
  6.     System.out.println(result);  
  7. }  

执行结果，最终会得到2017-01-01这样的结果

UDF的完整代码如下：

view plain "copy

  1. package my.udf;  
  2. import java.text.DateFormat;  
  3. import java.text.ParseException;  
  4. import java.text.SimpleDateFormat;  
  5. import java.util.Date;  
  6. import org.apache.hadoop.hive.ql.exec.UDF;  
  7. public class ParseDate extends UDF {  
  8.   
  9.     public String evaluate(String createiontime) throws ParseException{  
  10.   
  11.         DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");  
  12.         Date dt = dateFormat.parse( createiontime );  
  13.         return dateFormat.format(dt);  
  14.   
  15.     }  
  16. }  

6.将我们自定义的UDF打包成Jar文件。右键类文件，点击Export

在弹出框中输入jar，在列出的可选项中，选择JAR file

在弹出的窗口中，点击Browser，设置要导出jar文件的位置

将Jar文件导出到/data/edu3

7.执行测试。

重启hive命令行终端，在命令行下，直接输入命令

view plain "copy

  1. add jar /data/edu3/udf.jar;  

这句话的意思是，将编写的自定义函数分发到集群中去。这种方式不用改变集群环境。

输入命令，创建临时方法

view plain "copy

  1. create temporary function udf as 'my.udf.ParseDate';  

再执行需求4中查询命令

view plain "copy

  1. use edu3;  
  2. select  
  3.   udf(creationtime) as dt,  
  4.   count(1) as num  
  5. from pinglun  
  6. group by udf(creationtime)  
  7. order by num desc;  

执行测试后，可在窗口看到输出结果。至此实验完毕。

# hadoop # hive # 大数据
Logo
永洪数据分析社区

永洪科技，致力于打造全球领先的数据技术厂商，具备从数据应用方案咨询、BI、AIGC智能分析、数字孪生、数据资产、数据治理、数据实施的端到端大数据价值服务能力。

更多推荐

cover

Archery 系统配置 SQL优化审核平台配置【详解】

avatar 永洪数据分析社区
cover

举个栗子！Tableau 技巧（249）：为文本表中的不同度量设置不同颜色

avatar 永洪数据分析社区
cover

毕业设计：基于python渔业数据可视化分析系统 Flask框架 海洋捕捞品类 全国渔业 渔业加工（源码+文档）✅

avatar 永洪数据分析社区