常用Hive函数及自定义UDF函数创建

参考:  Hive常用函数总结

1. 字符串相关    

1.1 字符串替换

select regexp_replace('\n123\n','\n','');
select translate("MOBIN","BIN","M");
-- MOM

1.2 查找子串位置

1. 集合查找函数:  find_in_set
返回以逗号分隔的字符串中str第一次出现的位置，未找到则返回0，如果任一参数为NULL将返回NULL。
注意：这里位置从1开始的！！！

select find_in_set('aa','aa,bb,cc');
-- 1
select find_in_set('aa','bb,cc');
-- 0

# instr()返回一个整数值，表示被搜索字符串在搜索字符串中的起始位置；>0则为包含
select instr('aba', 'b');
-- 2  

2. 查找字符串str中的pos位置后字符串substr第一次出现的位置

locate(string substr, string str[, int pos])

1.3 判断一个字符串是否包含另一个字符串

1. like

% 是 LIKE 运算符中的通配符，表示可以匹配任意字符。

select '_cat_' LIKE '%cat%';
-- true

2. REGEXP

regexp 运算符来支持正则表达式匹配

select '_cat_' REGEXP 'cat';
-- true

1.4 取子串

1. 返回从指定位置开始(从1开始编码)，到最后的子串

select substr('abcde',3);
-- cde
select substr('abcde',0);
-- abcde
select substr('abcde',1);
-- abcde
select substr('abcde',-1);
-- e
select substr('abcde',-2);
-- de

2. 返回从指定位置开始，长度为len的字符串

-- 截取除后两位之外的子串
select substr('abcde--',1,length('abcde--')-2);
-- abcde

3. 截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取

substring_index(string A, string delim, int count)

 2. 时间相关

 2.1  时间戳与日期时间 互转

-- 获得当前时间戳
select unix_timestamp();
--  1604973490

-- 时间戳--->日期时间（可以指定格式）
select from_unixtime(1604973490,'yyyy/MM/dd HH:mm:ss');
-- 2020/11/10 09:58:10

-- 日期时间--->时间戳：默认格式为“yyyy-MM-dd HH:mm:ss“：
select unix_timestamp('2020-11-10 09:58:10');
select unix_timestamp('20201110 09:58:10','yyyyMMdd HH:mm:ss');  
--  1604973490

2. 2 日期时间--->日期

select to_date('2020-10-08 10:03:01');
--  2020-10-08
-- 日期时间--->年/月/日/时/分/秒/日期在当前的周数:   
year(string date)，month(),day(),hour(),minute(),second(),weekofyear()。

select year('2020-12-08 10:03:01');
--  2020
select second('2020-12-08 10:03:01');
--  1

2.3 计算日期差

datediff 函数支持以下两种日期格式：

（1）yyyy-MM-dd HH:mm:ss 

（2）yyyy-MM-dd

-- 获取用户注册日期与当前时间的之间差（day）
select datediff(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'), '2021-07-10');  -- 注册时长

select datediff('2021-06-04', '2021-06-01');
-- 3

2.4 日期加减

-- 指定日期增加days天后的日期
select date_add('2012-12-08', 10);
-- 指定日期减少days天后的日期
select date_sub('2012-12-08', 10);
select date_add('2012-12-08', -10);

-- 获取当前时间
select current_date;

3. 逻辑运算

3.1 除法取余

-- 返回a除b的余数的绝对值
pmod(int a, int b)
pmod(double a, double b)
-- 选取uid倒数第二位数字是偶数的数据
select *
from zz.table_name
where pmod(int(substr(uid,-2,1)),2)=0

3.2 平均数与分位数

-- 平均数
select avg(a.fuid_cnt);
-- 中位数（可以换成各分位数）
select percentile(cast(chat_7d_cnt as int),0.5) as chat_7d_cnt_avg;
select percentile_approx(cast(total_7d_minutes as double),0.5) as total_7d_minutes_avg;

4. 数组相关

1. 判断Array类型字段，是否包含某值

array_contains(businesstype, 'SDK')
if(ARRAY_CONTAINS(split(event_type, ','), '2'),1,0)

2. 取数组最值

-- 取数组生序，第top3元素（下标为2表示取由小到大的第三个元素）。
sort_array(array(a.cnt1,a.cnt2,a.cnt3))[2]
sort_array(array(3,10,null))[2]
或
select greatest(10,null,1);

-- 最小值
select least(-1, 0, 5, 8)
--  -1

3. 数组长度

select size(array(3,10,null));
-- 3

5. 字典相关

1. 使用两个分隔符将文本拆分为键值对
str_to_map(字符串参数, 分隔符1, 分隔符2)
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ','，对于分隔符2默认分隔符是 '='。

在table表中主键是dt+uid被储存的值是客户的状态。
现需要对客户状态进行统计。

select str_to_map(concat_ws(',',collect_set(concat(dt,':',label))),',',':')
from table
group by uid;

-- select str_to_map(concat_ws(',',collect_set(concat('zz',':','22'))),',',':')['zz'];

6. 创建自定义UDF

一般，自己可以创建一个Hive Helper Libaray，包含工作中常用的hive udf。

目的： 对于大部分日常分析工作而言，Hive是学习成本最低，使用最为广泛的工具。虽然Hive自带了很多常规的函数，但是和业务相关的功能是没有的。

        为了解决这一问题，Hive提供了UDF、UDAF、UDTF这些功能，让我们可以很方便的将业务逻辑封装为Hive函数。我们希望在这个项目下维护这些功能，不断扩充Hive工具，提升效率。

6.1 udf使用方法

1. 将代码打成jar包

2. jar包上传到hdfs

dfs -put -f hive-helper-lib.jar /user/zz/jars/

3. 在hive 中使用add jar 创建临时函数

-- 加载相应的jar文件
add jar hdfs://nswx/user/adst/jars/hive-helper-lib.jar; 
-- 创建临时函数
create temporary function get_bucket as 'com.zz.hive.udf.MurmurhashBucket'; 
select imei, get_bucket(imei, 20) as bucket_id from zz.tmp_imei_mac_inview_m limit 1;

6.2 编写皮尔逊平滑udf

//import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
//import org.apache.hadoop.hive.ql.metadata.HiveException;
//import org.apache.hadoop.hive.ql.parse.SemanticException;
//import org.apache.hadoop.hive.ql.udf.generic.AbstractGenericUDAFResolver;
//import org.apache.hadoop.hive.ql.udf.generic.GenericUDAFEvaluator;
//import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
//import org.apache.hadoop.io.BytesWritable;
//import org.apache.hadoop.hive.serde2.objectinspector.*;
//import org.apache.hadoop.hive.serde2.typeinfo.TypeInfo;

import org.apache.hadoop.hive.ql.exec.UDF;
public class GenericUDAFComputeRate extends UDF {
    public double evaluate(int a, int b) {
        double p_z = 2.0;
        double pos_rat = 0.0;
        double score = 0.0;
        if(b == 0)
            b+=1;
        if(a > b)
            a = b;
        try {
            pos_rat = a * 1.0 / b * 1.0;
            score = ((pos_rat + Math.pow(p_z, 2) / (2.0 * b))
                    - ((p_z / (2.0 * b)) * Math.pow(4.0 * b * (1.0 - pos_rat) * pos_rat + Math.pow(p_z, 2), 0.5)))
                    / (1.0 + Math.pow(p_z, 2) / b);
        } catch (Exception e){
            e.printStackTrace();
            return score;
        }
        return score;
    }
}