目录
一、hive函数的使用
1.hive函数分类
2.字符函数
3.类型转换函数
4.数学函数
5.日期函数
6.集合函数
7.条件函数
8.聚合函数
9.表生成函数：输出可以作为表使用
二、UDF函数
1.先创建maven项目
2.继承UDF并重写evaluate()方法
3.生成jar包，并导入到linux系统目录下
4.打开hive
一、hive函数的使用
1.hive函数分类
（1）从输入输出角度分类
标准函数：一行数据中的一列或多列为输入，结果为单一值
聚合函数：多行的零列到多列为输入，结果为单一值
表生成函数：零个或多个输入，结果为多列或多行
（2）从实现方式分类
内置函数：输入 show functions展现内置函数
desc function extended 函数名:可详细展示函数的源码
标准函数：字符函数、类型转换函数、数学函数、日期函数、集合函数、条件函数
聚合函数
表生成函数
自定义函数：UDF：自定义标准函数，UDAF：自定义聚合函数，UDTF：自定义表生成函数
2.字符函数
返回值 | 函数 | 描述 |
int | ascii(string str) | 将字符转成ascii码 |
stirng | base64(binary bin) | 返回二进制 bin 的 base 编码字符串 |
string | concat(string|binary A, string|binary B...) | 对二进制字节码或字符串按次序进行拼接 |
string | concat_ws(string SEP, string A, string B...) concat_ws(string SEP, array<string>) | 返回输入字符串连接后的结果,SEP 表示各个字符串间的分隔符 返回将数组链接成字符串后的结果,SEP 表示各个字符串间的分隔符 |
string | format_number(number x, int d) | 将数值 x 的小数位格式化成 d 位,四舍五入 |
int | instr(string str, string substr) | 查找字符串str中子字符串substr出现的位置 |
int | length(string A) | 返回字符串的长度 |
int | locate(string substr, string str[, int pos]) | 查找字符串str中的pos位置后字符串substr第一次出现的位置 |
string | printf(String format, Obj... args) | 将指定对象用 format 格式进行格式化 |
map<String,string> | str_to_map(text[, delimiter1, delimiter2]) | 将字符串按照给定的分隔符转换成 map 结构. |
binary | unbase64(string str) | 将给定的 base64 字符串解码成二进制. |
string | upper(string A) ucase(string A) | 返回字符串 A 的大写格式 |
string | lower(string A) lcase(string A) | 返回字符串 A 的小写格式 |
string | regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) | 按正则表达式PATTERN将字符串中符合条件的部分替换成REPLACEMENT所指定的字符串 |
string | regexp_extract(string subject, string pattern, intindex) | 将字符串 subject 按照 pattern 正则表达式的规则拆分,返回 index 指定的字符。 |
array | split(string str, string pat) | 按照正则表达式pat来分割字符串str |
string | substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) | 对字符串A,从start位置开始截取长度为len的字符串并返回 |
string | trim(string A) | 将字符串A前后出现的空格去掉 |
string | ltrim(string A)/rtrim(string A) | 去除字符串左边/右边的空格 |
binary | encode(string src, string charset) | 用指定字符集charset将字符串编码成二进制值 |
代码如下：
select ascii('a');
+------+--+
| _c0 |
+------+--+
| 97 |
+------+--+
select base64(binary("abc"));
+-------+--+
| _c0 |
+-------+--+
| YWJj |
+-------+--+
select concat("abc","def","gh");
+-----------+--+
| _c0 |
+-----------+--+
| abcdefgh |
+-----------+--+
select concat_ws(',','abc','def','gh');
+-------------+--+
| _c0 |
+-------------+--+
| abc,def,gh |
+-------------+--+
select concat_ws('|',array('a','b','c'));
+--------+--+
| _c0 |
+--------+--+
| a|b|c |
+--------+--+
select dept_num,name from employee_contract limit 10;
+-----------+----------+--+
| dept_num | name |
+-----------+----------+--+
| 1000 | Michael |
| 1000 | Will |
| 1000 | Wendy |
| 1000 | Steven |
| 1000 | Lucy |
| 1001 | Lily |
| 1001 | Jess |
| 1001 | Mike |
| 1002 | Wei |
| 1002 | Yun |
+-----------+----------+--+
列转行：
select dept_num,concat_ws(',',collect_set(name))as con_name from employee_contract group by dept_num;
+-----------+---------------------------------+--+
| dept_num | con_name |
+-----------+---------------------------------+--+
| 1000 | Michael,Will,Wendy,Steven,Lucy |
| 1001 | Lily,Jess,Mike |
| 1002 | Wei,Yun,Richard |
+-----------+---------------------------------+--+
select format_number(3.12312312,6);
+-----------+--+
| _c0 |
+-----------+--+
| 3.123123 |
+-----------+--+
select instr('abcdef','def');
+------+--+
| _c0 |
+------+--+
| 4 |
+------+--+
select length('abc');
+------+--+
| _c0 |
+------+--+
| 3 |
+------+--+
select locate('a','abcda');
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
select printf("%08x",123);
+-----------+--+
| _c0 |
+-----------+--+
| 0000007b |
+-----------+--+
3.类型转换函数
返回值 | 类型转换函数 | 描述 |
"type" | cast(expr as <type>) | 将expr转换成type类型 如：cast("1" as BIGINT) 将字符串1转换成了BIGINT类型 |
binary | binary(string|binary) | 将输入的值转换成二进制 |
4.数学函数
返回值 | 数学函数 | 描述 |
DOUBLE | round(DOUBLE a) | 返回对a四舍五入的BIGINT值 |
binary | round(DOUBLE a, INT d) | 返回对a四舍五入并保留d位小数位的值 |
BIGINT | floor(DOUBLE a) | 向下取整，如：6.10->6 -3.4->-4 |
DOUBLE | rand(INT seed) | 返回一个DOUBLE型随机数，seed是随机因子 |
DOUBLE | power(DOUBLE a, DOUBLE p) | 计算a的p次幂 |
DOUBLE | abs(DOUBLE a) | 计算a的绝对值 |
5.日期函数
返回值 | 函数 | 描述 |
string | from_unixtime(bigint unixtime[, string format]) | 将时间戳转换成format格式 |
int | unix_timestamp() | 获取本地时区下的时间戳 |
bigint | unix_timestamp(string date) | 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳 |
string | to_date(string timestamp) | 返回时间字符串的日期部分 |
int | year(string date) month/day/hour/minute/second/weekofyear | 返回时间字符串的年份部分 返回月/天/时/分/秒/第几周 |
int | datediff(string enddate, string startdate) | 计算开始时间到结束时间相差的天数 |
string | date_add(string startdate, int days) | 从开始时间startdate加上days |
string | date_sub(string startdate, int days) | 从开始时间startdate减去days |
date | current_date | 返回当前时间的日期 |
timestamp | current_timestamp | 返回当前时间戳 |
string | date_format(date/timestamp/string ts, string fmt) | 按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22 |
6.集合函数
返回值 | 函数 | 描述 |
int | size(Map<K.V>) | 返回map中键值对个数 |
int | size(Array<T>) | 返回数组的长度 |
array<K> | map_keys(Map<K.V>) | 返回map中的所有key |
array<V> | map_values(Map<K.V>) | 返回map中的所有value |
boolean | array_contains(Array<T>, value) | 如该数组Array<T>包含value返回true。，否则返回false |
array | sort_array(Array<T>) | 对数组进行排序 |
7.条件函数
返回值 | 函数 | 描述 |
T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull |
T | nvl(T value, T default_value) | value为NULL返回default_value,否则返回value |
T | COALESCE(T v1, T v2, ...) | 返回第一非null的值，如果全部都为NULL就返回NULL |
T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 如果a=b就返回c,a=d就返回e，否则返回f |
T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 如果a=ture就返回b,c= ture就返回d,否则返回e |
boolean | isnull( a ) | 如果a为null就返回true，否则返回false |
boolean | isnotnull ( a ) | 如果a为非null就返回true，否则返回false |
8.聚合函数
count(),sum(),max(),min(),avg(),var_samp()等
9.表生成函数：输出可以作为表使用
返回值 | 函数 | 描述 |
N rows | explode(array<T>) | 对于array中的每个元素生成一行且包含该元素 |
N rows | explode(MAP) | 每行对应每个map键值对 其中一个字段是map的键，另一个字段是map的值 |
N rows | posexplode(ARRAY) | 与explode类似，不同的是还返回各元素在数组中的位置 |
N rows | stack(INT n, v_1, v_2, ..., v_k) | 把k列转换成n行，每行有k/n个字段，其中n必须是常数 |
tuple | json_tuple(jsonStr, k1, k2, ...) | 从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值 |
二、UDF函数
1.先创建maven项目
搜索quick，点击next
将mave.xml文件进行修改
结果如下：
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
2.继承UDF并重写evaluate()方法
这是将小写转变为大写的方法
注意：因为是重写，所以不可以将方法名修改
public class TestUDF {
public Text evaluate(Text str){
if(null==str){
return null;
}
return new Text(str.toString().toUpperCase());
当然也可以查看，自己写的方法是否正确
3.生成jar包，并导入到linux系统目录下
放入后在linux下输入以下命令：
zip -d jar包名 'META-NEF/.SF' 'META-INF/.RSA' 'META-INF/*SF'
4.打开hive
使用UDF可以两种方法
（1）创建临时UDF函数
先将jar包导入进来
add jar '完整的包类名'
注：每次退出hive的时候，都要重新添加jar包
再创建临时方法：
create temporary function 方法名 as '完整包类名'
使用方法：
select demo1('a');
（2）创建永久UDF函数
将jar包上传到hdfs上
create function 方法名 as '完整的包类名' using jar 'hdfs:\\主机名或者本机ip:9000/jar包hdfs上的路径'
使用方法：
注意：1.尽量使用主机地址，用主机名容易报错
2.如果出错，尽量退出hive重试