通过JDBC连接Hive以及常见异常处理方式

环境

• 1.hadoop-2.7.3集群
• 2.hive-1.2.2

测试代码

• 1.JDBCUtils.java

package demo.utils;

import java.sql.*;

public class JDBCUtils {

    private static String driver = "org.apache.hive.jdbc.HiveDriver";
    //加载连接hive的JDBC驱动
    private static String url = "jdbc:hive2://192.168.142.130:10000/default?useUnicode=true&characterEncoding=UTF-8";
    //数据库的地址
    private static String username = "用户名";
    private static String password = "密码";

    //注册驱动
    static {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                resultSet = null;
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                statement = null;
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                connection = null;
            }
        }
    }

}

• 2.JDBCDemo.java

package demo.hive;

import demo.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCDemo {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        String sql = "select * from msg";

        try {
            //获取连接
            connection = JDBCUtils.getConnection();
            //创建运行环境
            statement = connection.createStatement();
            //运行SQL获取结果集
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int id = resultSet.getInt(1);
                String text = resultSet.getString(2);
                System.out.println(id + "\t" + text);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(connection, statement, resultSet);
        }
    }

}

• 3.需要的Jar包

commons-collections-3.2.2.jar
commons-logging-1.1.3.jar
hadoop-common-2.7.3.jar
hive-exec-1.2.2.jar
hive-jdbc-1.2.2.jar
hive-metastore-1.2.2.jar
hive-service-1.2.2.jar
httpclient-4.5.3.jar
httpclient-cache-4.5.3.jar
httpcore-4.4.6.jar
httpmime-4.5.3.jar
libfb303-0.9.2.jar
log4j-1.2.16.jar
mysql-connector-java-5.1.43-bin.jar
slf4j-api-1.7.10.jar

注：除了mysql-connector-java-5.1.43-bin.jar之外，其余的Jar包都可以在hive和hadoop的lib/目录下寻找。

注意

• 1.本次连接基于hive远程模式
• 2.测试代码中的“数据库地址”是安装hive的节点的地址
• 3.如果是本地模式则不需要Jar中的几个http*.jar

常见异常处理方式

• 1.异常①

[root@Master ~]# hive --service hiveserver
Starting Hive Thrift Server
Exception in thread "main" java.lang.ClassNotFoundException: org.apache.hadoop.hive.service.HiveServer
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:348)
    at org.apache.hadoop.util.RunJar.run(RunJar.java:214)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
[root@Master ~]#

处理方式：hive-1.2.1之后启动hive使用的是hive –service hiveserver2而不是hive –service hiveserver

• 2.异常②

java.lang.ClassNotFoundException: org.apache.hadoop.hive.jdbc.HiveDriver
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:335)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at demo.utils.JDBCUtils.<clinit>(JDBCUtils.java:15)
    at demo.hive.JDBCDemo.main(JDBCDemo.java:19)

处理方式：hive-1.2.1之后JDBC驱动是org.apache.hive.jdbc.HiveDriver而不是org.apache.hadoop.hive.jdbc.HiveDriver

• 3.异常③

java.sql.SQLException: No suitable driver found for jdbc:hive://192.168.142.130:10000/default?useUnicode=true&characterEncoding=UTF-8
    at java.sql.DriverManager.getConnection(DriverManager.java:689)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at demo.utils.JDBCUtils.getConnection(JDBCUtils.java:24)
    at demo.hive.JDBCDemo.main(JDBCDemo.java:19)
java.lang.NullPointerException
    at demo.hive.JDBCDemo.main(JDBCDemo.java:21)

处理方式：hive-1.2.1之后url地址是jdbc:hive2://192.168.142.130:10000/default?useUnicode=true&characterEncoding=UTF-8而不是jdbc:hive://192.168.142.130:10000/default?useUnicode=true&characterEncoding=UTF-8

吐槽

//获取连接
connection = JDBCUtils.getConnection();
//创建运行环境
statement = connection.createStatement();
//运行SQL获取结果集
resultSet = statement.executeQuery(sql);//之前程序不报任何错误，但是程序运行始终卡在这里，浪费了好几个小时找这个位子的问题，实在找不出来，第二天同样的程序直接运行，对了。QAQ...

Over