Hive函数

浅小兮2025-01-042025-01-02

系统内置函数

查看系统自带的函数

hive> show functions; 
OK 
tab_name 
abs 
……

显示自带的函数的用法

hive> desc function abs; 
OK 
tab_name 
abs(x) - returns the absolute value of x

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

hive> desc function extended abs; 
OK 
tab_name 
abs(x) - returns the absolute value of x 
Example: 
  > SELECT abs(0) FROM src LIMIT 1; 
  0 
  > SELECT abs(-5) FROM src LIMIT 1; 
  5 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs 
Function type:BUILTIN

常用内置函数

空字段赋值-NVL（防止空字段参与计算）

函数说明

hive(default)> desc function extended nvl; 
OK 
tab_name 
nvl(value,default_value) - Returns default value if value is null else returns value 
Example: 
  > SELECT nvl(null,'bla') FROM src LIMIT 1; 
  bla 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFNvl 
Function type:BUILTIN

解释：

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

案例实操

数据准备：采用员工表

查询：

如果员工的comm为NULL，则用0代替

hive (default)> select ename,comm,nvl(comm, 0) comm_0 from emp; 
OK 
ename   comm            comm_0 
fanfan  500000.0      500000.0 
SMITH   NULL           0 
ALLEN   300.0          300.0 
WARD    500.0          500.0 
……

如果员工的job为NULL，则用领导id代替

hive (default)> select ename, mgr,comm, nvl(job,mgr) comm_mgr from emp; 
OK 
job  _c1 
研发 研发 
财务 财务 
行政 行政 
销售 销售 
研发 研发 
研发 研发 
NULL mgr 
行政 行政 
销售 销售 
销售 销售 
行政 行政 
讲师 讲师 
行政 行政 
讲师 讲师

CASE WHEN THEN ELSE END

函数说明

1	hive (default)> desc function extended case;

解释：根据不同数据，返回不同的值

如：CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 当a=b时，返回c；当a=d时，返回d；当a=e时，放回e；其他情况返回f。

案例实操

数据准备，在/opt/module/hive/datas目录下创建emp_sex.txt，添加如下内容

[root@hadoop datas]$ vim emp_sex.txt 
悟空,A,男 
大海,A,男 
宋宋,B,男 
凤姐,A,女 
婷姐,B,女 
婷婷,B,女

创建emp_sex表并导入数据

hive(default)> create table emp_sex( 
name string,  
dept_id string,  
sex string 
)  
row format delimited fields terminated by ","; 
hive(default)> load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;

需求：求出不同部门男女各多少人。结果如下：

1
2
3

dept_id   man_num   woman_num 
A          2          1 
B          1          2

按需求查询数据

hive(default)> select  
  dept_id, 
  sum(case sex when '男' then 1 else 0 end) man_num, 
  sum(case sex when '女' then 1 else 0 end) woman_num 
from  
  emp_sex 
group by  dept_id;

行转列

相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

hive (default)> desc function extended concat; 
OK 
tab_name 
concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, 
bin2, ... binN) - returns the concatenation of bytes in binary data  bin1, bin2, ... binN 
Returns NULL if any argument is NULL. 
Example: 
  > SELECT concat('abc', 'def') FROM src LIMIT 1; 
  'abcdef' 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFConcat 
Function type:BUILTIN

CONCAT_WS(separator, str1, str2,…)：

hive (default)> desc function extended concat_ws; 
OK 
tab_name 
concat_ws(separator, [string | array(string)]+) - returns the concatenation of the strings 
separated by the separator. 
Example: 
  > SELECT concat_ws('.', 'www', array('facebook', 'com')) FROM src LIMIT 1; 
  'www.facebook.com' 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFConcatWS 
Function type:BUILTIN

- 它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。
- 分隔符可以是与剩余参数一样的字符串。
- 如果分隔符是 NULL，返回值也将为 NULL。
- 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。
- 分隔符将被加到被连接的字符串之间;
-  注意: CONCAT_WS must be "string or array<string>

COLLECT_SET(col)：

hive (default)> desc function extended collect_set; 
OK 
tab_name 
collect_set(x) - Returns a set of objects with duplicate elements eliminated 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDAFCollectSet 
Function type:BUILTIN

- 函数只接受基本数据类型
- 它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

COLLECT_LIST(col):

- 函数指接收基本数据类型
- 它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。

数据准备

name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
苍老师	白羊座	B

案例需求：把星座和血型一样的人归类到一起。结果如下：

1
2
3

射手座,A            大海|凤姐 
白羊座,A            孙悟空|猪八戒 
白羊座,B            宋宋|苍老师

数据准备：在/opt/module/hive/datas目录下创建文件constellation.txt，添加如下内容

[root@hadoop datas]$ vim person_info.txt 
孙悟空,白羊座,A 
大海,射手座,A 
宋宋,白羊座,B 
猪八戒,白羊座,A 
凤姐,射手座,A 
苍老师,白羊座,B

案例实操

创建person_info表并导入数据

hive (default)> create table person_info( 
name string,  
constellation string,  
blood_type string 
)  
row format delimited fields terminated by ",";

导入数据到person_info表中。

1 2	hive (default)> load data local inpath "/opt/module/hive/datas/person_info.txt" into table person_info;

按需求查询数据

hive (default)> 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

列转行

函数说明

EXPLODE(col)：将hive表的一列中复杂的array或者map结构拆分成多行。

hive (default)> desc function extended explode; 
OK 
tab_name 
explode(a) - separates the elements of array a into multiple rows, or the elements of a map into 
multiple rows and columns  
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDTFExplode 
Function type:BUILTIN

SPLIT(string str, string regex): 按照regex字符串分割str，会返回分割后的字符串数组

hive (default)> desc function extended split; 
OK 
tab_name 
split(str, regex) - Splits str around occurances that match regex 
Example: 
  > SELECT split('oneAtwoBthreeC', '[ABC]') FROM src LIMIT 1; 
  ["one", "two", "three"] 
Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFSplit 
Function type:BUILTIN

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会报一行拆分成一行或者多行， lateral view再把结果组合，产生一个支持别名表的虚拟表。

案例需求：将表中的categroy列的数据展开。

movie	category
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

结果如下：

《疑犯追踪》      悬疑 
《疑犯追踪》      动作 
《疑犯追踪》      科幻 
《疑犯追踪》      剧情 
《Lie to me》   悬疑 
《Lie to me》   警匪 
《Lie to me》   动作 
《Lie to me》   心理 
《Lie to me》   剧情 
《战狼2》        战争 
《战狼2》        动作 
《战狼2》        灾难

案例实操

在/opt/module/hive/datas/目录下创建文件movie.txt，添加如下数据：

[root@hadoop datas]$ vim movie_info.txt 
《疑犯追踪》 悬疑,动作,科幻,剧情 
《Lie to me》 悬疑,警匪,动作,心理,剧情 
《战狼2》 战争,动作,灾难

创建hive表并导入数据

hive (default)> create table movie_info( 
    movie string,  
    category string)  
row format delimited 
fields terminated by "\t"; 
hive (default)> load data local inpath "/opt/module/hive/datas/movie_info.txt" into table 
movie_info;

按需求查询数据

hive (default)> SELECT movie,category_name  
FROM movie_info  
lateral VIEW 
explode(split(category,",")) movie_info_tmp  AS category_name ;

窗口函数（开窗函数）

开窗函数介绍
- 窗口函数不同于我们熟悉的常规函数及聚合函数，它输入多行数据（一个窗口），为每行数据进行一次计算，返回一个值。
- 灵活运用窗口函数可以解决很多复杂的问题，如去重、排名、同比及和环比、连续登录等。

语法：

1	Function（arg1 ……） over（[partition by arg1 ……] [order by arg1 ……] [<window_expression>]）

Function		Over()		window_expression
解释：	支持的函数	解释：	指定分析函数工作的数据窗口大小，窗口会随着行的变化而变化。	解释：	窗口边界的设置
聚合函数：	sum()、max()、 min()、avg() 等。	Partition by:	表示将数据先按字段进行分区	n preceding	往前n行
				n following	往后n行
排序函数：	rank()， row_number()、 dens_rank()、 ntile()等。			current row	当前行
		Order by:	表示将各个分区内的数据按字段进行排序	unbounded preceding	从前面的起点开始
	lead()、 lag()、 first_value() 等。			unbounded following	到后面的终点结束

使用详解
- 如果不指定partition by，则不对数据进行分区，换句话说，所有数据看作同一个分区。
- 如果不指定order by，则不对各分区进行排序，通常用于那些顺序无关的窗口函数，如sum（）。
- 如果不指定窗口子句：

不指定order by，默认使用分区内所有行，等同于Function() over(rows between unbounded precedeing and unbounded following) 如果指定order by，默认使用分区内第起点到当前行，等同于Function() over(rows between unbounded preceding and current row)

数据准备

在/opt/module/hive/datas目录下创建business.txt，添加如下内容。

[root@hadoop datas]$ vim business.txt 
jack,2017-01-01,10 
tony,2017-01-02,15 
jack,2017-02-03,23 
tony,2017-01-04,29 
jack,2017-01-05,46 
jack,2017-04-06,42 
tony,2017-01-07,50 
jack,2017-01-08,55 
mart,2017-04-08,62 
mart,2017-04-09,68 
neil,2017-05-10,12 
mart,2017-04-11,75 
neil,2017-06-12,80 
mart,2017-04-13,94

创建hive表并导入数据

hive(default)> create table business( 
name string,  
orderdate string, 
cost int 
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','; 
hive(default)> load data local inpath "/opt/module/hive/datas/business.txt" into table business;

Rank

函数说明

RANK()：排序相同时会重复，总数不会变重复的名次一样但是下一名名次会以前面人数+1来定

DENSE_RANK()：排序相同时会重复，总数会减少就是若有重复则最后一名的名次不会和总数相等即并列

ROW_NUMBER()：会根据顺序计算，字段相同就按排头字段继续排

数据准备

在/opt/module/hive/datas目录下创建文件score.txt，添加如下内容

[root@hadoop datas]$ vim score.txt 
孙悟空 语文 87 
孙悟空 数学 95 
孙悟空 英语 68 
大海 语文 94 
大海 数学 56 
大海 英语 84 
宋宋 语文 64 
宋宋 数学 86 
宋宋 英语 84 
婷婷 语文 65 
婷婷 数学 85 
婷婷 英语 78

创建表score并导入数据

hive(default)> create table score( 
name string, 
subject string,  
score int)  
row format delimited 
fields terminated by "\t"; 
hive(default)> load data local inpath '/opt/module/hive/datas/score.txt' into table score;

需求：计算每门学科成绩排名。

hive(default)> 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; 
OK 
name    subject score   rp      drp     rmp 
孙悟空  数学    95      1       1       1 
宋宋    数学    86      2       2       2 
婷婷    数学    85      3       3       3 
大海    数学    56      4       4       4 
宋宋    英语    84      1       1       1 
大海    英语    84      1       1       2 
婷婷    英语    78      3       2       3 
孙悟空  英语    68      4       3       4 
大海    语文    94      1       1       1 
孙悟空  语文    87      2       2       2 
婷婷    语文    65      3       3       3 
宋宋    语文    64      4       4       4

自定义函数

内置函数：Hive 自带了一些函数。比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
自定义函数：当Hive提供的内置函数无法满足你的业务处理需要时此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
根据用户自定义函数类别分为以下三种：

UDF（User-Defined-Function）–> 一进一出

UDAF（User-Defined Aggregation Function） --> 聚合函数，多进一出，类似：count/max/min

UDTF（User-Defined Table-Generating Functions）–> 炸裂函数，一进多出，如：explode()

官方文档地址https://cwiki.apache.org/confluence/display/Hive/HivePlugins
编程步骤：

继承Hive提供的类

1 2	·org.apache.hadoop.hive.ql.udf.generic.GenericUDF ·org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

实现类中的抽象方法
实现类中的抽象方法

自定义UDF函数

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

1
2
3

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

案例实操

创建一个Maven工程Hive
在工程项目的pom.xml文件中导入依赖

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

创建一个类

package com.atguigu.hive; 
 
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 MyStringLength extends GenericUDF { 
    /** 
     * 
     * @param arguments 输入参数类型的鉴别器对象 
     * @return 返回值类型的鉴别器对象 
     * @throws UDFArgumentException 
     */ 
    @Override 
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { 
        // 判断输入参数的个数 
        if(arguments.length !=1){ 
            throw new UDFArgumentLengthException("Input Args Length Error!!!"); 
        } 
        // 判断输入参数的类型 
        if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){ 
            throw new UDFArgumentTypeException(0,"Input Args Type Error!!!"); 
        } 
        //函数本身返回值为int，需要返回int类型的鉴别器对象 
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector; 
    } 
 
    /** 
     * 函数的逻辑处理 
     * @param arguments 输入的参数 
     * @return 返回值 
     * @throws HiveException 
     */ 
    @Override 
    public Object evaluate(DeferredObject[] arguments) throws HiveException { 
       if(arguments[0].get() == null){ 
           return 0 ; 
       } 
       return arguments[0].get().toString().length(); 
    } 
 
    @Override 
    public String getDisplayString(String[] children) { 
        return ""; 
    } 
}

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

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

创建临时函数

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

1	hive (default)> create temporary function my_len as "com.atguigu.hive. MyStringLength";

在hql中使用自定义的函数

hive (default)> select ename,my_len(ename) ename_len from emp; 
OK 
ename   _c1 
fanfan  6 
SMITH   5 
ALLEN   5 
WARD    4 
JONES   5 
MARTIN  6 
BLAKE   5 
CLARK   5 
SCOTT   5 
KING    4 
TURNER  6 
ADAMS   5 
JAMES   5 
FORD    4 
MILLER  6

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

创建永久函数

注意：因为add jar的方式本身也是临时生效，所以在创建永久函数的时候，需要执行路径（应且因为元数据的原因，这个路径还得是HDFS上的路径）

hive (default)>  
create function my_len2  
as "com.atguigu.hive.udf.MyUDF"  
using jar "hdfs://hadoop102:8020/udf/myudf.jar";

即可在hql中使用自定义的永久函数

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

删除永久函数

1	hive (default)> drop function my_len2;

注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。

永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。

永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上库名.函数名。