hive sql函数集

类型转换函数

1.cast()函数

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

2.convert()函数

公式：convert(数据类型,表达式)

数值函数

1.指定精度取整函数 : round

语法: round(double a, int d)

返回值: DOUBLE

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

举例：

hive> select round(3.1415926,4); 3.1416

2.向下取整函数 : floor

语法: floor(double a)

返回值: BIGINT

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

举例：

hive> select floor(3.1415926); 3

hive> select floor(25); 25

3.向上取整函数 : ceil

语法: ceil(double a)

返回值: BIGINT

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

举例：

hive> select ceil(3.1415926); 4

hive> select ceil(46); 46

4.取随机数函数 : rand

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

返回值: double

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

举例：

hive> select rand(); 0.5577432776034763

5.绝对值函数 : abs

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

返回值: double int

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

举例：

hive> select abs(‐3.9) from dual; 3.9

hive> select abs(10.9); 10.9

日期函数

1.to_date(string timestamp):返回时间字符串中的日期部分

如to_date('1970-01-01 00:00:00')='1970-01-01'

2.current_date:返回当前日期

3.current_timestamp:返回当前日期和时间

4.year(date)：返回日期date的年,类型为int

如year('2019-01-01')=2019

5.month(date)：返回日期date的月,类型为int

如month('2019-01-01')=1

6.day(date): 返回日期date的天,类型为int

如day('2019-01-01')=1

7.hour(date):返回日期date的时，类型为int

8.weekofyear(date1)：返回日期date1位于该年第几周

如weekofyear('2019-03-06')=10

9.datediff(date1,date2):返回日期date1与date2相差的天数

如datediff('2019-03-06','2019-03-05')=1

10.date_add(date1,int1):返回日期date1加上int1的日期

如date_add('2019-03-06',1)='2019-03-07'

11.date_sub(date1,int1):返回日期date1减去int1的日期

如date_sub('2019-03-06',1)='2019-03-05'

12.months_between(date1,date2):返回date1与date2相差月份

如months_between('2019-03-06','2019-01-01')=2

13.add_months(date1,int1):返回date1加上int1个月的日期，int1可为负数

如add_months('2019-02-11',-1)='2019-01-11'

14.last_day(date1):返回date1所在月份最后一天

如last_day('2019-02-01')='2019-02-28'

15.trunc(date1,string1):返回日期最开始年份或月份。string1可为年(YYYY/YY/YEAR)或月 (MONTH/MON/MM)

如trunc('2019-03-06','MM')='2019-03-01'，trunc('2019-03-06','YYYY')='2019-01-01'

16.unix_timestamp():返回当前时间的unix时间戳，可指定日期格式

如unix_timestamp('2019-03-06','yyyy-mm-dd')=1546704180

17.from_unixtime():返回unix时间戳的日期，可指定格式

如select from_unixtime(unix_timestamp('2019-03-06','yyyy-MM-dd'),'yyyyMMdd')='20190306'

条件函数

1.if(boolean,t1,t2):若布尔值成立，则返回t1,反正返回t2

如if(1>2,100,200)返回200

2.case when boolean then t1 else t2 end:若布尔值成立，则t1,否则t2,可加多重判断

3.coalesce(v0,v1,v2):返回参数中的第一个非空值,若所有值均为null,则返回null

如 coalesce(null,1,2)返回1

4.isnull(a):若a为null则返回true，否则返回false

字符串函数

1.length(string1):返回字符串长度

2.concat(string1,string2):返回拼接string1及string2后的字符串

3.concat_ws(sep,string1,string2):返回按指定分隔符拼接的字符串

4.lower(string1):返回小写字符串

5.lcase(string1)。upper()/ucase()：返回大写字符串

6.trim(string1):去字符串左右空格

7.ltrim(string1):去字符串左空格

8.rtrim(string1):去字符串右空

9.repeat(string1，int1)：返回重复string1字符串int1次后的字符串

10.reverse(string1):返回string1反转后的字符串

如reverse('abc')返回'cba'

11.rpad(string1,len1,pad1):以pad1字符右填充string1字符串，至len1长度

如rpad('abc',5,'1')返回'abc11'。lpad()：左填充

12.split(string1,pat1):以pat1正则分隔字符串string1,返回数组

如split('a,b,c',',')返回["a","b","c"]

13.substr(string1,index1,int1):以index位置起截取int1个字符

如substr('abcde',1,2)返回'ab'

爆炸函数

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

LATERAL VIEW

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

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行

如：·原数据

《疑犯追踪》 悬疑，动作，科幻，剧情

《lie to me》 悬疑，警匪，动作，心理，剧情

《战狼2》 战争，动作，灾难

·需求：将电影分类中的数据展开，结果如下：

《疑犯追踪》 悬疑

《疑犯追踪》 动作

《疑犯追踪》 科幻

《疑犯追踪》 剧情

《lie to me》 悬疑

《lie to me》 警匪

《lie to me》 动作

《lie to me》 心理

《lie to me》 剧情

《战狼2》 战争

《战狼2》 动作

《战狼2》 灾难

自定义函数

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

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

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

3.1.UDF（User-Defined-Function）

一进一出

3.2.UDAF（User-Defined Aggregation Function）

聚集函数，多进一出

类似于：count/max/min

3.3.UDTF（User-Defined Table-Generating Functions）

一进多出

如lateral view explore()

3.4.官方文档地址

HivePlugins - Apache Hive - Apache Software Foundation

5.编程步骤:

5.1.继承org.apache.hadoop.hive.ql.exec.UDF

5.2.需要实现evaluate函数；evaluate函数支持重载；

5.3.在hive的命令行窗口创建函数

添加jar

add jar linux_jar_path

创建function

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

5.4.在hive的命令行窗口删除函数

Drop [temporary] function [if exists] [dbname.]function_name;

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

6.自定义UDF函数例子

6.1.创建一个Maven工程Hive

6.2.导入依赖

org.apache.hive

hive-exec

3.1.2

6.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(); } }

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

6.5.将jar包添加到hive的classpath

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

6.6.创建临时函数与开发好的java class关联

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

6.7.即可在hql中使用自定义的函数strip

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