Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供简单的SQL查询功能,可以将SQL语句转换为MapReduce任务进行运行。其优点是学习成本低,可以通过类SQL语句快速实现简单的MapReduce统计,不必开发专门的MapReduce应用,十分适合数据仓库的统计分析。 Hive会将常用的逻辑封装成函数给用户进行使用,类似于Java中的函数。好处在于可以避免用户反复写逻辑,可以直接拿来使用。重点在于用户需要知道函数叫什么,能做什么。 Hive函数大致分为五类:单行函数、聚合函数、炸裂函数、窗口函数、自定义函数,今天我们主要介绍一下除自定义函数外的函数部分案例使用。 自定义函数咱们之前已经细致的讲过了,可以根据连接查看:HIVE函数讲解之自定义函数 一、单行函数 单行函数的特点是一进一出,大致可以分为数学函数,日期函数,字符串函数,流程控制函数,集合函数。 1. 数学函数 数学函数针对于数值形式的计算,返回值均为数值类型。 示例: select 1 + 1; 2 select 1 - 1; 0 select 2 * 2; 4 select 3 / 2; 1.5 select 5 % 2; 1 select 1 & 2; 0 select 1 | 2; 3 select 1 ^ 2; 3 select ~1; -2 2. 日期函数 ① unix_timestamp(string time):返回值为bigint类型。 select unix_timestamp('2023-04-06 15:31:26'); 1680795086 ② from_unixtime(bigint time [,string format]):返回值为 string类型。 select from_unixtime(1680795086); 2023-04-06 15:31:26 ③ year(string time):返回值为int类型。 select year('2023-04-06 15:31:26'); 2023 ④ month(string time):返回值为int类型。 select month('2023-04-06 15:31:26'); 4 ⑤ day(string time):返回值为int类型。 select day('2023-04-06 15:31:26'); 6 ⑥ datediff(string time,string time):返回值类型为int类型。 select datediff('2023-04-07','2023-04-06'); 1 ⑦ date_add(string date,int day):返回值为date类型。 select date_add('2023-04-06',1); 2023-04-07 ⑧ date_sub(string date,int day):返回值为date类型。 select date_sub('2023-04-06',1); 2023-04-05 ⑨ date_format(string date,string format):返回值为string类型。 select date_format('2023-04-06 15:45:25','yyyy-MM-dd'); 2023-04-06 3. 字符串函数 ① upper(string str):返回值为string类型。 select upper('abc'); ABC ② lower(string str):返回值为string类型。 select lower('ABC'); abc ③ length(string str):返回值为int类型。 select length('atguigu'); 7 ④ trim(string str):返回值为string类型。 select trim(' abc '); abc ⑤ substring(string str,int start[,int end]):返回值为string类型。 select substring('atguigu',2,5); tguig ⑥ replace(string str,string bef,string las):返回值为string类型。 select replace('atguigu','a','b'); btguigu ⑦ split(string str,string par):返回值为array类型。 select split('atguigu','g'); ["at","ui","u"] ⑧concat_ws(string spl,string s1,string s2,...):返回值为string类型。 select concat_ws('-','a','b','c'); a-b-c ⑨ get_json_object(string json,string path):返回值为string类型。 select get_json_object('{"data1":1,"data2":2}','$.data2'); 2 4. 流程控制函数 示例: select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; mary select if(2>3,2,3); 3 5. 集合函数 ① size(array/map m):返回值为int类型。 select size(array('a','b','c')); 3 ② map_keys(map m):返回值为array类型。 select map_keys(test) from test; [1,2,3] test字段值为{"1":"a","2":"b","3","c"} ③ map_values(map m):返回值为array类型。 select map_values(test) from test; ["a","b","c"] ④ array_contains(array arr,string str):返回值为boolean类型。 select array_contains(array('1','2','3'),'4'); false ⑤ sort_array(array arr):返回值为array类型。 select sort_array(array(4,2,3,1)); [1,2,3,4] 二、聚合函数 聚合函数对一组数据进行计算,并返回单个值。 测试数据test表: 1. 普通聚合 示例: select count(*) from test; 4 select sum(score) from test; 260 select max(score) from test; 80 select min(score) from test; 50 select avg(score) from test; 65 2. 高级聚合 示例: select collect_list(name) from test; ["atguigu1","atguigu2","atguigu3","atguigu3"] select collect_set(name) from test; ["atguigu1","atguigu2","atguigu3"] 3. 分组 Group By语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作,同时可以使用having进行过滤。 示例: select id, avg(score) avg_s from test group by id having avg_s > 60; 三、炸裂函数 Hive处理的数据字段是可再分的,不满足原子性,即DDL可以定义一个字段类型为数组,因此才有了explode()函数,用于给这个字段展开降维,把指定的数组字段拆分降维展开为多行。类似于UDTF函数,作用于单/多个数据行,并且产生多个数据行,以一个表作为输出。 1. explode 将数组或者map展开 例: select explode(array('a','b','c','d')); 结果: a b c d 2. json_tuple 取出json字符串中的属性值 例: select json_tuple('{"name":"王二狗","sex":"男","age":"25"}','name','sex','age'); 结果: 王二狗 男 25 3. lateral view 侧写 炸裂函数和聚合函数一样不支持和普通列一起查询,所以一般会配合侧写进行查询。 用法:lateral view udtf(expression) tableAlias AS columnAlias 解释:lateral view用于和split,explode等UDTF一起使用,它能够将一行数据拆分成多行数据,在此基础上可以对拆分后的数据进行聚合。 lateral view首先为原始表的每行调用UDTF,UDTF会把每一行拆分成一行或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。 例: select movie, category_namefrom movie_infolateral viewexplode(split(category,",")) movie_info_tmp AS category_name; 四、窗口函数 窗口函数,也可以叫开窗函数,其从本质来看是将Hive 中一些复杂的查询封装成了窗口的形式,进行数据统计时使用并且操 作十分方便,窗口函数为统计时使用的聚合函数指定一个聚合的范围。 1. 语法 基本语法:函数 + over( [partition by ...] [order by ...] [窗口子句] ) (rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following (rows | range) between current row and (current row | (unbounded | [num]) following) (rows | range) between [num] following and (unbounded | [num]) following 示例: rows between unbounded preceding and unbounded following 行的范围为上无边界到下无边界(第一行到最后一行)。 注:窗口函数是一行一行执行的。 2. 使用 ① 窗口函数 lag(col,n,default_val):往前第n行数据。 lead(col,n, default_val):往后第n行数据。 first_value (col,true/false):当前窗口下的第一个值,第二个参数为true,跳过空值。 last_value (col,true/false):当前窗口下的最后一个值,第二个参数为true,跳过空值。 ② 聚合函数 max:最大值。 min:最小值。 sum:求和。 avg:平均值。 count:计数。 ③ 排名分析函数 rank:排名相同时会重复总数不会减少。 dense_rank:排名相同时会重复总数会减少。 row_number:行号。 ntile:分组并给上组号。 3. 注意事项 4. 使用示例 例如,表table,字段为id,sub,score,需求出表每门学科的成绩排名。 select id, sub, score, rank() over(distribute by sub sort by score desc) rank from table; 五、总结 本文给大家介绍了Hive中一些常用的基本函数、炸裂函数和窗口函数的理论及使用,各位程序猿们需要多多练习函数的使用,体会函数的效果以及它们之间的联合使用!下期为大家介绍HIVE自定义函数的方法,实现我们自己想要的效果。