Hive常用函数使用

Hive常用函数使用

本篇主要介绍一些经常会用到的hive函数，‘—’后序也会持续添加更新
msck repair table table_name 更新hive表的元数据

## 常用函数
date_format()
date_add()
date_sub()
last_day()
next_day()
months_between()
month()
day()
year()
round() ## 四舍五入，求近似值
----------------------------------
get_json_object()
select get_json_object('[{"name":"大浪","age":"35"},{"name":"西门庆","age":"45"}]','$[0].name');
大浪    
----------------------------------
select 
get_json_object('{"user":{"name":"大浪","age":"35"}}','$.user.name') as name ,
get_json_object('{"user":{"name":"大浪","age":"35"}}','$.user.age') as age
;
name    age
大浪    35
--------------------------------
sum(if(,,))
case when then else end 
nvl()
lag()  over()
lead() over()
explode()  ## select  from table_name lateral view explode(col) table_alias as clomuns...
cast( as timestamp [as decimal(10,2)])  格式转换
substring('',0,3)  ## 切分，从0开始，切分3个字符
split(,)[0]  
concat()
concat_ws()
collect_set()
collect_list()
array_contains(split(column_name,'|'),'xxx') 查看字符串中是否包含xxx
rlike '.*(a|b).*'

## 常用窗口函数
cluster by  ## 只能默认正序，而且必须保证distribute by 与sort by 的字段相同
distribute by c1 sort by c2  
sum()|max()|lag()|lead()  over(partition by c1 order by c2 rows between n|unbounded [preceeding|following]  and current_row )

## 插入宽表语句
with tmp1 as(),
tmp2 as (),
tmp3 as ()...
insert into|overwrite table table_name 
select 
from (
select c1 ... from tmp1 
    union all
select c1 ... from tmp2 
    union all
select c1 ... from tmp3     
) t  
group by c1
## 必须保证3个表选出的字段别名相同 组成一张临时表

explain extended select * from table_name ## 查看执行计划
show create table table_name ## 查看建表语句
desc table_name
show functions
desc function extended fun_name ## 查看函数基本使用