Hive函数

目录

1、 系统内置函数

2、 常用内置函数

2.1 空字段赋值

2.2 CASE WHEN

2.3 行转列

2.4 列转行

2.5 日期相关函数

2.6 unix_timestamp、from_unixtime时间戳函数的使用

2.7 cast函数

2.8 With as 函数用法 

2.9 named_struct

2.10 STR_TO_MAP函数

3、 自定义函数

4、 自定义UDF函数

---

---

1、 系统内置函数

1）查看系统自带的函数

hive> show functions;

2）显示自带的函数的用法

hive> desc function upper;

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

hive> desc function extended upper;

2、 常用内置函数

2.1 空字段赋值

1）函数说明

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

2）数据准备：采用员工表

3）查询：如果员工的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

4）查询：如果员工的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

2.2 CASE WHEN

1）数据准备

name	dept_id	sex
悟空	A	男
大大	A	男
拉拉	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

2）需求

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

A     2       1

B     1       2

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

[Mark@hadoop102 datas]$ vi emp_sex.txt

悟空 A   男
大大 A   男
拉拉 B   男
凤姐 A   女
婷姐 B   女
婷婷 B   女

4）创建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/datas/emp_sex.txt' into table emp_sex;

5）按需求查询数据

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;

2.3 行转列

1）相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。
分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

COLLECT_LIST(col)：不去重。

2）数据准备

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

3）需求

把星座和血型一样的人归类到一起。结果如下：

射手座,A            大海|凤姐

白羊座,A            孙悟空|猪八戒

白羊座,B            宋宋|苍老师

4）创建本地constellation.txt，导入数据

[Mark@hadoop102 datas]$ vi constellation.txt

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

5）创建hive表并导入数据

create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/datas/constellation.txt" into table person_info;

6）按需求查询数据

select
    t1.base,
    concat_ws('|', collect_set(t1.name)) name

from
    (select
        name,
        concat(constellation, ",", blood_type) base
    from
        person_info) t1
group by
t1.base;

方法二：

select
    concat(constellation,",",blood_type) xzxx,
    concat_ws("|",collect_list(name))
from
    person_info
group by
        constellation,blood_type;

2.4 列转行

1）函数说明

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

LATERAL VIEW

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

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

2）数据准备

表6-7 数据准备

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

3）需求

将电影分类中的数组数据展开。结果如下：

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

4）创建本地movie.txt，导入数据

[Mark@hadoop102 datas]$ vi movie.txt

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

5）创建hive表并导入数据

create table movie_info(
    movie string,
    category string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/datas/movie.txt" into table movie_info;

6）按需求查询数据

select
    m.movie,
    tbl.cate
from
    movie_info m
lateral view
    explode(split(category, ",")) tbl as cate;

2.5 日期相关函数

（1）current_date返回当前日期

select current_date();

（2）date_add, date_sub 日期的加减

--今天开始90天以后的日期

select date_add(current_date(), 90);

--今天开始90天以前的日期

select date_sub(current_date(), 90);

（3）两个日期之间的日期差

--今天和1990年6月4日的天数差

SELECT datediff(CURRENT_DATE(), "1990-06-04");

(4) date_format函数（根据格式整理日期）

hive (gmall)> select date_format('2020-06-14','yyyy-MM');
2020-06

 (5）next_day函数

（1）取当前天的下一个周一
hive (gmall)> select next_day('2020-06-14','MO');
2020-06-15
说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

（2）取当前周的周一
hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8

(6）last_day函数（求当月最后一天日期）

hive (gmall)> select last_day('2020-06-14');
2020-06-30

2.6 unix_timestamp、from_unixtime时间戳函数的使用

 unix_timestamp函数用法

UNIX_TIMESTAMP() ：若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳 

UNIX_TIMESTAMP(date) ：若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。

 from_unixtime函数用法 

语法：from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’) 
其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。 
对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如： 
select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cn from ttengine_hour_data where …

2.7 cast函数

CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。

语法：CAST (expression AS data_type)

expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。


可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

二进制，同带binary前缀的效果 : BINARY    
字符型，可带参数 : CHAR()     
日期 : DATE     
时间: TIME     
日期时间型 : DATETIME     
浮点数 : DECIMAL      
整数 : SIGNED     
无符号整数 : UNSIGNED 

 例子：

1.SELECT CAST('9.0' AS decimal)  结果：9
2.SELECT CAST('9.5' AS decimal(10,2))  结果：9.5(精度与小数位数分别为10与2。精度是总的数字位数，包括小数点左边和右边位数的总和
而小数位数是小数点右边的位数)
3.SELECT  CAST(NOW() AS   DATE) 结果：2017-11-27
now() 2017-11-27 10:43:22

2.8 With as 函数用法 

–- 针对一个别名
with tmp as (select * from tb_name)

–- 针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…

–- 相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;

–- 相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;

其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。

向一张表插入数据的with as用法

insert into table2
with
s1 as (select rownum c1 from dual connect by rownum <= 10),
s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where…;
select s1.sid, s2.sid from s1 ,s2需要有关联条件，不然结果会是笛卡尔积。
with as 相当于虚拟视图。

with as短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个sql片断，该sql片断会被整个sql语句所用到。有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。
　　
特别对于union all比较有用。因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select ‘no records’ from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);

WITH语句的优点:

(1). SQL可读性增强。比如对于特定with子查询取个有意义的名字等。

(2)、with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。

举例:在进行导入EXCEL的过程中，有时候，需要将数据存储在临时表中，当下一次在进行导入的时候，进行清除临时表的数据，但是这时候，有时候发生并发问题的话，两个用户可能会分别操作对方的数据，所以，可能造成混乱，但是可以使用WITH函数和UNION语句拼接一个SQL语句，存储在SESSION中，当需要导出错误信息的时候，可以使用该语句构造数据。

2.9 named_struct

主要用这个函数做json拼接:

select 	named_struct(
					  	'"supplier_sku_id"',supplier_sku_id,
						'"supplier_id"',t1.supplier_id,
						'"prefee_rate"',prefee_rate,
					  	'"order_rate"',order_rate,
					  	'"goRate"',go_rate,
					  	'"completeRate"',compliance_prefee_rate
					 ) as detail
from 	table1

特别记录下这个格式，如果是这个函数的返回值存储，存储类型要这样，比如我写了5个字段，创建的字段类型是：
struct<supplier_sku_id:bigint,supplier_id:bigint,prefee_rate:double,order_rate:double,goRate:double,completeRate:double>

 函数说明：
用途：使用给定的name/value列表建立STRUCT。

参数说明：
value：value可为任意类型。
name：指定的STRING类型的Field名称。此参数为常量。
返回值说明：返回STRUCT类型。Field的名称依次为name1，name2，…。

 最后结构体里面的key是以建表的语句为准。
如果用的是阿里云，建表时候也需要指定    set odps.sql.type.system.odps2 = true;
不指定的话放进去就是个地址值

2.10 STR_TO_MAP函数

-- 语法描述
STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter)

-- 功能描述
使用listDelimiter将text分隔成K-V对，然后使用keyValueDelimiter分隔每个K-V对，组装成MAP返回。默认listDelimiter为（ ，），keyValueDelimiter为（=）。

-- 案例
str_to_map('1001=2020-06-14,1002=2020-06-14',  ','  ,  '=')
输出
{"1001":"2020-06-14","1002":"2020-06-14"}

3、 自定义函数

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

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

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

（1）UDF（User-Defined-Function）

         一进一出

（2）UDAF（User-Defined Aggregation Function）

         聚集函数，多进一出

         类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）

         一进多出

         如lateral view explore()

4）官方文档地址

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

5）编程步骤：

（1）继承org.apache.hadoop.hive.ql.exec.UDF
（2）需要实现evaluate函数；evaluate函数支持重载；
（3）在hive的命令行窗口创建函数

    添加jar
    add jar linux_jar_path

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

（4）在hive的命令行窗口删除函数
    Drop [temporary] function [if exists] [dbname.]function_name;

6）注意事项：UDF必须要有返回类型，可以返回null，但是返回类型不能为void；

4、 自定义UDF函数

1）创建一个Maven工程Hive

2）导入依赖

<dependencies>
        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>
</dependencies>

3）创建一个类

package com.atguigu.hive;
import org.apache.hadoop.hive.ql.exec.UDF;

public class Lower extends UDF {
    public String evaluate (final String s) {

        if (s == null) {

            return null;
        }

        return s.toLowerCase();
    }
}

4）打成jar包上传到服务器/opt/module/jars/udf.jar

5）将jar包添加到hive的classpath

hive (default)> add jar /opt/module/datas/udf.jar;

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

hive (default)> create temporary function mylower as "com.atguigu.hive.Lower";

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

hive (default)> select ename, mylower(ename) lowername from emp;