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

59 阅读 0 评论 39 点赞

我是靠谱客的博主高挑心情，最近开发中收集的这篇文章主要介绍通过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...