PS:
笔者只整理了自己常用的一些函数,仅供参考。示例一部分是虚拟机内开启hive服务后运行hsq结果的展示,另一部分是在zeppelin使用hive运行的结果的展示。(没有放示例的是因为hive版本原因,无法使用个别函数)
查看全部函数
show functions;
查看函数用法
desc function extended FUNC_NAME;
eg:
desc function extended conv;
eg.result:
数学函数
round()
round(x[, d])==>将x舍入到d小数位;
eg:
select round(8.365,2);
eg.result:
floor()
floor(x) ==>查找不大于x的最大整数;
eg:
select floor(8.365);
eg.result:
ceil()
ceil(x) ==>查找不小于x的最小整数;
eg:
select ceil(8.365);
eg.result:
abs()
abs(x)==>计算x的绝对值;
eg:
select abs(16),abs(-16);
eg.result:
log()
log([b], x) ==>返回以b为底的x的对数;
eg:
select log(2,8);
eg.result:
pow()
pow(double d,double p) ==>d的p次幂,返回double型;
eg:
select pow(2,3);
eg.result:
conv()
conv(bigint num/string num,int from_base,int to_base) ==>将biginth或string类型的num从from_base进制转换成to_base进制,返回string类型;
eg:
select conv(27,10,2),conv(10010,2,10);
eg.result:
pmod()
pmod(int/double d1,int/double d2) ==>double型d1对double型d2取模,结果为double型(int型d1对int型d2取模,结果为int型);
eg:
select pmod(8,3);
eg.result:
[a]sin()
[a]sin(double d)==>返回d的正反正弦值,结果为double型;
eg:
select sin(1);
select asin(1);
eg.result:
[a]cos()
[a]sin(double d)==>返回d的正反余弦值,结果为double型;
eg:
select cos(1);
select acos(1);
eg.result:
[a]tan()
[a]sin(double d)==>返回d的正反余弦值,结果为double型;
eg:
select tan(1);
select atan(1);
eg.result:
degrees()
degrees(double d==>将弧度值d转换成角度值,结果为double型;
eg:
select degrees(2);
eg.result:
radians()
radians(double d)–将角度值d转换成弧度值,结果为double型;
eg:
select radians(90);
eg.result:
positive()
positive(int i==>等价有效表达式是+i,返回i,结果为int型;
positive(double d)==>等价有效表达式是d,返回d,结果为double型;
eg:
select positive(7),positive(-7);
eg.result:
negative()
negative(int i) ==>等价有效表达式是-i,返回i的负数,结果为int型;
negative(double d) ==>等价有效表达式是-i,返回d的负数,结果为double型;
eg:
select negative(13),negative(-26);
eg.result:
sign()
sign(double d) ==>如果d是正数的话,则返回float型1.0,如果d是负数的话,则返回-1.0,否则返回0.0;
eg:
select sign(245),sign(-0),sign(-28);
eg.result:
e()&pi()
e()–数学常熟e,超越数;
pi()–数学常数Pi,圆周率;
eg:
select e(),pi();
eg.result:
greatest()
greatest(T…vs)==>找出集合里最大的数返回;
eg:
select greatest(9,14,6,28,32.5,59,6);
eg.result:
least()
least(T…vs)==>找出集合里最小的数返回;
eg:
select least(9,14,6,28,32.5,59,6);
eg.result:
bound()
bround(x [,d]) ==>使用HALF_EVEN舍入模式将x舍入到d小数位。银行家的四舍五入,该值四舍五入到最接近的偶数。也称为高斯舍入;
factorial()
factorial(int) ==>返回n的阶乘;
shiftleft()
shiftleft(a,b) ==>按位左移(把a转换为二进制数之后全部位数往左移b位得到的数再转为十进制数返回);
shiftright()
shiftright(a,b) ==> 按位右移(把a转换为二进制数之后全部位数往右移b位得到的数再转为十进制数返回);
集合函数
size()
size(Map<K.V>) ==>返回map中元素的个数;
size(Array) ==>返回数组array的元素个数;
eg:
--字段address为一个数组
select size(address) from shop;
eg.result:
map_keys()/map_values()
map_keys(Map<K,V>) ==>返回Map中key的数组;
map_values(Map<K,V>)==>返回Map中value的数组;
eg:
--字段volumn为一个Map
select map_keys(volumn) from shop;
eg.result:
array_contains()
array_contains(Array,T) ==>判断字段T是否存在于数组T中;
eg:
--字段address为一个数组
select array_contains(address,"小行路28号") from shop;
eg.result:
sort_array()
sort_array(Array)==>把字段数组内的值按照升序排序;
eg:
--字段address为一个数组
select sort_array(map_values(volumn)) from shop;
eg.result:
类型转换
cast()
cast(expr as <type> )
任何整数类型都可以隐式地转换成一个范围更大的类型
BOOLEAN类型不能转换为其他任何数据类型!
eg:
select cast('1' as int);
eg.result:
日期函数
from_unixtime()
from_unixtime(bigint time,string time_format) ==>输入一个bigint,转换为日期,并返回指定日期格式的字符串;
eg:
select from_unixtime(1527327323,'yyyy-MM-dd');
eg.result:
PS:使用from_unxitime可以不加格式,默认输出格式为"yyyy-MM-dd HH:mm:ss";
date_format()
date_format(date/timestamp/string date,string format) ==>对日期进行格式调整,返回字符串类型的日期格式;
eg:
select date_fromat(current_date,'yyyy-MM-dd');
select date_format(current_date,'yyyy');
eg.result:
current_date()
current_date() ==>获取当前日期;
to_date()
to_date(string timestamp)==>将字符串类型的日期转为日期类型;
eg:
select to_date('2014-11-01');
eg.result:
current_timestamp()
current_timestamp() ==>返回当前日期时间;
eg:
unix_timestamp()
unix_timestamp() ==>当前时间转为bigint类型数值返回;
unix_timestamp(string datetime) ==>指定时间转为bigint类型数值返回;
unix_timestamp(string datetime,string format_pattern) ==>指定时间到指定类型内的时间转为bigint类型数值返回;
eg:
select unix_timestamp();
select unix_timestamp('2014-11-02 11:11:30');
select unix_timestamp('2014-11-02 11:11','yyyy-MM-dd hh:mm');
eg.result:
date_add()
date_add(string date,int days) ==>在日期date上增加数量为days的天数后得到的一个新的字符串格式的日期;
eg:
select date_add('2019-12-01',32);
eg.result:
add_months()
add_months(string date,int numberOfMonths) ==>在日期date上增加数量为number的月数后得到的一个新的字符串格式的日期;
eg:
select add_months('2014-07-23',6);
eg.result:
next_day()
next_day(string date,string dayOfWeek) ==>date之后的下一个dayOfweek为那一天 (Mon,Tu,We,Th,Fr,Sa,Su);
eg:
select next_day(current_date,'Mon');
select next_day(current_date,'Th');
eg.result:
last_day()
last_day(string date) ==>返回该月最后一天日期(string);
eg:
select last_day(current_date);
eg.result:
trunc()
trunc(string date,string format) ==>返回日期的最开始日期(format为’YY’/‘MM’…)
eg:
select trunc(current_date,'YY');
select trunc(current_date,'MM');
eg.result:
datediff()
datediff(endDate, startDate) ==>计算两个日期的差值,返回类型为int;
eg:
select datediff('2013-12-12','2015-09-20');
eg.result:
months_between()
months_between(string dateform,string dateto) ==>计算两个日期之间的月差值,返回类型为double;
详细注释:eg:
返回日期date1和date2之间的月数 如果date1晚于date2,则结果为正。 如果date1早于date2,则结果为负数。 如果date1和date2是月份的同一天或月份的最后几天,则结果始终是整数。 否则,UDF将基于31天的月份计算结果的小数部分,并考虑时间分量date1和date2的差异。
date1和date2类型可以是日期,时间戳或字符串,格式为“ yyyy-MM-dd”或“ yyyy-MM-dd HH:mm:ss”。 结果四舍五入到小数点后8位。
select months_between('2014-11-01','2014-05-20');
eg.result:
条件函数
if()
if(boolean testCondition, T valueTrue, T valueFalseOrNull) ==>当条件testCondition为true时,返回valueTrue;否则返回valueFalseOrNull;
eg:
select if(2=2,1,NULL);
eg.result:
nvl()
nvl(value,default_value) ==>如果value值为空,则返回default_value的值;
eg:
select nvl(1,0),nvl(NULL,2);
eg.result:
coalesce()
coalesce(T…as) ==>返回第一个非NULL;
eg:
select coalesce(NULL,NULL,19,2,7);
eg.result:
case when
case a when b then … when c then … else … end ==>多条件做判断并返回;
case when a then … when b then … else … end ==>多条件做判断并返回;
eg:
--两句hql表达同一意思
select shopid,case online when true then "online" else "offline" end from shop;
select shopid,case when online=true then "online" else "offline" end from shop;
eg.result:
isnull&isnotnull
isnull(a) ==>a为NULL返回true,否则false;
isnotnull(a) ==> a为NULL返回false,否则true;
eg:
select isnull(NULL);
select isnotnull(NULL);
eg.result:
字符串函数
ascii()
ascii(string a) ==>返回字符串首字母阿斯科码值
eg:
select ascii('abc');
select ascii('Abc');
eg.result:
concat&concat_ws()
concat(string a,string b…) ==>连接字符串;
concat_ws(string step,string a,string b…) ==>用字符串step连接后面的每个字符串;
eg:
select concat('a','b','c');
select concat_ws('-','a','b','c');
eg.result:
sentence()
sentence(string a) ==>拆分字符串为数组;
eg:
select sentences("I like NanJing");
eg.result:
ngrams()&context_ngrams()
ngrams(expr, n, k, pf)
估计由字符串序列(表示为字符串数组或字符串数组)组成的行中的前k个n-gram。 “ pf”是控制内存使用情况的可选精度因子。
参数“ n”指定正在估计的类型的n-gram。 单字组为n = 1,双字组为n =2。通常,n将不大于约5。“ k”参数指定UDAF返回多少个最高频率的n-gram。 可选的精度因子“ pf”指定用于估计的内存量; 更多的内存将提供更准确的频率计数,但可能会使JVM崩溃。 默认值为20,内部保留20 * k个n-gram,但仅返回k个最高频率的n-gram。 输出是带有前k个n-gram的结构数组。 explode()此UDAF的输出可能很方便。
encode()&decode()
encode(str, str) ==>使用第二个参数字符集对第一个参数进行编码字符集的可能选项是“ US_ASCII”,“ ISO-8859-1”,‘UTF-8’,‘UTF-16BE’,‘UTF-16LE’和’UTF-16’。 如果有任何参数为null,结果也将为null;
eg:
format_number()
format_number(X,D)==> 将数字X格式化为’#,###,###。##'之类的格式,四舍五入到D小数位,然后将结果作为字符串返回。;如果D为0,则结果没有小数点或小数部分;
TIPS:四舍六入五取偶
eg:
select format_number(2.4567,2)
eg.result:
get_json_object()
提取json字符数组里的某一个字段;
eg:
--在zeeplin里hsl末尾不用加';'
select get_json_object('{"name":"henry","hobbies":["s","a","c"],"address":{"province":"js","city":"nj"}}','$.address') address,
get_json_object('{"name":"henry","hobbies":["s","a","c"],"address":{"province":"js","city":"nj"}}','$.name') name
eg.result:
in_file()
in_file(string line,string path) ==>path指向的文件中是否包含line内容
eg:
--在zeeplin里hsl末尾不用加';'
select in_file('jack','/opt/test.txt') fir,in_file('jack_chen','/opt/test.txt') sec
eg.result:
parse_url()
parse_url(string urlstring,string part[,string key])==>提取url内指定内容信息;
part可用有效值为:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO
URL的组成结构与含义简述:http://c.biancheng.net/view/3278.html
--在zeeplin里hsl末尾不用加';'
select parse_url('https://www.baidu.com/s?wd=%E5%9B%BE%E7%89%87&rsv_spt=1&rsv_iqid=0xf3625b490001f054&issp=1&f=8&rsv_bp=1&rsv_idx=2&ie=utf-8&tn=baiduhome_pg&rsv_enter=1&rsv_dl=tb&rsv_sug3=8&rsv_sug1=8&rsv_sug7=101&rsv_sug2=0&rsv_btype=i&prefixsug=tupian%2520&rsp=5&inputT=1202&rsv_sug4=4268','HOST'),
parse_url('https://www.baidu.com/s?wd=%E5%9B%BE%E7%89%87&rsv_spt=1&rsv_iqid=0xf3625b490001f054&issp=1&f=8&rsv_bp=1&rsv_idx=2&ie=utf-8&tn=baiduhome_pg&rsv_enter=1&rsv_dl=tb&rsv_sug3=8&rsv_sug1=8&rsv_sug7=101&rsv_sug2=0&rsv_btype=i&prefixsug=tupian%2520&rsp=5&inputT=1202&rsv_sug4=4268','QUERY')
eg.result:
printf()
printf(string format,T…t) ==>格式化输出;
eg:
--在zeeplin里hsl末尾不用加';'
select printf('%s %d','henry',20)
eg.result:
like&rlike
like(str, pattern) ==>匹配相同字符的字段;
rlike 与like类似,不过相比like更加灵活,可以使用正则表达式来使用;
eg:
--在zeeplin里hsl末尾不用加';'
select a.shopname name from shop a where a.shopname like '%超市'
eg.result:
regexp_extract()
regexp_extract(string subject, string pattern, int index) ==>将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符
eg:
--在zeeplin里hsl末尾不用加';'
select regexp_extract('namehenryyokdalingduck','name(.*?)(ok)(.*?)duck',3)
eg.result:
spilt()
spilt(string src,string regex) ==>将目标字符串src按照分隔符regex进行分割返回;
eg:
--在zeeplin里hsl末尾不用加';'
select split('hello|world|quick|set','\\|') des
eg.result:
translate()
translate(string src,string chars,string dchars) ==>将目标字符串src里的符合chars的字符替换为dchars字符(一一对应);
eg:
--在zeeplin里hsl末尾不用加';'
select translate('aabbc','ab','#*') a,
translate('aabbc','ab','#') b
eg.result:
initcap()
initcap(string str) ==>将字符串str的首字母替换为大写(以’ '为界限判定是否连续)
eg:
--在zeeplin里hsl末尾不用加';'
select initcap('i like china') a,
initcap('i?like.china') b
eg.result:
substr()
substr(string src,int begin[,int len]) ==>截取字符串src,从begin位置开始,截取len长度的字符串;
eg:
--在zeeplin里hsl末尾不用加';'
select substr('helloworld',2,5) a,
substr('helloworld',2) b
eg.result:
locate()
locate(string sub,string src,int startPos) ==>查看子字符串sub在目标字符串src中从其第startPos开始出现的位置;
eg:
--在zeeplin里hsl末尾不用加';'
select locate('wo','wordoword') a,
locate('wo','wordoword',4) b
eg.result:
instr()
instr(string src,string sub) ==>查看子字符串sub在字符串src中的开始位置(位置从1开始);
--在zeeplin里hsl末尾不用加';'
select instr('helloworld','world') a
eg.result:
md5()
单向加密函数;
eg:
base64&()unbases64()
base64(binary)==>将参数从二进制转换为基本64字符串;
eg:
select base64(cast('abc'as binary)),
unbase64('YWJj')
eg.result:
hex&()unhex()
返回字符串a正反16进制后的值(对称加密);
eg:
sha2()
sha2(string/binary, len) ==>计算SHA-2系列哈希函数(SHA-224,SHA-256,SHA-384和SHA-512);
第一个参数是要哈希的字符串或二进制。 第二个参数表示结果的所需位长,该位的值必须为224、256、384、512或0(等于256)。 从Java 8开始支持SHA-224。如果任一参数为NULL或哈希长度不是允许的值之一,则返回值为NULL;
eg:
select sha2('abc',256),
sha2('abc',384),
sha2('abc',512)
eg.result:
soundex()
soundex(string)==>返回字符串的soundex代码。
soundex代码包含名称的第一个字母,后跟三个数字。
eg:
select soundex('abc')
eg.result:
substring&substr
substr(str, pos[, len])==>返回以pos开头且长度为len的str的子字符串或substr(bin,pos [,len])-返回以pos开头且长度为len的 字节数组的切片;
同义词:substring;
pos是从1开始的索引; 如果pos <0,则从str的末尾开始倒数来确定起始位置;
eg:
select substr('hello-world-hello-world',-5),
substr('hello-world-hello-world',7),
substr('hello-world-hello-world',7,5)
eg.result:
levenshtein()
leventhein(string a, string b)==>计算两个字符串的差异大小;
官方注释:Levenshtein距离是用于测量两个序列之间差异的字符串量度。 非正式地,两个单词之间的Levenshtein距离是将一个单词转换为另一个单词所需的最小单字符编辑(即插入,删除或替换)次数。
e.g:
select levenshtein("skating","skiing"),
levenshtein("warning","swiming"),
levenshtein("hello","hello")
eg.result:
聚合函数
count()
count(*)-返回检索到的行总数,包括包含NULL值的行;
count(expr)-返回为其提供的表达式为非NULL的行数;
count(DISTINCT expr [,expr ...])-返回为其提供的表达式唯一且非NULL的行数;
sum()
sum(x)==>返回一组数字的总和;
avg()
avg(x)==>返回一组数字的平均值;
max()&min()
max(expr)/min(expr)==>返回表达式的最大值/最小值;
var_pop()
var_pop(x) ==>返回一组数字的方差
var_sample()
studev_pop()
studev_sample()
covar_sample()
corr()
corr(x,y)==>返回相关的皮尔逊系数;
在一组数字对之间, 该函数将任何一对数字类型作为参数,并返回一个double。 任何带有NULL的对都将被忽略
如果该函数应用于空集或 单例集,将返回NULL;否则,它将计算以下内容:
COVAR_POP(x,y)/(STDDEV_POP(x)* STDDEV_POP(y))
如果x和y都不为空, COVAR_POP是总体协方差, STDDEV_POP是总体标准偏差;
percentile()
percentile(expr,pc)==>返回pc处expr的百分位数(范围:[0,1])。pc可以是双精度数组或双精度数组
collect_list()&collect_set()
collect_list(x)==>返回具有重复项的对象列表;
collect_set(x)==>返回消除重复元素的一组对象;
表生成函数
侧视图
explode()
explode(a)==>将数组a的元素分为多行,或将地图的元素分为多行和多列;
e.g:
原表数据(表中两个字段,user,friends,一个user对应一个很长的friends的字符串):
SQL如下:
#explode之前将字符串转为array,不然类型不匹配,无法explode
select
user_id,
friendid
from
user_friends
lateral view explode(split(friends," "))a as friendid
eg.result:
posexplode()
posexplode(a)==>行为类似于对数组进行爆炸,但包括原始数组中项目的位置;
e.g:
原表数据(表中两个字段,user,friends,一个user对应一个很长的friends的字符串):
SQL如下:
#explode之前将字符串转为array,不然类型不匹配,无法explode
select
user_id,
index,
friendid
from
user_friends
lateral view posexplode(split(friends," "))a as index,friendid
eg.result:
stack()
stack(n,cols …)==>将k列转换为大小为k / n的n行
json_tuple()
json_tuple(jsonStr,p1,p2,…,pn)==>类似于get_json_object,但是它使用多个名称并返回一个元组。 所有输入参数和输出列类型都是字符串。
parse_url_tuple()
parse_url_tuple(URL,partname1,partname2,…,partnameN)==>从URL中提取N(N> = 1)个部分;
它使用一个URL和一个或多个部件名,并返回一个元组。 所有输入参数和输出列类型都是字符串;
inline()
inline( ARRAY( STRUCT()[,STRUCT()] ==>爆炸数组和结构化成表;
PS:如果有写错或者写的不好的地方,欢迎各位大佬在评论区留下宝贵的意见或者建议,敬上!如果这篇博客对您有帮助,希望您可以顺手帮我点个赞!不胜感谢!
原创作者:wsjslient |