Hive中的基础函数(一)

一、hive中的内置函数根据应用归类整体可以分为8大种类型。

1、 String Functions 字符串函数
主要针对字符串数据类型进行操作，比如下面这些：

字符串长度函数：length

•字符串反转函数：reverse

•字符串连接函数：concat

•带分隔符字符串连接函数：concat_ws

•字符串截取函数：substr,substring

•字符串转大写函数：upper,ucase

•字符串转小写函数：lower,lcase

•去空格函数：trim

•左边去空格函数：ltrim

•右边去空格函数：rtrim

•正则表达式替换函数：regexp_replace

•正则表达式解析函数：regexp_extract

•URL解析函数：parse_url

•json解析函数：get_json_object

•空格字符串函数：space

•重复字符串函数：repeat

•首字符ascii函数：ascii

•左补足函数：lpad

•右补足函数：rpad

•分割字符串函数: split

•集合查找函数: find_in_set

【案例演示】

select length(“maxwell”); 获取字符串maxwell的长度

select reverse(“maxwell”); 字符串反转函数reverse

–字符串连接函数：concat(str1, str2, … strN)
select concat(“max”,“well”); 输出结果为maxwell

–带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws(‘.’, ‘www’, array(‘baidu’, ‘com’)); 输出结果为www.baidu.com

–字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr(“maxwell”,-2); --pos是从1开始的索引，如果为负数则倒着数
select substr(“maxwell”,2,2);

2、日期函数

主要针对时间、日期数据类型进行操作
•获取当前日期: current_date
•获取当前时间戳: current_timestamp
•UNIX时间戳转日期函数: from_unixtime
•获取当前UNIX时间戳函数: unix_timestamp
•日期转UNIX时间戳函数: unix_timestamp
•指定格式日期转UNIX时间戳函数: unix_timestamp
•抽取日期函数: to_date
•日期转年函数: year
•日期转月函数: month
•日期转天函数: day
•日期转小时函数: hour
•日期转分钟函数: minute
•日期转秒函数: second
•日期转周函数: weekofyear
•日期比较函数: datediff
•日期增加函数: date_add
•日期减少函数: date_sub

–获取当前日期: current_date
select current_date();

–获取当前时间戳: current_timestamp
–同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();

–获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();

3、Mathematical Functions数学函数

•取整函数: round
•指定精度取整函数: round
•向下取整函数: floor
•向上取整函数: ceil
•取随机数函数: rand
•二进制函数: bin
•进制转换函数: conv
•绝对值函数: abs

–取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);

–指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);

–向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);

–向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);

–取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();

–指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(2);

4、Collection Functions集合函数
•集合元素size函数: size(Map<K.V>) size(Array)
•取map集合keys函数: map_keys(Map<K.V>)
•取map集合values函数: map_values(Map<K.V>)
•判断数组是否包含指定元素: array_contains(Array, value)
•数组排序函数:sort_array(Array)

–集合元素size函数: size(Map<K.V>) size(Array) 查看集合中size
select size(array(11,22,33));
select size(map(“id”,10010,“name”,“zhangsan”,“age”,18));

–取map集合keys函数: map_keys(Map<K.V>)
select map_keys(map(“id”,10086,“name”,“zhangsan”,“age”,18));

–取map集合values函数: map_values(Map<K.V>)
select map_values(map(“id”,10086,“name”,“zhangsan”,“age”,18));

–判断数组是否包含指定元素: array_contains(Array, value)
select array_contains(array(11,22,33),11);
select array_contains(array(11,22,33),66);

5、 Conditional Functions 条件函数
主要用于条件判断、逻辑判断转换这样的场合
•if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
•空判断函数: isnull( a )
•非空判断函数: isnotnull ( a )
•空值转换函数: nvl(T value, T default_value)

•非空查找函数: COALESCE(T v1, T v2, …)
•条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END*

其中if条件判断函数和case函数和java中的if else道理一样，开发中if函数更方便

–if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex =‘男’,‘M’,‘W’) from student limit 3;
总结：if中的参数和三元运算符的逻辑一样 ，条件，值1，值2。条件true则返回值1，条件为false，则返回值2

–空值转换函数: nvl(T value, T default_value)
select nvl(“helloworld”,“maxwell”); nvl（值1，值2） 值1为null则返回值值2 ，值1不为null则返回值1
select nvl(null,“maxwell”);

6、Type Conversion Functions 类型转换函数
•任意数据类型之间转换:cast
–任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);

7、 Data Masking Functions 数据脱敏函数
–mask
–将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask(“abc123DEF”);
select mask(“abc123DEF”,‘-’,‘.’,‘^’); --自定义替换的字母

–mask_first_n(string str[, int n]
–对前n个进行脱敏替换
select mask_first_n(“abc123DEF”,4);

–mask_last_n(string str[, int n])
select mask_last_n(“abc123DEF”,4);

–mask_show_first_n(string str[, int n])
–除了前n个字符，其余进行掩码处理
select mask_show_first_n(“abc123DEF”,4);

–mask_show_last_n(string str[, int n])
select mask_show_last_n(“abc123DEF”,4);

–mask_hash(string|char|varchar str)
–返回字符串的hash编码。
select mask_hash(“abc123DEF”);

总结：在处理用户的敏感信息，如身份证号码，手机号码，地址等敏感信息时，可以采用脱敏函数

8、Misc. Functions 其他杂项函数
•hive调用java方法: java_method(class, method[, arg1[, arg2…]])
•反射函数: reflect(class, method[, arg1[, arg2…]])
•取哈希值函数:hash
•current_user()、logged_in_user()、current_database()、version()
•SHA-1加密: sha1(string/binary)
•SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
•crc32加密:
•MD5加密: md5(string/binary)

–hive调用java方法: java_method(class, method[, arg1[, arg2…]])
select java_method(“java.lang.Math”,“max”,11,22);

–反射函数: reflect(class, method[, arg1[, arg2…]])
select reflect(“java.lang.Math”,“max”,11,22);

–取哈希值函数:hash
select hash(“allen”);

–current_user()、logged_in_user()、current_database()、version()

–SHA-1加密: sha1(string/binary)
select sha1(“allen”);

–SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2(“allen”,224);
select sha2(“allen”,512);

–crc32加密:
select crc32(“allen”);

–MD5加密: md5(string/binary)
select md5(“allen”);

二、用户自定义函数

1、UDF普通函数
特点是一进一出，也就是输入一行输出一行。比如round这样的取整函数，接收一行数据，输出的还是一行数据。

2、UDAF函数
A所代表的单词就是Aggregation聚合的意思。其最大的特点是多进一出，也就是输入多行输出一行。比如count、sum这样的函数。
•count:统计检索到的总行数。
•sum:求和
•avg:求平均
•min:最小值
•max:最大值
•数据收集函数（去重）: collect_set(col)
•数据收集函数（不去重）: collect_list(col)

select collect_set(sex) from student; sex为student表中的字段名
select collect_list(sex) from student;

**3、**UDTF函数通常把它叫做表生成函数，T所代表的单词是Table-Generating表生成的意思。最大的特点是一进多出，也就是输入一行输出多行。比如explode函数
explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。

explode(array)将array列表里的每个元素生成一行；
explode(map)将map里的每一对元素作为一行，其中key为一列，value为一列；
一般情况下，explode函数可以直接使用即可，也可以根据需要结合lateral view侧视图使用。

select explode(array(11,22,33)) as item;

select explode(map(“id”,10086,“name”,“zhangsan”,“age”,18));