JDBC实现从Hive抽取数据导入Oracle

环境：浙江移动华为云平台

云平台大数据采用了 Kerberos 认证。

开发历程：

1、在宁波大数据实验环境测试通过了JDBC实现从Hive抽取数据导入Oracle功能。

2、通过查看其它项目的数据库访问配置，知道了云平台上的oracle配置。

3、获取hive的jdbc访问配置。但是无人知道，说保密不告知。

但是方法还是有的，从访问前source的shell文件里，找到了眉目。

[deepen@vd26wyjl01 ~]$ cat login.sh source /app/deepen/jc_client/bigdata_envexport KRB5CCNAME=/app/deepen/user_keytab/jc_deepen.cckinit -kt /app/deepen/user_keytab/user.keytab jc_deepenklist

[deepen@vd26wyjl01 ~]$ beelineConnecting to jdbc:hive2://ip:24002,ip:24002,ip:24002,ip:24002,ip:24002/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;sasl.qop=auth-conf;auth=KERBEROS;principal=hive/[e-mail] is  true storeKey false useTicketCache true useKeyTab false doNotPrompt false ticketCache is null isInitiator true KeyTab is null refreshKrb5Config is false principal is null tryFirstPass is false useFirstPass is false storePass is false clearPass is falseAcquire TGT from CachePrincipal is [e-mail] Succeeded 0: jdbc:hive2://ip:21066/> 

通过辛苦的调配后，最后的配置如下：

HIVE_Kerberos_URL=jdbc:hive2://ip地址:21066/deepen_hive_db;principal=hive/[e-mail]

HIVE_Kerberos_krb5_conf=/app/deepen/user_keytab/krb5.conf

HIVE_Kerberos_User=[e-mail]

HIVE_Kerberos_krb5_Keytab=/app/deepen/user_keytab/user.keytab

ORACLE_DRIVER=oracle.jdbc.driver.OracleDriver

ORACLE_URL=xxxxxx

ORACLE_UserName =xxxx

ORACLE_PassWord =xxxx

核心代码：

// 创建连接private Connection getConnnection() throws Exception { /** 使用Hadoop安全登录 **/        org.apache.hadoop.conf.Configuration conf = new org.apache.hadoop.conf.Configuration();        conf.set("hadoop.security.authentication", "Kerberos");                //System.setProperty("java.security.krb5.conf", "/app/deepen/user_keytab/krb5.conf");        System.setProperty("java.security.krb5.conf", krb5_conf);        try {            UserGroupInformation.setConfiguration(conf);            //UserGroupInformation.loginUserFromKeytab("hive/[e-mail]", "/app/deepen/user_keytab/user.keytab");            UserGroupInformation.loginUserFromKeytab(user, krb5_Keytab);                    } catch (IOException e1) {            e1.printStackTrace();        }        Class.forName(driverName);        Connection conn = DriverManager.getConnection(url);        System.out.println("#####获取Kerberos-Hive-conn成功：---"+conn.toString());        return conn;}