• Hive (八) --------- 函数

    目录

    一、系统内置函数

    查看系统自带的函数

    hive> show functions;

    • 1

    显示自带的函数的用法

    hive> desc function upper;

    • 1

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

    hive> desc function extended upper;

    • 1

    二、常用内置函数

    1. 空字段赋值

    函数说明

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

    数据准备：采用员工表

    查询：如果员工的 comm 为 NULL，则用 -1 代替

    hive (default)> select comm,nvl(comm, -1) from emp;
OK
comm  _c1
NULL -1.0
300.0 300.0
500.0 500.0
NULL -1.0
1400.0 1400.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0
0.0   0.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    查询：如果员工的 comm 为 NULL，则用领导 id 代替

    hive (default)> select comm, nvl(comm,mgr) from emp;
OK
comm  _c1
NULL 7902.0
300.0 300.0
500.0 500.0
NULL 7839.0
1400.0 1400.0
NULL 7839.0
NULL 7839.0
NULL 7566.0
NULL NULL
0.0  0.0
NULL 7788.0
NULL 7698.0
NULL 7566.0
NULL 7782.0

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    2. CASE WHEN THEN ELSE END

    在这里插入图片描述

    需求

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

    dept_Id 男 女
  A     2 1
  B     1 2

    • 1
    • 2
    • 3

    创建本地 emp_sex.txt，导入数据

    [fancyry@hadoop102 datas]$ vi emp_sex.txt
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    创建 hive 表并导入数据

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    按需求查询数据

    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

    3. 行转列

    相关函数说明

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

    查询示例：

    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
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    4. 列转行

    函数说明

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

    LATERAL VIEW

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

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

    查询示例：

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    5. 窗口函数 (开窗函数)

    相关函数说明

    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 类型。

    数据准备：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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    需求

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

    创建本地 business.txt，导入数据

    [fancyry@hadoop102 datas]$ vi business.txt

    • 1

    创建 hive 表并导入数据

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    按需求查询数据

    A、查询在 2017 年 4 月份购买过的顾客及总人数

    select name,count(*) over () from business where substring(orderdate,1,7) = '2017-04' group by name;

    • 1

    B、查询顾客的购买明细及月购买总额

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

    • 1

    C、将每个顾客的 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

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

    查看顾客上次的购买时间

    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;

    • 1
    • 2
    • 3
    • 4

    查询前 20% 时间的订单信息

    select * from 
(select name,orderdate,cost, ntile(5) 
over(order by orderdate) sorted
from business

    • 1
    • 2
    • 3
    • 4

    6. Rank

    函数说明

    RANK() 排序相同时会重复，总数不会变。
    DENSE_RANK() 排序相同时会重复，总数会减少
    ROW_NUMBER() 会根据顺序计算

    创建本地 score.txt，导入数据

    [fancyry@hadoop102 datas]$ vi score.txt

    • 1

    创建 hive 表并导入数据

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    按需求查询数据

    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
    • 3
    • 4
    • 5
    • 6
    • 7

    三、自定义函数

    Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。

    当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数 (UDF：user-defined function) 。

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

    A、UDF (User-Defined-Function)

    一进一出

    B、UDAF (User-Defined Aggregation Function)

    聚集函数，多进一出
    类似于：count/max/min

    C、UDTF (User-Defined Table-Generating Functions)

    一进多出，如 lateral view explode()

    官方文档地址

    https://cwiki.apache.org/confluence/display/Hive/HivePlugins

    编程步骤：

    A、继承 Hive 提供的类

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

    • 1
    • 2

    B、实现类中的抽象方法

    C、在 hive 的命令行窗口创建函数

    添加 jar

    add jar linux_jar_path

    • 1

    创建 function

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

    • 1

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

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

    • 1

    四、自定义 UDF 函数

    需求：

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

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

    • 1
    • 2

    创建一个 Maven 工程 Hive

    导入依赖

    <dependencies>
	<dependency>
	<groupId>org.apache.hivegroupId>
	<artifactId>hive-execartifactId>
	<version>3.1.2version>
	dependency>
dependencies>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    创建一个类

    package com.fancyry.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.PrimitiveObjectIn spectorFactory;

/**
* 自定义 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 LengthError!!!");
		}
		
		// 判断输入参数的类型
		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 "";
 	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54

    打成 jar 包上传到服务器 /opt/module/data/myudf.jar

    将 jar 包添加到 hive 的 classpath

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

    • 1

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

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

    • 1

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

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

    • 1

    五、需求

    自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：

    hive(default)> select myudtf("hello,world,hadoop,hive", ",");
hello
world
hadoop
hive

    • 1
    • 2
    • 3
    • 4
    • 5

    代码实现

    package com.fancyry.udtf;
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;
public class MyUDTF extends GenericUDTF {
	
	private ArrayList<String> outList = new ArrayList<>();
    
    @Override
	public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
	
		//1.定义输出数据的列名和类型
		List<String> fieldNames = new ArrayList<>();
		List<ObjectInspector> fieldOIs = new ArrayList<>();
		//2.添加输出数据的列名和类型
		fieldNames.add("lineToWord");
		fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
		return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);
    }
	
	@Override
	public void process(Object[] args) throws HiveException {
		//1.获取原始数据
		String arg = args[0].toString();
		//2.获取数据传入的第二个参数，此处为分隔符
		String splitKey = args[1].toString();
		//3.将原始数据按照传入的分隔符进行切分
		String[] fields = arg.split(splitKey)//4.遍历切分后的结果，并写出
		for (String field : fields) {
			//集合为复用的，首先清空集合
			outList.clear();
			//将每一个单词添加至集合
			outList.add(field);
			//将集合内容写出
			forward(outList);
		}
	}
	@Override
	public void close() throws HiveException {
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47

    打成 jar 包上传到服务器 /opt/module/hive/data/myudtf.jar

    将 jar 包添加到 hive 的 classpath 下

    hive (default)> add jar /opt/module/hive/data/myudtf.jar;

    • 1

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

    hive (default)> create temporary function myudtf as "com.fancyry.hive.MyUDTF"; 

    • 1
    hive (default)> select myudtf("hello,world,hadoop,hive",",");

    • 1
  • 相关阅读:
    「微服务 | Nginx」upstream 模块负载均衡算法详解
    ThreadLocal原理以及内存泄露问题
    DDD领域驱动设计基础
    进程与线程
    Java程序员进阶全过程
    软考 系统架构设计师 简明教程 | 案例分析解题技巧
    Swagger2的使用和springboot的集成
    八年测开经验面试28K公司后，吐血整理出高频面试题和答案
    模拟算法及其优化
    手机看cad图的软件有哪些？软件推荐
  • 原文地址：https://blog.csdn.net/m0_51111980/article/details/126154295