音乐网站建设教程,wordpress增加登录页面,网络销售有前途吗,嘉兴企业网站建设推广一、前言
在任何一种编程语言中#xff0c;函数可以说是必不可少的#xff0c;像mysql、oracle中#xff0c;提供了很多内置函数#xff0c;或者通过自定义函数的方式进行定制化使用#xff0c;而hive作为一门数据分析软件#xff0c;随着版本的不断更新迭代#xff0c…一、前言
在任何一种编程语言中函数可以说是必不可少的像mysql、oracle中提供了很多内置函数或者通过自定义函数的方式进行定制化使用而hive作为一门数据分析软件随着版本的不断更新迭代也陆续出现了很多函数以满足日常数据查询与分析的各种场景。 二、hive 函数概述 Hive内建了不少函数用于满足用户不同使用需求提高SQL编写效率查看hive的函数可以通过下面的方式 1、show functions —— 查看当下可用的所有函数 2、describe function extended funcname —— 查看函数的具体使用方式 show functions 查看当前版本下hive函数 这以上随机截取了一部分更详细的可以参考官方手册hive官方文档里面有hive函数的详细资料 三、hive 函数分类 Hive函数分为两大类
内置函数Built-in Functions用户定义函数UDFUser-Defined Functions3.1 内置函数 包括数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等 3.2 用户定义函数 根据输入输出的行数可分为3类UDF、UDAF、UDTF 用户定义函数UDF分类标准
用户自定义函数根据函数输入输出的行数进行划分具体来说如下 UDFUser-Defined-Function 普通函数一进一出 UDAFUser-Defined Aggregation Function 聚合函数多进一出 UDTFUser-Defined Table-Generating Functions 表生成函数一进多出 3.3 UDF分类标准扩大化 UDF分类标准本来针对的是用户自己编写开发实现的函数UDF分类标准可以扩大到Hive的所有函数中包括内置函数和用户自定义函数 因为不管是什么类型的函数一定满足于输入输出的要求那么从输入几行和输出几行上来划分没有任何问题。 千万不要被UDUser-Defined这两个字母所迷惑照成视野的狭隘。 四、内置函数 内置函数build-in顾名思义指的是Hive开发实现好直接可以使用的函数,也叫做内建函数官方文档地址hive官方文档内置函数根据应用归类整体可以分为8大种类型我们将对其中重要的使用频率高的函数使用进行详细的说明 8大类型的内置函数
字符串函数日期函数数学函数数据脱敏函数条件函数类型转换函数集合函数其他杂项函数4.1 字符串函数 字符串函数可以说在日常工作中使用的很频繁的了下面列举一些常用的字符串函数
字符串长度函数length字符串反转函数reverse字符串连接函数concat带分隔符字符串连接函数concat_ws字符串截取函数substr,substring字符串转大写函数upper,ucase字符串转小写函数lower,lcase去空格函数trim左边去空格函数ltrim右边去空格函数rtrim正则表达式替换函数regexp_replace正则表达式解析函数regexp_extractURL解析函数parse_urljson解析函数get_json_object空格字符串函数space重复字符串函数repeat首字符ascii函数ascii左补足函数lpad右补足函数rpad分割字符串函数: split集合查找函数: find_in_setconcat 函数
用于拼接字符串或者将不同类型的字符串拼接在一起 select concat(angela,baby); 带分隔符字符串连接函数concat_ws(separator, [string | array(string)]) select concat_ws(., www, array(taobao, com)); 字符串截取函数
substr(str, pos[, len]) 或者 substring(str, pos[, len]) select substr(angelababy,-2); --pos是从1开始的索引如果为负数则倒着数 select substr(angelababy,2,2); 正则表达式替换函数
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.taobao.com/path/p1.action?query1, HOST); 分割字符串函数
split(str, regex) select split(apache hive, \\s); json解析函数
get_json_object(json_txt, path)其中json_txt表示json对象 select get_json_object([{website:www.taobao.com,name:allenwoon}, {website:cloud.taobao.com,name:carbondata 中文文档}], $.[1].website); 字符串长度函数
length(str | binary) 字符串反转函数 select reverse(angelababy); 其他字符串函数总结
--字符串连接函数concat(str1, str2, ... strN)
--字符串转大写函数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 );
--空格字符串函数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, ??);
--集合查找函数: find_in_set(str,str_array)
select find_in_set(a,abc,b,ab,c,def); 4.2 日期函数
日期函数也是一种使用非常频繁的函数有必要深入的掌握以下列举常用的日期函数
获取当前日期: current_date获取当前时间戳: current_timestampUNIX时间戳转日期函数: 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获取当前日期 select current_date(); 获取当前时间戳
current_timestamp同一查询中对current_timestamp的所有调用均返回相同的值 select current_timestamp(); 获取当前UNIX时间戳函数 select unix_timestamp(); 日期比较函数
datediff 日期格式要求为yyyy-MM-dd HH:mm:ss or yyyy-MM-dd select datediff(2012-12-08,2012-05-09); 其他日期函数
--日期转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);--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, yyyy-MM-dd HH:mm:ss);--日期增加函数: date_add
select date_add(2012-02-28,10);--日期减少函数: date_sub
select date_sub(2012-01-1,10);--抽取日期函数: 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);4.3 数学函数 当需要对数据进行一些特殊的运算比如取整随机数等就需要用到hive中的熟悉函数以下列举常用的一些数学函数
取整函数: round指定精度取整函数: round向下取整函数: floor向上取整函数: ceil取随机数函数: rand二进制函数: bin进制转换函数: conv绝对值函数: abs取整函数 round 返回double类型的整数值部分 遵循四舍五入 指定精度取整函数
round(double a, int d) 返回指定精度d的double类型 select round(3.1415926,4); 取随机数函数
rand 每次执行都不一样 返回一个0到1范围内的随机数 select rand(); 其他数学函数
--向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);
--向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);--二进制函数: 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); 4.4 集合函数 集合元素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)获取集合中map的key列表 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)); 4.5 条件函数 if条件判断 if(boolean testCondition, T valueTrue, T valueFalseOrNull) 有没有觉得这个语法和 mybatis 中的动态sql有点类似呢 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,baidu); select nvl(null,baidu); 非空查找函数
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 male else female end from student limit 3; 其他条件函数
--nullif( a, b ):
-- 如果a b则返回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); select cast(hello as int); 4.6 数据脱敏函数 mask
将查询回的数据大写字母转换为X小写字母转换为x数字转换为n select mask(abc123DEF); select mask(abc123DEF,-,.,^); --自定义替换的字母 mask_first_n(string str[, int n])
对前n个进行脱敏替换 mask_last_n(string str[, int n])
对后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])
除了后n个字符其余进行掩码处理 select mask_show_last_n(abc123DEF,4); mask_hash(string|char|varchar str)
返回字符串的hash编码 select mask_hash(abc123DEF); 4.7 其他杂项函数 如果你要调用的java方法所在的jar包而不是hive自带的 可以使用add jar 的方式添加进来hive调用java方法格式为java_method(class, method[, arg1[, arg2..]]) 调用Java的max函数
select java_method(java.lang.Math,max,11,22); 五、自定义函数
用户自定义函数简称UDF源自于英文user-defined function在系统内置的函数无法满足实际的业务场景需求时可以考虑使用自定义函数 关于自定义函数的概念在本文开头有过介绍 UDF 普通函数 特点是一进一出也就是输入一行输出一行比如round这样的取整函数接收一行数据输出的还是一行数据 UDAF 聚合函数 UDAF 聚合函数A所代表的单词就是Aggregation聚合的意思多进一出也就是输入多行输出一行 如下图 UDTF 表生成函数
UDTF 表生成函数T所代表的单词是Table-Generating表生成的意思特点是一进多出也就是输入一行输出多行这类型的函数作用返回的结果类似于表同时UDTF函数也是我们接触比较少的函数比如explode函数 如下图所示 使用explode函数就可以得到一进多出的效果 六、explode 函数 6.1 explode 函数功能介绍 explode 函数属于UDTF类型函数explode接收map、array类型的数据作为输入然后把输入数据中的每个元素拆开变成一行数据一个元素一行explode执行效果正好满足于输入一行输出多行所有叫做UDTF函数 1、一般情况下explode函数可以直接单独使用即可 2、也可以根据业务需要结合lateral view侧视图一起使用 使用示例 explode(array) 将array里的每个元素生成一行 explode(map) 将map里的每一对元素作为一行其中key为一列value为一列 6.2 explode 函数操作演示 业务需求描述
有如下的数据 现在需要把这份数据最终转换成下面的表的数据 建表
create table the_nba_championship(team_name string,champion_year arraystring
) row format delimited
fields terminated by ,
collection items terminated by |;
执行上面的sql建表 加载数据文件到表中 load data local inpath /usr/local/soft/hivedata/The_NBA_Championship.txt into table the_nba_championship; 检查数据 使用explode函数对champion_year进行拆分
俗称炸开 select explode(champion_year) from the_nba_championship; 执行结果可以看到就把年份分成了一行行数据 使用lateral view侧输出流/视图 explode select a.team_name,b.year from the_nba_championship a lateral view explode(champion_year) b as year order by b.year desc; 执行结果 可能有同学想为什么不使用下面的sql呢 select team_name,explode(champion_year) from the_nba_championship; 其实这个是错误的执行一下就会报错这就要解释下UDTF的语法限制了 UDTF语法限制
explode函数属于UDTF表生成函数explode执行返回的结果可以理解为一张虚拟的表其数据来源于源表在select中只查询源表数据没有问题只查询explode生成的虚拟表数据也没问题但是不能在只查询源表的时候既想返回源表字段又想返回explode生成的虚拟表字段通俗点讲有两张表不能只查询一张表但是又想返回分别属于两张表的字段UDTF语法限制解决
从SQL层面上来说上述问题的解决方案是对两张表进行join关联查询Hive专门提供了语法lateral View侧视图专门用于搭配explode这样的UDTF函数以满足上述需要七、Hive Lateral View 侧视图 Lateral View 侧视图概述 Lateral View是一种特殊的语法主要搭配UDTF类型函数一起使用用于解决UDTF函数的一些查询限制的问题一般只要使用UDTF就会固定搭配lateral view使用 官方链接地址hive侧视图地址 Lateral View 原理
将UDTF的结果构建成一个类似于视图的表然后将原表中的每一行和UDTF函数输出的每一行进行连接生成一张新的虚拟表。这样就避免了UDTF的使用限制问题使用lateral view时也可以对UDTF产生的记录设置字段名称产生的字段可以用于group by、order by 、limit等语句中不需要再单独嵌套一层子查询
可以对照下图进行理解 lateral view侧视图基本语法
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……; 操作演示 获取球队的名称以及荣获冠军的年份
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year;
执行结果 上述结果根据年份倒序排序
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
order by b.year desc;
执行结果 统计每个球队获取总冠军的次数 并且根据倒序排序
select a.team_name ,count(*) as nums
from the_nba_championship a lateral view explode(champion_year) b as year
group by a.team_name
order by nums desc;
执行结果 八、聚合函数 在很多业务场景下需要对表的数据进行汇总聚合得到一个聚合的结果或者分组聚合的结果进行报表展示等比如我们熟悉的mysqlmongodbes等都提供了丰富的聚合函数对数据进行聚合统计分析。 8.1 hive聚合函数概述 hive 聚合函数的功能是对一组值执行计算并返回单一的值 1、聚合函数是典型的输入多行输出一行使用Hive的分类标准属于UDAF类型函数 2、通常搭配Group By语法一起使用分组后进行聚合操作 8.2 基础聚合
HQL提供了几种内置的UDAF聚合函数例如max...min...和avg...。这些我们把它称之为基础的聚合函数通常情况下聚合函数会与GROUP BY子句一起使用。如果未指定GROUP BY子句默认情况下它会汇总所有行数据8.3 数据准备 建一张数据表
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ,;
执行sql建表 加载数据到表中 8.4 操作演示 没有group by子句的聚合操作 --count(*)所有行进行统计包括NULL行 --count(1)所有行进行统计包括NULL行 --count(column)对column中非Null进行统计 执行下面的sql select count(*) as cnt1,count(1) as cnt2 from student; 执行结果 带有group by子句的聚合操作
这个同mysql的操作基本一致这里注意group by语法限制 select sex,count(*) as cnt from student group by sex; 执行结果 select时多个聚合函数一起使用 select count(*) as cnt1,avg(age) as cnt2 from student; 执行结果 聚合函数搭配其他条件函数一起使用
与case when条件转换函数、coalesce函数、if函数使用
selectsum(CASE WHEN sex 男THEN 1 ELSE 0 END)
from student;selectsum(if(sex 男,1,0))
from student;
执行结果 聚合参数不支持嵌套聚合函数 聚合操作时针对null的处理
CREATE TABLE tmp_1 (val1 int, val2 int);
INSERT INTO TABLE tmp_1 VALUES (1, 2),(null,2),(2,3);
select * from tmp_1;
依次执行上面的sql观察结果 在存在null数据的情况下进行下面的聚合看看效果如何 select sum(val1), sum(val1 val2) from tmp_1; 从执行结果来看第二行数据(NULL, 2) 在进行sum(val1 val2)的时候会被忽略 这个可以使用coalesce函数解决
selectsum(coalesce(val1,0)),sum(coalesce(val1,0) val2)
from tmp_1;
这时执行上面的sql就能得到期望的结果了 配合distinct关键字去重聚合
此场景下会编译期间会自动设置只启动一个reduce task处理数据 可能造成数据拥堵如下的聚合 select count(distinct sex) as cnt1 from student; 在这种情况下可以考了先去重 在聚合 通过子查询完成因为先执行distinct的时候 可以使用多个reducetask来跑数据改进后的sql如下 select count(*) as gender_uni_cnt from (select distinct sex from student) a; 找出student表中男女学生年龄最大的及名字
这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值 select sex
select sex,
max(struct(age, name)).col1 as age,
max(struct(age, name)).col2 as name
from student
group by sex;
执行上面的sql观察结果 8.5 增强聚合
常用的增强聚合函数包括grouping_sets、cube、rollup这几个函数它们主要适用于OLAP多维数据分析模式中多维分析中的维指的分析问题时看待问题的维度、角度接下来通过操作演示下增强聚合的使用。 8.6 数据准备 建表并加载数据
CREATE TABLE cookie_info(month STRING,day STRING,cookieid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ,;load data local inpath /usr/local/soft/selectdata/cookie_info.txt into table cookie_info; 查询数据是否加载成功 8.7 操作演示group sets group sets 操作1
-- grouping_id —— 表示这一组结果属于哪个分组集合 -- 根据grouping sets中的分组条件monthday1是代表month2是代表day
SELECTmonth,day,COUNT(DISTINCT cookieid) AS nums,GROUPING__ID
FROM cookie_info
GROUP BY month,day
GROUPING SETS (month,day) --这里是关键
ORDER BY GROUPING__ID;
执行结果 从这个执行结果来看其实group sets的操作相当于是把两种聚合结果做了union操作等价于下面的sql
SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day; group sets 操作2
从上一个操作对照理解这个sql就很好理解了
SELECTmonth,day,COUNT(DISTINCT cookieid) AS nums,GROUPING__ID
FROM cookie_info
GROUP BY month,day
GROUPING SETS (month,day,(month,day)) --1 month 2 day 3 (month,day)
ORDER BY GROUPING__ID;
执行sql观察结果 总结来说grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的GROUP BY结果集进行UNION ALL。GROUPING__ID表示结果属于哪一个分组集合。 8.8 增强聚合 -- cube与ROLLUP cube表示根据GROUP BY的维度的所有组合进行聚合。对于cube来说,如果有n个维度,则所有组合的总个数是2^n 比如cube有a,b,c 3个维度则所有组合情况是 (a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),() cube 的语法功能指的是根据GROUP BY的维度的所有组合进行聚合rollup是cube的子集以最左侧的维度为主从该维度进行层级聚合 比如ROLLUP有a,b,c3个维度则所有组合情况是(a,b,c),(a,b),(a),() 8.9 cube 操作演示 with cube
按照上面的理论说明使用cube进行聚合下面的sql
SELECTmonth,day,COUNT(DISTINCT cookieid) AS nums,GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
执行sql观察执行结果 从结果来看得到了多个维度的聚合统计结果其实就等价于下面的sql
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day; with rollup
以month维度进行层级聚合
SELECTmonth,day,COUNT(DISTINCT cookieid) AS nums,GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
执行结果 再把month和day调换顺序则以day维度进行层级聚合
SELECTday,month,COUNT(DISTINCT cookieid) AS uv,GROUPING__ID
FROM cookie_info
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
执行结果 本篇通过大量的篇幅展示了hive中各类函数的详细使用希望对看到的小伙们有用不足之处敬请执教本篇到此结束谢谢观看
