4.8 Hive基本函数
官方文档
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
函数简介
如同RDBMS中标准SQL语法一样，Hive SQL也内建了不少函数，满足于用户在不同场合下的数据分析需求，提高开发SQL数据分析的效率。
可以使用show functions查看当下版本支持的函数，并且可以通过describe function extended funcname来查看函数的使用方式和方法。
函数分类
Hive的函数很多，除了自己内置所支持的函数之外，还支持用户自己定义开发函数。
针对内置的函数，可以根据函数的应用类型进行归纳分类，比如：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等；
针对用户自定义函数，可以根据函数的输入输出行数进行分类，比如：UDF、UDAF、UDTF。
常用内置函数
字符串函数
- 字符串长度函数：length
- 字符串反转函数：reverse
- 字符串连接函数：concat
- 带分隔符字符串连接函数：concat_ws
- 字符串截取函数：substr,substring
- 字符串转大写函数：upper,ucase
- 字符串转小写函数：lower,lcase
- 去空格函数：trim
- 正则表达式替换函数：regexp_replace
- 正则表达式解析函数：regexp_extract
- URL解析函数：parse_url
- json解析函数：get_json_object
- 空格字符串函数：space
示例
select length("angelababy"); select reverse("angelababy"); select concat("angela","baby"); select concat_ws('.', 'www', array('hadoop', 'cn')); -- 字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len] select substr("angelababy",-2); -- pos是从1开始的索引，如果为负数则倒着数 select substr("angelababy",2,2); select upper("angelababy"); select ucase("angelababy"); select lower("ANGELABABY"); select lcase("ANGELABABY"); select trim(" angelababy "); -- 正则表达式替换函数：regexp_replace(str, regexp, rep) select regexp_replace('100-200', '(\\d+)', 'num'); -- 正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容 select regexp_extract('100-200', '(\\d+)-(\\d+)', 2); -- URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数 select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST'); -- 空格字符串函数：space(n) 返回指定个数空格 select space(4);
日期函数
- 获取当前日期: 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(); --UNIX时间戳转日期函数: from_unixtime select from_unixtime(1618238391); select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); --日期转UNIX时间戳函数: unix_timestamp select unix_timestamp("2011-12-07 13:01:03"); --指定格式日期转UNIX时间戳函数: unix_timestamp select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss'); --抽取日期函数: to_date select to_date('2009-07-30 04:17:52'); --日期转年函数: year select year('2009-07-30 04:17:52'); --日期转月函数: month select month('2009-07-30 04:17:52'); --日期转天函数: day select day('2009-07-30 04:17:52'); --日期转小时函数: hour select hour('2009-07-30 04:17:52'); --日期转分钟函数: minute select minute('2009-07-30 04:17:52'); --日期转秒函数: second select second('2009-07-30 04:17:52'); --日期转周函数: weekofyear 返回指定日期所示年份第几周 select weekofyear('2009-07-30 04:17:52'); --日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' select datediff('2012-12-08','2012-05-09'); --日期增加函数: date_add select date_add('2012-02-28',10); --日期减少函数: date_sub select date_sub('2012-01-1',10);
数学函数
- 取整函数: 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); --二进制函数: bin(BIGINT a) select bin(18); --进制转换函数: conv(BIGINT num, int from_base, int to_base) select conv(17,10,16); --绝对值函数: abs select abs(-3.9);
集合函数
- 集合元素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<T>) select size(`array`(11,22,33)); select size(`map`("id",10086,"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<T>, value) select array_contains(`array`(11,22,33),11); select array_contains(`array`(11,22,33),66); --数组排序函数:sort_array(Array<T>) select sort_array(`array`(12,2,32));
条件函数
- 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
- nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个
- 断言assert_true: 如果'condition'不为真，则引发异常，否则返回null
示例
--使用之前课程创建好的student表数据 select * from student limit 3; --if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) select if(1=2,100,200); select if(sex ='男','M','W') from student limit 3; --空判断函数: isnull( a ) select isnull("allen"); select isnull(null); --非空判断函数: isnotnull ( a ) select isnotnull("allen"); select isnotnull(null); --空值转换函数: nvl(T value, T default_value) select nvl("allen","itcast"); select nvl(null,"itcast"); --非空查找函数: COALESCE(T v1, T v2, ...) --返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL select COALESCE(null,11,22,33); select COALESCE(null,null,null,33); select COALESCE(null,null,null); --条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; select case sex when '男' then 'man' else 'women' end from student limit 3; --nullif( a, b ): -- 果a = b，则返回NULL；否则返回NULL。否则返回一个 select nullif(11,11); select nullif(11,12); --assert_true(condition) --如果'condition'不为真，则引发异常，否则返回null SELECT assert_true(11 >= 0); SELECT assert_true(-1 >= 0);
类型转换函数
--任意数据类型之间转换:cast select cast(12.14 as bigint); select cast(12.14 as string);
数据脱敏函数
mask
- mask_first_n(string str[, int n]
- mask_last_n(string str[, int n])
- mask_show_first_n(string str[, int n])
- mask_show_last_n(string str[, int n])
- mask_hash(string|char|varchar str)
示例
--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");
其他函数
- 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");
