Hive之函数

函数

1. 系统内置函数

1. 查看系统自带的函数

   show functions;
   

2. 显示自带函数的用法

   desc function upper;
   

3. 详细显示自带函数的用法

   desc function extended upper;
   

2. 常用内置函数

2.1字段赋值

NVL:给值为NULL的数据赋值，它的格式是NVL(value，default_value)。如果value为空，则返回default_value，否则返回value

// 如果员工的comm为NULL，则用-1代替
select comm, nvl(comm, -1) from emp;

// 如果员工comm为NULL，则用领导id(mgr)代替
select comm, nvl(comm, mgr) from emp;

2.2 case when then else end

// 求不同部门男女各多少人
create table if not exists emp_sex (
    name string,
    dept_id string,
    sex string
)
row format delimited fields terminated by '\t';

load data local inpath
'/opt/module/hive-3.1.2/datas/emp_sex.txt'
into table emp_sex;

select
    dept_id,
    sum(case sex when '男' then 1 else 0 end ) male_count,
    sum(case sex when '女' then 1 else 0 end ) female_count
from emp_sex
group by dept_id;

2.3 行转列

1. 相关函数说明
   • concat(): 返回输入字符串拼接后的结果，支持任意个输入字符串
   • concat_ws(separator,)：特殊形式的concat()，第一个参数作为剩余参数的分隔符
   • collect_set(): 只接受基本数据类型，作用是将某字段的值进行去重
2. 数据准备

create table if not exists person_info (
    name string,
    constellation string,
    blood_type string
)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/hive-3.1.2/datas/person_info.txt' into table person_info;

3. 需求

把星座和血型一样的人归类到一起

4. 按需求查询数据

select
    t1.c_b,
    concat_ws('|', collect_set(t1.name))
from (
     select
        name,
        concat_ws(',', constellation, blood_type) c_b
     from person_info
) t1 group by t1.c_b;

2.4 列转行

1. 函数说明
   • explode()：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行
   • lateral view：用来和想类似explode这种UDTF函数联用的，lateral view会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和原表进行join来达到连接UDTF外的字段的目的
2. 数据准备

create table if not exists movie_info (
    movie string,
    category string
)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/hive-3.1.2/datas/movie_info.txt' into table movie_info;

3. 需求

将电影分类中的数据展开

4. 按需求查询数据

select
    movie,
    category_name
from 
    movie_info 
lateral view 
    explode(split(category, ',')) movie_info_tmp as category_name;

2.5 窗口函数over()

1. 相关函数说明

   • 通过partition by 关键字来对窗口分组
   • 通过order by 来对order by字段排序后的行进行开窗，只不过注意的是第一行数据的窗口大小是1，第二行数据的窗口范围是前2行，第n行的窗口范围是前n行，以此类推。如果里面没有条件，则每一行对应整张表
   • rows between 开始位置 and 结束位置
     • current row：当前行
     • n preceding: 往前n行数据
     • n following：往后n行数据
     • unbounded：起点，
       • unbounded preceding：表示该窗口最前面的行
       • unbounded following：表示该窗口最后面的行
   • lag(col,n,default_val)：往前第n行数据
   • lead(col,n,default_val): 往后第n行数据
   • ntile(n): 把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号

2. 数据准备

create table if not exists business (
    name string,
    orderdate string,
    cost int
)
row format delimited fields terminated by ',';

load data local inpath '/opt/module/hive-3.1.2/datas/business.txt' into table business;

3. 需求

查询在 2017 年 4 月份购买过的顾客及总人数

查询顾客的购买明细及月购买总额

将每个顾客的 cost 按照日期进行累加

查询每个顾客上次的购买时间

查询前 20%时间的订单信息

4. 按需求查询数据

// 查询在2017年4月份购买过的顾客及总人数
select name, count(*) over ()
from business
where substring(orderdate,1,7)='2017-04'
group by name;

// 查询顾客的购买明细及月购买总额
select name, orderdate, cost, sum(cost) over (partition by month(orderdate)) from business;

// 将每个顾客的cost按照日期进行累加
select name, orderdate, cost,
    sum(cost) over () as sample1, --所有行相加
    sum(cost) over (partition by name) as sample2, --按name分组，组内数据相加
    sum(cost) over (partition by name order by orderdate) as sample3, --按name分组，组内数据累加
    sum(cost) over (partition by name order by orderdate rows between unbounded preceding and current row ) as sample4, --和sanple3一样，由起点到当前行的聚合
    sum(cost) over (partition by name order by orderdate rows between 1 preceding and current row ) as sample5, --当前行和前面一行做聚合
    sum(cost) over (partition by name order by orderdate rows between 1 preceding and 1 following) as sample6, --当前行和前边一行以及后面一行聚合
    sum(cost) over (partition by name order by orderdate rows between current row and unbounded following) as sample7 --当前行及后面的所有行
from business;

// 查看顾客上次的购买时间
select name, orderdate, cost,
       lag(orderdate, 1, '1900-01-01') over (partition by name order by orderdate) as time1,
       lag(orderdate, 2) over (partition by name order by orderdate) as time2
from business;

// 查询前20%时间的订单信息
select * from (
        select name, orderdate, cost, 
            ntile(5) over (order by orderdate) as sorted
        from business
    ) t
where sorted = 1;

2.6 Rank

1. 函数说明
   • RANK() 排序相同时会重复，总数不会变
   • DENSE_RANK() 排序相同时会重复，总数会减少
   • ROW_NUMBER() 会根据顺序计算
2. 数据准备

create table if not exists score (
    name string,
    subject string,
    score int
)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/hive-3.1.2/datas/score.txt' into table score;

3. 需求

计算每门学科成绩排名

求出每门学科前三名的学生

4. 按需求查询数据

// 计算每门学科成绩排名
select name, subject, score,
       rank() over (partition by subject order by score desc ) rp,
       dense_rank() over (partition by subject order by score desc ) drp,
       row_number() over (partition by subject order by score desc ) rmp
from score;

// 求出每门学科前三名的学生
select name, score, subject
from (
     select name, score, subject,
            rank() over (partition by subject order by score desc ) as rmp
     from score
         ) t
where t.rmp <= 3;

3. 自定义函数

1. Hive 自带了一些函数，但是数量有限，自己可以通过自定义函数来方便的扩展
2. 根据用户自定义函数类别分为以下三种
   • UDF：一进一出
   • UDAF：聚集函数，多进一出 count/max/min
   • UDTF：一进多出 lateral view explode()
3. 编程步骤
   • 继承 Hive 提供的类
     • org.apache.hadoop.hive.ql.udf.generic.GenericUDF
     • org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
   • 实现类中的抽象方法
   • 在 hive 的命令行窗口创建函数
     • 添加 jar : add jar linux_jar_path
     • 创建函数: create [temporary] function [dbname.]function_name AS class_name;
     • 删除函数: drop [temporary] function [if exists] [dbname.]function_name;

3.1 自定义 UDF 函数

1. 需求

   自定义一个 UDF 实现计算给定字符串的长度

2. 创建一个 Maven 工程并导入依赖

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

3. 代码实现

   package com.codecat.udf;
   
   import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
   import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
   import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
   import org.apache.hadoop.hive.ql.metadata.HiveException;
   import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   
   public class MyStringLength extends GenericUDF {
       /*
           计算指定字符串的长度
       */
       @Override
       public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
           // 判断输入参数的个数
           if(objectInspectors.length != 1) {
               throw new UDFArgumentLengthException("Input Args Length Error!!!");
           }
   
           // 判断输入参数的类型
           if(!objectInspectors[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
               throw new UDFArgumentTypeException(0, "Input Args Type Error!!!");
           }
   
           // 函数本身返回值为int，需要返回int类型的鉴别器对象
           return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
       }
   
       @Override
       public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
           if(deferredObjects[0].get() == null) {
               return 0;
           }
           return deferredObjects[0].get().toString().length();
       }
   
       @Override
       public String getDisplayString(String[] strings) {
           return "";
       }
   }
   

4. 打成 jar 包上传到服务器/opt/module/data/myudf.jar
5. 将 jar 包添加到 hive 的 classpath

   add jar /opt/module/data/myudf.jar;
   

6. 创建临时函数与开发好的 java class 关联

   create temporary function my_len as "com.codecat.hive.
   MyStringLength";
   

7. 使用自定义的函数
   

3.2 自定义 UDTF 函数

1. 需求

   将一个任意分割符的字符串切割成独立的单词

2. 代码实现

   package com.codecat.udtf;
   
   import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
   import org.apache.hadoop.hive.ql.metadata.HiveException;
   import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
   import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   
   import java.util.ArrayList;
   import java.util.List;
   
   public class MyUDTF extends GenericUDTF {
       /*
           将一个任意分割符的字符串切割成独立的单词
       */
   
       private ArrayList<String> outList = new ArrayList<>();
   
       @Override
       public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
           // 1. 定义输出数据的列名和类型
           ArrayList<String> fieldNames = new ArrayList<>();
           List<ObjectInspector> fieldOIs = new ArrayList<>();
   
           // 2. 增加输出数据的列名和类型
           fieldNames.add("linetoword");
   
           fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
   
           return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
       }
   
       @Override
       public void process(Object[] objects) throws HiveException {
           // 1. 获取原始数据
           String arg = objects[0].toString();
   
           // 2. 获取数据传入的第二个参数，此处为分隔符
           String splitKey = objects[1].toString();
   
           // 3. 将原始数据按照传入的分隔符进行切分
           String[] fields = arg.split(splitKey);
   
           // 4. 遍历切分后的结果，并写出
           for (String field : fields) {
               // 集合为复用的，首先清空集合
               outList.clear();
   
               // 将每一个单词添加至集合
               outList.add(field);
   
               // 将集合内容写出
               forward(outList);
           }
       }
   
       @Override
       public void close() throws HiveException {
   
       }
   }
   

3. 打成 jar 包上传到服务器/opt/module/hive/data/myudtf.jar
4. 将 jar 包添加到 hive 的 classpath 下

    add jar /opt/module/hive/data/myudtf.jar;
   

5. 创建临时函数与开发好的 java class 关联

   create temporary function myudtf as
   "com.codecat.hive.MyUDTF";
   

6. 使用自定义的函数