hive部分常用函数

一、时间函数

# 按照指定时间格式获取当前时间
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') ;

# 时间戳转日期
select from_unixtime(1505456567); 
select from_unixtime(1505456567,'yyyyMMdd'); 
select from_unixtime(1505456567,'yyyy-MM-dd HH:mm:ss'); 

# 获取当前时间戳
select unix_timestamp();

# 日期转时间戳
select unix_timestamp('2017-09-15 14:23:00'); 


# 计算时间差
select datediff('2018-06-18','2018-11-21');


# 查询当月第几天 
select dayofmonth(current_date);

#月末:
select  last_day(current_date)

#当月第1天: 
select date_sub(current_date,dayofmonth(current_date)-1)

#下个月第1天:
select  add_months(date_sub(current_date,dayofmonth(current_date)-1),1)

# 当前日期
select current_date

 

二、数学函数

round 四舍五入((42.3 =>42))

ceil 向上取整(42.3 =>43)

floor 向下取整(42.3 =>42)

 

三、字符串函数

lower（转小写）
upper（转大写）
length（字符串长度，字符数）
trim（去前后空格）
lpad（左填充）
rpad（右填充）
reverse （字符串反转函数）

#concat（字符串拼接）
hive> select concat(2019,12);
201912

#concat_ws （指定分隔符）
hive> select concat_ws('|', 'Apache', 'Hive');
Apache|Hive

#substr（求子串）
hive> select substr('ApacheHive',7);
Hive
hive> select substr('ApacheHive',-7);
cheHive

#split (字符串分割函数)
hive> select split('a#b#c', '#');
["a","b","c"]
hive> select split('a#b#c', '#')[0];
a

 

四、类型转换函数

#cast(value as type)
hive> select ip,CAST(port as string) as port from instances；
hive> select cast(123 as string);

 

五、正则函数

#regexp_extract
regexp_extract(str, regexp[, idx]) 

参数解释：
str 是被解析的字符串或字段名
regexp 是正则表达式
idx是返回结果 取表达式的哪一部分  默认值为1；
    0 表示把整个正则表达式对应的结果全部返回；
    1 表示返回正则表达式中第一个() 对应的结果， 以此类推 ；
idx的数字不能大于表达式中()的个数；

hive> select regexp_extract('ApacheHive', '(H)(.*?)(e)',0);
Hive

#REGEXP_REPLACE
语法: regexp_replace(string A, string B, string C)

操作类型: strings

返回值: string

说明: 将字符串A中的符合java正则表达式B的部分替换为C。

 

六、列转行

--------列转行--------
collect_list 将列转换成array
collect_set 将列转换成array并去重

hive> create table stu (id int, course string) 
    > partitioned by (day string)
    > row format delimited fields terminated by ',';

[root@node1 tmp]# cat student.txt 
1,语文
2,数学
3,英语
1,英语
1,语文
2,语文

hive> load data local inpath '/tmp/student.txt' into table stu partition (day='20191220');

hive> select * from stu;
1    语文    20191220
2    数学    20191220
3    英语    20191220
1    英语    20191220
1    语文    20191220
2    语文    20191220

hive> select id, collect_list(course) from stu group by id;
1    ["语文","英语","语文"]     #学了两次语文
2    ["数学","语文"]
3    ["英语"]

hive> select id, collect_set(course) from stu group by id;
1    ["语文","英语"]    #去重了
2    ["数学","语文"]
3    ["英语"]

 

七、row_number() OVER

row_number() OVER (PARTITION BY COL1 ORDERBY COL2)表示根据COL1分组，在分组内部根据COL2排序，

而此函数计算的值就表示每组内部排序后的顺序编号（该编号在组内是连续并且唯一的)。

 

八、COALESCE函数

COALESCE是一个函数， (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。

如果所有的表达式都是空值，最终将返回一个空值。

 

九、lateral view

lateral view 函数用于将数据一行转多列，一般与explode、split、collect_set函数一起使用；