首页 > 大数据 > Hadoop > 大数据开发之Hive SQL内置函数
前言
Hive内部提供了很多函数给开发者使用,包括数学函数,集合函数, 大数据培训类型转换函数,日期函数,条件函数,字符函数,聚合函数,表生成函数等等,这些函数都统称为内置函数。
语法: round(double a)
返回值: BIGINT
说明:返回double类型的整数值部分(遵循四舍五入)
举例:
hive> select round(3.1415926);
3
语法: round(double a, int d)
返回值: DOUBLE
说明:返回指定精度d的double类型
举例:
hive> select round(3.1415926,4);
3.1416
语法: floor(double a)
返回值: BIGINT
说明:返回等于或者小于该double变量的最大的整数【关注尚硅谷,轻松学IT】
举例:
hive> select floor(3.1415926);
3
语法: ceil(double a)
返回值: BIGINT
说明:返回等于或者大于该double变量的最小的整数
举例:
hive> select ceil(3.1415926) ;
4
语法: rand(),rand(int seed)
返回值: double
说明:返回一个0到1范围内的随机数。如果指定种子seed,则会返回固定的随机数
举例:
hive> select rand();0.5577432776034763
hive> select rand();0.6638336467363424
hive> select rand(100);0.7220096548596434
hive> select rand(100);0.7220096548596434
语法: pow(double a, double p)
返回值: double
说明:返回a的p次幂
举例:
hive> select pow(2,4) ;
16.0
语法: abs(double a) abs(int a)
返回值: double int
说明:返回数值a的绝对值
举例:
hive> select abs(-3.9);3.9
hive> select abs(10.9);10.9
语法: length(string A)
返回值: int
说明:返回字符串A的长度
举例:
hive> select length('abcedfg'); 7
语法: reverse(string A)
返回值: string
说明:返回字符串A的反转结果
举例:
hive> select reverse("abcedfg");
gfdecba
语法: concat(string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,支持任意个输入字符串
举例:
hive> select concat('hello','world');
helloworld
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符
举例:
hive> select concat_ws(',','abc','def','gh');
abc,def,gh
语法: 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
语法: 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
语法: lower(string A) ucase(string A)
返回值: string
说明:返回字符串A的大写格式
举例:
hive> select lower('abSEd'); ABSED
hive> select ucase('abSEd'); ABSED
语法: lower(string A) lcase(string A)
返回值: string
说明:返回字符串A的小写格式
举例:
hive> select lower('abSEd'); absed
hive> select lcase('abSEd'); absed
语法: trim(string A)
返回值: string
说明:去除字符串两边的空格
举例:
hive> select trim(' abc ');
abc
语法: ltrim(string A)
返回值: string
说明:去除字符串左边的空格
举例:
hive> select ltrim(' abc ');
abc
语法: rtrim(string A)
返回值: string
说明:去除字符串右边的空格
举例:
hive> select rtrim(' abc ');
abc
语法: regexp_replace(string A, string B, string C)
返回值: string
说明:将字符串A中的符合java正则表达式B的部分替换为C。注意,在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
举例:
hive> select regexp_replace('foobar', 'oo|ar', '');
fb
语法: 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
语法: split(string str, stringpat)
返回值: array
说明:按照pat字符串分割str,会返回分割后的字符串数组
举例:
hive> select split('abtcdtef','t');
["ab","cd","ef"]
语法: unix_timestamp()
返回值: bigint
说明:获得当前时区的UNIX时间戳
举例:
hive> select unix_timestamp();
1323309615
语法: 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
语法: unix_timestamp(string date)
返回值: bigint
说明:转换格式为"yyyy-MM-ddHH:mm:ss"的日期到UNIX时间戳。如果转化失败,则返回0。
举例:
hive> select unix_timestamp('2011-12-07 13:01:03');
1323234063
语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明:转换pattern格式的日期到UNIX时间戳。如果转化失败,则返回0。
举例:
hive> select unix_timestamp('20111207 13:01:03','yyyyMMddHH:mm:ss');
1323234063
语法: to_date(string timestamp)
返回值: string
说明:返回日期时间字段中的日期部分。
举例:
hive> select to_date('2011-12-08 10:03:01');
2011-12-08
语法: year(string date)
返回值: int
说明:返回日期中的年。
举例:
hive> select year('2011-12-08 10:03:01'); 2011
hive> select year('2012-12-08'); 2012
语法: month (string date)
返回值: int
说明:返回日期中的月份。
举例:
hive> select month('2011-12-08 10:03:01'); 12
hive> select month('2011-08-08'); 8
语法: day (string date)
返回值: int
说明:返回日期中的天。
举例:
hive> select day('2011-12-08 10:03:01'); 8
hive> select day('2011-12-24'); 24
同样的,还有 hour,minute,second函数,分别是获取小时,分钟和秒,使用方式和以上类似,这里就不再讲述。
语法: weekofyear (string date)
返回值: int
说明:返回日期在当前的周数。
举例:
hive> select weekofyear('2011-12-08 10:03:01');
49
语法: datediff(string enddate, string startdate)
返回值: int
说明:返回结束日期减去开始日期的天数。
举例:
hive> select datediff('2012-12-08','2012-05-09');
213
语法: date_add(string startdate, int days)
返回值: string
说明:返回开始日期startdate增加days天后的日期。
举例:
hive> select date_add('2012-12-08',10);
2012-12-18
语法: date_sub (string startdate, int days)
返回值: string
说明:返回开始日期startdate减少days天后的日期。
举例:
hive> select date_sub('2012-12-08',10);
2012-11-28
语法: date_format(string startdate, string format)
返回值: string
说明:对日期进行格式化
举例:
hive> select date_format('2019-1-1', 'yyyy-MM-dd');
2019-01-01
语法: 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
语法: 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 ; mary
hive> select case 200 when 50 then 'tom' when 100 then 'mary'else 'tim' end ; tim
语法: 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 ; mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else'tim' end ; tom
select 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);
1)行转列是指多行数据转换为一个列的字段。
2)Hive行转列用到的函数:
concat(str1,str2,...) --字段或字符串拼接
concat_ws(sep, str1,str2) --以分隔符拼接每个字符串
collect_set(col) --将某字段的值进行去重汇总,产生array类型字段
字段: deptno ename
1)建表
2)插入数据
3)转换
行转列,COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
4)结果查看
转自大数据老哥
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/70010293/viewspace-2849489/ ,如需转载,请注明出处,否则将追究法律责任。