Hive案例之成绩统计

需求

​ 创建classrecord.txt文件。其中数据如下：

序号 班级 总分
1 1603A 95 
2 1603B 85
3 1603C 75  
4 1603D 96 
5 1604F 94  
6 1604E 95 
7 1604K 91  
8 1604G 89 
9 1501A 79 
10 1502A 69 
11 1503A 59 
12 1504A 89  
13 1701A 99 
14 1702A 100 
15 1703A 65

• 创建Hive分区表表名为classrecord导入数据
• 将以上信息导入到Hive表中
• 求总分前三名
• 求每一届的前三名（16xx为16届，15xx为15届，17xx为17届）
• 创建Hive自定义函数，功能是划定班级类型。
  • 85-100：优秀班级
  • 75-84：良好班级
  • 60-74：及格班级
  • 其余为不合格

实现

• 创建Hive分区表表名为classrecord导入数据

  • 创建普通表加载数据

    create table classrecord_tmp (id int, classname string, score int) row format delimited fields terminated by ' ' stored as textfile tblproperties('skip.header.line.count'='1');
    

    load data local inpath '/home/test/hive-2.3.7/data/classrecord.txt' into table classrecord_tmp;
    

  • 创建分区表并动态插入数据

    create table classrecord (id int, classname string, score int) partitioned by (dt string) row format delimited fields terminated by ' ';
    

• 将以上信息导入到Hive表中

  insert into classrecord partition(dt) select *,substr(classname,0,2) dt from classrecord_tmp;
  

• 求总分前三名

  select * from classrecord order by score desc limit 3;
  

• 求每一届的前三名（16xx为16届，15xx为15届，17xx为17届）

  select * from (select *,row_number() over(partition by dt order by score desc) rank from classrecord)t where t.rank<=3;
  

• 创建Hive自定义函数，功能是划定班级类型

  • 导入依赖包

    <dependencies>
    	<dependency>
    		<groupId>org.apache.hive</groupId>
    		<artifactId>hive-exec</artifactId>
    		<version>2.3.7</version>
    	</dependency>
    </dependencies>
    

  • 编写UDF类

    package com.xxx.hive.udf;
    
    import org.apache.hadoop.hive.ql.exec.UDF;
    
    public class LevelUDF extends UDF {
        public String evaluate(Integer score) {
            String level = null;
            if (score >= 85 && score <=100) {
                level = "优秀";
            } else if (score >= 75 && score < 85){
                level = "良好";
            } else if (score >= 60 && score < 75) {
                level = "及格";
            } else {
                level = "不及格";
            }
            return level;
        }
    }
    

  • 打jar包

  

  • 上传到linux

  • 注册函数（二选一）

    • 注册临时函数

      bin/hive中，执行如下语句：

      • 添加jar到classpath中(不论jar是否在hive的lib目录下，都需要执行此语句)

        add jar /home/test/hive-2.3.7/hive_demo-1.0-SNAPSHOT.jar;
        

      • 注册函数

        create temporary function get_level as 'com.xxx.hive.udf.LevelUDF';
        

      • 查看函数

        show functions;
        

      • 使用函数

        select *,get_level(score) from classrecord;
        

      • 删除函数

        drop temporary function get_level;
        

    • 注册永久函数

      • 配置conf/hive-site.xml配置文件，添加如下配置

        <property>
             <name>hive.aux.jars.path</name>
             <value>file:///home/test/hive-2.3.7/hive_demo-1.0-SNAPSHOT.jar</value>
        </property>
        

      • 在bin/hive中，注册永久函数(函数在哪个database注册，就在哪个database中使用和删除)

        create function get_level as 'com.xxx.hive.udf.LevelUDF';
        

      • 在mysql的hive数据库下FUNCS表中查看新注册的函数

        mysql -uroot -pxxxx
        
        use hive
        
        select * from FUNCS;
        

      • 使用函数

        select *,get_level(score) from classrecord;
        

      • 删除函数

        drop function get_level;