hive UDF函数

hive的udf函数

一、简述

1. hive自带了一些函数比如：max/min 等，但是由于自带的函数数量有限，自己可以定义udf来方便扩展。
2. udf 函数可以直接应用于select 语句，对查询结构做格式化处理之后，然后再输出内容。

二、hive编写udf函数注意事项

1. 自定义udf函数需要继承org.apache.hadoop.hive.ql.UDF
2. 需要实现evaluate 函数，evaluate 函数支持重载。
3. udf 必须要有返回类型，可以返回null，但是返回类型不能为void；
4. udf 常用Text/LongWrite 等类型，不推荐使用java类型。

三、udf函数编写

使用maven导入jar包

 <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <scope>provided</scope>
            <version>1.1.0-cdh5.8.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <scope>provided</scope>
            <version>2.6.0-cdh5.8.0</version>
        </dependency>

编写udf java

public class JudgeIsInExhibitionArea extends UDF {

    //ak值与sysCode为系统默认配置
    /*private static final String DEFAULT_AK = "a99343a3e0264dd3a4c3430e286adedc";
    private static final String DEFAULT_SYSCODE = "EOS-FMS-RMS";
    */
    
    private static final String DEFAULT_AK = "24854409c8c646d5bdbcf0c7b13806c3";
    private static final String DEFAULT_SYSCODE = "SGS";
    
    /**
     * 通过经纬度判断是否在会展区域
     * @param url 服务地址
     * @param lng 经度
     * @param lat 纬度
     * @return
     */
    public static String evaluate(String url, String lng, String lat) {
        List<NameValuePair> params = new ArrayList<NameValuePair>();
        params.add(new BasicNameValuePair("lng", lng));
        params.add(new BasicNameValuePair("lat", lat));
        params.add(new BasicNameValuePair("level", "1"));
        return evaluate(url, params);
    }
    
    /**
     * 通过详细地址和城市代码判断是否在会展区域
     * @param url
     * @param address 详细地址
     * @param city 城市代码
     * @param level 
     * @return
     */
    public static String evaluate(String url, String address, String city, String level) {
        List<NameValuePair> params = new ArrayList<NameValuePair>();
        params.add(new BasicNameValuePair("level", level));
        params.add(new BasicNameValuePair("address", address));
        params.add(new BasicNameValuePair("cityCode", city));
        return evaluate(url, params);
    }
    
    public static String evaluate(String url, List<NameValuePair> params) {
        //默认参数
        params.add(new BasicNameValuePair("ak", DEFAULT_AK));
        params.add(new BasicNameValuePair("sysCode", DEFAULT_SYSCODE));
        
        CloseableHttpClient httpClient = HttpClients.createDefault();
        CloseableHttpResponse response = null;
        InputStream is = null;
        String str ;
        String result = "N";
        try {
            // 转换为键值对
            str = EntityUtils.toString(new UrlEncodedFormEntity(params, Consts.UTF_8));
            // 创建Get请求
            HttpGet httpPost = new HttpGet(url + "?" + str);
            // 执行Get请求，
            response = httpClient.execute(httpPost);
            // 得到响应体
            HttpEntity entity = response.getEntity();
            if (entity != null) {
                is = entity.getContent();
                // 转换为字节输入流
                BufferedReader br = new BufferedReader(new InputStreamReader(is, Consts.UTF_8));
                StringBuilder sb = new StringBuilder();
                String body = null;
                while ((body = br.readLine()) != null) {
                    sb.append(body);
                }
                Map mapTypes = JSON.parseObject(sb.toString());
                if (null == mapTypes.get("result")) {
                    return result;
                }
                
                Map resultMap = JSON.parseObject(String.valueOf(mapTypes.get("result")));
                if (null == resultMap.get("isExhibition") || "-1".equals(resultMap.get("isExhibition"))) {
                    return result;
                }
                
                if (null == resultMap.get("chname")) {
                    return result;
                }
                result = String.valueOf(resultMap.get("chname"));
            }
        } catch (Exception e) {
            result = "E";
        } finally {
            // 关闭输入流，释放资源
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                }
            }
            // 消耗实体内容
            if (response != null) {
                try {
                    response.close();
                } catch (IOException e) {
                }
            }
            // 关闭相应 丢弃http连接
            if (httpClient != null) {
                try {
                    httpClient.close();
                } catch (IOException e) {
                }
            }
        }
        return result;
    }
    
    public static void main(String[] args) {
        if (null == args || args.length == 0) {
            System.out.println("no args");
            return;
        }
        
        if (args.length == 3) {
            String url = args[0];
            List<NameValuePair> params = new ArrayList<NameValuePair>();
            params.add(new BasicNameValuePair("lng", args[1]));
            params.add(new BasicNameValuePair("lat", args[2]));
            params.add(new BasicNameValuePair("level", "1"));
            String result = JudgeIsInExhibitionArea.evaluate(url, params);
            System.out.println("args:" + args[0] + "-" + args[1] + "-" + args[2]);
            System.out.println("result:" + result);
        } else if (args.length == 4) {
            String url = args[0];
            List<NameValuePair> params = new ArrayList<NameValuePair>();
            params.add(new BasicNameValuePair("address", args[1]));
            params.add(new BasicNameValuePair("cityCode", args[2]));
            params.add(new BasicNameValuePair("level", args[3]));
            String result = JudgeIsInExhibitionArea.evaluate(url, params);
            System.out.println("args:" + args[0] + "-" + args[1] + "-" + args[2] + "-" + args[3]);
            System.out.println("result:" + result);
        } else {
            System.out.println("error input");
        }
    }
}

通过右键项目名，run-->maven install ,导出jar包后，将jar包上传到hive,jar
销毁udf函数

drop function rms.judge;
CREATE FUNCTION rms.judge AS
 'com.sinoservices.rms.report.JudgeIsInExhibitionArea' USING JAR 'hdfs:///user/89003269/upload/gis-rms.0.0.1.jar';