使用java中jdbc互通hive和oracle数据

使用java中jdbc互通hive和oracle数据

需求如下：从oracle数据库中读取数据，并将数据导入hive的数据库中，当中连接主要通过使用jdbc的方法连接。
当中需要依赖hive的包：

1. 首先如果是maven工程。直接在pom.xml中导入

org.apache.hadoop hadoop-client org.slf4j slf4j-api org.slf4j slf4j-log4j12 2.8.0

2.如果不是maven工程。则需要将提供的hivelib中的jar包引入工程中。
连接如下：
link.

3.分析思路如下
首先分别与hive还有oracle数据库建立连接。
首先初始化。然后读取数据并写入hive。剩下的就是sql语句的编写。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;


//这一块就是让hive直接定时处理已经获取到的数据。
//还需要一块就是hive从传输平台定时获取数据。
public class HiveApp {

    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private static String url = "jdbc:hive2://IP/databases";
    private static String user = "hive"; 
    private static String password = "hive";
    
	private static String sql = "";
	private static String urlO="jdbc:oracle:thin:@IP/sqms"; //URL地址
	private static String usernameO="user";
	private static String passwordO="password";
	public static PreparedStatement psto = null;
	
	private static ResultSet res;
	
	public static void main(String[] args)  {
		Connection conn = null;
		Statement stmt = null;
		Connection connOracle = null;
		Statement stmt1 = null;
		try {
			connOracle=getOracleConn();
			conn = getConn();
			stmt = conn.createStatement();
			stmt1 = connOracle.createStatement();
			init(stmt);
			//初始化文件地址
			initLoadDataInHdfs(stmt);
			// 第一步:存在就先删除
			 dropTable(stmt);
			// 第二步:创建数据
			createTable(stmt,"衡水");
			//第三部：删除全量的表
			deleteAllTables(stmt);
			// 第三步:创建全量的表
			createAllTables(stmt,"衡水");
			insertTable(stmt, connOracle, stmt1);
			//从oracle表中插入表内容。
			//这里可以插入需要形成哪里地级市的表格。
			insertTGTable(connOracle, stmt1,"衡水","INTF_RES_TOPO_DATA_TEST");
			System.out.println("mission is update");
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				psto.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void insertTGTable(Connection connOracle, Statement stmt1,String area,String tableName) throws SQLException {
		stmt1.execute("truncate table "+tableName);
		String insert_sql = "insert into " +tableName+ 
				"  select seq_topo.nextval, t.a_ne_id, t.a_ne_name, t.z_ne_id, t.z_ne_name,t.related_space,t.related_city" + 
				"    from intf_res_topo_test t" + 
				"   where t.related_city = ' "+ area +" '" + 
				"     and (instr(t.a_ne_name, 'CSG') > 0 or instr(t.a_ne_name, 'ASG') > 0 or" + 
				"         instr(t.z_ne_name, 'CSG') > 0 or instr(t.z_ne_name, 'ASG') > 0)";
		psto = connOracle.prepareStatement(insert_sql);
		psto.executeUpdate();
	}

	private static void insertTable(Statement stmt, Connection connOracle, Statement stmt1) throws SQLException {
		String insert_sql ="insert into intf_res_topo_test (OBJECT_RDN,LINE_ID,SYS_ID,"
				+ "A_NE_ID,A_NE_NAME,A_PORT_ID,A_PORT_NAME,Z_NE_ID,Z_NE_NAME,"
				+ "Z_PORT_ID,Z_PORT_NAME,UPDATE_TIME,RELATED_SPACE,RELATED_CITY) "
				+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		sql = "select * from dw.intf_res_topo";
		res = stmt.executeQuery(sql);
		stmt1.execute("truncate table INTF_RES_TOPO_TEST");
		psto = connOracle.prepareStatement(insert_sql);
	        
		while (res.next()) {
			psto.setString(1, res.getString("object_rdn"));
			psto.setString(2, res.getString("line_id"));
			psto.setString(3, res.getString("sys_id"));
			psto.setString(4, res.getString("a_ne_id"));
			psto.setString(5, res.getString("a_ne_name"));
			psto.setString(6, res.getString("a_port_id"));
			psto.setString(7, res.getString("a_port_name"));
			psto.setString(8, res.getString("z_ne_id"));
			psto.setString(9, res.getString("z_ne_name"));
			psto.setString(10, res.getString("z_port_id"));
			psto.setString(11, res.getString("z_port_name"));
			psto.setString(12, res.getString("update_time"));
			psto.setString(13, res.getString("related_space"));
			psto.setString(14, res.getString("related_city"));
			psto.executeUpdate();
		}
		System.out.println("insert success");

	}

	private static void showTable(Statement stmt) throws SQLException {
        sql = "select * from dw.intf_res_topo";
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString("object_rdn") + "\t" + res.getString("line_id")+ "\t" +res.getString("sys_id")+res.getString("a_ne_id") + "\t" + res.getString("a_ne_name")+ "\t" +res.getString("a_port_id")+res.getString("a_port_name") + "\t" + res.getString("z_ne_id")+ "\t" +res.getString("z_ne_name")+res.getString("z_port_id") + "\t" +res.getString("z_port_name") + "\t"+res.getString("update_time") + "\t"+ res.getString("related_space")+ "\t" +res.getString("related_city"));
        }
		
	}
	private static void initLoadDataInHdfs(Statement stmt) throws SQLException {
		//  加载那三张表数据进入hive中
		sql ="load data  inpath '/hive_database/data/INTF_RES_PUB_TOPO_LINK/INTF_RES_PUB_TOPO_LINK.csv' overwrite into table INTF_RES_PUB_TOPO_LINK";
		stmt.execute(sql);
		sql ="load data  inpath '/hive_database/data/INTF_RES_TRS_TRS_NE/INTF_RES_TRS_TRS_NE.csv' overwrite into table INTF_RES_TRS_TRS_NE";
		stmt.execute(sql);
		sql ="load data inpath '/hive_database/data/INTF_RES_TRS_MAINTENANCE_AREA/INTF_RES_TRS_MAINTENANCE_AREA.csv' overwrite into table INTF_RES_TRS_MAINTENANCE_AREA";
		stmt.execute(sql);
		System.out.println("load success");
		
	}
	private static void init(Statement stmt) throws SQLException {
		// 初始化进入ods层
		sql ="use ods";
		stmt.execute(sql);
		System.out.println("init success");
	}

	private static void deleteAllTables(Statement stmt)
			throws SQLException {
		sql = "drop table if exists dw.intf_res_topo_all";
		stmt.execute(sql);
		
	}
 
	private static void createAllTables(Statement stmt,String area)
			throws SQLException {
		sql = "create table dw.intf_res_topo_all as select *" + 
				"  from (select t.*, b.related_space, b.related_city" + 
				"          from intf_res_pub_topo_link        t," + 
				"               intf_res_trs_trs_ne           a," + 
				"               intf_res_trs_maintenance_area b" + 
				"         where t.a_ne_id = a.trs_ne_id" + 
				"           and a.related_maintenanceareaid = b.maintenanceareaid) tab" + 
				" where tab.related_space = '"+ area +"' ";
		stmt.execute(sql);
	}
 
	private static void createTable(Statement stmt,String area)
			throws SQLException {
		sql = "create table dw.intf_res_topo as select *" + 
				"  from (select t.*, b.related_space, b.related_city" + 
				"          from intf_res_pub_topo_link        t," + 
				"               intf_res_trs_trs_ne           a," + 
				"               intf_res_trs_maintenance_area b" + 
				"         where t.a_ne_id = a.trs_ne_id" + 
				"           and a.related_maintenanceareaid = b.maintenanceareaid) tab" + 
				" where tab.related_space = '"+ area +"' ";
		stmt.execute(sql);
	}
 
	private static void dropTable(Statement stmt) throws SQLException {
		// 创建的表名
		sql = "drop table if exists dw.intf_res_topo" ;
		stmt.execute(sql);
	}
 
	private static Connection getConn() throws ClassNotFoundException,
			SQLException {
		Class.forName(driverName);
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	private static Connection getOracleConn() throws SQLException, ClassNotFoundException {
		Class.forName("oracle.jdbc.driver.OracleDriver");//找到oracle驱动器所在的类
		Connection connOracle = DriverManager.getConnection(urlO, usernameO, passwordO);
		return connOracle;
	}

}