目录
2.6 unix_timestamp、from_unixtime时间戳函数的使用
1、 系统内置函数
1）查看系统自带的函数
hive> show functions;2）显示自带的函数的用法
hive> desc function upper;3）详细显示自带的函数的用法
hive> desc function extended upper;2、 常用内置函数
2.1 空字段赋值
1）函数说明
NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。
2）数据准备：采用员工表
3）查询：如果员工的comm为NULL，则用-1代替
hive (default)> select comm,nvl(comm, -1) from emp;
OK
comm _c1
NULL -1.0
300.0 300.0
500.0 500.0
NULL -1.04）查询：如果员工的comm为NULL，则用领导id代替
hive (default)> select comm, nvl(comm,mgr) from emp;
OK
comm _c1
NULL 7902.0
300.0 300.0
500.0 500.0
NULL 7839.02.2 CASE WHEN
1）数据准备
| name | dept_id | sex |
| 悟空 | A | 男 |
| 大大 | A | 男 |
| 拉拉 | B | 男 |
| 凤姐 | A | 女 |
| 婷姐 | B | 女 |
| 婷婷 | B | 女 |
2）需求
求出不同部门男女各多少人。结果如下：
A 2 1
B 1 2
3）创建本地emp_sex.txt，导入数据
[Mark@hadoop102 datas]$ vi emp_sex.txt
悟空 A 男
大大 A 男
拉拉 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女4）创建hive表并导入数据
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/emp_sex.txt' into table emp_sex;5）按需求查询数据
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from
emp_sex
group by
dept_id;2.3 行转列
1）相关函数说明
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。
分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。
这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
COLLECT_LIST(col)：不去重。2）数据准备
| name | constellation | blood_type |
| 孙悟空 | 白羊座 | A |
| 大海 | 射手座 | A |
| 宋宋 | 白羊座 | B |
| 猪八戒 | 白羊座 | A |
| 凤姐 | 射手座 | A |
| 苍老师 | 白羊座 | B |
3）需求
把星座和血型一样的人归类到一起。结果如下：
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师
4）创建本地constellation.txt，导入数据
[Mark@hadoop102 datas]$ vi constellation.txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
仓老师 射手座 B5）创建hive表并导入数据
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/datas/constellation.txt" into table person_info;6）按需求查询数据
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, ",", blood_type) base
from
person_info) t1
group by
t1.base;方法二：
select
concat(constellation,",",blood_type) xzxx,
concat_ws("|",collect_list(name))
from
person_info
group by
constellation,blood_type;2.4 列转行
1）函数说明
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
2）数据准备
表6-7 数据准备
| movie | category |
| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
| 《战狼2》 | 战争,动作,灾难 |
3）需求
将电影分类中的数组数据展开。结果如下：
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难4）创建本地movie.txt，导入数据
[Mark@hadoop102 datas]$ vi movie.txt
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难5）创建hive表并导入数据
create table movie_info(
movie string,
category string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/datas/movie.txt" into table movie_info;6）按需求查询数据
select
m.movie,
tbl.cate
from
movie_info m
lateral view
explode(split(category, ",")) tbl as cate;2.5 日期相关函数
（1）current_date返回当前日期
select current_date();（2）date_add, date_sub 日期的加减
--今天开始90天以后的日期
select date_add(current_date(), 90);--今天开始90天以前的日期
select date_sub(current_date(), 90);（3）两个日期之间的日期差
--今天和1990年6月4日的天数差
SELECT datediff(CURRENT_DATE(), "1990-06-04");(4) date_format函数（根据格式整理日期）
hive (gmall)> select date_format('2020-06-14','yyyy-MM');
2020-06(5）next_day函数
（1）取当前天的下一个周一
hive (gmall)> select next_day('2020-06-14','MO');
2020-06-15
说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
（2）取当前周的周一
hive (gmall)> select date_add(next_day('2020-06-14','MO'),-7);
2020-06-8(6）last_day函数（求当月最后一天日期）
hive (gmall)> select last_day('2020-06-14');
2020-06-302.6 unix_timestamp、from_unixtime时间戳函数的使用
unix_timestamp函数用法
UNIX_TIMESTAMP() ：若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳
UNIX_TIMESTAMP(date) ：若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。from_unixtime函数用法
语法：from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。
对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如：
select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cn from ttengine_hour_data where …2.7 cast函数
CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
语法：CAST (expression AS data_type)
expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED 例子：
1.SELECT CAST('9.0' AS decimal) 结果：9
2.SELECT CAST('9.5' AS decimal(10,2)) 结果：9.5(精度与小数位数分别为10与2。精度是总的数字位数，包括小数点左边和右边位数的总和
而小数位数是小数点右边的位数)
3.SELECT CAST(NOW() AS DATE) 结果：2017-11-27
now() 2017-11-27 10:43:222.8 With as 函数用法
–- 针对一个别名
with tmp as (select * from tb_name)
–- 针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…
–- 相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
–- 相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;
其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。
向一张表插入数据的with as用法
insert into table2
with
s1 as (select rownum c1 from dual connect by rownum <= 10),
s2 as (select rownum c2 from dual connect by rownum <= 10)
select a.c1, b.c2 from s1 a, s2 b where…;
select s1.sid, s2.sid from s1 ,s2需要有关联条件，不然结果会是笛卡尔积。
with as 相当于虚拟视图。
with as短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个sql片断，该sql片断会被整个sql语句所用到。有的时候，是为了让sql语句的可读性更高些，也有可能是在union all的不同部分，作为提供数据的部分。
特别对于union all比较有用。因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as短语所获取的数据放入一个temp表里，如果只是被调用一次，则不会。而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
with
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select ‘no records’ from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);
WITH语句的优点:
(1). SQL可读性增强。比如对于特定with子查询取个有意义的名字等。
(2)、with子查询只执行一次，将结果存储在用户临时表空间中，可以引用多次，增强性能。
举例:在进行导入EXCEL的过程中，有时候，需要将数据存储在临时表中，当下一次在进行导入的时候，进行清除临时表的数据，但是这时候，有时候发生并发问题的话，两个用户可能会分别操作对方的数据，所以，可能造成混乱，但是可以使用WITH函数和UNION语句拼接一个SQL语句，存储在SESSION中，当需要导出错误信息的时候，可以使用该语句构造数据。
2.9 named_struct
主要用这个函数做json拼接:
select named_struct(
'"supplier_sku_id"',supplier_sku_id,
'"supplier_id"',t1.supplier_id,
'"prefee_rate"',prefee_rate,
'"order_rate"',order_rate,
'"goRate"',go_rate,
'"completeRate"',compliance_prefee_rate
) as detail
from table1特别记录下这个格式，如果是这个函数的返回值存储，存储类型要这样，比如我写了5个字段，创建的字段类型是：
struct<supplier_sku_id:bigint,supplier_id:bigint,prefee_rate:double,order_rate:double,goRate:double,completeRate:double>
函数说明：
用途：使用给定的name/value列表建立STRUCT。
参数说明：
value：value可为任意类型。
name：指定的STRING类型的Field名称。此参数为常量。
返回值说明：返回STRUCT类型。Field的名称依次为name1，name2，…。
最后结构体里面的key是以建表的语句为准。
如果用的是阿里云，建表时候也需要指定 set odps.sql.type.system.odps2 = true;
不指定的话放进去就是个地址值2.10 STR_TO_MAP函数
-- 语法描述
STR_TO_MAP(VARCHAR text, VARCHAR listDelimiter, VARCHAR keyValueDelimiter)
-- 功能描述
使用listDelimiter将text分隔成K-V对，然后使用keyValueDelimiter分隔每个K-V对，组装成MAP返回。默认listDelimiter为（ ，），keyValueDelimiter为（=）。
-- 案例
str_to_map('1001=2020-06-14,1002=2020-06-14', ',' , '=')
输出
{"1001":"2020-06-14","1002":"2020-06-14"}3、 自定义函数
1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore()4）官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）编程步骤：
（1）继承org.apache.hadoop.hive.ql.exec.UDF
（2）需要实现evaluate函数；evaluate函数支持重载；
（3）在hive的命令行窗口创建函数
添加jar
add jar linux_jar_path
创建function
create [temporary] function [dbname.]function_name AS class_name;
（4）在hive的命令行窗口删除函数
Drop [temporary] function [if exists] [dbname.]function_name;6）注意事项：UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
4、 自定义UDF函数
1）创建一个Maven工程Hive
2）导入依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>3）创建一个类
package com.atguigu.hive;
import org.apache.hadoop.hive.ql.exec.UDF;
public class Lower extends UDF {
public String evaluate (final String s) {
if (s == null) {
return null;
}
return s.toLowerCase();
}
}4）打成jar包上传到服务器/opt/module/jars/udf.jar
5）将jar包添加到hive的classpath
hive (default)> add jar /opt/module/datas/udf.jar;6）创建临时函数与开发好的java class关联
hive (default)> create temporary function mylower as "com.atguigu.hive.Lower";7）即可在hql中使用自定义的函数strip
hive (default)> select ename, mylower(ename) lowername from emp;
235
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
