Hive 函数

Hive 函数

一、内置函数

1.1 内置函数帮助

# 查看系统自带的函数
show functions;

# 显示自带的函数的用法
desc function upper;

# 详细显示自带的函数的用法
desc function extended upper;

1.2 常用内置函数

1.2.1 空字段赋值

1. 函数说明

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

1. 例子

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

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

1.2.2 CASE WHEN THEN ELSE END

1. 函数说明

CASE WHEN THEN ELSE END：相当于if else，它的语法格式是case when sal>2000 then sal*1.5 else sal*1.2 end 或者case id when '1' then 1 else 0 end。它的功能是当when的条件满足时，返回then的值，否则返回else的值。

1. 例子

原始数据：

悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女

创建表，并导入数据：

create table emp_sex(
name string, 
dept_id string, 
sex string) 
row format delimited fields terminated by " ";

load data local inpath '/root/mydata/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;

1.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 类型字段。

1. 例子

原始数据：

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

创建表，并导入数据：

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/hive/data/person_info.txt" into table person_info;

按照需求查询数据：

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

1.2.4 列转行

1. 相关函数说明

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

LATERAL VIEW

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

解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

1. 例子

原始数据：

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

创建表、并导入数据：

create table movie_info(
	movie string,
	category string)
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/data/movie.txt" into table movie_info;

按需求查询数据：

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

1.2.5 窗口函数（开窗函数）

1. 相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

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 返回此行所属的组的编号。注意：n 必须为 int 类型。

1. 例子

原始数据：name，orderdate，cost

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

创建表，并导入数据

create table business(
name string,
orderdate string,
cost int) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/module/data/business.txt" into table business;

需求：

1. 查询在 2017 年 4 月份购买过的顾客及总人数
2. 查询顾客的购买明细及月购买总额
3. 上述的场景, 将每个顾客的 cost 按照日期进行累加
4. 查询每个顾客上次的购买时间
5. 查询前 20%时间的订单信息

按需求查询数据

# 查询在 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 ,--和 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;

# 查看顾客上次的购买时间
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) sorted
 from business
) t
where sorted = 1;

rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。

1.2.6 Rank

1. 相关函数说明

RANK() ：排序相同时会重复，总数不会变

DENSE_RANK() ：排序相同时会重复，总数会减少

ROW_NUMBER() ：会根据顺序计算

1. 例子

原始数据

孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78

创建表，并导入数据

create table score(
name string,
subject string, 
score int) 
row format delimited fields terminated by "\t";

load data local inpath '/opt/module/data/score.txt' into table score;

按需求查询数据

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.2.7 其他常用函数

常用函数.txt

1.2.8 窗口函数详解

1. 什么是窗口函数？

窗口函数是 SQL 中一类特别的函数。和聚合函数相似，窗口函数的输入也是多行记录。不同的是，聚合函数的作用于由 GROUP BY 子句聚合的组，而窗口函数则作用于一个窗口， 这里，窗口是由一个 OVER 子句 定义的多行记录。聚合函数对其所作用的每一组记录输 出一条结果，而窗口函数对其所作用的窗口中的每一行记录输出一条结果。一些聚合函数，如 sum, max, min, avg,count 等也可以当作窗口函数使用。

1. 窗口函数原理

在用group-by处理数据分组时，每一行只能进入一个分组。窗口函数基于称为框(frame)的一组行，计算表的每一输入行的返回值，每一行可以属于一个或多个框。常见用例就是查看某些值的滚动平均值，其中每一行代表一天，那么每行属于7个不同的框。

如下图所示，每一行是如何匹配多个窗口框的。

1. 基本语法

窗口函数分为四个部分：

• 函数子句：指明具体操作，如sum-求和，first_value-取frame的第一个值
• partition by子句：指明分区字段，如果没有，则将所有数据作为一个分区。
• order by子句：指明了每个分区排序的字段和方式，也是可选的，没有就按照表中的顺序。
• 窗口子句：指明相对当前记录的计算范围，也就是指定frame框。可以向上（preceding），也可以向下（following），也可以使用between指明，上下边界的值，没有的话默认为当前分区。rows between，也叫做window子句，后面可以跟：
  • 数字+preceding向前n条
  • 数字+following向后n条
  • current row当前行
  • unbounded无边界,表示从前面最开始到最终
  • unbounded preceding表示最前面一条
  • unbounded following表示最后一条

1. 窗口函数有那些？

窗口函数的功能分为：聚合、取值、排名、序列四种，前三种的使用场景比较常见，容易理解，最后一种(序列)的使用场景比较少。

• 聚合

  • count 统计条数
  • sum 求和
  • avg 求平均值
  • max 求最大值
  • min 求最小值

• 取值

  • first_value：取窗口中的第一个值

  • last_value：取窗口中的最后一个值

  • lag(col, n, default) ：用于统计窗口内向上n行的值。

    col：列名；n：向上n行[可选，默认为1]；default：当向上n行为null时，取默认值，若不指定，则为null。

  • lead(col, n, default)：用于统计窗口内向下n行的值。

    col：列名；n：向下n行[可选，默认为1]；default：当向下n行为null时，取默认值，若不指定，则为null。

• 排序

  • rank：排序，有相同分数，排名相同并后续跳过，如分数5，5，8，9，则结果为1，1，3，4
  • dense_rank：排序，有相同分数排名相同，但后续街上，如分数5，5，8，9，则结果为1，1，2，3
  • row_number：排序，相同分数按先来后到排序，无重复排序，如分数5，5，8，9，则结果为1，2，3，4

• 其他

  • cume_dist：小于等于当前值的行数/分组内总行数，比如：统计小于等于当前薪水的人数，所占总人数的比例。
  • percent_rank：计算给定行的百分比排名。(分组内当前行的rank值-1)/(分组内总行数-1)，可以用来计算超过了百分之多少的人。
  • ntile(n)：将分区中的数据按照顺序划分为n片，返回当前片的值。注意：如果切片不均匀，默认增加第一个切片的分布。不支持rows between。

二、自定义函数

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

根据用户自定义函数类别分为以下三种：

• UDF（User-Defined-Function）：一进一出
• UDAF（User-Defined Aggregation Function）：聚类函数，多进一出
• UDTF（User-Defined Table-Generating Functions）：一进多出，如lateral view explode()

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

编程步骤：

1. 继承 Hive 提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

1. 实现类中的抽象方法

2. 在 hive 的命令行窗口创建函数

   • 添加 jar：

     add jar linux_jar_path

   • 创建 function：

     create [temporary] function [dbname.]function_name AS class_name;

3. 在 hive 的命令行窗口删除函数

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

2.1 自定义UDF

1. 创建一个maven工程
2. 导入hive依赖

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

1. 创建一个类，并继承GenericUDF

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
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;

/**
 * @author Kewei
 * @Date 2021/8/29 9:45
 */

public class MyUDF extends GenericUDF {
		// 初始化，一般做一些判断
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        if (objectInspectors.length != 1) {
            throw new UDFArgumentException("参数个数不为1");
        }
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }
		
		// 计算逻辑
    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {

        String input = deferredObjects[0].get().toString();
        if (input == null) {
            return 0;
        }

        return input.length();
    }
		
		// 执行hive函数转换为MapReduce的说明，一般设置为空
    @Override
    public String getDisplayString(String[] strings) {
        return "";
    }
}

1. 打成 jar 包上传到服务器

2. 将 jar 包添加到 hive 的 classpath

   add jar /root/hivejar/myudf.jar;
   

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

   create temporary function my_len as "MyUDF";
   

4. 然后就可以使用它

   select my_len('hello');
   

2.2 自定义UDTF

1. 创建一个类，并继承GenericUDTF

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;

/**
 * @author Kewei
 * @Date 2021/8/29 9:59
 */

public class MyUDTF extends GenericUDTF {

    private ArrayList<String> outList = new ArrayList<String>();
		
		// 初始化，需要返回一个StructObjectInspector 
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) 
            throws UDFArgumentException {
        ArrayList<String> fieldName = new ArrayList<>();
        ArrayList<ObjectInspector> fieldOis = new ArrayList<>();

        // 输出数据的列名
        fieldName.add("lineToWord");

        // 输出数据的类型
        fieldOis.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldName,fieldOis);
    }

		// 计算逻辑，需要使用forward，传出数据。
    @Override
    public void process(Object[] args) throws HiveException {
        String arg = args[0].toString();

        String splitKey = args[1].toString();

        String[] fields = arg.split(splitKey);

        for (String field : fields) {
            outList.clear();
            outList.add(field);
            forward(outList);
        }

    }

		// 一般为空
    @Override
    public void close() throws HiveException {

    }
}

1. 打成 jar 包上传到服务器

2. 将 jar 包添加到 hive 的 classpath

   add jar /root/hivejar/myudtf.jar;
   

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

   create temporary function myudtf as "MyUDTF";
   

4. 然后就可以使用它

   select myudtf('hello:spark:hadoop:hive',':');