Hive函数

骑猪看日落 2021-09-23 07:32 399阅读 0赞

## 1. 内置函数 ##

show functions;                   // 查看内置函数
    desc function 函数名;              // 查看函数描述信息
    desc function extended 函数名;     // 查看函数详细信息

### 1.UDF ###

UDF（user defined function） 自定义函数，一对一，进一条数据解析出来一条。

sleect substring("hello world",0);         // hello world 位置下标
    sleect substring("hello world",2);         // ello world
    select substring("hello world",-2)         // ld 从右开始
    select substring("hello world",-5,2)       // wo 从右开始截取几位
    select instr("nihao","ha");                // 3 后面字符串在前面字符串中出现的下标,没有返回0
    select concat("ni","hao","a");             // 拼接 nihaoa
    select concat_ws('-',"ni","hao","a");      // ni-hao-a 第一个是分隔符
    select array(1,2,3,4,5);                        // [1,2,3,4,5]
    select concat_ws('-',array('rr','tt','yy'));    // rr-tt-yy
    select map(6,5,4,3,2,1);                        // {6:5,4:3,2:1}
    nvl(value, default_value);                      // 如果参数1为null,返回参数2的值
    
    select if(2>3,3,6);                              // 6
    if(address[3] is null,'zhongguo',address[3])

### 2. UDAF ###

UDAF（user defined aggregation function） 聚合函数，多条变一条，如 sum、avg、count。

**explode**

> explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns   
> 将数组分成多个行1列，或将map分成多个行和2个列，参数是array或map。  
> 同时查询炸裂字段和普通字段时，需要使用横向虚拟视图 lateral view。

--ad,pf是横向视图的别名,as后面的别名是字段的别名,map2个,array1个
    
    select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;
    select name,pf.mykey,pf.myvalue
    from user_piaofang lateral view explode(piaofang) pf as mykey,myvalue;
    select name,ad.city
    from user_address lateral view explode(address) ad as city;

### 3. UDTF ###

UDTF（user defined table function） 自定义表函数，一对多。

## 2. 自定义函数 ##

**1. 实现**

1. 继承UDF
    2. 实现一个evaluate()
          修饰必须是public
          同一个udf打了多个jar包,加载多次,只有第一次生效

如，自定义Date格式转TimeStamp函数。

DateToTimeStampUDF

public class DateToTimeStampUDF extends UDF {
        private static final SimpleDateFormat TIME_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
        public String evaluate(String str) {
            String times = "";
            // 判断str是date
    
            if (!str.isEmpty()) {
                if (str.length() == 19) {
                    try {
                        times = TIME_FORMAT.parse(str).getTime()+"";
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                } else if (str.length() == 13) {
                    times = str;
                } else if (str.length() == 10) {
                    times = str + "000";
                } else {
                   return times;
                }
            }
            return times;
        }
    }

**2. 定义函数**

1. 打jar包，上传到 hive 的 classpath 下。

# 添加jar,在hive客户端执行 
    add jar jar包路径;
    
    # jar放在hdfs
    add jar hdfs://BigdataCluster/user/hive/jars/hivefunction-1.0-SNAPSHOT.jar;
    
    # jar在服务器，最好集群上Hive运行的节点相应位置都有
    add jar /opt/cloudera/parcels/CDH-6.2.0-1.cdh6.2.0.p0.967373/lib/hive/lib/hivefunction-1.0-SNAPSHOT.jar;
    
    # 检测jar包是否添加成功
    list jars;
    
    # 删除jar
    delete jar hdfs://BigdataCluster/user/hive/jars/hivefunction-1.0-SNAPSHOT.jar;

![20190912133448893.png][]

2. 创建临时函数。

给自定义函数添加别名，并在hive中注册这个函数。

create temporary function date_timestamp as 'com.sm.udf.DateToTimeStampUDF';

![201909121334102.png][]

3. 查看hive函数库中是否添加。

show functions;

![20190912133612829.png][]

4. 删除临时函数

drop temporary function if exists date_timestamp;

> 注意：  
> 1. 一个函数多个实现，通过函数名和参数来确定调用哪一个evaluate方法。  
> 2. 这种方式创建是临时函数，会话关闭就没有了，下次使用要重新注册。

5. 创建永久函数

create function date_timestamp AS 'com.sm.udf.DateToTimeStampUDF' 
    using jar 'hdfs://cdh-master:9000/hive/hivefunction-1.0-SNAPSHOT.jar';

6. MySQL中查看Hive元数据中函数信息

MySQL -> Hive元数据库 -> funcs 表

use metastore;        -- 自定义的hive的元数据库
    select * from funcs;

[20190912133448893.png]: /images/20210923/c5b37b339abe4719928d1735d060e065.png
[201909121334102.png]: /images/20210923/49bf4942328943288e0bd986dd9249a4.png
[20190912133612829.png]: /images/20210923/cd12ea8a6d794dd0af9a25a65af2c254.png