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

Myth丶恋晨 2023-10-08 20:35 153阅读 0赞

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

*  1）导入依赖
 *  2）使用JDBC进行Hive数据库操作
 *   *  2.1.写入
     *  2.2.删除
     *  2.3.修改
     *  2.4.查询
 *  3）注意

## 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(字段名, 字段值)`