六、hive 的函数
查看有多少函数
show functions;
查看函数 substr 的详细信息
desc function substr；
查看函数 substr 的扩展信息，会有使用例子
desc function extended substr；
查询地址：
1、类型转化函数 cast
cast 属性强转，把字符串转成 int
select cast（'5' as int）；
查看上述结果的数据类型，可以新建一个表来实现
create table t_test
as select cast（“6”as int）
来查看字段的类型
desc t_test
[图片上传失败...(image-f38638-1546767417378)]
转化成 date 类型
select cast（“2017-08-09”as date）；
时间的强制转换：使用 to_date 函数也可以
current_timestamp 是hive里面的时间，是一个常量
current_date 是 2018-11-20
unix_timestamp() 是时间戳，带括号是因为可以传入字符参数转换为时间戳
[图片上传失败...(image-b42641-1546767417378)]
将时间戳转化为 date，结果如下图
select cast（current_timestamp as date）；
[图片上传失败...(image-f0287d-1546767417378)]
求当前时间的时间戳，这是一个函数，结果是一个常量，如下图
这个不能使用 cast 来转换成 date 类型，但是可以通过其他方式
[图片上传失败...(image-754d97-1546767417378)]
2、数学运算
四舍五入，得5
select round（5.4）：
保留 3 位小数，得 5.135
select round（5.1354，3）；
向上取整，得6，也可以写作 celling
select cell（5.4）；
向下取整，得 5
select floor（5.4）；
取绝对值，得5.4
select abs（-5.4）；
取最大值，得6，参数个数任意最少两个
select greatest（3，5，6）；
取最小值，得 3
select least（3，5，6）；
eg：表数据如下
[图片上传失败...(image-d92c3a-1546767417377)]
求每个人三种收入的最大的一个值是多少，并且要转换成 double，结果如下图
这是针对每一行来求的
select greatest（cast（s1 as double），cast（s2 as double），cast（s3 as double））from t_fun2；
[图片上传失败...(image-f8e975-1546767417377)]
这是聚合函数，是针对一个字段数据（一列）来求的
一般聚合函数是要使用 group by 分组，这里没加就是整个数据看做是一个组
select max（age）from t_person；
select min（age）from t_person；
3、字符串函数
a、字符串截取，substr
截取字段数据中的一部分，例如截取 [http://www.edu360.cn/job](http://www.edu360.cn/job) 这种的url
这里1 表示第一位，21为最后一个/,(从0开始和从1开始是一样的，所以写1比较好)
1 表示位置，21 表示长度如果没写表示截取后面的全部数据
select ip，substr（url，1，21）from t_access；
[图片上传失败...(image-da6aab-1546767417377)]
b、拼接函数，concat
字符串拼接，得到 abxy ,传入 string 类型的字段即可
select concat（“ab”，“xy”）；
使用分隔符来拼接，这里用点来连接，得到 192.168.109.17
select concat_ws（“.”, "192"，“168”，“109”，“17”）；
c、求长度，length
这里的长度是 14
select length（“192.168.109.17 ”）；
d、切割，split
返回一个数组，["18","male","beijing"],所以还可以用角标来得到数据
select split（“18：male：beijing”，“：”）
[图片上传失败...(image-ddf2ea-1546767417377)]
注意： 这里点号因为是正则表达式里的符号，所以要用 ,但是 \ 也是特舒符，所以用 \.
select split（“192.168.109.17 ”，“\.”）
e、大写：upper
4、时间函数
如果 cast 函数不能将一些时间表示转换为时间类型（如 cast（“2017-09-08 11：34：36”as date），不包含时分秒就能转成功），那么可以使用以下方法
当前的时间戳，结果如下图，这是 hive的时间戳，和 java里面的时间戳是常数不太一样
select current_timestamp；
[图片上传失败...(image-28fa59-1546767417377)]
当前的日期，结果如下图
select current_date；
[图片上传失败...(image-9b8243-1546767417377)]
取当前时间的毫秒数时间戳（unix 时间戳），是一个常量
select unix_timestamp()；
[图片上传失败...(image-257607-1546767417377)]
a、unix 时间戳转字符串
这里是使用常数来转换为字符串（这里是使用当前时间戳），格式可选传入
select from_unixtime（ unix_timestamp（））；
结果如下图，格式是什么就显示什么
select from_unixtime（ unix_timestamp（），“yyyy-MM-dd HH:mm:ss”） ;
[图片上传失败...(image-64e046-1546767417377)]
b、字符串转 unix时间戳
select unix_timestamp（“2017-08-19 18：50：30”）；
结果如下图
select unix_timestamp（“2017/08/10 17:50:30”, "yyyy/MM/dd HH:mm:ss"）
[图片上传失败...(image-169f43-1546767417377)]
c、将字符串转成日期 date：这个应该就是 hive 时间戳
这里不能传格式，只支持标准时间写法的字符串
select to_date（“2017-09-17 16：57：32”）
[图片上传失败...(image-e9041b-1546767417377)]
d、把 date 转换为 string：使用 cast 强制转换
七、表生成函数
：依靠函数就能生成一张表。平常是 select 可以生成一个表
1、行转列函数：explode（）
eg：如果有以下数据，表示每个人选修个课程
1，zhangsna，化学：物理：数学：语文
2，lisi，化学：数学：生物：生理卫生
3，wangwu，化学：语文：英语：体育：生物
将上面的数据映射为一张表，查询数据如下图
create table t_stu_subject（id int，name string，subjects array<string>）
row format delimited fields teminated by ","
collection items terminated by ":";
[图片上传失败...(image-4ced22-1546767417377)]
要求所有人选的课程里面去重后的结果，就是要求有多少个课程
使用 explode（）对数组字段‘炸裂’（把结构化的数据打散），结果如下图，每一个数组都炸成若干行
select explode（subjects）from t_stu_subject；
[图片上传失败...(image-52fc6b-1546767417377)]
根据上面的查询结果，就可以做去重的操作了，使用子查询
select distinct tmp.sub from
（select explode（subjects）as sub from t_stu_subject）tmp ；
2、lateral view（翻译：横向视图）
要求实现炸裂的每一条课程可以和对应的学生信息连接起来
注意 explode（subjects）是一个表，lateral view 是取横向连接两个表
tmp 是对炸裂后的表的重命名（跟在表后面是重命名），并且把炸裂后的字段命名为 sub ，子查询必须要取别名，所有有tmp。sub 可以省略但是新列就没有名字。
（这个写法挺奇怪的，在重命名表名后又重命名字段，可能要特殊记忆）
select id，name，tmp.sub
from t_stu_subject lateral view explode( subjects ) tmp as sub；
[图片上传失败...(image-562192-1546767417377)]
注意：这样炸裂后拼接，有利于后期的一些计算。
练习题：有一个文本文件如下，要求出每个单词出现的次数？
[图片上传失败...(image-dc64cd-1546767417377)]
思路：先建表映射上面的数据，使用 array 结构体类型，然后炸裂，再根据炸裂表分组查询统计
先建表映射数据，一行看做一个句子，所以只需要一个字段
create table t_wc( sentence string );
[图片上传失败...(image-98b278-1546767417377)]
切词，使用空格来切分，得到一个数组
select split（sentence," "）from t_wc；
[图片上传失败...(image-5d40df-1546767417377)]
把结构体类型 数组 炸裂，然后分组查询
并按照字符数量，倒序列出结果，如下图
select word，count（1）as cnts
from（select explode（split（sentence，" "））as word from t_wc）tmp
group by word
order by cnts desc; （这里可以使用查询结果排序，但是不能group by）
[图片上传失败...(image-c0afd2-1546767417377)]
select
b.b_movie,
b.b_rate,
b.b_timeStamp,
b.b_uid
from json a
lateral view json_tuple(a.data,'movie','rate','timeStamp','uid') b as b_movie,b_rate,b_timeStamp,b_uid;
