Hive常用函数大全一览

关系运算1、等值比较:=语法:A=B操作类型:所有基本类型描述:如果表达式A与表达式B相等,则为TRUE;否则为FALSEhive>select1fromiteblogwhere1=1;12、不等值比较:语法:AB操作类型:所有基本类型描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A与表达式B不相等,则为TRUE;否则为FALSEhive>select1fromiteblogwhere12;13、小于比较:select1fromiteblogwhere1select1fromiteblogwhere1语法:A>B操作类型:所有基本类型描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A大于表达式B,则为TRUE;否则为FALSEhive>select1fromiteblogwhere2>1;16、大于等于比较:>=语法:A>=B操作类型:所有基本类型描述:如果表达式A为NULL,或者表达式B为NULL,返回NULL;如果表达式A大于或者等于表达式B,则为TRUE;否则为FALSEhive>select1fromiteblogwhere1>=1;1注意:String的比较要注意(常用的时间比较可以先to_date之后再比较)hive>select*fromiteblog;OK201111120900:00:002011111209hive>selecta,b,ab,a=bfromiteblog;201111120900:00:002011111209falsetruefalse2/347、空值判断:ISNULL语法:AISNULL操作类型:所有类型描述:如果表达式A的值为NULL,则为TRUE;否则为FALSEhive>select1fromiteblogwherenullisnull;18、非空判断:ISNOTNULL语法:AISNOTNULL操作类型:所有类型描述:如果表达式A的值为NULL,则为FALSE;否则为TRUEhive>select1fromiteblogwhere1isnotnull;19、LIKE比较:LIKE语法:ALIKEB操作类型:strings描述:如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合表达式B的正则语法,则为TRUE;否则为FALSE.
B中字符"_"表示任意单个字符,而字符"%"表示任意数量的字符.

hive>select1fromiteblogwhere'football'like'foot%';1hive>select1fromiteblogwhere'football'like'foot____';1注意:否定比较时候用NOTALIKEBhive>select1fromiteblogwhereNOT'football'like'fff%';110、JAVA的LIKE操作:RLIKE3/34语法:ARLIKEB操作类型:strings描述:如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合JAVA正则表达式B的正则语法,则为TRUE;否则为FALSE.
hive>select1fromiteblogwhere'footbar'rlike'^f.
*r$';1注意:判断一个字符串是否全为数字:hive>select1fromiteblogwhere'123456'rlike'^\\d+$';1hive>select1fromiteblogwhere'123456aa'rlike'^\\d+$';11、REGEXP操作:REGEXP语法:AREGEXPB操作类型:strings描述:功能与RLIKE相同hive>select1fromiteblogwhere'footbar'REGEXP'^f.
*r$';1数学运算:1、加法操作:+语法:A+B操作类型:所有数值类型说明:返回A与B相加的结果.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
比如,int+int一般结果为int类型,而int+double一般结果为double类型hive>select1+9fromiteblog;10hive>createtableiteblogasselect1+1.
2fromiteblog;hive>describeiteblog;_c0double4/342、减法操作:-语法:A–B操作类型:所有数值类型说明:返回A与B相减的结果.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
比如,int–int一般结果为int类型,而int–double一般结果为double类型hive>select10–5fromiteblog;5hive>createtableiteblogasselect5.
6–4fromiteblog;hive>describeiteblog;_c0double3、乘法操作:*语法:A*B操作类型:所有数值类型说明:返回A与B相乘的结果.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
注意,如果A乘以B的结果超过默认结果类型的数值范围,则需要通过cast将结果转换成范围更大的数值类型hive>select40*5fromiteblog;2004、除法操作:/语法:A/B操作类型:所有数值类型说明:返回A除以B的结果.
结果的数值类型为doublehive>select40/5fromiteblog;8.
0注意:hive中最高精度的数据类型是double,只精确到小数点后16位,在做除法运算的时候要特别注意5/34hive>selectceil(28.
0/6.
999999999999999999999)fromitebloglimit1;结果为4hive>selectceil(28.
0/6.
99999999999999)fromitebloglimit1;结果为55、取余操作:%语法:A%B操作类型:所有数值类型说明:返回A除以B的余数.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
hive>select41%5fromiteblog;1hive>select8.
4%4fromiteblog;0.
40000000000000036注意:精度在hive中是个很大的问题,类似这样的操作最好通过round指定精度hive>selectround(8.
4%4,2)fromiteblog;0.
46、位与操作:&语法:A&B操作类型:所有数值类型说明:返回A和B按位进行与操作的结果.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
hive>select4&8fromiteblog;0hive>select6&4fromiteblog;47、位或操作:|6/34语法:A|B操作类型:所有数值类型说明:返回A和B按位进行或操作的结果.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
hive>select4|8fromiteblog;12hive>select6|8fromiteblog;148、位异或操作:^语法:A^B操作类型:所有数值类型说明:返回A和B按位进行异或操作的结果.
结果的数值类型等于A的类型和B的类型的最小父类型(详见数据类型的继承关系).
hive>select4^8fromiteblog;12hive>select6^4fromiteblog;29.
位取反操作:~语法:~A操作类型:所有数值类型说明:返回A按位取反操作的结果.
结果的数值类型等于A的类型.
hive>select~6fromiteblog;-7hive>select~4fromiteblog;-5逻辑运算:7/341、逻辑与操作:AND语法:AANDB操作类型:boolean说明:如果A和B均为TRUE,则为TRUE;否则为FALSE.
如果A为NULL或B为NULL,则为NULLhive>select1fromiteblogwhere1=1and2=2;12、逻辑或操作:OR语法:AORB操作类型:boolean说明:如果A为TRUE,或者B为TRUE,或者A和B均为TRUE,则为TRUE;否则为FALSEhive>select1fromiteblogwhere1=2or2=2;13、逻辑非操作:NOT语法:NOTA操作类型:boolean说明:如果A为FALSE,或者A为NULL,则为TRUE;否则为FALSEhive>select1fromiteblogwherenot1=2;1数值计算1、取整函数:round语法:round(doublea)返回值:BIGINT说明:返回double类型的整数值部分(遵循四舍五入)hive>selectround(3.
1415926)fromiteblog;38/34hive>selectround(3.
5)fromiteblog;4hive>createtableiteblogasselectround(9542.
158)fromiteblog;hive>describeiteblog;_c0bigint2、指定精度取整函数:round语法:round(doublea,intd)返回值:DOUBLE说明:返回指定精度d的double类型hive>selectround(3.
1415926,4)fromiteblog;3.
14163、向下取整函数:floor语法:floor(doublea)返回值:BIGINT说明:返回等于或者小于该double变量的最大的整数hive>selectfloor(3.
1415926)fromiteblog;3hive>selectfloor(25)fromiteblog;254、向上取整函数:ceil语法:ceil(doublea)返回值:BIGINT说明:返回等于或者大于该double变量的最小的整数hive>selectceil(3.
1415926)fromiteblog;4hive>selectceil(46)fromiteblog;469/345、向上取整函数:ceiling语法:ceiling(doublea)返回值:BIGINT说明:与ceil功能相同hive>selectceiling(3.
1415926)fromiteblog;4hive>selectceiling(46)fromiteblog;466、取随机数函数:rand语法:rand(),rand(intseed)返回值:double说明:返回一个0到1范围内的随机数.
如果指定种子seed,则会等到一个稳定的随机数序列hive>selectrand()fromiteblog;0.
5577432776034763hive>selectrand()fromiteblog;0.
6638336467363424hive>selectrand(100)fromiteblog;0.
7220096548596434hive>selectrand(100)fromiteblog;0.
72200965485964347、自然指数函数:exp语法:exp(doublea)返回值:double说明:返回自然对数e的a次方hive>selectexp(2)fromiteblog;7.
38905609893065自然对数函数:ln语法:ln(doublea)返回值:double说明:返回a的自然对数10/341hive>selectln(7.
38905609893065)fromiteblog;2.
08、以10为底对数函数:log10语法:log10(doublea)返回值:double说明:返回以10为底的a的对数hive>selectlog10(100)fromiteblog;2.
09、以2为底对数函数:log2语法:log2(doublea)返回值:double说明:返回以2为底的a的对数hive>selectlog2(8)fromiteblog;3.
010、对数函数:log语法:log(doublebase,doublea)返回值:double说明:返回以base为底的a的对数hive>selectlog(4,256)fromiteblog;4.
011、幂运算函数:pow语法:pow(doublea,doublep)11/34返回值:double说明:返回a的p次幂hive>selectpow(2,4)fromiteblog;16.
012、幂运算函数:power语法:power(doublea,doublep)返回值:double说明:返回a的p次幂,与pow功能相同hive>selectpower(2,4)fromiteblog;16.
013、开平方函数:sqrt语法:sqrt(doublea)返回值:double说明:返回a的平方根hive>selectsqrt(16)fromiteblog;4.
014、二进制函数:bin语法:bin(BIGINTa)返回值:string说明:返回a的二进制代码表示hive>selectbin(7)fromiteblog;11115、十六进制函数:hex12/34语法:hex(BIGINTa)返回值:string说明:如果变量是int类型,那么返回a的十六进制表示;如果变量是string类型,则返回该字符串的十六进制表示hive>selecthex(17)fromiteblog;11hive>selecthex('abc')fromiteblog;61626316、反转十六进制函数:unhex语法:unhex(stringa)返回值:string说明:返回该十六进制字符串所代码的字符串hive>selectunhex('616263')fromiteblog;abchive>selectunhex('11')fromiteblog;-hive>selectunhex(616263)fromiteblog;abc17、进制转换函数:conv语法:conv(BIGINTnum,intfrom_base,intto_base)返回值:string说明:将数值num从from_base进制转化到to_base进制hive>selectconv(17,10,16)fromiteblog;11hive>selectconv(17,10,2)fromiteblog;1000118、绝对值函数:abs语法:abs(doublea)abs(inta)13/34返回值:doubleint说明:返回数值a的绝对值hive>selectabs(-3.
9)fromiteblog;3.
9hive>selectabs(10.
9)fromiteblog;10.
919、正取余函数:pmod语法:pmod(inta,intb),pmod(doublea,doubleb)返回值:intdouble说明:返回正的a除以b的余数hive>selectpmod(9,4)fromiteblog;1hive>selectpmod(-9,4)fromiteblog;320、正弦函数:sin语法:sin(doublea)返回值:double说明:返回a的正弦值hive>selectsin(0.
8)fromiteblog;0.
717356090899522821、反正弦函数:asin语法:asin(doublea)返回值:double说明:返回a的反正弦值hive>selectasin(0.
7173560908995228)fromiteblog;0.
814/3422、余弦函数:cos语法:cos(doublea)返回值:double说明:返回a的余弦值hive>selectcos(0.
9)fromiteblog;0.
621609968270664423、反余弦函数:acos语法:acos(doublea)返回值:double说明:返回a的反余弦值hive>selectacos(0.
6216099682706644)fromiteblog;0.
924、positive函数:positive语法:positive(inta),positive(doublea)返回值:intdouble说明:返回ahive>selectpositive(-10)fromiteblog;-10hive>selectpositive(12)fromiteblog;1225、negative函数:negative语法:negative(inta),negative(doublea)返回值:intdouble说明:返回-a15/34hive>selectnegative(-5)fromiteblog;5hive>selectnegative(8)fromiteblog;-8日期函数1、UNIX时间戳转日期函数:from_unixtime语法:from_unixtime(bigintunixtime[,stringformat])返回值:string说明:转化UNIX时间戳(从1970-01-0100:00:00UTC到指定时间的秒数)到当前时区的时间格式hive>selectfrom_unixtime(1323308943,'yyyyMMdd')fromiteblog;201112082、获取当前UNIX时间戳函数:unix_timestamp语法:unix_timestamp()返回值:bigint说明:获得当前时区的UNIX时间戳hive>selectunix_timestamp()fromiteblog;13233096153、日期转UNIX时间戳函数:unix_timestamp语法:unix_timestamp(stringdate)返回值:bigint说明:转换格式为"yyyy-MM-ddHH:mm:ss"的日期到UNIX时间戳.
如果转化失败,则返回0.
hive>selectunix_timestamp('2011-12-0713:01:03')fromiteblog;132323406316/344、指定格式日期转UNIX时间戳函数:unix_timestamp语法:unix_timestamp(stringdate,stringpattern)返回值:bigint说明:转换pattern格式的日期到UNIX时间戳.
如果转化失败,则返回0.
hive>selectunix_timestamp('2011120713:01:03','yyyyMMddHH:mm:ss')fromiteblog;13232340635、日期时间转日期函数:to_date语法:to_date(stringtimestamp)返回值:string说明:返回日期时间字段中的日期部分.
hive>selectto_date('2011-12-0810:03:01')fromiteblog;2011-12-086、日期转年函数:year语法:year(stringdate)返回值:int说明:返回日期中的年.
hive>selectyear('2011-12-0810:03:01')fromiteblog;2011hive>selectyear('2012-12-08')fromiteblog;20127、日期转月函数:month语法:month(stringdate)返回值:int说明:返回日期中的月份.
hive>selectmonth('2011-12-0810:03:01')fromiteblog;1217/34hive>selectmonth('2011-08-08')fromiteblog;88、日期转天函数:day语法:day(stringdate)返回值:int说明:返回日期中的天.
hive>selectday('2011-12-0810:03:01')fromiteblog;8hive>selectday('2011-12-24')fromiteblog;249、日期转小时函数:hour语法:hour(stringdate)返回值:int说明:返回日期中的小时.
hive>selecthour('2011-12-0810:03:01')fromiteblog;1010、日期转分钟函数:minute语法:minute(stringdate)返回值:int说明:返回日期中的分钟.
hive>selectminute('2011-12-0810:03:01')fromiteblog;311、日期转秒函数:second18/34语法:second(stringdate)返回值:int说明:返回日期中的秒.
hive>selectsecond('2011-12-0810:03:01')fromiteblog;112、日期转周函数:weekofyear语法:weekofyear(stringdate)返回值:int说明:返回日期在当前的周数.
hive>selectweekofyear('2011-12-0810:03:01')fromiteblog;4913、日期比较函数:datediff语法:datediff(stringenddate,stringstartdate)返回值:int说明:返回结束日期减去开始日期的天数.
hive>selectdatediff('2012-12-08','2012-05-09')fromiteblog;21314、日期增加函数:date_add语法:date_add(stringstartdate,intdays)返回值:string说明:返回开始日期startdate增加days天后的日期.
hive>selectdate_add('2012-12-08',10)fromiteblog;2012-12-1819/3415、日期减少函数:date_sub语法:date_sub(stringstartdate,intdays)返回值:string说明:返回开始日期startdate减少days天后的日期.
hive>selectdate_sub('2012-12-08',10)fromiteblog;2012-11-28条件函数1、If函数:if语法:if(booleantestCondition,TvalueTrue,TvalueFalseOrNull)返回值:T说明:当条件testCondition为TRUE时,返回valueTrue;否则返回valueFalseOrNullhive>selectif(1=2,100,200)fromiteblog;200hive>selectif(1=1,100,200)fromiteblog;1002、非空查找函数:COALESCE语法:COALESCE(Tv1,Tv2,…)返回值:T说明:返回参数中的第一个非空值;如果所有值都为NULL,那么返回NULLhive>selectCOALESCE(null,'100','50′)fromiteblog;1003、条件判断函数:CASE语法:CASEaWHENbTHENc[WHENdTHENe]*[ELSEf]END返回值:T说明:如果a等于b,那么返回c;如果a等于d,那么返回e;否则返回f20/34hive>Selectcase100when50then'tom'when100then'mary'else'tim'endfromiteblog;maryhive>Selectcase200when50then'tom'when100then'mary'else'tim'endfromiteblog;tim4、条件判断函数:CASE语法:CASEWHENaTHENb[WHENcTHENd]*[ELSEe]END返回值:T说明:如果a为TRUE,则返回b;如果c为TRUE,则返回d;否则返回ehive>selectcasewhen1=2then'tom'when2=2then'mary'else'tim'endfromiteblog;maryhive>selectcasewhen1=1then'tom'when2=2then'mary'else'tim'endfromiteblog;tom字符串函数1、字符串长度函数:length语法:length(stringA)返回值:int说明:返回字符串A的长度hive>selectlength('abcedfg')fromiteblog;72、字符串反转函数:reverse语法:reverse(stringA)返回值:string说明:返回字符串A的反转结果hive>selectreverse(abcedfg')fromiteblog;gfdecba21/343、字符串连接函数:concat语法:concat(stringA,stringB…)返回值:string说明:返回输入字符串连接后的结果,支持任意个输入字符串hive>selectconcat('abc','def','gh')fromiteblog;abcdefgh4、带分隔符字符串连接函数:concat_ws语法:concat_ws(stringSEP,stringA,stringB…)返回值:string说明:返回输入字符串连接后的结果,SEP表示各个字符串间的分隔符hive>selectconcat_ws(',','abc','def','gh')fromiteblog;abc,def,gh5、字符串截取函数:substr,substring语法:substr(stringA,intstart),substring(stringA,intstart)返回值:string说明:返回字符串A从start位置到结尾的字符串hive>selectsubstr('abcde',3)fromiteblog;cdehive>selectsubstring('abcde',3)fromiteblog;cdehive>selectsubstr('abcde',-1)fromiteblog;(和ORACLE相同)e6、字符串截取函数:substr,substring语法:substr(stringA,intstart,intlen),substring(stringA,intstart,intlen)返回值:string说明:返回字符串A从start位置开始,长度为len的字符串22/34hive>selectsubstr('abcde',3,2)fromiteblog;cdhive>selectsubstring('abcde',3,2)fromiteblog;cdhive>selectsubstring('abcde',-2,2)fromiteblog;de7、字符串转大写函数:upper,ucase语法:upper(stringA)ucase(stringA)返回值:string说明:返回字符串A的大写格式hive>selectupper('abSEd')fromiteblog;ABSEDhive>selectucase('abSEd')fromiteblog;ABSED8、字符串转小写函数:lower,lcase语法:lower(stringA)lcase(stringA)返回值:string说明:返回字符串A的小写格式hive>selectlower('abSEd')fromiteblog;absedhive>selectlcase('abSEd')fromiteblog;absed9、去空格函数:trim语法:trim(stringA)返回值:string说明:去除字符串两边的空格hive>selecttrim('abc')fromiteblog;abc23/3410、左边去空格函数:ltrim语法:ltrim(stringA)返回值:string说明:去除字符串左边的空格hive>selectltrim('abc')fromiteblog;abc11、右边去空格函数:rtrim语法:rtrim(stringA)返回值:string说明:去除字符串右边的空格hive>selectrtrim('abc')fromiteblog;abc12、正则表达式替换函数:regexp_replace语法:regexp_replace(stringA,stringB,stringC)返回值:string说明:将字符串A中的符合java正则表达式B的部分替换为C.
注意,在有些情况下要使用转义字符,类似oracle中的regexp_replace函数.

hive>selectregexp_replace('foobar','oo|ar','')fromiteblog;fb13、正则表达式解析函数:regexp_extract语法:regexp_extract(stringsubject,stringpattern,intindex)返回值:string24/34说明:将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符.