Apache Hive 常用内置函数及其使用

1.内置函数：（270+）

show functions; -- 显示hive内置函数列表

2.查看hive函数的用法(描述信息)

desc function 函数名;
desc function sum;

desc function extended 函数名;
desc function extended sum; -- 可以查看使用案例

3.常用内置函数

1）字符串操作函数

split(str, regex) -- 字符串切分，返回array<string>

instr(str,substr)   -- 返回子字符串在原始字符串str中第一个字符的索引位置，索引从1开始（没有找到会返回0）

substr(str,pos[,len]) -- 字符串串截取，pos为起始位置，len是截取长度，不加len表示截取到末尾  （和substring完全一样），反向截取的话，使用负索引从末尾开始往前截取

upper(str) -- 字符串所有字符转大写(同ucase)
lower(str) -- 字符串所有字符转小写(同lcase)

concat(str1,str2,str3...) -- 字符串拼接，没有分隔符
contat_ws(separator,[string,array(string)]+) -- 拼接字符串时指定分隔符

length(str|binary) -- 取字符串长度

coalesce(a1,a2,a3,...)  -- 返回多个值中第一个不为null的值

trim(str)  -- 去前后空格
ltrim(str) -- 去前面空格
rtrim(str) -- 去后面空格

2）条件判断

① case when

-- 第一种 (只能做等值判断，相当于java中的switch)
case 需要判断的字段
	when 值1  then  返回值1
	when 值2  then  返回值2
	else 其他情况的返回值
end	

-- 第二种 (类似java中if elseif else)
case when 条件1 then 返回值1
	 when 条件2 then 返回值2
	 when 条件3 then 返回值3
	 else 返回值4
end

② if(相当于java中的三元表达式)

if(expr1,expr2,expr3) -- 参数1为表达式，参数2即表达式为true的返回值，参数三即表达式为false的返回值

③ nvl null值判断

nvl(value,deafault_value) --在表中找第一个参数的值，如果为null，则返回nvl，否则返回参数2

3) 数值操作

ceil(x) -- 向上取整
floor(x) -- 向下取整
round(x,[d])  -- 四舍五入,参数1位原始数值，参数二为保留位数，不传则取整
rand([seed])  -- 求随机数,不传参表示返回0~1之间的随机数，一般使用不传参的形式
max,min,avg,count,sum等同MySQL等数据库

4）日期操作

① 获取当前系统时间|时间戳

current_date() 返回当前系统日期
current_timestamp() 返回当前系统时间戳

② 时间戳转日期

-- 参数1位时间戳，参数2为日期格式,例如 yyyy-MM-dd HH:mm:ss,时间戳类型：bigint 
from_unixtime(unix_time,format) 

③ 日期转时间戳

unix_timestamp(date[,pattern]) -- 参数1：日期，参数2：参数1的日期表达式
select unix_timestamp("1990/03/20","yyyy-MM-dd")

④ 日期提取

year(date|string) -- 日期字符串（日期使用-分割）或日期
weekofyear() -- 第几周
dayofmonth() -- 一个月第几天
dayofweek()  -- 一个周第几天
month,day,hour,minute,second,

5）类型转换

cast(原始数据 as 需要转换的类型) 

6) 集合操作函数

array(n0,n2,n3....) -- 是数组构建函数,可以传递多个参数，然后转换为数组类型(传递可变参数)
array_containrs(array,需要找的元素)  -- 判断数组中是否包含元素，包含则返回true,否则返回值

map（） -- 构建映射，参数必须是偶数个，默认:key  偶数
map_keys(mp) -- 返回当前的key的
map_values(mp) -- 返回当前的value的

7) 收集函数（本质上是一个聚合函数）

 -- 将多条数据的某一个字段，进行收集到一个集合中，手机为set集合的，去重的，返回的是一个数组
collect_set(需要收集的字段)
-- 将多条数据的某一个字段，进行收集到一个集合中，手机为list集合的，不去重的，返回的是一个数组 
collect_list(需要收集的字段) 

8）表函数（explode）

explode(array|map)  -- 把数组的元素分成多行,参数只能是数组或map
select explode(word_add) from test_array;

单独explode一个map或者array是可以的，但是添加查询字段，如id就会报错。

例如：

select id,explode(word_add) from test_array;

解决方案：(使用侧视图先存储explode结果)

select id,work_address from test_array lateral view explode(work_add) as work_address;

select id,pf.moviename,pf.moviepf from test_map lateral view explode(piaofang) pf as moviename,moviepf;