【JDBC-Hive】使用JDBC操作Hive数据库
【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();}//连接Hivetry {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();}//连接Hivetry {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();}//连接Hivetry {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();}//连接Hivetry {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(字段名, 字段值)
朴灿烈づ我的快乐病毒、
Myth丶恋晨
「爱情、让人受尽委屈。」
Bertha 。
骑猪看日落
迷南。
还没有评论，来说两句吧...