Hive用户接口(二)—使用Hive JDBC驱动连接Hive操作实例
问题导读:
1、Hive提供了哪三种用户访问方式?
2、使用HiveServer时候,需要首先启动哪个服务?
3、HiveServer的启动命令是?
4、HiveServer是通过哪个服务来提供远程JDBC访问的?
5、如何修改HiveServer的默认启动端口?
6、Hive JDBC驱动连接需要哪些包?
7、HiveServer2与HiveServer在使用上的不同点?
Hive提供了三种用户接口:CLI、HWI和客户端。其中客户端即是使用JDBC驱动通过thrift,远程操作Hive。HWI即提供Web界面远程访问Hive,可参考我的另外一篇博文:Hive用户接口(一)—Hive Web接口HWI的操作及使用。但是最常见的使用方式还是使用CLI方式。下面介绍Hive使用JDBC驱动连接操作Hive,我的Hive版本是Hive-0.13.1。
Hive JDBC驱动连接分为两种,早期的是HiveServer,最新的是HiveServer2,前者本身存在很多的问题,如安全性、并发性等,后者很好的解决了诸如安全性和并发性等问题。我先介绍HiveServer的用法。
一、启动元数据MetaStore
使用任何方式连接Hive,都首先需要启动Hive元数据服务,否则执行HQL操作无法进行。
- [hadoopUser@secondmgt ~]$ hive --service metastore
- Starting Hive Metastore Server
- 15/01/11 20:11:56 INFO Configuration.deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
- 15/01/11 20:11:56 INFO Configuration.deprecation: mapred.min.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize
- 15/01/11 20:11:56 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
- 15/01/11 20:11:56 INFO Configuration.deprecation: mapred.min.split.size.per.node is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.node
- 15/01/11 20:11:56 INFO Configuration.deprecation: mapred.input.dir.recursive is deprecated. Instead, use mapreduce.input.fileinputformat.input.dir.recursive
HiveServer使用thrift服务来为客户端提供远程连接的访问端口,在JDBC连接Hive之前必须先启动HiveServer。
- [hadoopUser@secondmgt ~]$ hive --service hiveserver
- Starting Hive Thrift Server
- 15/01/12 10:22:54 INFO Configuration.deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
- 15/01/12 10:22:54 INFO Configuration.deprecation: mapred.min.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize
- 15/01/12 10:22:54 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
- 15/01/12 10:22:54 INFO Configuration.deprecation: mapred.min.split.size.per.node is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.node
- 15/01/12 10:22:54 INFO Configuration.deprecation: mapred.input.dir.recursive is deprecated. Instead, use mapreduce.input.fileinputformat.input.dir.recursive
注意:hiveserver不能和hwi服务同时启动使用。
三、在IDE中创建Hive工程
我们使用Eclipse作为开发IDE,在Eclipse中创建hive工程,并导入Hive JDBC远程连接相关包,所需的包如下所示:
- hive-jdbc-0.13.1.jar
- commons-logging-1.1.3.jar
- hive-exec-0.13.1.jar
- hive-metastore-0.13.1.jar
- hive-service-0.13.1.jar
- libfb303-0.9.0.jar
- slf4j-api-1.6.1.jar
- hadoop-common-2.2.0.jar
- log4j-1.2.16.jar
- slf4j-nop-1.6.1.jar
- httpclient-4.2.5.jar
- httpcore-4.2.5.jar
- package com.gxnzx.hive;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class HiveServer2 {
- private static Connection conn=null;
- public static void main(String args[]){
- try {
- Class.forName("org.apache.hadoop.hive.jdbc.HiveDriver");
- conn=DriverManager.getConnection("jdbc:hive://192.168.2.133:10000/hive", "hadoopUser", "");
- Statement st=conn.createStatement();
- String sql1="select name,age from log";
- ResultSet rs=st.executeQuery(sql1);
- while(rs.next()){
- System.out.println(rs.getString(1)+" "+rs.getString(2));
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
- Tom 19
- Jack 21
- HaoNing 12
- Hadoop 20
- Rose 23
hiveserver2在安全性和并发性等方面比hiveserver好,在JDBC实现上面差别不大,主要有以下方面不同:
1、服务启动不一样,首先要启动hiveserver2服务
- [hadoopUser@secondmgt ~]$ hive --service hiveserver2
- Starting HiveServer2
- 15/01/12 10:13:42 INFO Configuration.deprecation: mapred.reduce.tasks is deprecated. Instead, use mapreduce.job.reduces
- 15/01/12 10:13:42 INFO Configuration.deprecation: mapred.min.split.size is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize
- 15/01/12 10:13:42 INFO Configuration.deprecation: mapred.reduce.tasks.speculative.execution is deprecated. Instead, use mapreduce.reduce.speculative
- 15/01/12 10:13:42 INFO Configuration.deprecation: mapred.min.split.size.per.node is deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize.per.node
- 15/01/12 10:13:42 INFO Configuration.deprecation: mapred.input.dir.recursive is deprecated. Instead, use mapreduce.input.fileinputformat.input.dir.recursive
2、驱动名不一样
- HiveServer—>org.apache.hadoop.hive.jdbc.HiveDriver
- HiveServer2—>org.apache.hive.jdbc.HiveDriver
- HiveServer—>DriverManager.getConnection("jdbc:hive://<host>:<port>", "<user>", "");
- HiveServer2—>DriverManager.getConnection("jdbc:hive2://<host>:<port>", "<user>", "");
- package com.gxnzx.hive;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class HiveJDBCTest {
- private static Connection conn=null;
- public static void main(String args[]){
- try {
- Class.forName("org.apache.hive.jdbc.HiveDriver");
- conn=DriverManager.getConnection("jdbc:hive2://192.168.2.133:10000/hive", "hadoopUser", "");
- Statement st=conn.createStatement();
- String sql1="select name,age from log";
- ResultSet rs=st.executeQuery(sql1);
- while(rs.next()){
- System.out.println(rs.getString(1)+" "+rs.getString(2));
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- }
- }
- }
异常或错误一
- SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
- SLF4J: Defaulting to no-operation (NOP) logger implementation
- Failed to load class org.slf4j.impl.StaticLoggerBinder
- This error is reported when the org.slf4j.impl.StaticLoggerBinder class could not be loaded into memory. This happens when no appropriate SLF4J binding could be found on the class path. Placing one (and only one) of slf4j-nop.jar, slf4j-simple.jar, slf4j-log4j12.jar, slf4j-jdk14.jar or logback-classic.jar on the class path should solve the problem.
- since 1.6.0 As of SLF4J version 1.6, in the absence of a binding, SLF4J will default to a no-operation (NOP) logger implementation.
异常或错误二
- Job Submission failed with exception 'org.apache.hadoop.security.AccessControlException(Permission denied: user=anonymous,
- access=EXECUTE, inode="/tmp":hadoopUser:supergroup:drwx------
- at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.check(FSPermissionChecker.java:234)
- at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkTraverse(FSPermissionChecker.java:187)
- at org.apache.hadoop.hdfs.server.namenode.FSPermissionChecker.checkPermission(FSPermissionChecker.java:150)
- at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkPermission(FSNamesystem.java:5185)
- at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkPermission(FSNamesystem.java:5167)
- at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.checkOwner(FSNamesystem.java:5123)
- at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.setPermissionInt(FSNamesystem.java:1338)
- at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.setPermission(FSNamesystem.java:1317)
- at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.setPermission(NameNodeRpcServer.java:528)
- at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.setPermission
- (ClientNamenodeProtocolServerSideTranslatorPB.java:348)
- at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod
- (ClientNamenodeProtocolProtos.java:59576)
- at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:585)
- at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:928)
- at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2048)
- at org.apache.hadoop.ipc.Server$Handler$1.run(Server.java:2044)
- at java.security.AccessController.doPrivileged(Native Method)
- at javax.security.auth.Subject.doAs(Subject.java:415)
- at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1491)
- at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2042)
- conn=DriverManager.getConnection("jdbc:hive2://192.168.2.133:10000/hive", "", "");
解决办法:
- conn=DriverManager.getConnection("jdbc:hive2://192.168.2.133:10000/hive", "hadoopUser", "");
更多内容,请参考官网网址学习:HiveServer2 Clients。