通过JDBC连接Hive Server2

通过JDBC连接Hive Server2，首先要准备好JDBC连接所需要的JAR包。

C:\Program Files\DbVisualizer\jdbc\hive\commons-logging-1.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\hadoop-common-2.3.0-cdh5.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\hive-common-0.12.0-cdh5.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\hive-exec-0.12.0-cdh5.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\hive-jdbc-0.12.0-cdh5.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\hive-metastore-0.12.0-cdh5.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\hive-service-0.12.0-cdh5.1.3.jar
C:\Program Files\DbVisualizer\jdbc\hive\libfb303-0.9.0.jar
C:\Program Files\DbVisualizer\jdbc\hive\log4j-1.2.16.jar
C:\Program Files\DbVisualizer\jdbc\hive\slf4j-api-1.7.5.jar
C:\Program Files\DbVisualizer\jdbc\hive\slf4j-log4j12-1.7.5.jar
C:\Program Files\DbVisualizer\jdbc\hive\httpclient-4.2.5.jar
C:\Program Files\DbVisualizer\jdbc\hive\httpcore-4.2.5.jar

如果没添加最后两个，可能会出现下面的报错

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/http/HttpRequestInterceptor
     at org.apache.hive.jdbc.HiveDriver.connect(HiveDriver.java:105)
     at java.sql.DriverManager.getConnection(DriverManager.java:571)
     at java.sql.DriverManager.getConnection(DriverManager.java:215)
     at com.simon.HiveJDBCTest.main(HiveJDBCTest.java:33)
Caused by: java.lang.ClassNotFoundException: org.apache.http.HttpRequestInterceptor
     at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
     at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
     at java.security.AccessController.doPrivileged(Native Method)
     at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
     at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
     at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
     at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
     ... 4 more

第一步：JDBC连接需要设置好驱动程序，连接url等信息。

private static String driverName = "org.apache.hive.jdbc.HiveDriver";

连接URL

private static String url = "jdbc:hive2://<SERVER_IP>:<THRIFTSEVER2_PORT>/<DATABASE_NAME>";

如果需要设置队列
private static String url = "jdbc:hive2://<SERVER_IP>:<THRIFTSEVER2_PORT>/<DATABASE_NAME>?mapred.job.queue.name=<QUEUE_NAME>";

使用聚集函数
private static String url = "jdbc:hive2://<SERVER_IP>:<THRIFTSEVER2_PORT>/<DATABASE_NAME>?mapred.job.queue.name=<QUEUE_NAME>;hive.mapred.mode=nonstrict";

在Apache官方的wiki上，有关于连接URL的详细帮助

The HiveServer2 URL is a string with the following syntax:

jdbc:hive2://<host1>:<port1>,<host2>:<port2>/dbName;initFile=<file>;sess_var_list?hive_conf_list#hive_var_list

where

<host1>: <port1>,<host2>:<port2> is a server instance or a comma separated list of server instances to connect to (if dynamic service discovery is enabled). If empty, the embedded server will be used.

dbName is the name of the initial database.

<file> is the path of init script file (Hive 2.2.0 and later). This script file is written with SQL statements which will be executed automatically after connection. This option can be empty.

hive_conf_list is a semicolon separated list of key=value pairs of Hive configuration variables for this session

hive_var_list is a semicolon separated list of key=value pairs of Hive variables for this session.

说明一下

• 关于多台服务器实例的情况，可以用逗号把服务器分开，前提是
  在$HIVE_HOME/conf/hive-site.xml里面配置dynamic.service.discovery

<property>
     <name>hive.server2.support.dynamic.service.discovery</name>
     <value>true</value>
</property>

• 关于initFile，可以把脚本写到命令里，默认执行这里面的脚本，这个属于Hive2.2.0版本之后的功能

• 关于hive_conf_list
  当URL里面没有hive_conf_list ，也就是格式如下

private static String url = "jdbc:hive2://<SERVER_IP>：<THRIFTSEVER2_PORT>/<DATABASE_NAME>?mapred.job.queue.name=<QUEUE_NAME>;hive.mapred.mode=nonstrict";

代码如下：

 conn = DriverManager.getConnection(url, user,password );

• 关于hive_var_list

而当URL里面包含了sess_var_list，如下：

private static String url = "jdbc:hive2://<SERVER_IP>:<THRIFTSEVER2_PORT>/<DATABASE_NAME>;username=username;password=password?mapred.job.queue.name=<QUEUE_NAME>;hive.mapred.mode=nonstrict";

连接代码是

conn = DriverManager.getConnection(url);

第二步：通过反射机制获取驱动程序

try {
      Class.forName(driverName);
    } catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      System.exit(1);
    }

第三步：建立连接

conn = DriverManager.getConnection(url, user,password);

或者是

conn = DriverManager.getConnection(url);

第四步：创建statement

stmt = conn.createStatement();

第五步：准备SQL脚本

例如，查看所有的表：
sql = "show tables";

或者，统计test表有多少记录
sql = "select count(1) from test";

第六步：statement执行脚本

res = stmt.executeQuery(sql);

第七步，处理结果集

while(res.next()) {
     System.out.println(res.getString(1));
}

第八步，关闭statement

if (stmt != null) {
     stmt.close();
     stmt = null;
}

第九步，关闭连接

if (conn != null) {
     conn.close();
     conn = null;
}

注意，关闭statement和connection都要捕捉异常。整个过程可以使用try/catch/finally完成，将关闭异常处理放在finally里面。

详细资料，请使用
参考文档
https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients