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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
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;
            }
        }
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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);
        }
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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
2
3
4
5
6
7
8
9
10
11
[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 ~]#

1
2
3
4
5
6
7
8
9
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)

1
2
3
4
5
6
7
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)

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