大数据开发之 Hive SQL 内置函数

作者：飞不起来的童年

2021 年 12 月 27 日
本文字数：4441 字
阅读完需：约 15 分钟

前言

Hive 内部提供了很多函数给开发者使用，包括数学函数，集合函数，大数据培训类型转换函数，日期函数，条件函数，字符函数，聚合函数，表生成函数等等，这些函数都统称为内置函数。

Hive 的内置函数

一、数学函数

1、取整函数: round

语法: round(double a)

返回值: BIGINT

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

举例：

hive> select round(3.1415926);3

2、指定精度取整函数: round

语法: round(double a, int d)

返回值: DOUBLE

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

举例：

hive> select round(3.1415926,4);3.1416

3、向下取整函数: floor

语法: floor(double a)

返回值: BIGINT

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

举例：

hive> select floor(3.1415926);3

4、向上取整函数: ceil

语法: ceil(double a)

返回值: BIGINT

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

举例：

hive> select ceil(3.1415926) ;4

5、取随机数函数: rand

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

返回值: double

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

举例：

hive> select rand();0.5577432776034763hive> select rand();0.6638336467363424hive> select rand(100);0.7220096548596434hive> select rand(100);0.7220096548596434

6、幂运算函数: pow

语法: pow(double a, double p)

返回值: double

说明:返回 a 的 p 次幂

举例：

hive> select pow(2,4) ;16.0

7、绝对值函数: abs

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

返回值: double int

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

举例：

hive> select abs(-3.9);3.9hive> select abs(10.9);10.9

二、字符串函数

1、字符串长度函数：length

语法: length(string A)

返回值: int

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

举例：

hive> select length('abcedfg'); 7

2、字符串反转函数：reverse

语法: reverse(string A)

返回值: string

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

举例：

hive> select reverse("abcedfg");gfdecba

3、字符串连接函数：concat

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

返回值: string

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

举例：

hive> select concat('hello','world');helloworld

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

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

返回值: string

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

举例：

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

5、字符串截取函数：substr,substring

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

返回值: string

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

举例：

hive> select substr('abcde',3); cdehive> select substring('abcde',3); cdehive>select substr('abcde',-1); e

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); cdhive> select substring('abcde',3,2); cdhive>select substring('abcde',-2,2); de

7、字符串转大写函数：lower,ucase

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

返回值: string

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

举例：

hive> select lower('abSEd'); ABSEDhive> select ucase('abSEd'); ABSED

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

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

返回值: string

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

举例：

hive> select lower('abSEd'); absedhive> select lcase('abSEd'); absed

9、去空格函数：trim

语法: trim(string A)

返回值: string

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

举例：

hive> select trim(' abc ');abc

10、左边去空格函数：ltrim

语法: ltrim(string A)

返回值: string

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

举例：

hive> select ltrim(' abc ');abc

11、右边去空格函数：rtrim

语法: rtrim(string A)

返回值: string

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

举例：

hive> select rtrim(' abc ');abc

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

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.comhive> select parse_url("http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1", "PATH"); /path1/p.phphive> select parse_url("http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1", "QUERY","k1"); v1

14、分割字符串函数: split

语法: split(string str, stringpat)

返回值: array

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

举例：

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

三、日期函数

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

语法: unix_timestamp()

返回值: bigint

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

举例：

hive> select unix_timestamp();1323309615

2、UNIX 时间戳转日期函数:from_unixtime

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

返回值: string

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

举例：

hive>select from_unixtime(1598079966,'yyyy-MM-dd HH:mm:ss');2020-08-22 15:06:06

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

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

5、日期时间转日期函数:to_date

语法: to_date(string timestamp)

返回值: string

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

举例：

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

6、日期转年函数: year

语法: year(string date)

返回值: int

说明:返回日期中的年。

举例：

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

7、日期转月函数: month

语法: month (string date)

返回值: int

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

举例：

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

8、日期转天函数: day

语法: day (string date)

返回值: int

说明:返回日期中的天。

举例：

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

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

9、日期转周函数:weekofyear

语法: weekofyear (string date)

返回值: int

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

举例：

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

10、日期比较函数: datediff

语法: datediff(string enddate, string startdate)

返回值: int

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

举例：

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

11、日期增加函数: date_add

语法: date_add(string startdate, int days)

返回值: string

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

举例：

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

12、日期减少函数: date_sub

语法: date_sub (string startdate, int days)

返回值: string

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

举例：

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

13、date_format 函数

语法: date_format(string startdate, string format)

返回值: string

说明:对日期进行格式化

举例：

hive> select date_format('2019-1-1', 'yyyy-MM-dd');2019-01-01

四、条件函数

1、if 函数: if

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

返回值: T

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

举例：

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

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 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; maryhive> select case 200 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; tim

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 when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ; maryhive> select case when 1=1 then 'tom' when 2=2 then 'mary' else'tim' end ; tomselect sid ,case when sscore>=60 then '及格' when sscore<60 then '不及格' else '其他' end from score

五、转换函数

类似于 java 中的强转转换函数

公式：cast(表达式 as 数据类型)

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

select cast(12.35 as int);select cast('20190607' as int)select cast('2020-12-05' as date);

六、Hive 的行转列

1、介绍

1）行转列是指多行数据转换为一个列的字段。

2）Hive 行转列用到的函数：

concat(str1,str2,...) --字段或字符串拼接

concat_ws(sep, str1,str2) --以分隔符拼接每个字符串

collect_set(col) --将某字段的值进行去重汇总，产生 array 类型字段

2、测试数据

字段: deptno ename

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

复制代码

3、操作步骤

1）建表

create table emp( deptno int, ename string ) row format delimited fields terminated by '\t';

复制代码

2）插入数据

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

复制代码

3）转换

select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;

复制代码

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

4）结果查看

转自大数据老哥

发布于: 1 小时前

飞不起来的童年

关注

关注尚硅谷，轻松学IT 2021.11.23 加入

还未添加个人简介

发布

暂无评论

创作场景

大数据开发之 Hive SQL 内置函数

Hive 的内置函数

一、数学函数

1、 取整函数: round

2、指定精度取整函数: round

3、向下取整函数: floor

4、向上取整函数: ceil

5、取随机数函数: rand

6、幂运算函数: pow

7、绝对值函数: abs

二、字符串函数

1、字符串长度函数：length

2、字符串反转函数：reverse

3、字符串连接函数：concat

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

5、字符串截取函数：substr,substring

6、字符串截取函数：substr,substring

7、 字符串转大写函数：lower,ucase

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

9、去空格函数：trim

10、 左边去空格函数：ltrim

11、右边去空格函数：rtrim

12、正则表达式替换函数：regexp_replace

13、URL 解析函数：parse_url

14、分割字符串函数: split

三、日期函数

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

2、UNIX 时间戳转日期函数:from_unixtime

3、日期转 UNIX 时间戳函数:unix_timestamp

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

5、日期时间转日期函数:to_date

6、日期转年函数: year

7、日期转月函数: month

8、日期转天函数: day

9、日期转周函数:weekofyear

10、日期比较函数: datediff

11、日期增加函数: date_add

12、日期减少函数: date_sub

13、date_format 函数

四、条件函数

1、if 函数: if

2、条件判断函数：CASE

3、条件判断函数：CASE

五、转换函数

六、Hive 的行转列

1、介绍

2、测试数据

3、操作步骤

飞不起来的童年

评论

1、取整函数: round

7、字符串转大写函数：lower,ucase

10、左边去空格函数：ltrim