Hive函数整理—Hadoop生态圈（七）

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；

详细注释：
返回日期date1和date2之间的月数 如果date1晚于date2，则结果为正。 如果date1早于date2，则结果为负数。 如果date1和date2是月份的同一天或月份的最后几天，则结果始终是整数。 否则，UDF将基于31天的月份计算结果的小数部分，并考虑时间分量date1和date2的差异。
date1和date2类型可以是日期，时间戳或字符串，格式为“ yyyy-MM-dd”或“ yyyy-MM-dd HH：mm：ss”。 结果四舍五入到小数点后8位。

eg:

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

作者主页：https://blog.csdn.net/wsjslient

---