Hive函数详解以及案例参考_hive base函数

hive函数和mysql的函数在使用上有比较大的相似性，但具体在类别上还是有所区别，下面从数学、集合类型转换、日期、条件、字符串这几个维度整理了常用的函数。

一、在Hive中查看函数的介绍

1、desc function extended FUNC_NAME;

二、数学函数

1、 log(double base,double a) 简介：底数为base的a的对数，base可以自定义 返回值类型：double

2、 pow(double base,double p) 简介：幂运算，底数为base的p的幂的值，若p为0.5，则为开根号运算

3、conv(bigint/string v,int from_base,int to_base)
简介：进制之间的转换，第一个参数可以为字符串，第二个参数为第一个参数当前的进制，第三个参数为要转换的进制

4、pmod(int/double a,int/double b) 简介：取余运算

5、 [a]sin(double/decimal a)/[a]cos(double/decimal
a)/[a]tan(double/decimal a) 简介：正弦余弦和正切函数，中括号的[a]加上之后就是反的

6、degrees(double/decimal a)#弧度转化为角度
radians(double/decimal a)#角度转化为弧度

7、 positive(int/double a)#返回数字本身
negative(int/double a)#返回数字的相反数
8、e()/pi()#自然对数和圆周率

9、greatest(T…vs)#横向找出所有值里的最大值
least(T…vs)#横向找出所有值里的最小值

10、在1.0版本目前没有
bround(a,b)#财务舍入法，对小数5的处理，四舍六入五取偶
factorial(a)#int 20以内的阶乘，只能在20以内
shiftleft(a,b)#位左移
shiftright(a,b)#位右移

三、集合函数

11、 size(Map<K,V>/Array)，求map或array的长度，返回值是int。struct结构体是不可以使用size的
案例table：

12、 map_keys(Map<K,V>)，获取map里的键，返回值是存放所有键的数组

13、map_values(Map<K,V>)，获取map里的值，返回值是存放所有值的数组

14、array_contains(Array,T)，判断参数1的数组里是否包含参数2的元素，返回布尔值
15、sort_array(Array)，对参数里的数组进行排序，从小到大

四、类型转换

16、cast(expr as )把expr转换为新的type类型，比如说在使用concat函数时，需要把数字类型的字段转换为字符串类型的

五、日期函数

17、from_unixtime(bigint time,string time_format)#将参数1的长整数转换为参数2位置的日期时间格式，返回一个日期格式的字符串；格式可以根据需要自由选取，可以只选日期也可以值选时间
通过长整数和substr配合，取到日期

18、date_format(date/timestamp/string date,string format)#参数1为日期格式的日期，根据参数2的需要返回一个字符串值，参数2可以为YYYY-MM-dd hh:mm:ss中任意的一项或者多项，如果是一项，比如说M，则可以获取该日期的月份
19、current_date()#获取当前的日期select current_date();
20、to_date(string timestamp)#保留日期格式，等同于date_format(current_date(),‘YYYY-MM-dd’)

21、current_timestamp()#获取当前的日期和时间

22、 unix_timestamp()#获取当前时间的长整数
23、unix_timestamp(string datetime)#获取指定日期时间的长整数，不加参数就是当前日期，是重载

24、unix_timestamp(string datetime,string format_pattern)#指定格式的部分转换为长整数，没有在格式内的部分忽略掉
select from_unixtime(unix_timestamp(‘2020-12-14’,‘yyyy-MM-dd’),‘yyyy-MM-dd’)
select from_unixtime(unix_timestamp(concat(regexp_replace(‘2020/12/14’,’/’,’-’),’ 00:00:00’)),‘yyyy-MM-dd’)
select from_unixtime(unix_timestamp(‘20201214’,‘yyyyMMdd’),‘yyyyMMdd’)
上面3条分别把不同的日期格式转化为长整数再转为统一的日期格式
25、date_add(string date,int days)#加减天数，第一个参数为日期，第二个参数为±值
26、add_months(string date,int months)#加减月数±3/6/12
27、next_day(string date,string dayOfWeek)#返回接下来第一个 参数2指定的“星期几”格式 的日期，
28、last_day(string date)#返回该月的最后一天
29、trunc(string date,string format)#返回日期本年、本月的第一天，参数2的格式可以为多种YY/YEAR/MM/MONTH/MON
30、datediff(string datefrom,string dateto)#天数差，输入两个日期参数，返回前面的参数减后面的参数
31、 months_between(string datefrom,string dateto)#月数差
几个日期函数的测试题
a.本年第一天
select trunc(current_date(),‘YY’);
b.本月第一天
select trunc(current_date(),‘MM’);
c.本季度第一天
select concat_ws(’-’,cast(year(current_date()) as string),cast(ceil(month(current_date())/3)*3-2 as string),‘1’);
d求本周第一天的日期
select date_add(next_day(current_date(),‘SU’),-7);

六、条件函数

31、if(boolean,T vtrue,T vfalse)#嵌套时用case when
32、 nvl(T value,T default)#和mysql里的ifnull相同，如果是空值返回第二个参数，如果不是空值null则返回第一个参数的值
33、coalesce(T…vs)#返回第一个非空null的值
34、case a when b then … when c then … else …end
case when a then … when b then … else … end

35、isnull(v)，isnotnull(v)#判断是否是null,

七、字符串函数

36、ascii(v)#返回参数字符串首位的字节码值
37、concat_ws(string sep,array/string…array)#通过参数1指定的分隔符号，对参数2“可以是数组或连续的多个字符串”进行拼接，然后返回拼接好的字符串。
对数组进行处理

聚合后的拼接

38、sentences(string sentence)#拆分长字符串（句子）为字符串数组，返回一个嵌套数组array<array>，根据感叹号！分割为多个数组，每个数组根据空格或者逗号分割为多个元素。
39、ngrams(array<array> arr,int n,int k)#select ngrams(sentences(‘hello boy,how are you,hello word,hello you’),1,2);#第一个参数是多个字符串组成数组的集合（嵌套数组），第二个参数是几个连续的单词，第三个参数是取前几个，按N个单词出现频次，倒序取前K个，返回一个结构数组（array<struct<string,double>>，数组元素为{“ngram”:[“xxx”],“estfrequency”:n}）
40、context_ngrams(array<array> arr,array,int k)#与 sentences()函数一起使用,分词后,统计分词结果中与数组中指定的单词一起出现(包括顺序)频次最高的 TOP-K 结果，返回值是array<struct<string,double>>。
41、encode(string source,string charset)/decode(string source,string charset)
#加密select encode(‘中国’,‘UTF-16BE’);
#还原select decode(encode(‘中国’,‘UTF-16BE’),‘UTF-16BE’);

42、format_number(decimal number,int d)#将数值 x 的小数位格式化成 d
位,四舍五入，返回值是个字符串

43、get_json_object()#字面意思，拿到json对象实例；解析 json 的字符串json_string,返回 path 指定的内容。如果输入的 json字符串无效,那么返回 NULL。
#select get_json_object(’{“name”:“henry”}’,’KaTeX parse error: Expected 'EOF', got '#' at position 21: …'); #̲select get_json….info.city’);

44、in_file(string line,string path)#path指向的文件中是否包含line内容，返回布尔值；判断这个东西在不在这个文件(文件路径)里

45、parse_url(string urlString,string part[,string key]) parse_url(url,‘QUERY’,‘key’)#把URL分解为QUERY引用(友链),REF,HOST主机,PATH路径,PROTOCOL协议(http单向加密，https双向加密),AUTHORITY,FILE,and
USERINFO#案例select
parse_url(‘https://www.baidu.com/s?ie=utf-8&f=8&rsv_bp=1&ch=13&tn=02049043_8_pg&wd=binary&oq=binary&rsv_pq=d31eade500131b45&rsv_t=3e18npmwKa13DGSc12m7wnCFfrrn6LvnrKWpbkN6e6MkObt90gJMtDq80Xlh0cbNPXtOMw&rqlang=cn&rsv_dl=tb&rsv_enter=0&rsv_btype=t’,‘HOST’);

46、printf(string format,T…t)#%s %d
%.nf#格式化字符串(类似拼接)，某种情况下可以取代concat#select
printf(’%s,%d,%.2f’,‘henry’,18,234423.34535);

47、like# % #模糊匹配#select * from shop where contact.mobile like ‘18%’;

48、rlike#正则的模糊匹配# [] {} ? + * \d \w … #select * from shop where contact.mobile rlike ‘18\d{9}’;

49、regexp_replace(string src,string newStr,string oldStr)#select regexp_replace(‘you me young’,‘you’,‘YOU’);#select regexp_replace(‘y1 me y_u youngad’,‘y\w{2,3}’,‘YOU’);找到以y开头，后面有至少2个字符串时，替换y和后面的3个字符串为参数三的值

50、regexp_extract(string src,string regex,int index)#以括号为单位，分组要有界限，参数3是要提取的内容#select regexp_extract(‘namehenryokdalingduck’,‘name(.?)(ok)(.?)duck’,3);

2019-06-16 14:24:34 INFO com.noriental.praxissvr.answer.util.PraxisSsdbUtil:45 [SimpleAsyncTaskExecutor-1] [020765925160] req: set se1_3443441_8195023659591_7637r 0.5

52、split(string src,string regex)#正则分割，返回一个数组#select split(regexp_replace(’[“henry”,“pola”,“ariel”]’,’

$$|$$ |"’,’’),’,’);#select split(‘henry.lili@foxmail.com’,’\.|@’);

53、str_to_map(string src,string regex)#自动变为map键值，返回一组键值对map<string,string>#select str_to_map(‘name:henry,age:22,gender:male’);#select str_to_map(‘name#henry|age#22|gender#male’,’\|’,’#’);select str_to_map(‘name#henry|age#22+gender#male’,’\||\+’,’#’);#正则，|要转义\#

54、translate(string src,string chars,string dchars)#参数2的字符匹配多少替换多少#替换#select translate(‘abcabcabcabaac’,‘ab’,’’);a和b分别变成了，按照字母来的#select translate(‘abcabcabcabaac’,‘ab’,’*#’);

55、initcap(string str)#单词的首字母大写#select initcap(‘henry haha’);

56、substr(string src,int bigint [,int len])#select substr(‘henry’,2);#select substr(‘henry’,2,1);截取字符串，第一个参数是起始位置，最后一个参数是长度

57、locate(string sub,string src,int startPos)#startPos从1开始#select locate(‘en’,‘henry’,2);

58、instr(string src,string sub)#返回参数2的位置，常用于判断某个词是否出现过#select instr(‘henry’,‘en’);

59、md5(string src)#单向加密，不需要还原的

60、base64(string src)#双向加密#select base64(cast(‘henry’ as binary));#select unbase64(‘aGVucnk=’);

61、hex()/unhex()#字符串加密为数字，unhex数字可以还原

62、sha2(string/binary,int len)#根据哈希码做加密，是单向的，第二个参数只能是0/1，位的长度

63、soundex(string str)#把字符串转换为描述其语音表示的字母数字模式的算法#首字母+3个数字，解决字符串的相似性的。在写错别字时，可以通过soundex模糊找出错别字

八、聚合函数

64、int count(*)#all rows，统计表里的所有行，有一行算一行 int count(expr)#rows where
expr is not NULL，加表达式，空列不统计 bigint count(distinct
expr[,expr,…])#rows where all the exprs is unique and not
NULL所有的表达式都是唯一的，重复值不计算，并且不为空。不同写法统计的数字不一样，根据业务来 double sum(distinct
expr)/avg(distinct expr)/min(col)/max(col)#可以不计算重复值，一般不这么做
double var_pop(col)#方差，统计数据的离散程度 double var_sample(col)#样本方差，变异程度
double stddev_pop(col)#标准方差 double studev_sample(col)#样本标准偏差，表观误差
double covar_pop(col1,col2)#协方差
double covar_sample(col1,col2)#样本协方差
double corr(col1,col2)#两列数值的相关系数
double percentile(bigint col,int p)#返回col的p(0~1)%分位数

65、collect_list(col)#列转行，提取很多行的数据变成一个数组

66、collect_set(col)#列转行，并去重

九、表生成函数

67、explode(array/Map<K,V>)#炸开，数组字段拆分为列，和concat是相反的过程#select explode(array(‘aa’,‘bb’,‘cc’));

	![在这里插入图片描述](https://img-blog.csdnimg.cn/20201112195515616.png#pic_center)
1

#select name,city from employee_id lateral view explode(cities) ct as city;用侧视图,数据挂在后面，再反向匹配到表里面
#select name,pos,score from employee_id lateral view explode(scores) st as pos,score;同一个人有两个科目，两个位置，便于查询#

68、posexplode(array)#炸开，并且给出每个元素在数组中的位置索引

69、stack(rowNum,v1,…,vm)#m/n为整数，把后面的值拆为第一个参数的行数量，m需要能平均分配到n;可以用来抽样和聚合

tuple json_tuple(string json,string…key)#tuple格式(key,value)#
#json_tuple案例
{“name”:{“first”:“chen”,“last”:“henry”},“hobbies”:[“swimming”,“travelling”,“reading”],“age”:“18”}
{“name”:{“first”:“chen”,“last”:“arial”},“hobbies”:[“swimming”,“climbing”],“age”:“23”}
{“name”:{“first”:“li”,“last”:“pola”},“hobbies”:[“fishing”,“travelling”,“gambling”],“age”:“22”}
{“name”:{“first”:“ma”,“last”:“zhiguo”},“hobbies”:[“swimming”,“travelling”,“skating”],“age”:“21”}
{“name”:{“first”:“tang”,“last”:“rongfeng”},“hobbies”:[“swimming”,“skiing”,“reading”],“age”:“19”}

vi jsontuple.log

create external table jsontuple(
line string
)
stored as textfile;

load data local inpath ‘/root/hadooptmp/jsontuple.log’ into table jsontuple;

select printf(’%s %s’,fn,ln) name,age,hobby from jsontuple
lateral view json_tuple(line,‘name’,‘hobbies’,‘age’) jt as name,hobbies,age
lateral view json_tuple(name,‘first’,‘last’) jt1 as fn,ln
lateral view explode(split(regexp_replace(hobbies,’

$$|$$ |"’,’’),’,’)) hs as hobby
;

70、parse_url_tuple(string url,string…parts)#返回键值格式的tuple，和parse_url(string urlString,string part[,string key])这个函数功能类似，展现形式略有不同,

71、inline(array(struct_NAME))#拆数组里的结构体#select inline(array(contact)) from shop;