27.通过JDBC连接Hive—Java

27.1 实操环境

• 如何使用Java代码通过JDBC的方式连接Hive
  • 集群已安装HiveServer2服务正常
  • Kerberos和非Kerberos集群HiveServer2服务正常
  • Kerberos集群CDH5.11.2，OS为Redhat7.2
  • 非Kerberos集群CDH5.13，OS为CentOS6.5

27.2 实操内容

• 创建Java工程jdbcdemo
• 添加Maven依赖

<dependency>
    <groupId>org.apache.hadoop</groupId>
    <artifactId>hadoop-client</artifactId>
    <version>2.6.5</version>
</dependency>
<dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-jdbc</artifactId>
    <version>1.1.0</version>
</dependency>

27.2.1 非Kerberos环境

private static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";
private static String CONNECTION_URL ="jdbc:hive2://54.251.129.76:10000/";

static {
    try {
        Class.forName(JDBC_DRIVER);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) {
    System.out.println("通过JDBC连接非Kerberos环境下的HiveServer2");
    Connection connection = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    try {
        connection = DriverManager.getConnection(CONNECTION_URL);
        ps = connection.prepareStatement("select * from test_table");
        rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt(1) + "-------" + rs.getString(2));
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.disconnect(connection, rs, ps);
    }
}

27.2.2 Kerberos环境

• 连接Kerberos环境下的Hive需要准备Kerberos的krb5.conf文件及keytab文件。
  • 注意：Fayson的测试环境是AWS，所以将kdc和HiveServer2服务的IP地址修改为相应的外网地址，JDBC的URL后Kerberos信息是连接的HiveServer2服务对应Prinical信息。
• krb5.conf配置，直接将集群的krb5.conf文件拷贝至本地开发环境下

# Configuration snippets may be placed in this directory as well
includedir /etc/krb5.conf.d/

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 dns_lookup_realm = false
 ticket_lifetime = 24h
 renew_lifetime = 7d
 forwardable = true
 rdns = false
 default_realm = CLOUDERA.COM
 #default_ccache_name = KEYRING:persistent:%{uid}

[realms]
 CLOUDERA.COM = {
  kdc = 54.179.148.25
  admin_server = 54.179.148.25
 }

• keytab文件生成

[ec2-user@ip-172-31-22-86 ~]$ sudo kadmin.local
Authenticating as principal hive/admin@CLOUDERA.COM with password.
kadmin.local:  xst -norandkey -k fayson.keytab fayson@CLOUDERA.COM 

• 示例代码

private static String JDBC_DRIVER = "org.apache.hive.jdbc.HiveDriver";
private static String CONNECTION_URL ="jdbc:hive2://54.179.148.25:10000/;principal=hive/ip-172-31-22-86.ap-southeast-1.compute.internal@CLOUDERA.COM";

static {
    try {
        Class.forName(JDBC_DRIVER);

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) throws Exception {
    //登录Kerberos账号
    System.setProperty("java.security.krb5.conf", "/Volumes/Transcend/keytab/krb5.conf");
    Configuration configuration = new Configuration();
    configuration.set("hadoop.security.authentication" , "Kerberos" );
    UserGroupInformation. setConfiguration(configuration);
    UserGroupInformation.loginUserFromKeytab("fayson@CLOUDERA.COM", "/Volumes/Transcend/keytab/fayson.keytab");

    Connection connection = null;
    ResultSet rs = null;
    PreparedStatement ps = null;
    try {
        connection = DriverManager.getConnection(CONNECTION_URL);
        ps = connection.prepareStatement("select * from test_table");
        rs = ps.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.disconnect(connection, rs, ps);
    }
}

大数据视频推荐：
腾讯课堂
CSDN
大数据语音推荐：
企业级大数据技术应用
大数据机器学习案例之推荐系统
自然语言处理
大数据基础
人工智能：深度学习入门到精通