【JDBC-Hive】使用JDBC操作Hive数据库

1）导入依赖

<dependency>
   <groupId>org.apache.hive</groupId>
   <artifactId>hive-jdbc</artifactId>
   <version>2.1.1</version>
   <exclusions>
       <exclusion>
           <groupId>jdk.tools</groupId>
           <artifactId>jdk.tools</artifactId>
       </exclusion>
       <exclusion>
           <groupId>org.slf4j</groupId>
           <artifactId>slf4j-api</artifactId>
       </exclusion>
       <exclusion>
           <groupId>org.slf4j</groupId>
           <artifactId>slf4j-log4j12</artifactId>
       </exclusion>
       <exclusion>
           <groupId>org.apache.logging.log4j</groupId>
           <artifactId>log4j-slf4j-impl</artifactId>
       </exclusion>
   </exclusions>
</dependency>
<dependency>
   <groupId>org.apache.hadoop</groupId>
   <artifactId>hadoop-client</artifactId>
   <version>2.7.2</version>
   <exclusions>
       <exclusion>
           <groupId>org.slf4j</groupId>
           <artifactId>slf4j-api</artifactId>
       </exclusion>
       <exclusion>
           <groupId>org.slf4j</groupId>
           <artifactId>slf4j-log4j12</artifactId>
       </exclusion>
   </exclusions>
</dependency>
<dependency>
   <groupId>org.apache.hadoop</groupId>
   <artifactId>hadoop-common</artifactId>
   <version>2.7.2</version>
</dependency>

2）使用JDBC进行Hive数据库操作

2.1.写入

import java.io.IOException;
import java.sql.*;

public class Test{
    //Hive连接信息
    private static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";
    private static String HIVE_URL = "jdbc:hive2://192.168.1.1:25005/test";
    private static String HIVE_USERNAME = "root";
    private static String HIVE_PASSWORD = "123456";

    //Hive源
    private static String SOURCE = "`ods_x8v`.`ods_tsp_tab_tbox_change`";
    private static String TARGET = "`dw_x8v`.`dwd_tsp_tab_tbox_change`";

    private static Connection conn = null;
    private static ResultSet rs = null;
    private static PreparedStatement ps = null;

    public static void main(String[] args) {
        //HiveJDBC驱动
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接Hive
        try {
            conn = DriverManager.getConnection(HIVE_URL, HIVE_USERNAME, HIVE_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            ps = conn.prepareStatement("insert into " + SOURCE + " values(?, ?)");
            //写入数据id为1234，name为张三
            ps.setInt(1,1234);
            ps.setString(2, "张三");
            rs = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.2.删除

import java.io.IOException;
import java.sql.*;

public class Test{
    //Hive连接信息
    private static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";
    private static String HIVE_URL = "jdbc:hive2://192.168.1.1:25005/test";
    private static String HIVE_USERNAME = "root";
    private static String HIVE_PASSWORD = "123456";

    //Hive源
    private static String SOURCE = "`ods_x8v`.`ods_tsp_tab_tbox_change`";
    private static String TARGET = "`dw_x8v`.`dwd_tsp_tab_tbox_change`";

    private static Connection conn = null;
    private static ResultSet rs = null;
    private static PreparedStatement ps = null;

    public static void main(String[] args) {
        //HiveJDBC驱动
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接Hive
        try {
            conn = DriverManager.getConnection(HIVE_URL, HIVE_USERNAME, HIVE_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            ps = conn.prepareStatement("delete from " + SOURCE + " where id = ?");
            //删除id为1234的数据
            ps.setInt(1,1234);
            //执行数据更新操作
            rs = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.3.修改

import java.io.IOException;
import java.sql.*;

public class Test{
    //Hive连接信息
    private static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";
    private static String HIVE_URL = "jdbc:hive2://192.168.1.1:25005/test";
    private static String HIVE_USERNAME = "root";
    private static String HIVE_PASSWORD = "123456";

    //Hive源
    private static String SOURCE = "`ods_x8v`.`ods_tsp_tab_tbox_change`";
    private static String TARGET = "`dw_x8v`.`dwd_tsp_tab_tbox_change`";

    private static Connection conn = null;
    private static ResultSet rs = null;
    private static PreparedStatement ps = null;

    public static void main(String[] args) {
        //HiveJDBC驱动
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接Hive
        try {
            conn = DriverManager.getConnection(HIVE_URL, HIVE_USERNAME, HIVE_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            ps = conn.prepareStatement("update " + SOURCE + " set name = ? where id = ?");
			//修改id为1234的数据name字段为张三
            ps.setString(1, "张三");
            ps.setInt(2,1234);
            //执行数据更新操作
            rs = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2.4.查询

import java.io.IOException;
import java.sql.*;

public class Test{
    //Hive连接信息
    private static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";
    private static String HIVE_URL = "jdbc:hive2://192.168.1.1:25005/test";
    private static String HIVE_USERNAME = "root";
    private static String HIVE_PASSWORD = "123456";

    //Hive源
    private static String SOURCE = "`ods_x8v`.`ods_tsp_tab_tbox_change`";
    private static String TARGET = "`dw_x8v`.`dwd_tsp_tab_tbox_change`";

    private static Connection conn = null;
    private static ResultSet rs = null;
    private static PreparedStatement ps = null;

    public static void main(String[] args) {
        //HiveJDBC驱动
        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        //连接Hive
        try {
            conn = DriverManager.getConnection(HIVE_URL, HIVE_USERNAME, HIVE_PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
        	//查询表中id，name字段数据
            ps = conn.prepareStatement("select id, name from " + SOURCE);
            //将查询结果返回结果集
            rs = ps.executeQuery();
            //遍历结果集
            while (rs.next()) {
                int id = rs.getInt(1);
                String name = rs.getString(2);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3）注意

（1）ps.setString()：空指针异常

在用JDBC操作Hive数据库执行写入操作时，由于要使用 ps.setString(字段名, 字段值) 的方式进行数据值的设置，所以无可避免的会出现 ps.setString(字段名, null) 这种 null 值的写入。这时就会出现空指针异常。

解决方式：

使用ps.setObject(字段名, 字段值)