hive常用函数

条件函数

if

• if(boolean testCondition, T valueTrue, T valueFalseOrNull)

isnull

nvl

• nvl( string1, replace_with)

case when

• case A when B then C else D end
• case when A then B else C end

coalesce

• 返回第一个非空的值

isfalse

istrue

nullif

• nullif(expression_1,expression_2);- 如果第一个参数等于第二个参数返回null，否则返回第一个参数

日期函数

todate()

• 将时间格式转化为日期格式- to_date(string timestamp)

unix_timestamp(string date, string pattern)

• 转换 pattern 格式的日期到 UNIX 时间戳。如果转换失败，则返回 NULL。- select unix_timestamp('20221111 02:40:00', 'yyyyMMdd HH:mm:ss');

from_unixtime(时间戳）

date_format(timestamp,pattern)

month/year/day/hour/minute/second

• 日期转年月日时分秒

weekofyear

• 日期转周函数

dayofmonth/dayofweek

• 日期转日函数

datediff(string enddate, string startdate)

• 结束日期减去开始日期的天数

date_add(string startdate, int days)

• 返回开始日期后增加n天后的日期

date_sub

• 与上一个函数相反

数值函数

round

• round(num)
• round(num,保留几位小数 )

ceil

• 向上取整

floor

• 向下取整

rand(int seed)

• 返回一个 0 到 1 范围内的随机数。如果指定种子 seed，则会得到一个稳定的随机数序列。

exp(double a)

• 返回e的a次方

log10(double a)

• 以10为底的对数函数

log(double base, double a)

log2(double a)

pow(double a, double p)

• a的p次幂

sqrt(double a)

• 返回a的平方根

bin(bigint a)

• 返回二进制代码

hex(bigint a)

• 返回十六进制代码

conv(bigint num, int from_base, int to_base)

• 进制转换函数

abs(int a )

• 绝对值函数

sin(double a)

asin(double a)

• 反余弦函数

cos

acos

positive

• 返回本身

negative

• 返回相反数

cast(... as type)

• 类型转换

字符串函数

ascii(string)

• 返回字符串中第一个字符的ASCII码

base64(binary bin)

• 返回二进制bin的base编码字符串

concat(string A, string B...)

• 连接若干个字符串

concat_ws(string separator, string A, string B...)

concat_ws(string separator, array<string>,...)

format_number(number x, int d)

• 将数值 x 的小数位格式化成 d 位,四舍五入

substr(string,position[,lenth])

• 只有position参数，截取从position开始至字符串末尾
• 有length参数，从position开始截取length长度的字符串

instr(string,substr)

• 找到sunstr在string出现的第一个位置- hive的位置是从1开始计

length(string)

• 返回字符串的长度

locate(string substr, string str[, int pos])

• 返回从位置position开始substr在string第一次出现的位置。- 查询不到返回0

printf()格式化字符串

• printf(String format, Obj... args)- select printf('name : %s, id : %d',name,id) from temp_emp;

lower()

• 转为小写字母

upper()

• 转为大写字母

trim()

• 去除字符串两边的空格

regexp_replace(string A, string regex, string C)

• 将字符串A中符合regex格式的字符替换为C- select regexp_replace('aa11vv4+4nn22','\d{2}','&'); 返回 aa&vv4+4nn&

split(string str, string pat)根据正则表达式进行分割

• select split('abc11def','\d+')[0];- 输出abc

parse_url(url, partToExtract[, key])

• partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]
• with tmp as ( select parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'QUERY','keyword')as keyword, parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'QUERY','enc')as enc )select reflect('java.net.URLDecoder','decode',keyword,enc) as keyword from tmp;- 华为手机mate60

集合函数

str_to_map(text[, delimiter1, delimiter2])

• select str_to_map('a/d,b/h,c/f,e/t',',','/');- 第一个分隔符为map元素之间的分隔符，第二个分隔符是键值对之间的分隔符

size()

• 返回集合的大小- select size(map('Chinese',100,'Math',20));- select size(array('Chinese','Math','English'));

struct()

• 逗号分割

named_struct()

• 逗号分割，键，值，键，值....

array_contains()

• 判断数组里有无指定元素- select array_contains(array('11','22','33'),'11')

sort_array()

• 对数组进行排序- select sort_array(array(22,33,11));- select sort_array(array( named_struct('name','zebra','age',22), named_struct('name','ant','age',22), named_struct('name','tom','age',26), named_struct('name','cat','age',26) ));

sort_array_by()

• 根据结构体数组关键字进行排序- select sort_array_by(array( named_struct('name','henry','age',22), named_struct('name','jerry','age',25), named_struct('name','tom','age',27) ),'age','desc');//根据年龄倒序排序

map_keys()

• select map_keys(map('Chinese',100,'Math',99));- 返回["Chinese","Math"]

map_values()

• select map_values(map('Chinese',100,'Math',99));

- 返回[100,99]

collect_set()

• 对某列进行去重合并- select customer_lname,collect_set(customer_fname) as fanme from cb_customers group by customer_lname;

collect_list

• 对某列进行不去重合并- select customer_lname,collect_list(customer_fname) as fanme from cb_customers group by customer_lname;

聚合函数

count()

sum()

min()

max()

avg()