精品建站,泉州网页建站模板,手机好看的网站,人工智能培训心得写在前面#xff1a;HQL同SQL有很多的类似语法#xff0c;同学熟悉SQL后一般学习起来非常轻松#xff0c;写一篇文章列举常用函数#xff0c;方便查找和学习。
1. 执行模式
1.1 Batch Mode 批处理模式
当使用-e或-f选项运行$ HIVE_HOME / bin / hive时#xff0c;它将以…写在前面HQL同SQL有很多的类似语法同学熟悉SQL后一般学习起来非常轻松写一篇文章列举常用函数方便查找和学习。
1. 执行模式
1.1 Batch Mode 批处理模式
当使用-e或-f选项运行$ HIVE_HOME / bin / hive时它将以批处理模式执行SQL命令。所谓的批处理可以理解为一次性执行执行完毕退出
#-e
$HIVE_HOME/bin/hive -e show databases#-f
#执行 从客户端所在机器的本地磁盘加载文件
$HIVE_HOME/bin/hive -f /root/createtable.sql1.2 Interactive Shell 交互式模式
交互式模式可以理解为客户端和hive服务一直保持连接
#--service
$HIVE_HOME/bin/hive --service metastore
$HIVE_HOME/bin/hive --service hiveserver2$HIVE_HOME/bin/beeline被称之为第二代客户端或者新客户端是一个JDBC客户端是官方强烈推荐使用的Hive命令行工具和第一代客户端相比性能加强安全性提高。Beeline在嵌入式模式和远程模式下均可工作。
# /export/server/hive/bin/beeline beeline ! connect jdbc:hive2://node1:10000
2. 函数概述 如同RDBMS中标准SQL语法一样Hive SQL也内建了不少函数满足于用户在不同场合下的数据分析需求提高开发SQL数据分析的效率。 可以使用show functions查看当下版本支持的函数并且可以通过describe function extended funcname来查看函数的使用方式和方法。
show functionsdescribe function extended funcname
3. 函数分类
Hive的函数很多除了自己内置所支持的函数之外还支持用户自己定义开发函数。
针对内置的函数可以根据函数的应用类型进行归纳分类比如数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等
针对用户自定义函数可以根据函数的输入输出行数进行分类比如UDF、UDAF、UDTF。 3.1 内置函数
官方文档地址LanguageManual UDF - Apache Hive - Apache Software Foundation
内置函数根据应用归类整体可以分为以下8大种类型字符串函数、日期函数、数学函数、集合函数、条件函数、类型转换函数、数据脱敏函数、其他函数
3.1.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示例如下
------------String Functions 字符串函数------------
describe function extended find_in_set;--字符串长度函数length(str | binary)
select length(angelababy);--字符串反转函数reverse
select reverse(angelababy);--字符串连接函数concat(str1, str2, ... strN)
select concat(angela,baby);--带分隔符字符串连接函数concat_ws(separator, [string | array(string)])
select concat_ws(., www, array(itcast, cn));--字符串截取函数substr(str, pos[, len]) 或者 substring(str, pos[, len])
select substr(angelababy,-2); --pos是从1开始的索引如果为负数则倒着数
select substr(angelababy,2,2);--字符串转大写函数upper,ucase
select upper(angelababy);
select ucase(angelababy);--字符串转小写函数lower,lcase
select lower(ANGELABABY);
select lcase(ANGELABABY);--去空格函数trim 去除左右两边的空格
select trim( angelababy );--左边去空格函数ltrim
select ltrim( angelababy );--右边去空格函数rtrim
select rtrim( 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?query1, HOST);--json解析函数get_json_object
--空格字符串函数space(n) 返回指定个数空格
select space(4);--重复字符串函数repeat(str, n) 重复str字符串n次
select repeat(angela,2);--首字符ascii函数ascii
select ascii(angela); --a对应ASCII 97--左补足函数lpad
select lpad(hi, 5, ??); --???hi
select lpad(hi, 1, ??); --h--右补足函数rpad
select rpad(hi, 5, ??);--分割字符串函数: split(str, regex)
select split(apache hive, \\s);--集合查找函数: find_in_set(str,str_array)
select find_in_set(a,abc,b,ab,c,def);
3.1.2 Date Functions日期函数
主要针对时间、日期数据类型进行操作
•获取当前日期: 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);3.1.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);--二进制函数: 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);3.1.4 Collection Functions集合函数
主要针对集合这样的复杂数据类型进行操作
•集合元素size函数: size(MapK.V) size(ArrayT)
•取map集合keys函数: map_keys(MapK.V)
•取map集合values函数: map_values(MapK.V)
•判断数组是否包含指定元素: array_contains(ArrayT, value)
•数组排序函数:sort_array(ArrayT)示例如下
--集合元素size函数: size(MapK.V) size(ArrayT)
select size(array(11,22,33));
select size(map(id,10086,name,zhangsan,age,18));--取map集合keys函数: map_keys(MapK.V)
select map_keys(map(id,10086,name,zhangsan,age,18));--取map集合values函数: map_values(MapK.V)
select map_values(map(id,10086,name,zhangsan,age,18));--判断数组是否包含指定元素: array_contains(ArrayT, value)
select array_contains(array(11,22,33),11);
select array_contains(array(11,22,33),66);--数组排序函数:sort_array(ArrayT)
select sort_array(array(12,2,32));3.1.5 条件函数
主要用于条件判断、逻辑判断转换这样的场合
•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(12,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);
3.1.6 Type Conversion Functions类型转换函数
主要用于显式的数据类型转换
•任意数据类型之间转换:cast
--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);3.1.7 数据脱敏函数
主要完成对数据脱敏转换功能屏蔽原始数据
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);3.1.8 其他函数
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);
