【大数据】Hive 内置函数和 UDF 讲解

2023-05-06 17:40:38 hive 数据函数讲解 UDF

一、概述

Hive是基于Hadoop的一个数据仓库工具，可以将结构化数据文件映射为一张数据库表，并提供类SQL语句操作。Hive内置了很多函数，可以满足基本的查询需求，同时还支持自定义函数(UDF)来实现更加灵活的操作。

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

下面简单介绍Hive内置函数和UDF的相关内容：

1）内置函数

Hive内置函数主要用于集合函数、数学函数、日期函数、字符串函数和条件判断函数等方面。例如：

条件判断函数：IF、WHEN、CASE、COALESCE等。
字符串函数：LENGTH、SUBSTR、CONCAT、TRIM、LOWER、UPPER等；
集合函数：SUM、MAX、MIN、AVG、COUNT等；
数学函数：ROUND、EXP、LOG、SIGN等；
日期函数：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等；

2）自定义函数（UDF）

除了Hive内置函数之外，用户还可以自定义函数来实现更加灵活的操作。 Hive支持三种类型的自定义函数：

标量函数（UDF）：将一行中的一个值转换为另外一个值，比如字符串转小写；
集合函数（UDAF）：作用于多个值上，并且返回一个结果，比如平均值；
行级别函数（UDTF）：将一行中的一个或多个字段转换为多行，比如对一行中的字符串进行单词切分。

自定义函数用Java语言编写，需要继承Hive提供的UDF、UDAF或UDTF类，然后实现相应的方法。例如，下面是一个自定义的UDF函数，用于将字符串转为小写：

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class Lowercase extends UDF {
public Text evaluate(Text str) {
if (str == null) {
return null;
    } else {
return new Text(str.toString().toLowerCase());
    }
  }
}

以上是简单介绍Hive内置函数和UDF的相关内容，使用Hive内置函数可以满足常用的查询需求，而自定义函数可以更加灵活地满足特定的业务需求。

二、环境准备

如果已经有了环境了，可以忽略，如果想快速部署环境可以参考我这篇文章：通过 docker-compose 快速部署 Hive 详细教程

# 登录容器
docker exec -it hive-hiveserver2 bash
# 连接hive
beeline -u jdbc:hive2://hive-hiveserver2:10000  -n hadoop

三、Hive 内置函数

先创建一张表来测试

# 登录容器
docker exec -it hive-hiveserver2 bash
# 登录hive客户端
beeline -u jdbc:hive2://hive-hiveserver2:10000  -n hadoop

# 建表
CREATE EXTERNAL TABLE external_table1 (
    column1 STRING,
    column2 INT,
    column3 DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/hive/external_table/data';

添加数据

# 登录容器
docker exec -it hive-hiveserver2 bash

# 模拟一些数据
cat >data<<EOF
c1,12,56.33
c2,14,58.99
c3,15,66.34
c4,16,76.78
EOF

# 登录hive客户端
beeline -u jdbc:hive2://hive-hiveserver2:10000  -n hadoop
# 加载数据，local 是加载本机文件数据
load data local inpath './data' into table external_table1;

1）条件判断函数

1、If函数: if

语法:

if(boolean testCondition, T valueTrue, T valueFalseOrNull)
# 返回值: T
# 说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

示例：

# 注意，这里查询的记录必须存在，要不然也返回空
hive> select if(1=2,100,200) from external_table1;
200
hive> select if(1=1,100,200) from external_table1;
100

3、条件判断函数：CASE

语法:

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
#返回值: T
#说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

示例：

hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from external_table1;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from external_table1;
tim

4、非空查找函数: COALESCE

语法:

COALESCE(T v1, T v2, …)
#返回值: T
#说明: 返回参数中的个非空值；如果所有值都为NULL，那么返回NULL

示例：

hive> select COALESCE(null,'100','50') from external_table1;
100

2）字符串函数

1、字符串长度函数：length

语法：

length(string A)
#返回值: int
#说明：返回字符串A的长度

示例：

hive> select length('abcedfg') from external_table1;
7

2、字符串截取函数：substr,substring

语法:

substr(string A, int start, int len)
substring(string A, int start, int len)
# int len，可省略，就是到后一个字符
#返回值: string
#说明：返回字符串A从start位置开始，长度为len的字符串

示例：

hive> select substr('abcde',3) from external_table1;
cde
hive> select substring('abcde',3) from external_table1;
cde
hive>  select substr('abcde',-1) from external_table1;
e

hive> select substr('abcde',3,2) from external_table1;
cd
hive> select substring('abcde',3,2) from external_table1;
cd
hive>select substring('abcde',-2,2) from external_table1;
de

3、字符串连接函数：concat

语法:

concat(string A, string B…)
#返回值: string
#说明：返回输入字符串连接后的结果，支持任意个输入字符串

示例：

hive> select concat('abc','def','gh') from external_table1;
abcdefgh

4、去空格函数：trim

语法:

trim(string A)
#返回值: string
#说明：去除字符串两边的空格

示例：

hive> select trim(' abc ') from external_table1;
abc

5、字符串转小写函数：lower,lcase

语法:

lower(string A),lcase(string A)
#返回值: string
#说明：返回字符串A的小写格式

示例：

# 两个函数的作用是相同的，其区别仅仅是函数名不同。
hive> select lower('abSEd') from external_table1;
absed
hive> select lcase('abSEd') from external_table1;
absed

6、字符串转大写函数：upper,ucase

语法:

upper(string A), ucase(string A)
#返回值: string
#说明：返回字符串A的大写格式

示例：

hive> select upper('abSEd') from external_table1;
ABSED
hive> select ucase('abSEd') from external_table1;
ABSED

3）集合函数

1、总和统计函数: sum

语法:

sum(col), sum(DISTINCT col)
#返回值: double
#说明: sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果

示例：

hive> select sum(column2) from external_table1;
hive> select sum(distinct column2) from external_table1;

2、大值统计函数: max

语法:

max(col)
#返回值: double
#说明: 统计结果集中col字段的大值

示例：

hive> select max(column2) from external_table1;

3、小值统计函数: min

语法:

min(col)
#返回值: double
#说明: 统计结果集中col字段的小值

示例：

hive> select min(column2) from external_table1;

4、平均值统计函数: avg

语法:

avg(col), avg(DISTINCT col)
#返回值: double
#说明: avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值

示例：

hive> select avg(column2) from external_table1;
hive> select avg (distinct column2) from external_table1;

5、个数统计函数: count

语法:

count(*), count(expr), count(DISTINCT expr[, expr_.])
#返回值: int
#说明: count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的个数

示例：

hive> select count(*) from external_table1;
hive> select count(distinct column2) from external_table1;

4）数学函数

1、取整函数: round

语法:

round(double a)
#返回值: BIGINT
#说明: 返回double类型的整数值部分 （遵循四舍五入）

#也可以指定精度
round(double a, int d)

示例：

hive> select round(3.1415926) from external_table1;
3
hive> select round(3.5) from external_table1;
4
hive> create table external_table2 as select round(9542.158) from external_table1;
hive> describe external_table2;
_c0     decimal(5,)

# 指定精度
hive> select round(3.1415926,4) from external_table1;
3.1416

2、向下取整函数: floor

语法:

floor(double a)
#返回值: BIGINT
#说明: 返回等于或者小于该double变量的大的整数

示例：

hive> select floor(3.1415926) from external_table1;
3
hive> select floor(25) from external_table1;
25

3、向上取整函数: ceil

语法:

ceil(double a)
#返回值: BIGINT
#说明: 返回等于或者大于该double变量的小的整数

示例：

hive> select ceil(3.1415926) from external_table1;
4
hive> select ceil(46) from external_table1;
46

4、取随机数函数: rand

语法:

rand(),rand(int seed)
#返回值: double
#说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

示例：

hive> select rand() from external_table1;
0.5577432776034763
hive> select rand() from external_table1;
0.6638336467363424
hive> select rand(100) from external_table1;
0.7220096548596434
hive> select rand(100) from external_table1;
0.7220096548596434

5、值函数: abs

语法:

abs(double a) abs(int a)
#返回值: double int
#说明: 返回数值a的值

示例：

hive> select abs(-3.9) from external_table1;
3.9
hive> select abs(10.9) from external_table1;
10.9

6、自然指数函数: exp

语法:

exp(double a)
#返回值: double
#说明: 返回自然对数e的a次方

示例：

hive> select exp(2) from external_table1;
7.38905609893065

7、对数函数: log

语法:

log(double base, double a)
#返回值: double
#说明: 返回以base为底的a的对数

示例：

hive> select log(4,256) from external_table1;
4.0

5）日期函数

1、日期转年函数: year

语法:

year(string date)
#返回值: int
#说明: 返回日期中的年。

示例：

hive> select year('2023-05-04 22:03:01') from external_table1;
2023
hive> select year('2024-05-04') from external_table1;
2024

2、日期转月函数: month

语法:

month (string date)
#返回值: int
#说明: 返回日期中的月份。

示例：

hive> select month('2011-12-08 10:03:01') from external_table1;
12
hive> select month('2011-08-08') from external_table1;
8

3、日期转天函数: day

语法:

day (string date)
#返回值: int
#说明: 返回日期中的天。

示例：

hive> select day('2011-12-08 10:03:01') from external_table1;
8
hive> select day('2011-12-24') from external_table1;
24

4、日期转小时函数: hour

语法:

hour (string date)
#返回值: int
#说明: 返回日期中的小时。

示例：

hive> select hour('2011-12-08 10:03:01') from external_table1;
10

5、日期转分钟函数: minute

语法:

minute (string date)
#返回值: int
#说明: 返回日期中的分钟。

示例：

hive> select minute('2011-12-08 10:03:01') from external_table1;
3

6、日期转秒函数: second

语法:

second (string date)
#返回值: int
#说明: 返回日期中的秒。

示例：

hive> select second('2011-12-08 10:03:01') from external_table1;
1

7、日期转周函数: weekofyear

语法:

weekofyear (string date)
#返回值: int
#说明: 返回日期在当前的周数。

示例：

hive> select weekofyear('2011-12-08 10:03:01') from external_table1;
49

四、Hive UDF

在Hive中，UDF函数是指用户定义的函数，其目的是为了满足某些特殊或个性化的需求，或者是为了优化SQL查询语句的性能。UDF函数可以分为三种类型：标量函数、集合函数和行级函数。下面分别对三种类型的UDF函数进行介绍：

1）标量函数（UDF）

标量函数（也称为单行函数）是指一次输入一行数据，一次输出一行数据的函数。它们语法简单，通常用于实现对某一列数据的单独转换或处理。标量函数可以接受多个参数，但只能返回一个结果。

示例：下面是一个用于计算两数之和的简单标量函数示例：

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class ToUpper extends UDF {
public Text evaluate(Text input) {
if (input == null) {
return null;
    }
return new Text(input.toString().toUpperCase());
  }
}

2）集合函数（UDAF）

集合函数（也称为聚合函数）是指将多行数据一起处理并返回单个结果的函数，例如平均值、大值、小值等。UDAF函数可以接收任意数量的参数，并为每个输入行返回一个中间累加器（Mapper端计算），后返回一个终结果（Reducer端计算）。

示例：下面是一个用于计算平均数的简单聚合函数示例：

import org.apache.hadoop.hive.ql.exec.UDAF;
import org.apache.hadoop.hive.ql.udf.generic.AbstractGenericUDAFResolver;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator.AggregationBuffer;
import org.apache.hadoop.io.IntWritable;

public class UDAFAverage extends AbstractGenericUDAFResolver {
public UDAFAverage() {}

@Override
public GenericUDAFEvaluator getEvaluator(TypeInfo[] parameters) throws SemanticException {
return new UDAFAverageEvaluator();
  }

public static class UDAFAverageEvaluator extends GenericUDAFEvaluator {
public UDAFAverageEvaluator() {}

// 定义中间累加器
public static class UDAFAverageAgg implements AggregationBuffer {
int sum;
int count;
    }

// 初始化中间累加器
public AggregationBuffer getNewAggregationBuffer() throws HiveException {
UDAFAverageAgg result = new UDAFAverageAgg();
      reset(result);
return result;
    }

// 重置中间累加器
public void reset(AggregationBuffer agg) throws HiveException {
      ((UDAFAverageAgg) agg).count = ;
      ((UDAFAverageAgg) agg).sum = ;
    }

// 处理单个输入行数据
public void iterate(AggregationBuffer agg, Object[] parameters) throws HiveException {
if (parameters[] != null) {
        ((UDAFAverageAgg) agg).sum += ((IntWritable) parameters[]).get();
        ((UDAFAverageAgg) agg).count++;
      }
    }

// 合并各个Mapper返回的中间累加器
public void merge(AggregationBuffer agg, Object partial) throws HiveException {
if (partial != null) {
UDAFAverageAgg other = (UDAFAverageAgg) partial;
        ((UDAFAverageAgg) agg).sum += other.sum;
        ((UDAFAverageAgg) agg).count += other.count;
      }
    }

// 计算终的结果
public Object terminate(AggregationBuffer agg) throws HiveException {
if (((UDAFAverageAgg) agg).count == ) {
return null;
      } else {
return new Double(((double) ((UDAFAverageAgg) agg).sum) / ((UDAFAverageAgg) agg).count);
      }
    }

// 结束计算
public Object terminatePartial(AggregationBuffer agg) throws HiveException {
return new IntWritable(((UDAFAverageAgg) agg).sum);
    }
  }
}

3）行级别函数（UDTF）

行级函数（也称为表生成函数）是指将一行数据拆分成多行数据进行处理的函数。它们可以接受多个输入行，并将它们转换为多个输出行，常用于文本处理、数据拆分等场景。

示例：下面是一个简单的行级函数示例，用于将输入字符串按照分隔符切分并返回多行：

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ListObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;
import org.apache.hadoop.io.Text;
import java.util.ArrayList;

public class Split extends GenericUDTF {
private transient ListObjectInspector listOI = null;
private transient StringObjectInspector elementOI = null;

public void initialize(ObjectInspector[] argOIs) throws UDFArgumentException {
// 确保输入是一个列表类型
if (argOIs[].getCategory() != ObjectInspector.Category.LIST) {
throw new UDFArgumentException("split() takes an array as a parameter");
    }
// 获取列表元素类型
    listOI = (ListObjectInspector) argOIs[];
    elementOI = (StringObjectInspector) listOI.getListElementObjectInspector();
// 确保输出为两列
    ArrayList<String> fieldNames = new ArrayList<String>();
    ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
    fieldNames.add("value");
    fieldOIs.add(PrimitiveObjectInspectorFactory.writableStringObjectInspector);
    fieldNames.add("pos");
    fieldOIs.add(PrimitiveObjectInspectorFactory.writableIntObjectInspector);
    initialize(fieldNames, fieldOIs);
  }

public void process(Object[] record) throws HiveException {
// 获取输入字符串
Object list = record[];
int index = ;
// 切分字符串并向下传递
for (int i = ; i < listOI.getListLength(list); i++) {
      index++;
String value = elementOI.getPrimitiveJavaObject(listOI.getListElement(list, i));
      forward(new Object[] { new Text(value), new IntWritable(index) });
    }
  }

public void close() throws HiveException {
// nothing to do
  }
}

以上是Hive UDF函数的介绍，三种类型各自适用于不同的场合，可以根据业务需求选择相应的UDF函数来实现。

相关文章