Apache Hive常用函数举例

一、字符函数

select concat('abc','cd','fff')				连接字符串
select instr('abcdef','bc')					返回字符串子字符串的位置  2
select length('adfs')						返回字符串长度
select lower('ABCDEF')						字符串改为小写
select upper('abcdef')						字符串改为大写
字符串正则替换
select regexp_replace('1999/5.6','[/|\\.]','-')			   	 		1999-5-6
select regexp_replace('1999-5-6 13:24:56.234','\\.[0-9]+','') 		1999-5-6 13:24:56		
select split('1999-5-6 13:24:56.234','\\.')[0]						1999-5-6 13:24:56	字符串分割
select substring('1999-5-6 13:24:56.234',1,8)						1999-5-6			固定字符串截取
select trim('1999-5-6 13:24:56.234  ')		去空
select str_to_map('a:b,c:d,e:{f:d}')     	按指定方式将字符串转换为map
select encode('你好','utf8')					按特定方式进行编码
select reverse('你好')						字符串反转
select ltrim(' abc')						左去空
select rtrim('abc  ')						右去空
字符串精确匹配截取
select regexp_extract('1999-5-6 13:24:56.001','([0-9]{4}-[0-9]{1,2}-[0-9]{1,2}) ([0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2})(\\.[0-9]{2,3})',2)
											分组截取				13:24:56
select repeat('abc',3)         abcabcabc	字符串重复
select format_number(3.1415926,3)     3.142	保留小数后几位，四舍五入  / 数字格式化输出
select get_json_object('{"name":"zs","age":"40"}','$.name')		获取json字符串对象
select lpad('abcd',10,'0')		000000abcd						左填充到10个字符，不够截取左边几位
select parse_url('http://www.taobao.com','HOST')www.taobao.com	拿网址
select parse_url('http://www.taobao.com?userid=1&v=2','QUERY')  userid=1&v=2
select parse_url('http://www.taobao.com?userid=1&v=2','PROTOCOL')   http	拿协议
select sentences('hello world!how do you do')		[["hello","world"],["how","do","you","do"]]
select initcap('hello world, how do you do')  	以空格作分段，首字母大写	

二、数学函数

select round(2.14158,3)			2.142			保留3位小数，四舍五入
select floor(2.4158)   2						向下取整
select ceil(2.656)    3							向上取整
select rand(10)									种子不变生成的序列永远不变			
select pow(10,2)       100
select sqrt(4)      2						
select pmod(10,3)     1							求模，求余

三、类型转换函数

select cast("123" as string)		转换为string型

四、日期函数

select from_unixtime(1250111000,'yyyy-MM-dd')   				2009-08-13		
select from_unixtime(1250111000,'yyyy-MM-dd'),unix_timestamp()  1592192787
								获取时间戳，毫秒数
select from_unixtime(1250111000,'yyyy-MM-dd'),unix_timestamp('1970-1-1 8:00:00')   0
								时间戳从1970-1-1 8:00:00开始
								时间只能到秒，有毫秒直接舍掉
select to_date(from_unixtime(unix_timestamp()))    				当前时间戳到日期
select weekofyear(from_unixtime(unix_timestamp()))				今天是今年的多少周
select datediff(current_date(),'2020-5-20')     				当前日期，算出两者日期差
select date_add(current_date(),2)		加两天
select date_sub(current_date(),2)		减两天
select current_timestamp()				当前时间戳
select add_months(current_date(),7)					往后加7个月
select last_day(current_date()) 2020-6-30			本月最后一天
select next_day(current_date(),'SA')				下一个周几
select trunc(current_date(),'YY')  2020-01-01		截取年和月，保持日期格式
select months_between(current_date(),'2020-12-31')    -6.51612903
									前面减后面月份差
select date_format(current_date(),'M')            6				
select size(split('hello world how do you do',' '))  6  大小

四、条件函数

select if(1>2,"hello","world")    返回world
select nvl(null,10)，select nvl(1,10)	如果为null返回10，为1返回1
select coalesce(null,null,null,21,null,null,10)			返回第一个不为空的值
select case  when age < 25 then 0 else 1 end from userinfos
select case  when age < 25 then 0 when age > 28 then 1 else 2 end from userinfos
两种case语句写法
select isnull(null)      	true
select isnotnull(null)		false

四、表生成函数

select explode(split('hello,world',','))		生成表
hello
world

补充：

今天用zeppelin时遇到一点问题，

select count(*) from userinfos

问题描述

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

解决方案

关掉所有，在yarn-site.xml中加入
<property>
   <name>yarn.scheduler.minimum-allocation-mb</name>
   <value>2048<alue>
   <description>default value is 1024</description>
</property>

继续报错

报 SemanticException [Error 10001]: Line 1:21 Table not found 'userinfos'错误
org.apache.hadoop.security.AccessControlException: Permission denied: user=hive, access=EXECUTE, inode="/tmp/hadoop-yarn":root:supergroup:drwx------

解决方案

hdfs dfs -chmod -R 777 /tmp/hadoop-yarn