1.函数分类
UDF 单进单出函数
UDAF 多进单出函数(比如Count,Sum...多个记录输出一个结果)
UDTF 单进多出函数(比如explode,一个记录输出多行结果)
2.操作符
值相等
Equals(两个同时为Null为真,只有一个Null为假,否则Equals)
不等于(同Null为假,只有一个NUll为真,否则Equals取反)
[B,C]A是否在B,C之间(包含边界B,C)
A是否为Null
同MySQL Like操作符
模糊匹配(正则)
A除以B的整数部分(小数直接摄取)
3.复杂对象
用指定的键值创建一个Map对象
用指定的值创建一个struct对象,字段名依次为col1.col2.....
用指定的字段名,字段值创建一个struct对象
用指定的值创建一个array对象
使用标记参数指向的值创建一个联合类型
获取数组对象A下标为n的值
获取键值对对象M的键为key的值
获取结构体S的字段x的值
返回Map或数组的元素个数
返回Map的键数组对象
返回Map的值数组对象
返回Map是否包含指定的值
数组排序(升序)
4.UDF函数
4.1 数学函数
返回a的四舍五入的值
返回a四舍五入保留d位小数的值
返回a的高斯四舍五入的值
返回a向上取整的值
返回a向下取整的值
返回一个随机数
返回指定值中最大的值
返回指定值中最小的值
4.2 类型转换函数
将 expr转换为<type>类型
4.3 日期函数
将一个时间戳转换为指定格式的字符串,默认(yyyy-MM-dd)
获取当前时间时间戳(秒)
将一个pattern格式(默认yyyy-MM-dd hh:mm:ss)的字符串转换为时间戳
将一个时间戳转换为yyyy-MM-dd
返回日期、时间戳或范围为1到4的字符串的季度
返回字符串日期的年 year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970
返回字符串日期的月
返回字符串日期的天
返回字符串日期的小时
返回字符串日期的分钟
返回字符串日期的秒
返回字符串日期的星期几
从时间source中提取属性信息
results in 10.
results in 5.
results in 5.
results in 3.
results in 20.
返回两个日期差异的天数(记住第一个参数是结束日期,第二个参数是开始日期)
返回指定日期加days天的日期
返回指定日期减去days天的日期
返回当前日期
返回当前日期时间戳
4.4 条件函数
如果testCondition表达式结果为真返回valueTrue,否则返回valueFalseOrNull
返回a是否为Null
返回a是否不会Null
如果value不为Null则返回value,否则返回default_value
如果a为真返回b 否则c
4.5 字符串函数
返回字符串在UTF8编码下的长度
多个字符串拼接
多个字符串拼接,中间用SEP连接
字符串用pa分割成多段数组
字符串从start之后截取[len位], 从1开始, substr('foobar', 4) = 'bar'
6. UDTF函数
UDTF函数语法限制:
使用UDTF函数,不能再跟上其它的表达式 (禁止 UDTF(),其它列或表达式)
UDTF函数不能再嵌套使用其它的UDTF函数
UDTF函数不能再使用GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY
常用UDTF函数
将一个数组对象转换为多行记录,每行记录一列
explode(MAP<Tkey,Tvalue> m) 将一个Map对象转换为多行记录,每行记录有两列,[key,value]
将一个数组对象转换为多行记录.每行记录有两列,[数组下标,值]
inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a) 将一个数组结构体对象转换为多行记录,每行记录的列对应结构体的属性
stack(int r,T1 V1,...,Tn/r Vn)
json_tuple(string jsonStr,string k1,...,string kn) 将一个Json字符串转换为多行数据,每行记录的列来自后续列指定k1...kn(只有后续指定的列才会读取)
parse_url_tuple(string urlStr,string p1,...,string pn) 将一个URL地址字符串解析成单行多列信息
parse_url_tuple('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1','HOST', 'PATH', 'QUERY', 'QUERY:k1', 'QUERY:k2')
facebook.com /path1/p.php k1=v1&k2=v2 v1 v27. UDAF函数
sum() count()......
更多函数见官网 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
8.开窗函数
8.1 OVER 字句
OVER 字句,可以使用在所有的标准聚合函数中(COUNT,SUM,MIN,MAX,AVG),或者特有分析函数(RANK|ROW_NUMBER.....),并且在2.1.0之后,支持DISTINCT关键字和Over字句内嵌套标准聚合函数
[COUNT|SUM|MIN|MAX|AVG|......] OVER ( PARTITION BY <字段A....> ORDER BY <字段A....> [DESC])
PARTITION BY 字段,表示按字段分组 后续 ORDER BY 字段 表示分组内按字段排序
8.2 示例
COUNT(DISTINCT a) OVER (PARTITION BY c) 按c分组后,在每个分组内对a执行去重计数
select* from (
select name,dept,age row_number() over (patition by dept order by age) as idx from employee
) as t where t.idx <=2 查询每个部门的成员中年级最小的两位
8.自定义函数
8.1 函数的定义
所有Hive的函数都是继承UDF类的Java子类.自定义函数就是实现UDF类,并将jar注册到Hive的过程.
1.Maven导入 hive-exec hadoop-common,
2.定义一个类继承UDF,并实现需要的evaluate方法
8.2 注册到Hive
注意函数名注册最好才采用全小写+_形式,因为函数名会全部转为小写,会失去驼峰
8.2.1 临时函数
临时函数随会话相关,所以创建的函数可以用在任意数据库中,但会话切换就会立即失败
8.2.1.1 jar注册
临时注册 add jar jar本地路径
永久注册 将jar拷贝到Hive的lib文件夹(需要重启hive)
8.2.1.2 临时函数注册
CREATE TEMPORARY FUNCTION function_name AS "class_name(类的全名:包+类名)";
8.2.2 永久函数注册
永久函数随数据库相关,作为数据库元数据的一部分,所以切换会话仍可使用,但必须显示声明所处的数据库
永久函数的Jar文件是必须放入HDFS的.
CREATE FUNCTION [db_name.]function_name AS class_name [USING JAR|FILE|ARCHIVE 'file_uri' [, JAR|FILE|ARCHIVE 'file_uri'] ];
8.3 函数的删除
DROP [TEMPORARY] FUNCTION [IF EXISTS] function_name;
8.4 函数的注册机制以及自动注册
Hive的函数注册其实是FunctionRegistry类的结果.
默认函数也是要基于同样的注册流程.之所以默认函数不注册,是因为FunctionRegistry的静态方法实现了默认函数的自动注册
如果需要实现自动注册,照此执行即可(需要编辑源码重新编译Hive哦)
