系统内置函数

查看系统自带的函数：

show functions;

显示自带的函数的用法：

desc function upper;

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

desc function extended upper;

Hive的函数分为三类：

UDF：一行输入对应一行输出，例如：upper这类的字段转换
UDAF：多行输入对应一行输出（聚合函数），例如：max、count这类聚合函数
UDTF：一行输入对应多行输出（炸裂函数），例如：explode将一个Array、Map字段拆分成多行返回
常用函数
普通类型字段函数
大致同Oracle的各个函数： ```sql — nvl空值转换 select nvl(null, ‘a’)

— case when then else end select deptno, case deptno when 10 then ‘aaa’ when 20 then ‘bbb’ else ‘ccc’ end from dept;

— 或者 select deptno, case when deptno < 30 then ‘aaa’ when deptno < 40 then ‘bbb’ else ‘ccc’ end from dept

— concat 拼接字符串 select concat(dname, ‘,’, loc), o.* from dept o;

— 其他的，upper、lower、max、min、count、avg、abs、length、trim、lpad、rpad等函数同oracle select lpad(‘aa’, 8, ‘0’); — 位数不足8位时，向左边补零

— 取整： — ceil：向上取整 — floor：向下取整 — round: 四舍五入 select floor(1.25);


其他常用函数：
```sql
-- if 函数
select 
    deptno,
    -- if(判断条件, 判断为真, 否则)
    if(deptno > 30, 'aaa', 'bbb')
from dept;
-- concat_ws 拼接字符串：把分隔符提到最前面
-- 等同于 select concat(ename, ',', job, ',',  hiredate) , e.* from emp e;
select concat_ws(',', ename, job, hiredate) , e.* from emp e;
-- concat_ws函数 也可以将ARRAY类型的字段的每个元素使用指定分隔符拼接成字符串
select concat_ws(',', friends), t.* from test3 t;
-- split：将字符串转换成数组
select split(name,'n'), s.* from student1 s;
-- 获取当前时间的时间戳（长整形数字，相对于unix时间的偏移量）
select unix_timestamp();
-- 将日期字符串转换成时间戳的长整型
select unix_timestamp('2020-02-01', 'yyyy-MM-dd');
-- 将相对于unix时间偏移量的长整型数字转换为日期格式
select from_unixtime(1580515200); 
select from_unixtime(1580515200, 'yyyy/MM/dd');
-- 获取当前日期
select current_date();
-- 获取当前时间戳
select current_timestamp();
-- 获取时间戳中的日期
select to_date(current_timestamp());
select to_date('2022-01-01 10:12:00');
-- 也可以通过 year、month、day、hour、minute、second、weekofyear、dayofmonth 等相关函数获取年月日时分秒
select year('2022-01-01 10:12:00');
-- 两个日期之间的月数
select months_between('2022-01-01 10:12:00','2021-10-05 10:12:00');
-- 几个月后/前
select add_months('2022-01-01 10:12:00', -2);
select add_months('2022-01-01 10:12:00', 2, 'yyyy/MM/dd');
-- 计算两个日期之间相差的天数
select datediff('2022-01-01 10:12:00', '2021-10-24 10:12:00');
-- 日期加上n天
select date_add('2022-01-01 10:12:00', 2);
-- 获取月末的日期
select last_day('2022-01-01 10:12:00');
-- 日期格式化
select date_format('2022-01-01 10:12:00', 'yyyy/MM/dd');
-- 正则表达式匹配替换
select regexp_replace('2020/10/25', '/', '-')

集合操作

示例：

-- 获取Array、Map字段中的元素个数
select size(friends),size(children) from test3;
-- 获取map中的所有key
select map_keys(children) from test3;
-- 获取map中的所有value
select map_values(children) from test3;
-- 判断array中是否包含某个元素
select array_contains(friends,'Jack') from test3;
-- 将array中的元素进行排序
select sort_array(friends) from test3;

行转列函数

行转列：将多行数据的某个字段合并成一个值

-- collect_set：将多行数据中的某个字段合并拼接成一个数组（行转列）
-- collect_set只接收基本数据类型，且会将字段的值进行去重，然后产生Array类型的字段
select collect_set(deptno) from dept;
-- 功能大致同collect_set，但是collect_list不会进行去重，collect_set会进行去重
select collect_list(deptno) from dept;

示例：

-- 将相同部门、相同工作的雇员拼接到一起
select
    concat_ws('|',collect_list(ename)),   -- 使用collect_list聚合，然后使用concat_ws将数组元素拼接成字符串
    concat(job,',',deptno)  -- 分组字段job、deptno，将分组字段使用逗号拼接到一起
from emp 
group by 
    job,deptno;

列转行函数

列转行：将一个值拆分成多行进行展示

-- explode：将Array字段或者Map字段拆分成多行（列转行）
-- 将数组元素拆分成多行返回，每行只存储一个元素
select explode(friends) from test3;
-- 将map拆分成多行返回，返回两个字段，一个key字段，一个value字段，每行存储一对key-value
select explode(children) from test3;
-- lateral view ：侧写
-- 直接select explode函数时，不能再同时查询其他字段，否则会报错
-- 如果想要同时关联展示表中的其他字段，可以使用 lateral view 进行侧写
select 
    name,  -- 可以正常查询其他字段
    friends,
    children, 
    perFriend   -- 查询侧写字段（即展开成多行的friends字段），最后效果类似联合查询
from 
    test3
lateral view 
    explode(friends) perFriend as perFriend; -- 通过lateral view 将展开了的friends字段进行侧写，将输出的结果列起别名为perFriend
-- 如果是map，会展开成两列，所以侧写时，as语句后要有两个字段名
select 
    name,
    friends,
    children, 
    perChildName,  -- 展开了的chidren中每个key
    perChildAge    -- 展开了的chidren中每个value
from 
    test3
lateral view 
    explode(children) perChildInfo as perChildName,perChildAge  -- map会展开成两列，所以as语句后要有两个字段名

Over窗口函数

函数说明

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

其中over()函数内可以使用的关键字：

CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：没有边界的
- UNBOUNDED RRECEDING：从前面的起点
- UNBOUNDED FOLLOWING：到后面的终点

示例

查询10号部门的每个雇员，以及总人数：

select 
    ename, 
    -- 每个雇员的数据行中，都会显示10号部门的总人数
    count(*) over()   -- 此处count(*)的分组条件是over开窗内的分组条件，因为此处over内容为空，所以就直接统计了总人数
from emp 
where deptno = 10;

查询10号部门的每个雇员，以及和该雇员从事相同工作的总人数：

select 
    ename,
    deptno, 
    job, 
    count(*) over(partition by job) -- 此处在over内增加分组条件 job，就会按job进行分组统计
from emp 
where deptno = 30;

查询每个雇员信息，并且在雇员后面展示该雇员所在部门的薪资累加：同一个部门的1号雇员展示自己的薪资，2号雇员展示1号和2号的累加，3号雇员展示1/2/3雇员的累加，以此类推

select 
    e.*, 
    sum(sal) over(
        partition by deptno 
        order by deptno,empno) -- 加入 order by ，使其按顺序进行累加展示（默认统计的就是从起点数据到当前行）
from emp e 
order by deptno,empno;
-- 或者完整版写法：
select 
    e.*, 
    sum(sal) over(
        partition by deptno 
        order by deptno,empno 
        rows between unbounded preceding and current row) -- 累加时，只累加从起点到当前行的数据，不计算后面的数据
from emp e 
order by deptno,empno

查询每个雇员信息，并在雇员后展示该雇员所在部门的薪资汇总：汇总该雇员前面1位雇员 (如果存在）+ 自己 + 自己后面1位雇员(如果存在)

select 
    e.*, 
    sum(sal) over(
        partition by deptno 
        order by deptno,empno 
        rows between 1 preceding and 1 following) -- 累加时：从自己的前一行开始，到自己的后一行
from emp e 
order by deptno,empno

注意事项

如果order by排序的数据有重复的，hive、oracle会将这两行数据认为是同一行数据。

例如：有以下数据

1
2
3
3  // 同时存在两条数据为3
4
5

此时使用 SQL 查询：

select
  id,
  sum(id) over(order by id)
from test

得到的结果为：

1    1
2    3
3    9   // 两条id=3的数据的sum值都是9
3    9
4    13
5    18

总结

带有over()窗口的SQL：

先由where条件限定结果集的范围
然后partition by决定如何进行划定窗口分区，类似group by
然后在分区内，再由order by决定数据的排序，rows between决定数据计算的范围。
如果只有order by，没有rows between，则默认的数据范围是从该分区的起点到当前行

其他搭配over使用的函数

Oracle、Hive都有下列函数。

LAG(col, n, default_val)：往前第n行数据

LEAD(col, n, default_val)：往后第n行数据

NTILE(n)：把有序窗口的行分发到指定数量的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。n必须为int类型

示例：

获取同一个部门中，上一个雇员的薪资，如果该雇员为第一个，则返回0

select 
    e.*,
    -- 使用lag获取前1个雇员的薪资，lag(要获取的字段， 往前第几个, 如果不存在则返回的默认值/字段) 
    -- 默认值也可以不设置，如果不设置则不存在时返回值为null
    -- 默认值如果设置的是个字段，则取的是当前行的字段（因为前面那行不存在，所以是直接本身的字段）
    lag(sal, 1, 0) over (partition by deptno order by empno)
from emp e
order by deptno, empno;

将雇员按照雇佣时间分为5组：

select 
    e.*,
    ntile(5) over(order by hiredate)  -- 按照雇佣时间排序，均分为5组（如果不能均分，最后一组的数量会少一些）
from emp e
order by hiredate

窗口内排序

搭配over()使用的排序的相关函数：（Oracle、Hive相同）

rank()：排序时如果数值相同会重复，总数不变
DENSE_RANK()：排序时如果数值相同会重复，总数会减少
ROW_NUMBER()：会根据顺序计算（即最后显示的是行号）

示例：
将每个部门的雇员按薪资从高到低排序：

select 
    e.*,
    rank() over(partition by deptno order by sal desc),  -- 使用 rank() 排序
    dense_rank() over(partition by deptno order by sal desc), -- 使用 dense_rank() 排序
    row_number() over(partition by deptno order by sal desc) -- 使用row_number()排序
from emp e
order by deptno, sal desc

其中deptNo=30的雇员薪资有重复的，三种不同的排序方式的结果为：

SAL	rank	dense_rank	row_number
2850	1	1	1
1600	2	2	2
1500	3	3	3
1250	4	4	4
1250	4	4	5
950	6	5	6

数据中存在两条SAL=1250的数据，rank和dense_rank对这两条数据的排序结果都是4。但是对于下一条数据（SAL=950），rank的排序号是在两个1250后的6，而dense_rank的排序号是连续的5。

Grouping Sets 多维分析函数

Grouping Sets用于多维分析使用，等价于将几个group by语句进行union。

Grouping Sets中传入统计的维度，可以传入多个维度，按逗号分隔

例如：

按deptNo + job 进行统计

select deptno,job,sum(sal) from emp group by deptno ,job
grouping sets((deptno,job));
-- 等价于
select deptno,job,sum(sal) from emp group by deptno ,job

按deptNo + job 统计，并按deptNo进行统计：

select deptno,job,sum(sal) from emp group by deptno ,job
grouping sets((deptno,job), deptNo);
-- 等价于
select deptno,job,sum(sal) from emp group by deptno ,job
union 
select deptno,sum(sal) from emp group by deptno

按deptNo + job统计，按deptNo统计，统计合计：

select deptno,job,sum(sal) from emp
group by deptno ,job
grouping sets((deptno,job), deptNo, ());
-- 等价于
select deptno,job,sum(sal) from emp group by deptno ,job
union 
select deptno,sum(sal) from emp group by deptno
union
select sum(sal) from emp

自定义函数

当Hive提供的内置函数无法满足需求时，就可以考虑使用自定义函数 UDF （user-defined function）。

用户自定义函数分为三种：

UDF（User-Defined-Function)：单行输入单行输出
UDAF（User-Defined Aggregation Function）：聚合函数，多行输入单行输出
UDTF（User-Defined Table-Generating Function）：炸裂函数，单行输入多行输出

开发自定义函数步骤

开发步骤：

继承Hive提供的类

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

实现类中的抽象方法
在Hive的命令行窗口创建函数。
添加jar：
```
add jar 路径
```
创建function：

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

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

自定义UDF函数示例

创建maven项目，引入依赖：

<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <!-- 对应hive版本的依赖 -->
    <version>3.1.3</version>
</dependency>

编写自定义函数MyUDF，用于计算字符串长度：

package com.study.udf;
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.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorConverter;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
/**
 * 自定义函数：返回字符串长度
 * @author tengyer 2022/08/18 15:22
 */
public class MyUDF extends GenericUDF {
    /**
     * 初始化操作，可以用于做一些校验等
     * @param arguments 传入的参数
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        if(arguments.length != 1) {
            throw new UDFArgumentException("参数个数不为1");
        }
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;  // 最后函数的返回值类型，此处是返回字符串长度，所以设置为Int类型
    }
    /**
     * 运算：获取输入的字符串的长度
     * @param arguments 输入的参数
     */
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        if(arguments[0] == null) {
            return 0;
        }
        // 获取输入的字符串
        String inputArgument = arguments[0].get().toString();
        return inputArgument.length();  // 返回字符串的长度
    }
    /**
     * 用于展示执行计划用，自定义函数直接返回空就行
     */
    @Override
    public String getDisplayString(String[] strings) {
        return "";
    }
}

然后使用maven的package打成jar包。

将 jar 包重命名成一个简单的名称，然后上传到 Hive 的 lib 目录。（实际上，上传到Linux的任意路径都可以，但是习惯上一般上传到Hive的lib目录）。

Hive不会自动加载lib下的jar，只有重启Hive时才会重新加载。如果想要立即生效，可以在Hive中手动加入该jar：

add jar /opt/module/hive-3.1.3/lib/MyUDF.jar

然后便可以创建函数：

create temporary function my_len as "com.study.udf.MyUDF";

测试自定义函数：

select my_len('aaa');

自定义UDTF函数示例

编写Java：

package com.study.udf;
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 org.apache.logging.log4j.core.appender.routing.Route;
import java.util.ArrayList;
import java.util.List;
/**
 * 自定义炸裂函数：将一个字符串按逗号拆分，分成多个数据行
 * 例如：aaa,bbb,ccc,ddd 炸裂成4行数据
 * @author tengyer 2022/08/18 15:53
 */
public class MyUDTF extends GenericUDTF {
    /**
     * 初始化方法
     * @param argOIs
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 输出数据的列名、类型都是集合，因为可以从一个字段值炸裂出多个列。例如可以将一个map值explode出：一个key列、一个value列
        // 所以炸裂函数可以输出多个列。输出的列名、类型都需要是集合
        // 输出数据的默认列名，可以被SQL中的as别名覆盖掉
        List<String> fieldNames = new ArrayList<>();
        fieldNames.add("word"); // 此处我们只需要将字符串拆分出一个列即可，列名叫word
        // 输出数据的类型
        List<ObjectInspector> fieldOIs = new ArrayList<>();
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);  // 我们的字符串拆分出来的结果列的类型依然是字符串
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }
    /**
     * 处理输入数据：
     * @param args  以逗号为分隔符的字符串，例如aaa,bbb,ccc,ddd
     * @throws HiveException
     */
    @Override
    public void process(Object[] args) throws HiveException {
        // 取出输入的数据并按逗号拆分
        String input = args[0].toString();
        String[] words = input.split(",");
        for (String word : words) {
            // 输出的结果。因为炸裂的结果可能不止一列，所以是个集合
            // 此处我们只拆分成一列，所以只需要add一个数据
            List<String> resultWordList = new ArrayList<>();
            resultWordList.add(word);
            // 通过forward方法输出
            forward(resultWordList);
        }
    }
    /**
     * 结束时关闭资源
     * @throws HiveException
     */
    @Override
    public void close() throws HiveException {
        // 这里我们没有打开的IO流等资源，所以不需要关闭资源
    }
}

使用maven编译打包，上传Linux服务器。

通过add jar 引入该jar，创建函数：

create temporary function my_split as "com.study.udf.MyUDTF";

测试函数：

select my_split('aaa,bbb,ccc,ddd');

最终输出一个word列，将字符串拆分成该列的多行数据。

自定义UDTF函数示例2

将字符串炸裂成多列：

package com.study.udf;
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;
import java.util.List;
/**
 * 自定义炸裂函数：将一个字符串按逗号拆分，分成多个数据行；每行再按冒号拆分成两个字段
 * 例如 aaa:123,bbb:456,ccc:789，其中按逗号拆分成多行，每行再按冒号拆分成多列
 * @author tengyer 2022/08/18 15:53
 */
public class MyUDTF2 extends GenericUDTF {
    /**
     * 初始化方法
     * @param argOIs
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 输出数据的默认列名，可以被SQL中的as别名覆盖掉
        List<String> fieldNames = new ArrayList<>();
        fieldNames.add("word_key"); // 冒号前面的字符炸裂的字段名
        fieldNames.add("word_value"); // 冒号后面的字符炸裂的字段名
        // 输出数据的类型
        List<ObjectInspector> fieldOIs = new ArrayList<>();
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);  // 冒号前面的字符类型是字符串
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);  // 冒号后面的字符类型是字符串
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }
    /**
     * 处理输入数据：
     * @param args  以逗号+冒号为分隔符的字符串，例如 aaa:123,bbb:456,ccc:789
     * @throws HiveException
     */
    @Override
    public void process(Object[] args) throws HiveException {
        // 取出输入的数据并按逗号拆分
        String input = args[0].toString();
        String[] entries = input.split(",");  // 按逗号拆分成多行
        for (String entry : entries) {
            // 每行按冒号拆分成多个字段
            String[] result = entry.split(":");
            List<String> resultWordList = new ArrayList<>();
            resultWordList.add(result[0]);
            resultWordList.add(result[1]);
            // 通过forward方法输出
            forward(resultWordList);
        }
    }
    /**
     * 结束时关闭资源
     * @throws HiveException
     */
    @Override
    public void close() throws HiveException {
        // 这里我们没有打开的IO流等资源，所以不需要关闭资源
    }
}

指定为自定义函数：

create temporary function my_split2 as "com.study.udf.MyUDTF2";

查询：

select my_split2('aaa:123,bbb:456,ccc:789');

查询结果：

word_key    word_value
aaa            123
bbb            456
ccc            789

大数据Hadoop

08-函数