Hive函数怎么用

这篇文章主要介绍了Hive函数怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

创新互联长期为成百上千客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为个旧企业提供专业的成都网站设计、成都网站建设、外贸网站建设，个旧网站改版等技术服务。拥有10余年丰富建站经验和众多成功案例,为您定制开发。

1、函数

1.1 、系统内置函数

1）查看系统自带的函数
 show functions;   --289个
 
2）显示自带的函数的用法
 desc function upper;
 
3）详细显示自带的函数的用法
 desc function extended upper;

1.2、常用内置函数

1.2.1、空字段赋值（NVL）

--格式：NVL(value，default_value)  value为字段 default_value返回默认值

--案例：当奖金为Null时,用0替代
select ename, comm, sal, nvl(comm,0)
from emp;

1.2.2、CASE WHEN THEN ELSE END

--格式：case 字段 when 'char' then 1 else 0 end;

--案例：
+---------------+------------------+--------------+
| emp_sex.name  | emp_sex.dept_id  | emp_sex.sex  |
+---------------+------------------+--------------+
| 悟空           | A                | 男           |
| 大海           | A                | 男           |
| 宋宋           | B                | 男           |
| 凤姐           | A                | 女           |
| 婷姐           | B                | 女           |
| 婷婷           | B                | 女           |
+---------------+------------------+--------------+
+----------+-------+---------+
| dept_id  | male  | female  |
+----------+-------+---------+
| A        | 2     | 1       |
| B        | 1     | 2       |
+----------+-------+---------+
select 
  dept_id,
  sum(case sex when '男' then 1 else 0 end) male,
  sum(case sex when '女' then 1 else 0 end) female
from emp_sex
group by dept_id;

--拓展函数之if:  格式:if(sex='男',1,0)  true返回1，false返回0
select 
  dept_id,
  sum(if(sex='男',1,0)) male,
  sum(if(sex='女',1,0)) female
from emp_sex
group by dept_id;

1.2.3、行转列

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类型字段。
--COLLECT_LIST(col)：函数只接受基本数据类型，主要作用是将某字段的值进行不去重汇总，产生array类型字段。

1.2.3.2、案例实操

数据准备：
+---------------+------------------+--------------+
|     name      |  constellation   |  blood_type  |
+---------------+------------------+--------------+
| 悟空           | 射手座           | A            |
| 八戒           | 天秤座           | A            |
| 路飞           | 射手座           | B            |
| 娜美           | 射手座           | A            |
| 女帝           | 天秤座           | A            |
| 罗宾           | 射手座           | B            |
+---------------+------------------+--------------+

需求：把星座和血型一样的人归类到一起。结果如下：
+-------------+-----------------+
|  天秤座，A   |   八戒|女帝     |
|  射手座，A   |   悟空|娜美     |
|  射手座，B   |   路飞|罗宾     |
+-------------+-----------------+

按需求查询数据：
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.2.4.1、相关函数说明

--Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。

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

--LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆
分后的数据进行聚合。

lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一
个支持别名表的虚拟表。

1.2.4.2、案例实操

数据准备：
+------------------+--------------------------+
|      movie       |  category                |
+------------------+--------------------------+
| 《疑犯追踪》      |  悬疑,动作,科幻,剧情      |
| 《Lie to me》    |  悬疑,警匪,动作,心理,剧情  |
| 《战狼2》        |  战争,动作,灾难            |
+-----------------+---------------------------+

需求：将电影分类中的数组数据展开。结果如下
《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》    悬疑
《Lie to me》    警匪
《Lie to me》    动作
《Lie to me》    心理
《Lie to me》    剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

按需求查询数据：
SELECT movie,category_name 
FROM movie_info 
lateral VIEW
explode(split(category,",")) movie_info_tmp  AS category_name ;

1.2.5、窗口函数（开窗函数）

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行数据

--FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值
--LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值

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

1.2.5.2、案例实操

数据准备：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）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的cost按照日期进行累加
（4）查询顾客购买明细以及上次的购买时间和下次购买时间
（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
（6）查询前20%时间的订单信息

按需求查询数据：
（1）查询在2017年4月份购买过的顾客及总人数
	select name,count(name) over() Person_num
	from business
	where month(orderdate)=4  //或者where substring(orderdate,1,7)='2017-04'
	group by name;

（2）查询顾客的购买明细及月购买总额
	select name,orderdate,cost,
		sum(cost) over(partition by name,month(orderdate)) month_amount
	from business;

（3）将每个顾客的cost按照日期进行累加
	select name,orderdate,cost,
		sum(cost) over(partition by name order by orderdate 
		//rows between unbounded preceding and current row) accu_cost --默认 可以不加
	from business;
注意：
rows必须跟在Order by子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量
order by子句后面不加 rows between and,默认为rows between unbounded preceding and current row

（4）查询顾客购买明细以及上次的购买时间和下次购买时间
	select name,orderdate,cost,
	     lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) last_date,
	     lead(orderdate,1,'1970-01-01') over(partition by name order by orderdate) next_date
	from business;

（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
	select name,orderdate,cost,
	     first_value(orderdate)
		     over(partition by name,month(orderdate) order by orderdate 
		     rows between unbounded preceding and unbounded following) first_date,
	     last_value(orderdate) 
		     over(partition by name,month(orderdate) order by orderdate 
		     rows between unbounded preceding and unbounded following) last_date
	from business;

（6）查询前20%时间的订单信息
	select *
	from (
		select name, orderdate, cost, ntile(5) over (order by orderdate) date_rank
		from business
		 )t1
	where t1.date_rank=1;

1.2.6、Rank

1.2.6.1、相关函数说明

--RANK() 排序相同时会重复(考虑并列,会跳号)，总数不会变
--DENSE_RANK() 排序相同时会重复(考虑并列,不跳号)，总数会减少
--ROW_NUMBER() 会根据顺序计算(不考虑并列,不跳号,行号)

1.2.6.2、案例实操

数据准备：
	name	subject	score
	孙悟空	语文	87
	孙悟空	数学	95
	孙悟空	英语	68
	路飞	语文	94
	路飞	数学	56
	路飞	英语	84
	柯南	语文	64
	柯南	数学	86
	柯南	英语	84
	艾伦	语文	65
	艾伦	数学	85
	艾伦	英语	78

需求：计算每门学科成绩排名。

按需求查询数据：
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;

结果：
name    subject score   rp      drp     rmp
孙悟空   数学    95      1       1       1
柯南     数学    86      2       2       2
艾伦     数学    85      3       3       3
路飞     数学    56      4       4       4
柯南     英语    84      1       1       1
路飞     英语    84      1       1       2
艾伦     英语    78      3       2       3
孙悟空   英语    68      4       3       4
路飞     语文    94      1       1       1
孙悟空   语文    87      2       2       2
艾伦     语文    65      3       3       3
柯南     语文    64      4       4       4

1.3、自定义函数

1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数
3）根据用户自定义函数类别分为以下三种：
	--UDF（User-Defined-Function）
		一进一出

	--UDAF（User-Defined Aggregation Function）
		用户自定义聚合函数，多进一出
		类似于：count/max/min

	--UDTF（User-Defined Table-Generating Functions）
		用户自定义表生成函数，一进多出
		如lateral view explode()
4）官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）编程步骤：
	①继承Hive提供的类
 		org.apache.hadoop.hive.ql.udf.generic.GenericUDF  
		org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
	②实现类中的抽象方法
	③在hive的命令行窗口创建函数
		添加jar
			add jar linux_jar_path
		创建function
			create [temporary] function [dbname.]function_name AS class_name;
		④在hive的命令行窗口删除函数
			drop [temporary] function [if exists] [dbname.]function_name;

1.4、自定义UDF函数

需求:自定义一个UDF实现计算给定字符串的长度，例如：
	select my_len("abcd");
	4
1）创建一个Maven工程Hive
2）导入依赖

	
		org.apache.hive
		hive-exec
		3.1.2
	

3）创建一个类
/**
 * 一、自定义UDF函数，需要继承GenericUDF类
 * 需求: 计算指定字符串的长度
 */
public class Mylength extends GenericUDF {

    /**
     * 二、初始化方法,里面要做三件事
     * 1.约束函数传入参数的个数
     * 2.约束函数传入参数的类型
     * 3.约束函数返回值的类型
     * @param arguments  函数传入参数的类型
     * @return
     * @throws UDFArgumentException
     */
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        //1.约束函数传入参数的个数
        if (arguments.length != 1){
            throw new UDFArgumentLengthException("Input args num error!!!");
        }
        //2.约束函数传入参数的类型
        if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE))
            //第一个参数：第几个数据
            //第二个参数 ：错误信息
            throw new UDFArgumentTypeException(0,"Input args type error!!!");
        //3.约束函数返回值的类型
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
     * 函数逻辑处理方法
     * @param arguments  函数传入参数的值
     * @return
     * @throws HiveException
     */
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        //获取函数传入参数的值
        Object o = arguments[0].get();
        //将object转换为字符串
        int length = o.toString().length();
        //因为在上面的初始化方法里面已经对函数返回值类型做了约束,必须返回一个int类型
        //所以我们要在这个地方直接返回length
        return length;
    }

    /**
     * 返回显示字符串方法,这个方法不用管,直接返回一个空字符串
     * @param children
     * @return
     */
    public String getDisplayString(String[] children) {
        return "";
    }
}

4）创建临时函数
	①打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
	②将jar包添加到hive的classpath，临时生效
		hive (default)> add jar /opt/module/hive/datas/myudf.jar;
	③创建临时函数与开发好的java class关联
		hive (default)> create temporary function my_len as "com.atguigu.test.Mylength";
	④即可在hql中使用自定义的临时函数
		hive (default)> select ename,my_len(ename) ename_len from emp;
	⑤删除临时函数
		hive (default)> drop  temporary function my_len;

--注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以
使用，其他会话全都不能使用。

5）创建永久函数
	①在$HIVE_HOME下面创建auxlib目录(固定名称不能更改)
		mkdir auxlib
	②将jar包上传到$HIVE_HOME/auxlib下，然后重启hive
	③创建永久函数
		hive (default)> create function my_len2 as " com.atguigu.test.Mylength";
	④即可在hql中使用自定义的永久函数 
		hive (default)> select ename,my_len2(ename) ename_len from emp;
	⑤删除永久函数 
		hive (default)> drop function my_len2;

--注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。
永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名

感谢你能够认真阅读完这篇文章，希望小编分享的“Hive函数怎么用”这篇文章对大家有帮助，同时也希望大家多多支持创新互联，关注创新互联行业资讯频道，更多相关知识等着你来学习!