通过jdbc连接hive报java.sql.SQLException: Method not supported问题-阿里云开发者社区

开发者社区> 大数据> 正文
登录阅读全文

通过jdbc连接hive报java.sql.SQLException: Method not supported问题

简介: 通过jdbc连接hive报java.sql.SQLException: Method not supported异常或Required field 'client_protocol' is unset异常

今天尝试通过jdbc连接hive,JDBC直接连接是正常成功的,实例:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SampleHiveJdbc {

    public static void main(String[] args) throws SQLException {
        Connection conn = getConnection();
        
        String sql = "SELECT * FROM log where day='20180828' limit 5";

        PreparedStatement stmt = conn.prepareStatement(sql);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getLong(1));
        }
        stmt.close();
        conn.close();
    }

    static Connection getConnection() {
        Connection con = null;
        String JDBC_DB_URL = "jdbc:hive2://192.168.44.141:10001/db";
        try {
            Class.forName("org.apache.hive.jdbc.HiveDriver");
            con = DriverManager.getConnection(JDBC_DB_URL, "hive", "hive");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return con;
    }
}

但是在spark程序通过jdbc连接hive,却报错,实例:

import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.sql.SQLContext;

public class SparkHiveJdbc {
    public static void main(String[] args) {

        String master = "local[1]";
        String appName = "SparkHiveJdbc";

        SparkConf sparkConf = new SparkConf();

        if (StringUtils.isNoneBlank(master) && master.startsWith("local")) {
            // 如果是本地模式
            sparkConf.setAppName(appName).setMaster(master);
        }

        SparkContext sc = SparkContext.getOrCreate(sparkConf);

        SQLContext sqlContext = new SQLContext(sc);

        /* 1. 读出为RDD */
        String readUrl = "jdbc:hive2://192.168.44.14110001/db";
        String readTable = "log";
        String readUsername = "hive";
        String readPassword = "hive";
        String driverClassName = "org.apache.hive.jdbc.HiveDriver";

        Properties readProperties = new Properties();
        readProperties.setProperty("user", readUsername);
        readProperties.setProperty("password", readPassword);
        readProperties.setProperty("driver", driverClassName);

        sqlContext
                .read().jdbc(readUrl, readTable,
                        new String[] { "day='20180828'" }, readProperties)
                .show();

        sc.stop();

    }
}

在pom.xml中引入的是hive-jdbc-0.13.0.jar包会报

hive jdbc java.sql.SQLException: Method not supported

但是更改为hive-jdbc-2.3.3.jar或hive-jdbc-3.1.0.jar,升级包后,又会报错:

Required field 'client_protocol' is unset

即使本地spark换为2.3,也不行。尝试多次后,发现其实很简单,因为集群hive为CDH5.8版本,所以也使用cloudera的hive-jdbc版本就行,可以去cloudera官网下载 https://www.cloudera.com/downloads/connectors/hive/jdbc/2-6-2.html

使用实例:

import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.apache.spark.SparkConf;
import org.apache.spark.SparkContext;
import org.apache.spark.sql.SQLContext;

public class SparkHiveJdbc {
    public static void main(String[] args) {

        String master = "local[1]";
        String appName = "SparkHiveJdbc";
        SparkConf sparkConf = new SparkConf();

        if (StringUtils.isNoneBlank(master) && master.startsWith("local")) {
            // 如果是本地模式
            sparkConf.setAppName(appName).setMaster(master);
        }

        SparkContext sc = SparkContext.getOrCreate(sparkConf);

        SQLContext sqlContext = new SQLContext(sc);

        /* 1. 读出为RDD */
        String readUrl = "jdbc:hive2://192.168.44.141:10001/db";
        String readTable = "log";
        String readUsername = "hive";
        String readPassword = "hive";
        String driverClassName = "com.cloudera.hive.jdbc41.HS2Driver";

        Properties readProperties = new Properties();
        readProperties.setProperty("user", readUsername);
        readProperties.setProperty("password", readPassword);
        readProperties.setProperty("driver", driverClassName);

        sqlContext
                .read().jdbc(readUrl, readTable,
                        new String[] { "day='20180801'" }, readProperties)
                .show();

        sc.stop();

    }
}

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
大数据
使用钉钉扫一扫加入圈子
+ 订阅

大数据计算实践乐园,近距离学习前沿技术

其他文章
最新文章
相关文章