Java JDBC连接Kerberos认证的HIVE和Impala方式_Java

1. 版本

hive 2.1.1-cdh6.3.2版本需对应版本的 hive jdbc 和 impala jdbc，如下：

注意：单独使用hive jdbc ，使用版本 3.1.0，使用 impala jdbc 需要搭配 hive jdbc 1.1.0使用，不然会报错；搭配hive jdbc 3.1.0 报：

java.lang.noclassdeffounderror:org/apache/hive/service/cli/thrift/tcliservice$client

       <dependency>
         <groupid>org.apache.hive</groupid>
         <artifactid>hive-exec</artifactid>
         <version>3.1.0</version>
       </dependency>
       <dependency>
         <groupid>org.apache.hadoop</groupid>
         <artifactid>hadoop-common</artifactid>
         <version>3.1.0</version>
       </dependency>
       <dependency>
<!--        <dependency>-->
<!--            <groupid>org.apache.hive</groupid>-->
<!--            <artifactid>hive-jdbc</artifactid>-->
<!--            <version>3.1.0</version>-->
<!--        </dependency>-->
       <dependency>
         <groupid>org.apache.hadoop</groupid>
         <artifactid>hadoop-auth</artifactid>
         <version>3.1.0</version>
       </dependency>
       <dependency>
         <groupid>com.cloudera.impala</groupid>
         <artifactid>impala-jdbc41</artifactid>
         <version>2.5.28</version>
       </dependency>
       <dependency>
           <groupid>org.apache.hive</groupid>
           <artifactid>hive-jdbc</artifactid>
           <version>1.1.0</version>
       </dependency>

1.1 手动安装 maven

如果 maven 库没有相应jdbc，手动安装 maven，如下：

mvn install:install-file -d file=c:\users\mx\desktop\impala-jdbc41-0.0.1.jar -d groupid=com.cloudera.impala -d artifactid=impala-jdbc42 -d version=2.6.33 -d packaging=jar

2. 映射地址

因kerberos spn配置必须使用主机名链接，linux 和 windows需要配置hosts，格式：ip地址主机名，配置完成之后 telnet 检测

说明：如果是docker容器链接 hive 或 impala ，还需要在docker容器中配置 hosts，保证主机和容器 telent 都是通的

telnet hostname port

2.1 端口

开通 hive 和 impala 相应的端口，本地调试按需将 ip 和端口加入白名单

hive tcp端口：jdbc:hive2://hostname:10000
impala tcp端口：jdbc:impala://hostname:21050
hdfs tcp端口：hdfs://hostname:8020
kerberos udp端口：88

2.2 调试

使用 telnet hostname port ，调试以上 hostname 和端口，保障网络连通

3. hive jdbc 连接 kerberos 认证的 hive 集群

package com.test;

import java.sql.connection;
import java.sql.drivermanager;
import java.sql.resultset;
import java.sql.statement;
import org.apache.hadoop.conf.configuration;
import org.apache.hadoop.security.usergroupinformation;

public class hivekerberosconnectionexample {

    private static string krb5 = "src/main/resources/krb5.conf";
    private static string keytab = "src/main/resources/hive.keytab";
    private static string principal = "hive@example.com";
    private static string hiveurl = "jdbc:hive2://host:10000/test;principal=hive/_host@example.com";

    public static void main(string[] args) throws exception {
        // 设置kerberos配置文件路径
        system.setproperty("java.security.krb5.conf", krb5);
        system.setproperty("sun.security.krb5.debug", "true"); // 可选，用于调试 kerberos 认证过程
        // 初始化hadoop配置
        configuration conf = new configuration();
        conf.set("hadoop.security.authentication", "kerberos");
        // 使用keytab进行kerberos身份验证
        usergroupinformation.setconfiguration(conf);
        usergroupinformation.loginuserfromkeytab(principal, keytab);
        
        // 建立hive连接
        class.forname("org.apache.hive.jdbc.hivedriver");
        connection connection = drivermanager.getconnection(hiveurl, "", "");

        // 执行查询
        statement stmt = connection.createstatement();
        resultset res = stmt.executequery("select * from test.tblname");

        // 处理结果
        while (res.next()) {
            //a,b,c,ds 为 tblname表字段
            string data = res.getstring("a") + "," + res.getstring("b") + "," + res.getstring("c") + "," + res.getstring("ds");
            system.out.println("************** 输出 tblname***************************");
            system.out.println(data);
        }

        // 关闭资源
        res.close();
        stmt.close();
        connection.close();
    }
}

4. hive jdbc 连接 kerberos 认证的 impala 集群

package com.test;

import org.apache.hadoop.conf.configuration;
import org.apache.hadoop.security.usergroupinformation;

import java.security.privilegedaction;
import java.sql.*;


public class impalakerberosconnectionexample {
    private static string krb5 = "src/main/resources/krb5.conf";
    private static string keytab = "src/main/resources/impala.keytab";
    private static string principal = "impala@example.com";
    private static string impalaurl = "jdbc:hive2://host:21050/test;principal=impala/_host@example.com";
    public static void main(string[] args) throws exception {
        // 设置kerberos配置文件路径
        system.setproperty("java.security.krb5.conf", krb5);
        system.setproperty("sun.security.krb5.debug", "true"); // 可选，用于调试 kerberos 认证过程
        // 初始化hadoop配置
        configuration conf = new configuration();
        conf.set("hadoop.security.authentication", "kerberos");
        // 使用keytab进行kerberos身份验证
        usergroupinformation.setconfiguration(conf);
        usergroupinformation.loginuserfromkeytab(principal, keytab);
        usergroupinformation loginuser = usergroupinformation.getloginuser();

        //使用hive jdbc 建立impala连接
        class.forname("org.apache.hive.jdbc.hivedriver");
        // impala jdbc 和 hive jdbc 查询
//        loginuser.doas((privilegedaction<void>) () -> {
//            try {
//                try (connection connection = drivermanager.getconnection(impalaurl,"","")) {
//                    try (statement statement = connection.createstatement()) {
//                        resultset resultset = statement.executequery("select * from test.tblname limit 10");
//                        while (resultset.next()) {
//                            string data = resultset.getstring("a") + "," + resultset.getstring("b") + "," + resultset.getstring("c") + "," + resultset.getstring("ds");
//                            system.out.println("************** 输出 tblname***************************");
//                            system.out.println(data);
//                        }
//                        resultset.close();
//                    }
//                }
//            } catch (sqlexception e) {
//                e.printstacktrace();
//            }
//            return null;
//        });

        // hive jdbc 查询,impala jdbc 查询报 kerberos 认证失败 1312 码
        connection connection = drivermanager.getconnection(impalaurl, "", "");
        statement stmt = connection.createstatement();
        resultset res = stmt.executequery("select * from test.tlbname limit 10");

        // 处理结果
        while (res.next()) {
            string data = res.getstring("a") + "," + res.getstring("b") + "," + res.getstring("c") + "," + res.getstring("ds");
            system.out.println("************** impala 输出 tblname ***************************");
            system.out.println(data);
        }

        // 关闭资源
        res.close();
        stmt.close();
        connection.close();
    }
}

5. impala jdbc 连接 kerberos 认证的 impala 集群

package com.test;

import org.apache.hadoop.conf.configuration;
import org.apache.hadoop.security.usergroupinformation;

import java.security.privilegedaction;
import java.sql.*;


public class impalakerberosconnectionexample {
    private static string krb5 = "src/main/resources/krb5.conf";
    private static string keytab = "src/main/resources/impala.keytab";
    private static string principal = "impala@example.com";
    private static string impalaurl = "jdbc:impala://host:21050/test;authmech=1;krbrealm=example.com;krbhostfqdn=host;krbservicename=impala";
    public static void main(string[] args) throws exception {
        // 设置kerberos配置文件路径
        system.setproperty("java.security.krb5.conf", krb5);
        system.setproperty("sun.security.krb5.debug", "true"); // 可选，用于调试 kerberos 认证过程
        // 初始化hadoop配置
        configuration conf = new configuration();
        conf.set("hadoop.security.authentication", "kerberos");
        // 使用keytab进行kerberos身份验证
        usergroupinformation.setconfiguration(conf);
        usergroupinformation.loginuserfromkeytab(principal, keytab);
        usergroupinformation loginuser = usergroupinformation.getloginuser();
        // 使用 impala jdbc 建立impala连接
        class.forname("com.cloudera.impala.jdbc41.driver");// 搭配hive jdbc 3.1.0报：java.lang.noclassdeffounderror: org/apache/hive/service/cli/thrift/tcliservice$client

        //使用hive jdbc 建立impala连接
//        class.forname("org.apache.hive.jdbc.hivedriver");
        // impala查询
        loginuser.doas((privilegedaction<void>) () -> {
            try {
                try (connection connection = drivermanager.getconnection(impalaurl,"","")) {
                    try (statement statement = connection.createstatement()) {
                        resultset resultset = statement.executequery("select * from test.tblname limit 10");
                        while (resultset.next()) {
                            string data = resultset.getstring("a") + "," + resultset.getstring("b") + "," + resultset.getstring("c") + "," + resultset.getstring("ds");
                            system.out.println("************** impala 输出 tlbname ***************************");
                            system.out.println(data);
                        }
                        resultset.close();
                    }
                }
            } catch (sqlexception e) {
                e.printstacktrace();
            }
            return null;
        });
    }
}