【大數(shù)據(jù)】Hive 內(nèi)置函數(shù)和 UDF 講解

作者：liugp 2023-05-06 07:15:59

Hive是基于Hadoop的一個(gè)數(shù)據(jù)倉庫工具，可以將結(jié)構(gòu)化數(shù)據(jù)文件映射為一張數(shù)據(jù)庫表，并提供類SQL語句操作。Hive內(nèi)置了很多函數(shù)，可以滿足基本的查詢需求，同時(shí)還支持自定義函數(shù)(UDF)來實(shí)現(xiàn)更加靈活的操作。

一、概述

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

下面簡單介紹Hive內(nèi)置函數(shù)和UDF的相關(guān)內(nèi)容：

1）內(nèi)置函數(shù)

Hive內(nèi)置函數(shù)主要用于集合函數(shù)、數(shù)學(xué)函數(shù)、日期函數(shù)、字符串函數(shù)和條件判斷函數(shù)等方面。例如：

條件判斷函數(shù)：IF、WHEN、CASE、COALESCE等。
字符串函數(shù)：LENGTH、SUBSTR、CONCAT、TRIM、LOWER、UPPER等；
集合函數(shù)：SUM、MAX、MIN、AVG、COUNT等；
數(shù)學(xué)函數(shù)：ROUND、EXP、LOG、SIGN等；
日期函數(shù)：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等；

2）自定義函數(shù)（UDF）

除了Hive內(nèi)置函數(shù)之外，用戶還可以自定義函數(shù)來實(shí)現(xiàn)更加靈活的操作。 Hive支持三種類型的自定義函數(shù)：

標(biāo)量函數(shù)（UDF）：將一行中的一個(gè)值轉(zhuǎn)換為另外一個(gè)值，比如字符串轉(zhuǎn)小寫；
集合函數(shù)（UDAF）：作用于多個(gè)值上，并且返回一個(gè)結(jié)果，比如平均值；
行級(jí)別函數(shù)（UDTF）：將一行中的一個(gè)或多個(gè)字段轉(zhuǎn)換為多行，比如對(duì)一行中的字符串進(jìn)行單詞切分。

自定義函數(shù)用Java語言編寫，需要繼承Hive提供的UDF、UDAF或UDTF類，然后實(shí)現(xiàn)相應(yīng)的方法。例如，下面是一個(gè)自定義的UDF函數(shù)，用于將字符串轉(zhuǎn)為小寫：

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內(nèi)置函數(shù)和UDF的相關(guān)內(nèi)容，使用Hive內(nèi)置函數(shù)可以滿足常用的查詢需求，而自定義函數(shù)可以更加靈活地滿足特定的業(yè)務(wù)需求。

二、環(huán)境準(zhǔn)備

如果已經(jīng)有了環(huán)境了，可以忽略，如果想快速部署環(huán)境可以參考我這篇文章：通過 docker-compose 快速部署 Hive 詳細(xì)教程

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

三、Hive 內(nèi)置函數(shù)

先創(chuàng)建一張表來測試

# 登錄容器
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';

添加數(shù)據(jù)

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

# 模擬一些數(shù)據(jù)
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
# 加載數(shù)據(jù)，local 是加載本機(jī)文件數(shù)據(jù)
load data local inpath './data' into table external_table1;

1）條件判斷函數(shù)

1、If函數(shù): if

語法:

if(boolean testCondition, T valueTrue, T valueFalseOrNull)
# 返回值: T
# 說明: 當(dāng)條件testCondition為TRUE時(shí)，返回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、條件判斷函數(shù)：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、非空查找函數(shù): COALESCE

語法:

COALESCE(T v1, T v2, …)
#返回值: T
#說明: 返回參數(shù)中的第一個(gè)非空值；如果所有值都為NULL，那么返回NULL

示例：

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

2）字符串函數(shù)

1、字符串長度函數(shù)：length

語法：

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

示例：

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

2、字符串截取函數(shù)：substr,substring

語法:

substr(string A, int start, int len)
substring(string A, int start, int len)
# int len，可省略，就是到最后一個(gè)字符
#返回值: 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、字符串連接函數(shù)：concat

語法:

concat(string A, string B…)
#返回值: string
#說明：返回輸入字符串連接后的結(jié)果，支持任意個(gè)輸入字符串

示例：

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

4、去空格函數(shù)：trim

語法:

trim(string A)
#返回值: string
#說明：去除字符串兩邊的空格

示例：

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

5、字符串轉(zhuǎn)小寫函數(shù)：lower,lcase

語法:

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

示例：

# 兩個(gè)函數(shù)的作用是相同的，其區(qū)別僅僅是函數(shù)名不同。
hive> select lower('abSEd') from external_table1;
absed
hive> select lcase('abSEd') from external_table1;
absed

6、字符串轉(zhuǎn)大寫函數(shù)：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）集合函數(shù)

1、總和統(tǒng)計(jì)函數(shù): sum

語法:

sum(col), sum(DISTINCT col)
#返回值: double
#說明: sum(col)統(tǒng)計(jì)結(jié)果集中col的相加的結(jié)果；sum(DISTINCT col)統(tǒng)計(jì)結(jié)果中col不同值相加的結(jié)果

示例：

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

2、最大值統(tǒng)計(jì)函數(shù): max

語法:

max(col)
#返回值: double
#說明: 統(tǒng)計(jì)結(jié)果集中col字段的最大值

示例：

hive> select max(column2) from external_table1;

3、最小值統(tǒng)計(jì)函數(shù): min

語法:

min(col)
#返回值: double
#說明: 統(tǒng)計(jì)結(jié)果集中col字段的最小值

示例：

hive> select min(column2) from external_table1;

4、平均值統(tǒng)計(jì)函數(shù): avg

語法:

avg(col), avg(DISTINCT col)
#返回值: double
#說明: avg(col)統(tǒng)計(jì)結(jié)果集中col的平均值；avg(DISTINCT col)統(tǒng)計(jì)結(jié)果中col不同值相加的平均值

示例：

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

5、個(gè)數(shù)統(tǒng)計(jì)函數(shù): count

語法:

count(*), count(expr), count(DISTINCT expr[, expr_.])
#返回值: int
#說明: count(*)統(tǒng)計(jì)檢索出的行的個(gè)數(shù)，包括NULL值的行；count(expr)返回指定字段的非空值的個(gè)數(shù)；count(DISTINCT expr[, expr_.])返回指定字段的不同的非空值的個(gè)數(shù)

示例：

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

4）數(shù)學(xué)函數(shù)

1、取整函數(shù): round

語法:

round(double a)
#返回值: BIGINT
#說明: 返回double類型的整數(shù)值部分 （遵循四舍五入）

#也可以指定精度
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,0)

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

2、向下取整函數(shù): floor

語法:

floor(double a)
#返回值: BIGINT
#說明: 返回等于或者小于該double變量的最大的整數(shù)

示例：

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

3、向上取整函數(shù): ceil

語法:

ceil(double a)
#返回值: BIGINT
#說明: 返回等于或者大于該double變量的最小的整數(shù)

示例：

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

4、取隨機(jī)數(shù)函數(shù): rand

語法:

rand(),rand(int seed)
#返回值: double
#說明: 返回一個(gè)0到1范圍內(nèi)的隨機(jī)數(shù)。如果指定種子seed，則會(huì)等到一個(gè)穩(wěn)定的隨機(jī)數(shù)序列

示例：

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、絕對(duì)值函數(shù): abs

語法:

abs(double a) abs(int a)
#返回值: double int
#說明: 返回?cái)?shù)值a的絕對(duì)值

示例：

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

6、自然指數(shù)函數(shù): exp

語法:

exp(double a)
#返回值: double
#說明: 返回自然對(duì)數(shù)e的a次方

示例：

hive> select exp(2) from external_table1;
7.38905609893065

7、對(duì)數(shù)函數(shù): log

語法:

log(double base, double a)
#返回值: double
#說明: 返回以base為底的a的對(duì)數(shù)

示例：

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

5）日期函數(shù)

1、日期轉(zhuǎn)年函數(shù): 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、日期轉(zhuǎn)月函數(shù): 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、日期轉(zhuǎn)天函數(shù): 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、日期轉(zhuǎn)小時(shí)函數(shù): hour

語法:

hour (string date)
#返回值: int
#說明: 返回日期中的小時(shí)。

示例：

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

5、日期轉(zhuǎn)分鐘函數(shù): minute

語法:

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

示例：

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

6、日期轉(zhuǎn)秒函數(shù): second

語法:

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

示例：

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

7、日期轉(zhuǎn)周函數(shù): weekofyear

語法:

weekofyear (string date)
#返回值: int
#說明: 返回日期在當(dāng)前的周數(shù)。

示例：

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

四、Hive UDF

在Hive中，UDF函數(shù)是指用戶定義的函數(shù)，其目的是為了滿足某些特殊或個(gè)性化的需求，或者是為了優(yōu)化SQL查詢語句的性能。UDF函數(shù)可以分為三種類型：標(biāo)量函數(shù)、集合函數(shù)和行級(jí)函數(shù)。下面分別對(duì)三種類型的UDF函數(shù)進(jìn)行介紹：

1）標(biāo)量函數(shù)（UDF）

標(biāo)量函數(shù)（也稱為單行函數(shù)）是指一次輸入一行數(shù)據(jù)，一次輸出一行數(shù)據(jù)的函數(shù)。它們語法簡單，通常用于實(shí)現(xiàn)對(duì)某一列數(shù)據(jù)的單獨(dú)轉(zhuǎn)換或處理。標(biāo)量函數(shù)可以接受多個(gè)參數(shù)，但只能返回一個(gè)結(jié)果。

示例：下面是一個(gè)用于計(jì)算兩數(shù)之和的簡單標(biāo)量函數(shù)示例：

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）集合函數(shù)（UDAF）

集合函數(shù)（也稱為聚合函數(shù)）是指將多行數(shù)據(jù)一起處理并返回單個(gè)結(jié)果的函數(shù)，例如平均值、最大值、最小值等。UDAF函數(shù)可以接收任意數(shù)量的參數(shù)，并為每個(gè)輸入行返回一個(gè)中間累加器（Mapper端計(jì)算），最后返回一個(gè)最終結(jié)果（Reducer端計(jì)算）。

示例：下面是一個(gè)用于計(jì)算平均數(shù)的簡單聚合函數(shù)示例：

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 = 0;
      ((UDAFAverageAgg) agg).sum = 0;
    }

    // 處理單個(gè)輸入行數(shù)據(jù)
    public void iterate(AggregationBuffer agg, Object[] parameters) throws HiveException {
      if (parameters[0] != null) {
        ((UDAFAverageAgg) agg).sum += ((IntWritable) parameters[0]).get();
        ((UDAFAverageAgg) agg).count++;
      }
    }

    // 合并各個(gè)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;
      }
    }

    // 計(jì)算最終的結(jié)果
    public Object terminate(AggregationBuffer agg) throws HiveException {
      if (((UDAFAverageAgg) agg).count == 0) {
        return null;
      } else {
        return new Double(((double) ((UDAFAverageAgg) agg).sum) / ((UDAFAverageAgg) agg).count);
      }
    }

    // 結(jié)束計(jì)算
    public Object terminatePartial(AggregationBuffer agg) throws HiveException {
      return new IntWritable(((UDAFAverageAgg) agg).sum);
    }
  }
}

3）行級(jí)別函數(shù)（UDTF）

行級(jí)函數(shù)（也稱為表生成函數(shù)）是指將一行數(shù)據(jù)拆分成多行數(shù)據(jù)進(jìn)行處理的函數(shù)。它們可以接受多個(gè)輸入行，并將它們轉(zhuǎn)換為多個(gè)輸出行，常用于文本處理、數(shù)據(jù)拆分等場景。

示例：下面是一個(gè)簡單的行級(jí)函數(shù)示例，用于將輸入字符串按照分隔符切分并返回多行：

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 {
    // 確保輸入是一個(gè)列表類型
    if (argOIs[0].getCategory() != ObjectInspector.Category.LIST) {
      throw new UDFArgumentException("split() takes an array as a parameter");
    }
    // 獲取列表元素類型
    listOI = (ListObjectInspector) argOIs[0];
    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[0];
    int index = 0;
    // 切分字符串并向下傳遞
    for (int i = 0; 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函數(shù)的介紹，三種類型各自適用于不同的場合，可以根據(jù)業(yè)務(wù)需求選擇相應(yīng)的UDF函數(shù)來實(shí)現(xiàn)。

責(zé)任編輯：武曉燕來源：大數(shù)據(jù)與云原生技術(shù)分享