hive-常用操作及函数

select '我们' from file_cto_user_info limit 1
select 1 from file_cto_user_info where 'football' like 'foot____' limit 1
--注意：否定比较时候用NOT A LIKE B
select 1 from file_cto_user_info where NOT 'football' like 'fff%' limit 1
 
--注意：判断一个字符串是否全为数字：
select 1 from file_cto_user_info where '123456' rlike '^\\d+$' limit 1
select 1 from file_cto_user_info where 'footbar' REGEXP '^f.*r$' limit 1
 
 --注意：hive中最高精度的数据类型是double,只精确到小数点后16位，在做除法运算的时候要特别注意
select ceil(28.0/6.999999999999999999999) from file_cto_user_info limit 1

--注意：精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度
select round(8.4 % 4 , 2) from file_cto_user_info limit 1

--逻辑非操作: NOT
select 1 from file_cto_user_info where not 1=2 limit 1

--向下取整函数: floor
select floor(3.1415926) from file_cto_user_info limit 1
--向上取整函数: ceil
select ceil(3.1415926) from file_cto_user_info limit 1
select ceiling(3.1415926) from file_cto_user_info limit 1

--随机数
select rand(100) from file_cto_user_info limit 1

--开平方
select sqrt(16) from file_cto_user_info limit 1

--二进制函数: bin
select bin(7) from file_cto_user_info limit 1

--十六进制函数: hex
 select hex('abc') from file_cto_user_info limit 1

--反转十六进制函数: unhex
select unhex(616263) from file_cto_user_info limit 1

--进制转换函数: conv
--说明: 将数值num从from_base进制转化到to_base进制
select conv(8,10,2) from file_cto_user_info limit 1
select conv(111,2,10) from file_cto_user_info limit 1
select conv(9,10,5) from file_cto_user_info limit 1

--绝对值函数: abs
select abs(-3.9) from file_cto_user_info limit 1

--正取余函数: pmod
select pmod(-9,4) from file_cto_user_info limit 1

--相反数
select negative(8) from file_cto_user_info limit 1

--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1458814863,'yyyy-MM-dd hh:mm:ss') from file_cto_user_info limit 1
select from_unixtime(1458814863,'yyyy-MM-dd HH:mm:ss') from file_cto_user_info limit 1
select from_unixtime(1458814863,'HH') from file_cto_user_info limit 1

select from_unixtime(1426672632,'yyyy-MM-dd') from file_cto_user_info limit 1
select from_unixtime(cast(substr(1454285291013,1,10) as bigint),'yyyyMMdd hh:mm:ss') from file_cto_user_info limit 1



select from_unixtime(unix_timestamp(),'yyyy-MM-dd') from file_cto_user_info limit 1

--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp() from file_cto_user_info limit 1
--日期转时间戳
select unix_timestamp('2011-12-07 13:01:03') from file_cto_user_info limit 1

--日期时间转日期函数: to_date
select to_date('2011-12-08 10:03:01') from file_cto_user_info limit 1

--日期转年函数: year
select year('2012-12-08') from file_cto_user_info limit 1
select year('2011-12-08 10:03:01') from file_cto_user_info limit 1

--日期转月函数: month
select month('2011-12-08 10:03:01') from file_cto_user_info limit 1;
select month('2011-08-08') from file_cto_user_info limit 1

--日期转天函数: day
select day('2011-12-08 10:03:01') from file_cto_user_info limit 1;
select day('2011-08-12') from file_cto_user_info limit 1

--日期转小时函数: hour
select hour('2011-12-08 10:03:01') from file_cto_user_info limit 1;

select concat(split('2011-12-08 10:03:01',' ')[0],' ',concat(substr(split('2011-12-08 10:03:01',' ')[1],1,2)+8,split('2011-12-08 10:03:01',' ')[1],3,6))
from file_cto_user_info limit 1;

--日期转分钟函数: minute
select minute('2011-12-08 10:03:01') from file_cto_user_info limit 1;

--日期转秒函数: second
select second('2011-12-08 10:03:01') from file_cto_user_info limit 1;

--日期转周函数: weekofyear  返回指定日期所在一年中的星期号，范围为0到53。
select weekofyear('2011-12-08 10:03:01') from file_cto_user_info limit 1;

--两个时间参数的日期之差
select datediff('2015-11-30','2015-11-28') from file_cto_user_info limit 1;

--返回周几
select pmod(datediff('2015-11-09','2013-01-07'),7)+1 from file_cto_user_info limit 1;

--给定时间，在现在时间基础上加上指定的时间段。
select date_add('2015-04-05 10:00：00',0.5) from file_cto_user_info limit 1;
from_unixtime(unix_timestamp(),'yyyy-MM-dd hh:mm:ss')
--给定时间，在此基础上减去指定的时间段
select date_sub('2015-04-05',5) from file_cto_user_info limit 1;

--判断是否满足条件，如果满足返回一个值，如果不满足则返回另一个值
select if(1>0,'条件满足','条件为满足') from file_cto_user_info limit 1;

--返回一组数据中，第一个不为NULL的值，如果均为NULL,返回NULL
select coalesce(null,'1',2,'3','',null) from file_cto_user_info limit 1;

--返回倒序字符串
select reverse('abcdefg') from file_cto_user_info limit 1;

--链接多个字符串，字符串之间以指定的分隔符分开。
select concat_ws('@','1111','2222','3333') from file_cto_user_info limit 1;

--字符串A中的B字符被C字符替代
select regexp_replace('asdc://','://','a') from file_cto_user_info limit 1;

--通过下标返回正则表达式指定的部分。
select regexp_extract('asdc','a(*)dc',1) from file_cto_user_info limit 1;

--获得主机名
select parse_url('http://www.csdn.net/aaa.txt','HOST') from file_cto_user_info limit 1;

--获得参数值
select parse_url('http://www.csdn.net/aaa.htm?userid=1234','userid') from file_cto_user_info limit 1;

--substr
set mapred.job.queue.name=hadoop;
select substr('2015-05-04',1,4) from file_cto_user_info limit 1;