java:jdbc连接hive2,1.1.0-cdh5.7.6版本

虽然过程磕磕绊绊，但好歹还是给我连成功了！愉快得记录下过程吧~

1 首先查询hive 和 hadoop版本

查询hive：

参考hive版本查看：https://blog.csdn.net/zyzzxycj/article/details/79268754

命令：jps     找到RunJar的id

命令：lsof -g 204273 | grep hive-service

所以找到版本是：1.1.0-cdh5.7.6

[root@cdp3 opt]# jps
62211 -- process information unavailable
172715 -- process information unavailable
62185 -- process information unavailable
270190 -- process information unavailable
63057 -- process information unavailable
204273 RunJar
78167 -- process information unavailable
24025 HugeGraphServer
247418 HugeGraphStudio
136024 -- process information unavailable
270174 -- process information unavailable
172991 -- process information unavailable
204732 Jps
[root@cdp3 opt]# lsof -g 204273 | grep hive-service
java    204273 204273 root  mem    REG                8,3   2050656  8005252 /opt/cloudera/parcels/CDH-5.7.6-1.cdh5.7.6.p0.6/jars/hive-service-1.1.0-cdh5.7.6.jar
java    204273 204273 root  219r   REG                8,3   2050656  8005252 /opt/cloudera/parcels/CDH-5.7.6-1.cdh5.7.6.p0.6/jars/hive-service-1.1.0-cdh5.7.6.jar

查看hadoop版本:

命令：hadoop version

此处hadoop版本：2.6.0-cdh5.7.6

[root@cdp3 opt]# hadoop version
Hadoop 2.6.0-cdh5.7.6
Subversion http://github.com/cloudera/hadoop -r a3edabab129caaada2958ab32061aa0e52a3f7df
Compiled by jenkins on 2017-02-21T23:08Z
Compiled with protoc 2.5.0
From source with checksum 3de3a6f2894b326919cd7a9b4e573e
This command was run using /opt/cloudera/parcels/CDH-5.7.6-1.cdh5.7.6.p0.6/jars/hadoop-common-2.6.0-cdh5.7.6.jar

2 搭建maven项目，代码和配置走起

pom.xml文件中引入如下代码：

    <dependencies>
       <!--  jdbc连接 和 hadoop版本-->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.1.0-cdh5.12.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>2.6.0-cdh5.7.6</version>
        </dependency>
        <!--   cdh版本的需要引用客户端 -->
        <dependency>
            <groupId>org.apache.hbase</groupId>
            <artifactId>hbase-client</artifactId>
            <version>1.2.0-cdh5.7.6</version>
        </dependency>
    </dependencies>

    <repositories>
        <repository>
            <id>cloudera</id>
            <url>https://repository.cloudera.com/artifactory/cloudera-repos/</url>
        </repository>
    </repositories>

其实hive-jdbc是有1.1.0-cdh5.7.6版本，但会报如下错误，而且吧，去找TCLIService这个类，还真找不到。不是版本有问题就是包有问题，反正我就大胆的换了个包，换成1.1.0-cdh5.12.1版本也行。能用哒！

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/hive/service/cli/thrift/TCLIService$Iface
	at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at HiveJdbc.main(HiveJdbc.java:19)
Caused by: java.lang.ClassNotFoundException: org.apache.hive.service.cli.thrift.TCLIService$Iface
	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	... 4 more

3 Java代码的JDBC连接

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

public class HiveJdbc {
    /*hiverserver 版本使用此驱动*/
    //private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
    /*hiverserver2 版本使用此驱动*/
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";

    public static void main(String[] args) throws Exception {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
        Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.0.97:10000/default", "hdfs", "hdfs");
        /**  root 用户能查询 select * from t_emp, 但查询 select * from t_emp where age > 30 会报错 ，可能是权限问题，还没有彻底试过。 用hdfs用户就查询*/
      //  Connection conn = DriverManager.getConnection("jdbc:hive2://192.168.0.97:10000/default", "root", "123456");
        try {
            Statement st = conn.createStatement();
            ResultSet ret = st.executeQuery("select * from t_emp where age > 30");
            if (ret.next()) {
                while (ret.next()) {
                    StringBuilder sb = new StringBuilder();
                    sb.append("id:" + ret.getInt("id"));
                    sb.append("  name:" + ret.getString("name"));
                    sb.append("  age:" + ret.getInt("age"));
                    //sb.append("  deptName:" + ret.getString("dept_name"));
                    System.out.println(sb.toString());
                }
                System.out.println(ret.getInt(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
    }
}