Hive Day04
hive的函数
内置函数
show functions
desc function f_name
desc function extended f_name
数值类型
round(需要处理的数据，[位数])
floor 向下取整
ceil 向上取整
rand([seed]) seed是随机数种子
字符串
字符串截取
字符串的起始下标从1开始；从左侧向右侧
字符串也可以从右向左访问；下标从-1开始的
substr（需要处理的字符串，起始下标，截取长度）
substring
字符串拼接
concat
concat_ws
select concat("www","facebook.com")；
select concat_ws("-","2018","11","12");
字符串切分 --- 返回的类型是数组类型
是获取数组类型的一种方式 ；
split（字符串，切分条件-正则表达式）
字符串查找
用于判断某一个字段只能够是否包含关键字
instr(str,substr)
存在则返回子字符串第一个字符所在的位置；
不存在，则返回0；
字符串替换
replace(需要处理的字符串，需要替换的字符串，替换后的字符串)
if(判断条件，返回值1，返回值2) # 三目表达式;通常用于处理null的场景；
select if(names[2] is not null,names[2],"dlreba") from test_array;
nvl # 专门用来处理null值的
nvl(查询的字段,默认值)；
如果第一个参数为null，则返回第二个参数的值；
如果不为null；则返回第一个参数；
cast # 类型转换
cast(需要处理的数据 as 处理成的类型)
select cast("1" as int);
length # 字符串长度
集合生成函数：
array
array # 数组生成函数
array_contains # 判断数组中是否存在该元素；
map
map 映射生成函数，参数必须是偶数个，奇数位置的为key，偶数位置的为value;
日期处理函数
unix_timestamp（data[,pattern]） # 生成时间戳
将给定的日期转换为时间戳
current_timestamp() 获取当前系统的时间戳
unix_timestamp() 获取当前系统的时间戳
select unix_timestamp("2018-11-11"."yyyy-MM-dd");
from_unixtime(unix_time,format)；时间戳转日期
select from_unixtime(123141431，"yyyy-MM-dd HH-mm-ss");
year # 取给定的日期或时间戳的年份
month # 取月
day # 取日期的
hour # 取小时的
weekofyear # 取一年的中的第几周
表生成函数 explode
# 进一路 出多路
explode(array|map)
- 将数组的多个元素炸裂到多行，最终每一个元素放在一行
每行只有一列
[1,2,3]
1
2
3
- 将map集合的元素炸裂到多行，每一个元素一行
每一行有两列
{1:2,3:4}
1 2
3 4
解决：
当explode与其他字段一起查询的时候：
select id，v.* from test_array lateral view explode(names) v;
select id,v.vk from test_map lateral view explode(family) v as vk,vv;
虚拟试图的名字是v
explode函数和表中的普通字段一起查询的时候，一定要注意将explode炸裂的函数放在一个虚拟的lateral view中，在从这个lateral view中取字段；
分析函数
数据统计分析的时候用的；
row_number添加行号；必须与over子句一起用；over子句用于添加规则的
语法
row_number() over(分组条件 排序条件)
over子句中可以放两种形式，
distrbute by 指定分桶 sort by 指定排序；
partition by 指定分桶 order by 指定排序；
在每一组中进行顺序添加行号；
案例：
学生信息表的每一个部门中年龄最大的前2个；
分组：部门；排序：年龄
# 错误语句：
select * from student_test group by department order by age limit 2;
# 正确语句：
select *,row_number() over(distribute by deparment sort by age desc) from student_test ;
执行： 先按照指定的分桶（分区）规则进行分桶/分区，在再每一个分桶/分区按照指定的排序规则进行排序；最后在每一个桶中添加行号
结果：
95006 孙庆 男 23 CS 1
95013 冯伟 男 21 CS 2
95001 李勇 男 20 CS 3
95012 孙花 女 20 CS 4
95014 王小丽 女 19 CS 5
95010 孔小涛 男 19 CS 6
95008 李娜 女 18 CS 7
95020 赵钱 男 21 IS 1
95002 刘晨 女 19 IS 2
95004 张立 男 19 IS 3
95019 邢小丽 女 19 IS 4
95018 王一 女 19 IS 5
95017 王风娟 女 18 IS 6
95003 王敏 女 22 MA 1
95022 郑明 男 20 MA 2
95007 易思玲 女 19 MA 3
95015 王君 男 18 MA 4
95011 包小柏 男 18 MA 5
95009 梦圆圆 女 18 MA 6
95005 刘刚 男 18 MA 7
95021 周二 男 17 MA 8
# 最终的语句
select * from (select *,row_number() over(distribute by deparment sort by age desc) from student_test ) t where index <=2;
应用场景：分组求topN
添加排名函数
rank
dense_rank
也要over子句一起用；
over子句指定分桶/ 分区的依据，指定排序的依据；
select *,rank() over(distribute by deparment sort by age desc) from student_test;
排名的时候将并列的进行直接累加跳过；
结果： 排名的时候将并列的进行直接累加跳过
95006 孙庆 男 23 CS 1
95013 冯伟 男 21 CS 2
95001 李勇 男 20 CS 3
95012 孙花 女 20 CS 3
95014 王小丽 女 19 CS 5
95010 孔小涛 男 19 CS 5
95008 李娜 女 18 CS 7
select *,dense_rank() over(distribute by deparment sort by age desc) from student_test;
循序添加排名的，有并列的不跳过排序序号；
结果：顺序添加排名的 有并列的不进行排名名次累加的
95006 孙庆 男 23 CS 1
95013 冯伟 男 21 CS 2
95001 李勇 男 20 CS 3
95012 孙花 女 20 CS 3
95014 王小丽 女 19 CS 4
95010 孔小涛 男 19 CS 4
95008 李娜 女 18 CS 5
hive中271个内置函数
当内置函数无法满足业务需要的时候，需要自定函数
自定义函数
java语言
UDF 进一路出一路
UDAF 聚合函数
UDTF 不定义
步骤
- 创建工程 导入依赖包
- 创建类，继承UDF类
- 实现一个或多个名字为evalues方法；evaluate方法是会被hive底层执行器和解析器调用到；evaluate的返回值和参数是根据实际的业务需求自己定义的；参数：函数调用的传入的参数 返回值：函数调用完成后返回的值；
round函数：底层也是evaluate方法，参数类型double；返回值是 int类型，
package com.ghgj.cn.tesUDF;
import org.apache.hadoop.hive.ql.exec.UDF;
public class MyUDF extends UDF {
/*
* 求3个数的和
* 参数： 3个int
* 返回值 int
*/
public int evaluate(int a,int b,int c ){
return a+b+c;
}
}
- 定义完成之后，将UDF的代码打成JAR包，上传到服务器；
- 将jar包添加到hive的classpath下；
- 在hive的客户端执行
0: jdbc:hive2://> add jar /home/hadoop/MyUDF.jar
. . . . . . . . > ;
Added [/home/hadoop/MyUDF.jar] to class path
Added resources: [/home/hadoop/MyUDF.jar]
No rows affected (0.346 seconds)
```
-
验证： list jar/jars
0: jdbc:hive2://> list jar . . . . . . . . > ; +-------------------------+ | resource | +-------------------------+ | /home/hadoop/MyUDF.jar | +-------------------------+ 1 row selected (0.076 seconds) 0: jdbc:hive2://> list jars; +-------------------------+ | resource | +-------------------------+ | /home/hadoop/MyUDF.jar | +-------------------------+ 1 row selected (0.024 seconds) -
创建一个临时函数关联UDF类
create temparory function 函数名 as "类全路径名" 0: jdbc:hive2://> create temporary function three_add as "com.ghgj.cn.tesUDF.MyUDF";
OK
No rows affected (0.316 seconds)
```
- 验证自定义函数
show functions; 0: jdbc:hive2://> select three_add(1,3,4);
OK
+------+
| _c0 |
+------+
| 8 |
+------+
1 row selected (6.177 seconds)
0: jdbc:hive2://>
```
使用
当使用这个自定义函数的时候，本质调用com.ghfj.n.tesUDF.MyUDF下的evaluate的方法；
注意：
- 临时函数的作用域只对当前客户端生效；
当前客户端退出临时函数就删除了；
再次进入客户端，如果还想用函数就需要重新添加；重新操作上面的第5步开始； - 同个函数可以写多个evaluate函数的重载；
在调用的时候，根据传入的参数的不同的evaluate方法的； - 写evaluate方法的时候注意
- 方法必须是public的
- 返回值不能为void的
json解析
hive中接触的数据有很多都是json格式的数据
json格式类似于java中的类的结构；或者是对象；
{属性：值，属性：值}
web 前后台之间数据传输的时候就是使用json格式；
json格式数据的解析方式：
- 自定义函数进行解析
- 内置函数进行解析
get_json_object（json_txt,path）
json_tuept 容易丢失数据，不常用
用法：
参数1：需要解析的json串
参数2：需要解析的jsoin
返回值：返回的是查询的路径对应的值；
json串的目录结构：
从最外层的结构叫做json的根目录，使用$表示；代表根目录的对象
属性1叫做根目录的子目录，取的时候用.,
$.属性
$.属性[0]
$.属性.属性
支持的路径的表达式：
$ : Root object 根目录
. : Child operator 字目录
[] : Subscript operator for array 属性对应的值如果是数组的时候，用于去下标，取到数组中对应的值；
* : Wildcard for [] 所有的值；
Syntax not supported that's worth noticing:
'' : Zero length string as key
.. : Recursive descent
&#064; : Current object/element
() : Script expression
?() : Filter (script) expression.
[,] : Union operator
[start:end:step] : array slice operator
取rate对应的值的路径：$.content[].rate
select get_json_object('{"content":[{"movie":"1193","rate":"5","timeStamp":"978300760","uid":"1"}]}',
'$.content[0].rate');
多字节分隔符
hive中的数据的分隔符默认都是单字节形式的；
但是在实际中采集的数据有多字节分割的数据；
1::zs::23
多字节建表：
create table test01（id int，name sting ,age int）
row format delimited fields terminated by "::"；
加载完数据查询：数据不正常
原因：定义表的时候，虽然定义了多字节的分隔符，默认类中不能识别，只能识别单字节的分隔符；实际分割的时候是按照:分割；
分割完成： 1，空，zs，空，23
根据数据类型判断 zs不能转为int所以就是null；
解决：
-
将多字节分隔符全部替换为单字节分隔符；这种方式替换的时候要求必须足够了解数据；防止替换的单字节在数据中不是分隔符；
-
可以修改源码，将源码中的单字节，替换为多字节分隔符；不可取；一般不用；
-
采用自定义的输入输出的格式解决多字节分隔符；
在建表的时候，SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat ``` 输入：正则表达式 (.*)::(.*)::(.*) 输出：在正则表达式中取 取正则表达式的第一组，第二组 ```bash 1::asdf:asdf::23 2::asdf:asdf::23 3::asdf:asdf::23解决：
解析类：正则表达式解析
输入类：正则表达式输入
输出类：取create table test02(id int,name string,age int) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties('input.regex'='(.*)::(.*)::(.*)', 'output.format.string'='%1$s %2$s %3$s') stored as textfile;serde:指定解析类 org.apache.hadoop.hive.serde2.RegexSerDe with serdeproperties 指定解析属性的 input.regex 指定输入的正则表达式的 output.format.string 指定需要取的组 $s 没有意义；就是占位符；
常用第三种方式；
transform的方式
hive解析脚本的方式
案例：
test_json中求周一到周日期间那一天的评分人数最多；
将数据评分时间的字段--> 周几
- 自定义函数
- 内置函数
- 用脚本方式 - python脚本
python 脚本
#!/usr/bin/python
import sys
import datetime
for line in sys.stdin://取每一条数据
line = line.strip()
movie,rate,unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movie, rate, str(weekday),userid])
脚本文件编辑完；
- 将脚本文件加载在hive的classpath下
add file /home/hadoop/tmpdata/my.py - 验证
list filesselect transform(movie,rate,time01,uid)
using 'python my.py' as (movie,rate,weekday,userid)
from test_json where movie is not null ;
```
3. 插入数据，transform指定需要使用脚本进行解析；参数是需要解析的字段
浙公网安备 33010602011771号