方便完成我们一些复杂的操作,就好像我们 Spark 中的 UDF 函数,避免用户反复写逻辑。
Hive 提供了大量的内置函数,主要可以分为以下几类:
下面的命令可以查看内置函数的相关信息:
show functions;
查看函数 upper 的用法(显示函数参数):
desc function upper;
查看函数 upper 的详细用法:
desc function extended upper;
特点:输入一行,输出一行(或多行)。
按照功能可以分为:
处理常用的 +、-、*、/ 还有 %、&、^、~、| 等
查询出员工薪资后全部 +100 显示:
select sal+1 from emp;
默认不保留小数部分,直接四舍五入为整数。
select round(3.5); -- 输出4
可以设置第二个参数(控制小数部分的长度):
select round(3.1415,2) --输出3.14
select ceil(3.11); -- 输出4
select floor(3.6); --输出3
解释:从第 beg 个下标对 str 进行截取,共截取 len 长度的字符串。
注意: 下标是从 1 开始的,负数代表取后 beg 位(-4代表取后4位)。
- select substring("hello",1); --返回hello
-
- select substring("hello",1,2); --返回he
-
- select substring("hello",-4); --返回ello
-
- select substring("hello",-4,2); --返回el
解释:将字符串 str1 中的子字符串 str2 用 str3 替换。
select replace("hello sxau",'sxau','tylg'); --返回 hello tylg
解释:将字符串 str1 中满足正则表达式 regex 的部分用 str2 替换掉。
select regexp_replace("身份证号:141125...","\\d",'*'); --返回 身份证号:******...
注意:这里的正则表达式语法是我们 Java 中的语法,比正常的正则表达式多一个斜杠 \;
解释:满足正则表达式返回 true 否则返回 false。
select '141125' regexp '\\d+'; --返回true
将字符串 str 重复 count 次。
select repeat('good ',3); --返回 good good good
解释:根据正则表达式 regex 将字符串切割开来。
注意:这里返回的是一个数组。
select split("name sex id sal",' '); --返回 ["name","sex","id","sal"]
解释:如果 a 不是null,则返回a,否则返回 b。
用法:通常用于判断字段是否为空。
select nvl(null,1); --返回1
select concat('tom','jerry','school'); --返回 tomjerryschool
select concat_ws('.','192','168','88','134') --返回 192.168.88.134
JSON 的相关知识可以查看菜鸟教程。
解释:解析 json 字符串 json_string,并返回 path 指定的内容。如果输入的 json 字符串无效,那么返回 null。
- -- 返回 lyh
- select get_json_object('{"name": "lyh", "friends": ["my","zht"],"students": {"drj": 48,"lyf": 30},"address": {"street": "chang an jie","city": "beijing","postal_code": 10010}}','$.name');
注意:
我们的日期时间基本格式为:年-月-日 时:分:秒
解释:将时间字符串 date_str 根据 format 格式解析为时间戳。
注意:一定要注意字符串 date_str 中的间隔符必须要和 format 中一一对应。
- select unix_timestamp(); --当前时间的时间戳 1696390814
-
- select unix_timestamp('2023/10/04 11-41-08','yyyy/MM/dd HH-mm-ss'); -- 1696419668
注意:
- select from_unixtime(1696419668); --输出 2023-10-04 11:41:08
-
- select from_utc_timestamp(cast(1696419668 as bigint)*1000,'GMT+8');
注意:括号可以省略。
select current_date; --返回 2023-10-04
select current_timestamp(); --返回 2023-10-04 11:49:51.696000000
select month(current_date()); --返回 10
select day(current_date()); --返回 4
select hour('2023-10-04 11:59:10'); --返回 11
select datediff('2023-10-4','2023-10-1'); --返回 3
注意:有 add 就有 sub,一个加,一个减。
- select date_add('2023-10-1',365); --返回 2024-9-30
-
- select date_sub('2023-10-1',365); --返回 2022-10-1
select date_format('2023-10-4 12:00:05','yyyy年MM月dd日-HH时mm分:ss秒'); --返回2023年10月04日-12时00分:05秒
- select stu_id,
- course_id,
- score,
- case
- when score>=90 then 'A'
- when score>=80 then 'B'
- when score>=70 then 'C'
- when score>=60 then 'D'
- else '不及格'
- end as grade
- from score_info;
如果是等值查询(直接 case 后面跟字段名,when 条件处直接写值即可):
- select stu_id,
- course_id,
- score,
- case score
- when 90 then 'A'
- when 80 then 'B'
- when 70 then 'C'
- when 60 then 'D'
- end as grade
- from score_info;
- select stu_id,course_id,
- if(score>60,'及格','不及格') `grade`
- from score_info;
集合函数用来处理复杂的数据类型,比如 array、map 和 struct。
select size(array(1,2,3)); --3
select map('hadoop',1,'spark',1); --{"hadoop":1,"spark":1}
select map_keys(map('hadoop',1,'spark',1)); --["hadoop","spark"]
select map_values(map('hadoop',1,'spark',1)); --[1,1]
select array(1,2,3); --[1,2,3]
select array_contains(array(1,2,3),3); --true
select sort_array(array(5,3,4,1)); --[1,3,4,5]
select struct('name','age','sex'); --{"col1":"name","col2":"age","col3":"sex"}
select named_struct('name',"tom",'age',"18",'sex',"男"); --{"name":"tom","age":"18","sex":"男"}
- create table employee(
- name string, --姓名
- sex string, --性别
- birthday string, --出生年月
- hire_date string, --入职日期
- job string, --岗位
- salary double, --薪资
- bonus double, --奖金
- friends array<string>, --朋友
- children map<string,int> --孩子
- );
- insert into employee
- values('张无忌','男','1980/02/12','2022/08/09','销售',3000,12000,array('阿朱','小昭'),map('张小无',8,'张小忌',9)),
- ('赵敏','女','1982/05/18','2022/09/10','行政',9000,2000,array('阿三','阿四'),map('赵小敏',8)),
- ('宋青书','男','1981/03/15','2022/04/09','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
- ('周芷若','女','1981/03/17','2022/04/10','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
- ('郭靖','男','1985/03/11','2022/07/19','销售',2000,13000,array('南帝','北丐'),map('郭芙',5,'郭襄',4)),
- ('黄蓉','女','1982/12/13','2022/06/11','行政',12000,null,array('东邪','西毒'),map('郭芙',5,'郭襄',4)),
- ('杨过','男','1988/01/30','2022/08/13','前台',5000,null,array('郭靖','黄蓉'),map('杨小过',2)),
- ('小龙女','女','1985/02/12','2022/09/24','前台',6000,null,array('张三','李四'),map('杨小过',2));
- select month(replace(hire_date,'/','-')) as month,
- count(*) as cn
- from employee
- group by
- month(replace(hire_date,'/','-'));
- select name,
- concat(if(month>=0,year,year-1),'年'
- ,if(month>=0,month,12+month),'月') age
- from
- (
- select name,
- year(current_date())-year(t1.birthday) year,
- month(current_date())-month(t1.birthday) month
- from (
- select name,replace(birthday,'/','-') birthday
- from employee
- )t1
- )t2;
- select name,salary+nvl(bonus,0)
- from employee
- order by salary;
select name,size(friends) `朋友` from employee;
select name,map_keys(children) from employee;
- select job,
- sum(if(sex='男',1,0)) male,
- sum(if(sex='女',1,0)) female
- from employee
- group by job;