Hive常用函数与自定义函数

柔光的暖阳◎ 2022-12-25 00:49 281阅读 0赞

# Hive常用函数与自定义函数 #

### 目录 ###

*  Hive常用函数与自定义函数
 *   *  一、系统内置函数
     *  二、常用内置函数
     *   *  2.1 空字段赋值
         *  2.2 CASE WHEN判断
         *  2.3 行转列(拼接函数)
         *  2.4 列转行
         *  2.5 窗口函数（开窗函数）
         *  2.6 排序函数
     *  三、自定义函数
     *  四、自定义UDF函数
     *   *  4.1 编写udf函数
         *  4.2 创建临时函数
         *  4.3 创建永久函数

## 一、系统内置函数 ##

**1）查看系统自带的函数**

hive> show functions;

**2）显示自带的函数的用法**

hive> desc function upper;

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

hive> desc function extended upper;

## 二、常用内置函数 ##

### 2.1 空字段赋值 ###

> **函数说明：**  
> NVL：给值为NULL的数据赋值，它的格式是**NVL( value，default\_value)**。它的功能是**如果value为NULL，则NVL函数返回default\_value的值，否则返回value的值**，如果两个参数都为NULL ，则返回NULL。

### 2.2 CASE WHEN判断 ###

> **函数说明：**  
> 用法：**CASE 字段 WHEN 判断条件 THEN 进行的操作 ELSE 不满足条件进行的操作 END**  
> 对某一字段进行按条件操作，类似于java中的if…else…

### 2.3 行转列(拼接函数) ###

> **1）相关函数说明**  
> **CONCAT(string A/col, string B/col…)**：返回输入字符串连接后的结果，支持任意个输入字符串;  
> **CONCAT\_WS(separator, str1, str2,…)**：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;  
> **注意：CONCAT\_WS must be "string or array"**  
> **COLLECT\_SET(col)**：函数只接受基本数据类型，它的主要作用是将某字段的值进行**去重汇总**，产生array类型字段。  
> **COLLECT\_LIST(col)**：函数只接受基本数据类型，它的主要作用是将某字段的值进行**不去重汇总**，产生array类型字段。

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）函数说明**  
> **Split(str, separator)**：将字符串按照后面的分隔符切割，转换成字符array。  
> **EXPLODE(col)**：将hive一列中复杂的array或者map结构拆分成多行。

> **LATERAL VIEW**  
> 用法：**LATERAL VIEW udtf(expression) tableAlias AS columnAlias**  
> 解释：lateral view用于和split, explode等UDTF一起使用，**它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合**。  
> lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。  
> **lateral view用于维持查询表和炸开后虚拟表的联系**

#按需求查询数据
    SELECT movie,category_name 
    FROM movie_info 
    
    lateral VIEW
    explode(split(category,",")) movie_info_tmp  AS category_name ;

### 2.5 窗口函数（开窗函数） ###

> **1）相关函数说明**  
> **OVER()**：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。  
> **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行数据  
> **FIRST\_VALUE (col,true/false)**：当前窗口下的第一个值，第二个参数为true，跳过空值  
> **LAST\_VALUE (col,true/false)**：当前窗口下的最后一个值，第二个参数为true，跳过空值  
> **NTILE(n)**：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

#按需求查询数据
    #（1）查询在2017年4月份购买过的顾客及总人数
    select name,count(*) over () 
    from business 
    where substring(orderdate,1,7) = '2017-04' 
    group by name;
    
    
    #（2）查询顾客的购买明细及月购买总额
    select
    	name,
    	orderdate,
    	cost,
    	sum(cost) over(partition by name,month(orderdate)) name_month_cost
    from business;
    
    
    #（3）将每个顾客的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 ,--和sample3一样,由起点到当前行的聚合 
    	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;
    
    #rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
    
    
    #（4）查询顾客购买明细以及上次的购买时间和下次购买时间
    select 
    	name,orderdate,cost,
    	lag(orderdate,1,'1970-01-01') over(PARTITION by name order by orderdate) prev_time,
    	lead(orderdate,1,'1970-01-01') over(PARTITION by name order by orderdate) next_time
    from business;
    
    
    #（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
    select
    	name,
    	orderdate,
    	cost,
    	FIRST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) first_time,
    	LAST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) last_time
    from business;
    
    
    #（6）查询前20%时间的订单信息
    select * from (
        select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
        from business
    ) t
    where sorted = 1;

### 2.6 排序函数 ###

> **函数说明**  
> **RANK()** 排序相同时会重复(考虑并列,会跳号)，总数不会变  
> **DENSE\_RANK()** 排序相同时会重复(考虑并列,不跳号)，总数会减少  
> **ROW\_NUMBER()** 会根据顺序计算(不考虑并列,不跳号,行号)

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;

## 三、自定义函数 ##

1.  Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2.  当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3.  根据用户自定义函数类别分为以下三种：  
    （1）**UDF（User-Defined-Function）**  
    一进一出  
    （2）**UDAF（User-Defined Aggregation Function）**  
    用户自定义聚合函数，多进一出  
    类似于：count/max/min  
    （3）**UDTF（User-Defined Table-Generating Functions）**  
    用户自定义表生成函数，一进多出  
    如lateral view explode()
4.  官方文档地址  
    https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5.  编程步骤：

> （1）**继承Hive提供的类**  
> org.apache.hadoop.hive.ql.udf.generic.GenericUDF  
> org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;  
> （2）**实现类中的抽象方法**  
> （3）**在hive的命令行窗口创建函数**

#添加jar 
    add jar linux_jar_path
    #创建function
    create [temporary] function [dbname.]function_name AS class_name;

> （4）在hive的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

## 四、自定义UDF函数 ##

### 4.1 编写udf函数 ###

**0）需求:**  
自定义一个UDF实现计算给定字符串的长度，例如：

hive(default)> select my_len("abcd");
    4

**1）创建一个Maven工程Hive  
2）导入依赖**

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

**3）创建一个类**

package com.hadoop.hive.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;
    
    /** * 自定义UDF函数，需要继承GenericUDF类 * 需求: 计算指定字符串的长度 */
    public class MyLength extends GenericUDF { 
        /** * 初始化方法,里面要做三件事 * 1.约束函数传入参数的个数 * 2.约束函数传入参数的类型 * 3.约束函数返回值的类型 * @param arguments 函数传入参数的类型 * @return * @throws UDFArgumentException */
        @Override
        public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { 
            //1.约束函数传入参数的个数
            if (arguments.length != 1) { 
                throw new UDFArgumentLengthException("Input Args Num Error,You can only input one arg...");
            }
            //2.约束函数传入参数的类型
            if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) { 
                throw new UDFArgumentTypeException(0,"Input Args Type Error,You can only input PRIMITIVE Type...");
            }
            //3.约束函数返回值的类型
            return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
        }
    
        /** * 函数逻辑处理方法 * @param arguments 函数传入参数的值 * @return * @throws HiveException */
        @Override
        public Object evaluate(DeferredObject[] arguments) throws HiveException { 
            //获取函数传入参数的值
            Object o = arguments[0].get();
            //将object转换成字符串
            int length = o.toString().length();
            //因为在上面的初始化方法里面已经对函数返回值类型做了约束,必须返回一个int类型
            //所以我们要在这个地方直接返回length
            return length;
        }
    
        /** * 返回显示字符串方法,这个方法不用管,直接返回一个空字符串 * @param children * @return */
        @Override
        public String getDisplayString(String[] children) { 
            return "";
        }
    }

### 4.2 创建临时函数 ###

（1）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar  
（2）将jar包添加到hive的classpath，临时生效

hive (default)> add jar /opt/module/hive/datas/myudf.jar;

（3）创建临时函数与开发好的java class关联

hive (default)> create temporary function my_len as "com.hadoop.hive.udf.MyLength";

（4）即可在hql中使用自定义的临时函数

hive (default)> select ename,my_len(ename) ename_len from emp;

（5）删除临时函数

hive (default)> drop  temporary function my_len;

> **注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以使用，其他会话全都不能使用。**

### 4.3 创建永久函数 ###

1.  在HIVE家目录下面创建auxlib目录