北京市政百姓信件分析实战——三、利用HiveSql语句离线分析信件内容数据

基础概述

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行。

其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。

Hive构建在基于静态批处理的Hadoop之上，由于Hadoop通常都有较高的延迟并且在作业提交和调度的时候需要大量的开销。因此，Hive并不适合那些需要低延迟的应用，它最适合应用在基于大量不可变数据的批处理作业，例如，网络日志分析。

Hive的特点是：可伸缩（在Hadoop集群上动态的添加设备）、可扩展、容错、输出格式的松散耦合。

Hive将元数据存储在关系型数据库（RDBMS）中，比如MySQL、Derby中。

 

需求概述

在本节中，我们将使用Hive对以下指标进行统计并演示：

1.需求1：分析每年的信件数量

2.需求2：分析信件是属于投诉还是属于咨询，及投诉和咨询的数量

3.需求3：分析对信件进行回答的政府部门5.自定义UDF，区分信件时间的月份

4.使用UDF

初始化操作

1.首先，使用jps查看hadoop相关进程是否已经启动

1. jps  

若未启动，则启动hadoop：

1. cd /apps/hadoop/sbin  
2. ./start-all.sh  

 

再次输入JPS查看进程：

1. jps  

 

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

1. sudo service mysql start  

 

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

1. cd /data  
2. mkdir edu3  

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

1. cd /data/edu3  
2. wget http://192.168.1.100:60000/allfiles/second/edu3/govdata  

4.执行命令，启动Hiveshow

1. hive  

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

1. create database edu3;  
2. use edu3;  

 

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

1. leixing        string          类型  
2. biaoti         string          标题  
3. laixinren      string          来信人  
4. shijian        string          时间  
5. number         int             网友同问或者网友评论数  
6. problem        string          信件类容  
7. offic          string          官方回答部门  
8. officpt        string          官方回答时间  
9. officp         string          官方回答内容  

6.在Hive中创建内部表：

1. create table govdata(  
2.   biaoti         string,  
3.   laixinren      string,  
4.   shijian        string,  
5.   number         int,  
6.   problem        string,  
7.   offic          string,  
8.   officpt        string,  
9.   officp         string  
10. )  row format delimited  
11. fields terminated by '\t';  

 

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

 

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

1. create external table if not exists govdata(  
2.   leixing        string,  
3.   biaoti         string,  
4.   laixinren      string,  
5.   shijian        string,  
6.   number         int,  
7.   problem        string,  
8.   offic          string,  
9.   officpt        string,  
10.   officp         string  
11. )  row format delimited  
12. fields terminated by '\t';  

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

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

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

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

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

 

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

view plain copy

1. select * from govdata limit 10;  

 

查看数据条数

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

 

需求1：分析每年的信件数量

SQL语句：

1. select  
2.  substr(shijian,0,4) as dt,  
3.  count(1) as num  
4. from govdata  
5. group by substr(shijian,0,4);  

 

由结果分析，可得近10年中，2014年群众的举报信件最多，2012年最少。

其中2015年数据从5月开始采集到12月，5月数据不完整；2017年数据采集时间是1月到8月，8月数据不完整。如下所示：

 

 

需求2：分析信件是属于投诉还是属于咨询，及投诉和咨询的数量

SQL语句：

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

 

通过分析结果，我们可以清楚地了解到信件内容属于投诉举报的有7514条，属于咨询问答的有15407条，信件内容大多属于投诉举报。

需求3：分析对信件进行回答的政府部门

SQL语句：

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

结果部分截图如下：

 

通过分析，我们可以得出看出市人力社保局接受的群众信件数量最多。

使用UDF

1.在需求1中，对日期的处理，我们使用了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包

1. cd /data/edu3  
2. wget http://192.168.1.100:60000/allfiles/second/edu3/hive-udf-libs.tar.gz  

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

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函数即可。

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

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

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

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

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

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

 

UDF的完整代码如下：

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

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

 

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

 

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

 

将Jar文件导出到/data/edu3

 

7.执行测试。运行UDF测试有两种方式。

第一种方式：

将导出的udf.jar拷贝到/apps/hive/lib目录下

1. cp /data/edu3/udf.jar /apps/hive/lib  

重启hive命令行，在hive命令行下，创建临时function

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

再执行需求1中查询命令

1. use edu3;  
2. select  
3.  substr(shijian,0,4) as dt,  
4.  count(1) as num  
5. from govdata  
6. group by substr(shijian,0,4);  

第二种方式：

先删除第一种方式中，/apps/hive/lib目录下的udf.jar文件

1. rm -rf /apps/hive/lib/udf.jar  

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

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

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

剩余步骤与第一种方式相同。

直接输入命令，创建临时方法

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

再执行需求1中查询命令

1. use edu3;  
2. select  
3.  substr(shijian,0,4) as dt,  
4.  count(1) as num  
5. from govdata  
6. group by substr(shijian,0,4);  

执行测试后，两者结果相同。至此实验完毕。

将项目命名为myudf3。

选中项目名myudf3，右键，依次点击New=》Package，创建包，

将包命名为my.udf

选中包my.udf，右键依次点击New=》Class创建类

将类命名为ParseDate

选中项目名myudf3，右键依次点击New=>Folder，创建目录，并将目录命名为libs。用于存放项目所依赖的jar文件

最终项目框架，如下：

3.切换目录到/data/edu3目录下，使用wget命令下载实验所需的jar包

1.  cd /data/edu3  

2.  wget http://192.168.1.100:60000/allfiles/second/edu3/hive-udf-libs.tar.gz  

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

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。