HIVE常用内置函数、高级函数、自定义函数
Hive自定义函数包括三种UDF、UDAF、UDTF
UDF(User-Defined-Function) ：一进一出
UDAF(User- Defined Aggregation Funcation) ：聚集函数，多进一出。Count/max/min
UDTF(User-Defined Table-Generating Functions) ：炸裂函数， 一进多出，如lateral view explore()
求总行数（count）
select count(*) cnt from emp;
求工资的最大值（max）
select max(sal) max_sal from emp;
求工资的最小值（min）
select min(sal) min_sal from emp;
求工资的总和（sum）
select sum(sal) sum_sal from emp;
求工资的平均值（avg）
select avg(sal) avg_sal from emp;
LIMIT子句用于限制返回的行数
select * from emp limit 5;
空字段赋值 NVL( value，default_value)
如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。
select nvl(null,0) as amount;
字符串处理
CONCAT(str1,str2,…) 字符串拼接
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。可以有一个或多个参数。
select concat('key' ,':', 'value') as col; --key:value
CONCAT_WS(separator,str1,str2,…) 字符串拼接
是CONCAT()的特殊形式，第一个参数是分隔符。分隔符可以是一个字符串，也可以是其它参数。
如果分隔符为 NULL，则结果为 NULL。CONCAT_WS()不会忽略任何空字符串，然而会忽略所有的 NULL。
SELECT CONCAT_WS(',','First',NULL,'Thrid','Last'); --First,Thrid,Last
instr(string string, string substring) 字符串包含操作
返回查找字符串string中子字符串substring出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，位置为从1开始。
select instr("aaa:bbb:ccc:ddd", "cc"),instr("aaa:bbb:ccc:ddd", "cc")>0
split(string string, string regex) 字符串分隔成数组
返回分割后的字符串数组
select array_contains(split("aaa:bbb:ccc:ddd",":"), "ccc")
array_contains(array arr, element) 字符串数组包含元素
返回数组中是否包含element元素：true or false
select array_contains(split("aaa:bbb:ccc:ddd",":"), "ccc"), array_contains(split("aaa:bbb:ccc:ddd",":"), "cc")
日期操作
to_date：日期时间转日期函数
select to_date('2015-04-02 13:34:12');
输出：2015-04-02
unix_timestamp：获取当前unix时间戳
select unix_timestamp();
输出：1430816254
select unix_timestamp('2015-04-30 13:51:20');
输出：1430373080
from_unixtime：转化unix时间戳到当前时区的时间格式
select from_unixtime(unix_timestamp(),'yyyy-MM-dd');
输出：2021-09-30
year：返回日期中的年
select year('2015-04-02 11:32:12');
输出：2015
month：返回日期中的月份
select month('2015-12-02 11:32:12');
输出：12
day：返回日期中的天
select day('2015-04-13 11:32:12');
输出：13
hour：返回日期中的小时
select hour('2015-04-13 11:32:12');
输出：11
minute：返回日期中的分钟
select minute('2015-04-13 11:32:12');
输出：32
second：返回日期中的秒
select second('2015-04-13 11:32:56');
输出：56
weekofyear：返回日期在当前周数
select weekofyear('2015-05-05 12:11:1');
输出：19
datediff：返回开始日期减去结束日期的天数
select datediff('2015-04-09','2015-04-01');
输出：8
date_sub：返回日期前n天的日期
select date_sub('2015-04-09',4);
输出：2015-04-05
date_add：返回日期后n天的日期
select date_add('2015-04-09',4);
输出：2015-04-13
from_unixtime+ unix_timestamp Hive中yyyymmdd和yyyy-mm-dd日期之间的切换
思想：先转换成时间戳，再由时间戳转换为对应格式。
--20171205转成2017-12-05
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;
--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;
Hive中取最近30天数据
datediff(CURRENT_TIMESTAMP ,gmt_create)<=30
Hive中 两个日期相差多少小时
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/3600
输出：1
Hive中 两个日期相差多少分钟
select (unix_timestamp('2018-05-25 12:03:55') - unix_timestamp('2018-05-25 11:03:55'))/60
输出：60
hive 计算某一个日期属于星期几，如2018-05-20 是星期日
SELECT IF(pmod(datediff('2018-05-20', '1920-01-01') - 3, 7)='0', 7, pmod(datediff('2018-05-20', '1920-01-01') - 3, 7))
输出：7
hive返回上个月第一天和最后一天
--上个月第一天
select trunc(add_months(CURRENT_TIMESTAMP,-1),'MM')
select concat(substr(add_months(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),-1),1,7),'-01');
--上个月最后一天
select date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1);
二、where语句
算术运算符
| 运算符 | 描述 |
|---|---|
| A+B | A和B 相加 |
| A-B | A减去B |
| A*B | A和B 相乘 |
| A/B | A除以B |
| A%B | A对B取余 |
| A&B | A和B按位取与 |
| A|B | A和B按位取或 |
| A^B | A和B按位取异或 |
| ~A | A按位取反 |
比较运算符
下表描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中
| 操作符 | 支持的数据类型 | 描述 |
|---|---|---|
| A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE |
| A<=>B | 基本数据类型 | 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL |
| A<>B, A!=B | 基本数据类型 | A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE |
| A<B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE |
| A<=B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE |
| A>B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE |
| A>=B | 基本数据类型 | A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE |
| A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 |
| A IS NULL | 所有数据类型 | 如果A等于NULL，则返回TRUE，反之返回FALSE |
| A IS NOT NULL | 所有数据类型 | 如果A不等于NULL，则返回TRUE，反之返回FALSE |
| IN(数值1, 数值2) | 所有数据类型 | 使用 IN运算显示列表中的值 |
| A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式，也叫通配符模式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。 |
| A RLIKE B, A REGEXP B | STRING 类型 | B是基于java的正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 |
Like和RLike
1）使用LIKE运算选择类似的值，选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)；_ 代表一个字符。
2）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
逻辑运算符
| 含义 | 操作符 |
|---|---|
| 逻辑并 | AND |
| 逻辑或 | OR |
| 逻辑否 | NOT |
三、分组
group by语句
GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
--计算emp每个部门中每个岗位的最高薪水
select
t.deptno,
t.job,
max(t.sal) as max_sal
from emp t
group by t.deptno, t.job;
Join语句
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
内连接
只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
select
e.empno, e.ename, d.deptno
from emp e
join dept d
on e.deptno = d.deptno;
左外连接
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
select
e.empno, e.ename, d.deptno
from emp e
left join dept d
on e.deptno = d.deptno;
右外连接
JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
select
e.empno, e.ename, d.deptno
from emp e
right join dept d
on e.deptno = d.deptno;
满外连接
将会返回所有表中符合WHERE语句条件的所有记录。
如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。
select
e.empno, e.ename, d.deptno
from emp e
full join dept d
on e.deptno = d.deptno;
多表连接查询
SELECT
e.ename, d.dname, l.loc_name
FROM emp e
JOIN dept d
ON d.deptno = e.deptno
JOIN location l
ON d.loc = l.loc;
大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。
本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。
优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。
笛卡尔积
省略连接条件；连接条件无效；所有表中的所有行互相连接。
select
empno, dname
from emp, dept;
全局排序 Order By
全局排序，只有一个Reducer。ASC: 升序（默认）；DESC: 降。
select * from emp order by sal desc;
--按别名排序
select ename, sal*2 twosal from emp order by twosal;
--多列排序
select ename, deptno, sal from emp order by deptno, sal ;
分区排序 Distribute By
在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。
distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
每个MapReduce内部排序 Sort By
对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。
Cluster By
当distribute by和sorts by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
CASE WHEN
----- 写法一
case
when tb1.os = 'android' then 'android'
when tb1.os = 'ios' then 'iPhone'
else 'PC'
end as os，
----- 写法二
case tb1.os
when 'android' then 'android'
when 'ios' then 'iPhone'
else 'PC'
end as os,
行转列
CONCAT_WS(separator, str1, str2,…)： 多个字符串拼接
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from (select
name
from person_info
) t1
group by t1.base;
列转行
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行，是一个炸裂函数。
LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
1）数据准备 movie_info表
| movie | category |
|---|---|
| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
| 《战狼2》 | 战争,动作,灾难 |
2）sql
select
movie,
category_name
from
movie_info
lateral view explode(category) table_tmp as category_name;
3) 结果
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难