一、时间函数
# 按照指定时间格式获取当前时间 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函数一起使用；