开发者社区> 填坑大人> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

通过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();

    }
}

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

相关文章
使用QueryRunner报错:java.sql.SQLException: Cannot create
使用QueryRunner报错:java.sql.SQLException: Cannot create
0 0
Caused by: java.sql.SQLException: No value specified for parameter 1的解决过程
Caused by: java.sql.SQLException: No value specified for parameter 1的解决过程
0 0
spark中连接oracle报异常java.sql.SQLException: No suitable driver
本地环境运行正常,spark任务放到spark集群上运行出错
0 0
java.sql.SQLException: sql injection violation
本文目录 1. 报错信息 2. 问题分析 3. 排除法 4. 解决方案
0 0
java.sql.SQLException: No suitable driver 问题解决
最近在学习java,用到c3p0数据库连接池,遇到一个很奇怪的现象,用main方法测试是可以正常连接数据库的,但是使用jsp调用代码,就会报如下图的错误!   最下面的java.sql.SQLException: No suitable driver才是重点 解决办法是将mysql的驱动jar包,放到D:\Program Files\Java\jdk1.
1670 0
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
详细错误信息: org.springframework.dao.TransientDataAccessResourceException: ### Error querying database.
570 0
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
版权声明:本文为 testcs_dn(微wx笑) 原创文章,非商用自由转载-保持署名-注明出处,谢谢。 https://blog.csdn.net/testcs_dn/article/details/50015665 详细错误信息: org.
587 0
Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Connectio
严重: StandardWrapper.Throwableorg.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.
914 0
+关注
填坑大人
码了十余年砖,谈得了需求、设计得了架构、编得了文档、撸得了代码。写得了前端,做得了后台,搞得了数据。最近对大数据颇感兴趣
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
The state of SQL-on-Hadoop in the Cloud
立即下载
Major advancements in Apache Hive towards full support of SQL compliance
立即下载