12 hive函数

 

 

 

 

 

 

 

hive内置函数

 

零、判断函数

 

 

nvl函数

SELECT nvl(itcast_school_id, -1) FROM itcast_intention_dwd dwd;

如果第一个参数为空，返回第二个参数，否则返回第一个参数。

 

if函数

SELECT if(itcast_school_id is null, -1, itcast_school_id) FROM itcast_intention_dwd dwd;

 

如果itcast_school_id 为null则返回-1，否则返回itcast_school_id

 

 

case when then

一、case和when分开，进行等值判断

 

select case 100     when 50 then 'tom'     when 100 then 'mary'     else 'tim' end from lxw_dual; #输出：mary

 

 

 

 

 

二、case和when在一起，类似于if判断

select case when 50=100 then 0 when 50=50 then 1 else 2 end;

 

 

缺点：写起来麻烦

优点：读起来简单

 

 

coalesce

 

select coalesce(null,null,3); #  3

返回第一个不为空的参数；如果所有参数都为空，则返回空。

 

isnull

语法：isnull( a )

返回值：boolean

说明：如果a为null就返回true，否则返回false。

isnotnull

语法：isnotnull(a)

返回值：boolean

说明：如果a为非null就返回true，否则返回false。

 

 

一、数学函数

 

1.1 取整函数: round

语法: round(double a)

返回值:BIGINT

说明:返回double类型的整数值部分（遵循四舍五入）

举例：

 

hive> select round(3.1415926);

3.0

---

 

1.2 指定精度取整函数: round

 

语法: round(double a, int d)

返回值:DOUBLE

说明:返回指定精度d的double类型

举例：

 

hive> select round(3.1415926,4);

3.1416

 

---

 

1.3 向上取整函数: ceil

语法: ceil(double a)

返回值: BIGINT

说明:返回等于或者大于该double变量的最小的整数

举例：

hive> select ceil(3.1415926) ;

4

 

 

1.4  向下取整函数: floor (小于当前值的最大整数)

语法:floor(double a)

返回值:BIGINT

说明:返回等于或者小于该double变量的最大的整数

举例：

hive> select floor(3.1415926);

3

 

1.5 取随机数函数: rand

语法: rand(),rand(int seed)

返回值: double

说明:返回一个0到1范围内的随机数。如果指定种子seed，则会返回固定的随机数

 

# 取0~100 之间的整数

select round(rand()*100);

 

 

1.6 幂运算函数: pow

语法:pow(double a, double p)

返回值:double

说明:返回a的p次幂

举例：

hive> select pow(2,4) ;

16.0

 

1.7 绝对值函数: abs

语法: abs(double a)  abs(int a)

返回值: double       int

说明:返回数值a的绝对值

举例：

hive> select abs(-3.9);

3.9

hive> select abs(10.9);

10.9

 

 

---

---

 

二、字符串函数

 

 

2.1 字符串长度函数：length

语法:length(string A)

返回值:int

说明：返回字符串A的长度

举例：

hive> select length('abcedfg');

7

2.2 字符串反转函数：reverse

语法: reverse(string A)

返回值:string

说明：返回字符串A的反转结果

 

举例：

hive> select reverse(‘abcedfg’);

gfdecba

2.3 字符串连接函数：concat

语法:concat(string A, string B…)

返回值:string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

 

举例：

hive> select concat(‘abc’,'def’,'gh’);;

abcdefgh

 

2.4 字符串连接函数-带分隔符：concat_ws

语法:concat_ws(string SEP, string A, string B…)

返回值:string

说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

举例：

hive> select concat_ws(',','abc','def','gh');

abc,def,gh

 

 

2.5 字符串截取函数：substr,substring

语法:substr(string A, int start),substring(string A, int start)

返回值:string

说明：返回字符串A从start位置到结尾的字符串

 

举例：

hive> select substr('abcde',3);

cde

hive> select substring('abcde',3);

cde

hive>select substr('abcde',-1);

e

 

2.6  字符串截取函数：substr,substring

 

语法:substr(string A, int start, int len),substring(string A, intstart, int len)

返回值:string

说明：返回字符串A从start位置开始，长度为len的字符串

 

举例：

 

hive> select substr('abcde',3,2);

cd

hive> select substring('abcde',3,2);

cd

hive>select substring('abcde',-2,2);

de

 

2.7 字符串转大写函数：upper,ucase

 

语法: upper(string A) ucase(string A)

返回值:string

说明：返回字符串A的大写格式

 

举例：

hive> select upper('abSEd'); ABSED hive> select ucase('abSEd'); ABSED

 

 

2.8 字符串转小写函数：lower,lcase

语法: lower(string A) lcase(string A)

返回值:string

说明：返回字符串A的小写格式

 

举例：

 

hive> select lower('abSEd');

absed

hive> select lcase('abSEd');

absed

 

 

2.9 去空格函数：trim

 

语法:trim(string A)

返回值:string

说明:去除字符串两边的空格

 

举例：

hive> select trim(' abc ');

abc

 

2.10 左边去空格函数：ltrim

语法: ltrim(string A)

返回值:string

说明：去除字符串左边的空格

 

举例：

hive> select ltrim(' abc ');

abc

 

 

 

2.11 右边去空格函数：rtrim

 

语法:rtrim(string A)

返回值:string

说明：去除字符串右边的空格

 

举例：

hive> select rtrim(' abc '); abc

 

 

 

2.12 正则表达式替换函数：regexp_replace

语法:regexp_replace(string A, string B, string C)

 

返回值:string

说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。

 

举例：

hive> select regexp_replace('foobar', 'oo|ar', '');

fb

 

 

2.13  URL解析函数：parse_url

语法:parse_url(string urlString, string partToExtract [, stringkeyToExtract])

返回值: string

说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

 

举例：

 

hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');

facebook.com

 

hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'PATH');

/path1/p.php

 

hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY','k1');

v1

 

 

2.14 分割字符串函数: split

语法: split(string str, stringpat)

返回值: array

说明:按照pat字符串分割str，会返回分割后的字符串数组

 

举例：

 

hive> select split('abtcdtef','t'); ["ab","cd","ef"]

 

 

---

 

三、日期函数

 

3.1 获取当前UNIX时间戳函数:unix_timestamp

语法: unix_timestamp()

返回值:bigint

说明:获得当前时区的UNIX时间戳

 

举例：

hive> select unix_timestamp();

1323309615

3.2 UNIX时间戳转日期函数:from_unixtime

语法:from_unixtime(bigint unixtime[, string format])

返回值:string

说明:转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

 

举例：

hive> select from_unixtime(1323308943,'yyyyMMdd'); 20111208

 

3.3 日期转UNIX时间戳函数:unix_timestamp

 

语法:unix_timestamp(string date)

返回值: bigint

说明:转换格式为"yyyy-MM-ddHH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。

 

举例：

hive> select unix_timestamp('2011-12-07 13:01:03');

1323234063

3.4  指定格式日期转UNIX时间戳函数:unix_timestamp

语法: unix_timestamp(string date, string pattern)

返回值:bigint

说明:转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

 

举例：

hive> select unix_timestamp('20111207 13:01:03','yyyyMMddHH:mm:ss'); 1323234063

 

 

 

3.5 日期时间转日期函数:to_date

 

语法:to_date(string timestamp)

返回值:string

说明:返回日期时间字段中的日期部分。

 

举例：

hive> select to_date('2011-12-08 10:03:01'); 2011-12-08

 

3.6 日期转年函数: year

 

语法: year(string date)

返回值: int

说明:返回日期中的年。

 

举例：

hive> select year('2011-12-08 10:03:01'); 2011 hive> select year('2012-12-08'); 2012

 

 

3.7  日期转月函数: month

 

语法:month (string date)

返回值: int

 

说明:返回日期中的月份。

 

举例：

hive> select month('2011-12-08 10:03:01'); 12 hive> select month('2011-08-08'); 8

 

 

3.8  日期转天函数: day

 

语法: day (string date)

返回值: int

说明:返回日期中的天。

 

举例：

hive> select day('2011-12-08 10:03:01'); 8 hive> select day('2011-12-24'); 24

同样的，还有 hour,minute,second函数，分别是获取小时，分钟和秒，使用方式和以上类似，这里就不再讲述。

3.9 日期转周函数:weekofyear

语法: weekofyear (string date)

返回值:int

说明:返回日期在当前的周数。

 

举例：

hive> select weekofyear('2011-12-08 10:03:01');

49

3.10 日期比较函数: datediff

语法: datediff(string enddate, string startdate)

返回值: int

说明:返回结束日期减去开始日期的天数。

 

举例：

hive> select datediff('2012-12-08','2012-05-09'); 213

 

 

 

3.11 日期增加函数: date_add

 

语法: date_add(string startdate, int days)

返回值: string

说明:返回开始日期startdate增加days天后的日期。

 

举例：

hive> select date_add('2012-12-08',10); 2012-12-18

 

 

 

3.12 日期减少函数: date_sub

 

语法: date_sub (string startdate, int days)

返回值: string

说明:返回开始日期startdate减少days天后的日期。

 

举例：

hive> select date_sub('2012-12-08',10); 2012-11-28

 

 

---

 

 

四、条件函数

 

4.1 if函数: if

语法:if(boolean testCondition, T valueTrue, T valueFalseOrNull)

 

返回值: T

说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull

 

举例：

hive> select if(1=2,100,200) ; 200 hive> select if(1=1,100,200) ; 100

 

4.2 条件判断函数：CASE

语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

返回值: T

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

 

hive> select case 100 when50 then'tom' when100 then'mary' else 'tim' end;

mary

hive> select case 200 when50 then'tom' when100 then'mary' else 'tim' end;

tim

4.3 条件判断函数：CASE

语法:CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

返回值: T

说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e

 

举例：

hive> select case when1=2 then'tom' when2=2 then'mary' else 'tim' end; mary hive> select case when1=1 then'tom' when2=2 then'mary' else 'tim' end; tom

 

 

---

 

 

五、转换函数

 

cast()函数。

公式：

cast(表达式 as 数据类型)

 

  cast函数，可以将"20190607"这样类型的时间数据转化成int类型数据。

  cast("20190607" as int)

 

select cast('2017-06-12' as date) filed;

 

 

---

 

六、表生成函数（explode）

 

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

explode(ARRAY)  列表中的每个元素生成一行

explode(MAP)    map中每个key-value对，生成一行，key为一列，value为一列

 

案列:

数据：

 

10 CLARK|KING|MILLER 20 SMITH|JONES|SCOTT|ADAMS|FORD 30 ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

 

建表语句

 

create table emp3( deptno int, names array<string> ) row format delimited fields terminated by '\t' collection items terminated by '|';

 

插入 数据

 

load data local inpath "/export/server/hivedatas/emp3.txt" into table emp3;

 

查询数据

 

 

使用explode查询

 

如果查询的时候想带上deptno编号呢？

 

 

LATERAL VIEW

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

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

 

select deptno,name from emp3 lateral view explode(names) tmp1 as name;

 

这样理解：将侧视图理解为一张临时表，让这个表和 emp3 进行 join 操作，在 join过程中，让原来的字段数据和对应字段数据进行拼接在一起，形成最终的结果集。

 

 

• UDTF函数: 一进多出

• UDF函数：一进一出，大多数场景使用的UDF

• UDAF函数：多进一出，大部分的聚合函数都是UDAF

 

需求:查看每个员工对应的部门号 0: jdbc:hive2://node3:10000> select deptno, explode(names) from emp3; Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

 

UDTF 函数使用注意事项：

1. 查询字段中，如果使用UDTF函数，不允许其他字段

2. 查询字段中，如果是UDTF函数，不允许嵌套到其他函数中

如果现在需要加上其他的字段，需要使用 侧视图（LATERAL VIEW ）

语法格式：

LATERAL VIEW udtf(expression) tableAlias AS columnAlias

 

 

七、Reflect函数

 

reflect函数可以支持在sql中调用java中的自带函数

 

# 获取比当前值小的最大整数

select reflect('java.lang.Math','floor',-3.1214926); #-4.0

# 求绝对值

select reflect('java.lang.Math','abs',-3.1214926); #3.1214926