Hive函数

自定义函数

自定义函数包括：
1.UDF
User-Defined-Function 一进一出
1.1、继承UDF
1.2、重写evaluate
实现传入的参数，并且封装了很多的方法
2.UDAF
User-Defined Aggregation Function
聚合函数，多对一
如：Count/max/min
3.UDTF
User-Defined Table-Generating Functions
一对多
如：explore()

使用

1. 添加hive依赖

<dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>1.2.1</version>
</dependency>
<dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>1.2.1</version>
 </dependency>

2、创建udf自定义函数

 public class TestJdbc1 {
     private static String driverName = "org.apache.jdbc.HiveDriver";
        public static void main(String[] args) {
            try {
                Class.forName(driverName);
            } catch (Exception e) {
                e.printStackTrace();
                System.exit(1);
            }
        }
    }

3、打包并上传到linux集群

启动hadoop
启动hive
将集群中的jar包上传到hive中：

  add jar ***.jar；

4、创建属于自己的函数

create temporary function 函数名 as "com.hpe.TestUdf.TestUdf";
temporary 指临时表     
as 后写入你自己的包名.类名

5、使用函数执行

select 函数名(字段) from 库.表;

连接JDBC

import java.sql.*;

public class TestJdbc {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";


    public static void main(String[] args)  throws SQLException {
        try {
            //加载驱动信息
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        //建立连接
        Connection con = DriverManager.getConnection(
                "jdbc:hive2://192.168.79.123:10000/test", "root", "root");
        Statement stmt = con.createStatement();

        String tableName = "jdbcTest2";
        stmt.execute("drop table if exists " + tableName);
        stmt.execute("create table " + tableName +
                " (key int, value string)");
        System.out.println("Create table success!");

        // show tables
        String sql = "show tables '" + tableName + "'";
        System.out.println("Running: " + sql);
        ResultSet res = stmt.executeQuery(sql);
        if (res.next()) {
            System.out.println(res.getString(1));
        }

        // describe table
        sql = "describe " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1) + "\t" + res.getString(2));
        }


        sql = "select * from " + tableName;
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(String.valueOf(res.getInt(1)) + "\t"
                    + res.getString(2));
        }

        sql = "select count(1) from " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1));
        }
    }
}