Hive远程链接(hiveserver2/beeline)

前言

在生产中, 我们不可能每次都登陆到Hive安装的机器上进行操作. 在本章内, 我们就介绍下Hive远程链接操作.

远程链接Hive Server

Hive服务器是通过Thrift协议进行约定调用的. 我们通过启动bin/hiveserver2来启动服务端的操作. 通过bin/beeline来启动客户端.

PS: 历史版本貌似通过hive hiveserver来启动版本1, 个人猜测可能是因为Hadoop在2.x的架构上使用Yarn作为资源调度了, 导致Hive也跟着升级了.

服务端

^Clocalhost:bin Sean$ ./hiveserver2
2019-04-07 17:20:11: Starting HiveServer2
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding&＃61;UTF-8
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/Users/Sean/Software/Hive/apache-hive-2.3.4-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/Users/Sean/Software/hadoop/hadoop-2.7.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]


客户端

localhost:bin Sean$ ./beeline
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding&＃61;UTF-8
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/Users/Sean/Software/Hive/apache-hive-2.3.4-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/Users/Sean/Software/hadoop/hadoop-2.7.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Beeline version 2.3.4 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: Sean
Enter password for jdbc:hive2://localhost:10000: *********
Connected to: Apache Hive (version 2.3.4)
Driver: Hive JDBC (version 2.3.4)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000> show databases;
&＃43;----------------&＃43;
| database_name |
&＃43;----------------&＃43;
| default |
| flow |
&＃43;----------------&＃43;
2 rows selected (1.556 seconds)
0: jdbc:hive2://localhost:10000> use flow;
No rows affected (0.384 seconds)
0: jdbc:hive2://localhost:10000> show tables;
&＃43;---------------&＃43;
| tab_name |
&＃43;---------------&＃43;
| flowcount |
| t_phone_type |
&＃43;---------------&＃43;
2 rows selected (0.155 seconds)


• 链接 !connect jdbc:hive2://localhost:10000
• 输入当前用户的用户名与密码.(启动hiveserver2程序的用户.)(当然使用其他的配置也可以.)

Others

看到有配置Hive的运行目录等的配置. 一并记录如下:

Q & A

当然, 我在试验的过程中, 也不是一帆风顺的. 也稍微遇到一些的问题.

Q1: 远程登陆用户验证异常

Enter username for jdbc:hive2://localhost:10000:
Enter password for jdbc:hive2://localhost:10000:
19/04/07 16:17:36 [main]: WARN jdbc.HiveConnection: Failed to connect to localhost:10000
Error: Could not open client transport with JDBC Uri: jdbc:hive2://localhost:10000: Failed to open new session: java.lang.RuntimeException: >org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: Sean is not allowed to impersonate anonymous (state&＃61;08S01,code&＃61;0)


解决办法: 本来以为,默认用户登陆就不需要任何配置的. 后来发现想多了. 还是要输入某些用户的. 输入hiveserver2运行程序的当前用户重试即可.

Q2: 远程登陆用户验证异常

beeline> !connect jdbc:hive2://localhost:10000
Connecting to jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: Sean
Enter password for jdbc:hive2://localhost:10000: *********
19/04/07 16:19:57 [main]: WARN jdbc.HiveConnection: Failed to connect to localhost:10000
Error: Could not open client transport with JDBC Uri: >jdbc:hive2://localhost:10000: Failed to open new session: >java.lang.RuntimeException: >org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: Sean is not allowed to impersonate Sean (state&＃61;08S01,code&＃61;0)


解决办法: 更改了,输入用户的用户名和密码后. 它还是不让我登陆. 有点难受. 查询后, 原有有2点:

1. 是因为到Hive到Hadoop的超级用户配置没有配置.
2. Hive默认端口没开.(这个应该不用开, 只是我的电脑上有这个问题.)

具体操作:
解决1. 更改hadoop/etc/core-site.xml文件的配置. (中间的proxyuser.x.host的x为你的用户.)

hadoop.proxyuser.Sean.hosts*hadoop.proxyuser.Sean.groups*


解决2. hive-site.xml

hive.server2.thrift.port10000Port number of HiveServer2 Thrift interface when hive.server2.transport.mode is &＃39;binary&＃39;.


Q2: 删除表操作报错.
服务端报错:

localhost:bin Sean$ ./hiveserver2
2019-04-07 17:13:30: Starting HiveServer2
Picked up JAVA_TOOL_OPTIONS: -Dfile.encoding&＃61;UTF-8
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/Users/Sean/Software/Hive/apache-hive-2.3.4-b in/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/Users/Sean/Software/hadoop/hadoop-2.7.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
FAILED: Execution Error, return code 1 from >org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:You have >an error in your SQL syntax; check the manual that corresponds to your >MySQL server version for the right syntax to use near &＃39;OPTION >SQL_SELECT_LIMIT&＃61;DEFAULT&＃39; at line 1)
FAILED: SemanticException Unable to fetch table t_phone_type. You have an >error in your SQL syntax; check the manual that corresponds to your MySQL >server version for the right syntax to use near &＃39;OPTION >SQL_SELECT_LIMIT&＃61;DEFAULT&＃39; at line 1


客户端报错:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near &＃39;OPTION SQL_SELECT_LIMIT&＃61;DEFAULT&＃39; at line 1at sun.reflect.GeneratedConstructorAccessor60.newInstance(Unknown Source)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)at java.lang.reflect.Constructor.newInstance(Constructor.java:423)at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)at com.mysql.jdbc.Util.getInstance(Util.java:381)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)at com.mysql.jdbc.StatementImpl.executeSimpleNonQuery(StatementImpl.java:1531)at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1433)at com.mysql.jdbc.ConnectionImpl.getTransactionIsolation(ConnectionImpl.java:3216)at com.jolbox.bonecp.ConnectionHandle.getTransactionIsolation(ConnectionHandle.java:825)at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getConnection(ConnectionFactoryImpl.java:427)at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getXAResource(ConnectionFactoryImpl.java:361)at org.datanucleus.store.connection.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:316)at org.datanucleus.store.connection.AbstractConnectionFactory.getConnection(AbstractConnectionFactory.java:84)at org.datanucleus.store.AbstractStoreManager.getConnection(AbstractStoreManager.java:347)at org.datanucleus.store.AbstractStoreManager.getConnection(AbstractStoreManager.java:310)at org.datanucleus.store.rdbms.query.JDOQLQuery.performExecute(JDOQLQuery.java:591)at org.datanucleus.store.query.Query.executeQuery(Query.java:1855)at org.datanucleus.store.query.Query.executeWithArray(Query.java:1744)at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:368)... 53 more (state&＃61;08S01,code&＃61;1)
0: jdbc:hive2://localhost:10000> DROP TABLE t_phone_type;


解决办法: 读异常, 感觉是Mysql关键字问题. 经过网上搜索, 发现是Mysql-conncetor包的问题.hive/lib mysql-connector-java-5.1.08.jar 替换为 mysql-connector-java-5.1.44.jar后. 重启hiveserver2解决.
Reference :
[1]. 使用hive OPTION SQL_SELECT_LIMIT&＃61;DEFAULT
[2]. 【mysql案例】Java程序访问Mysql报错’OPTION SQL_SELECT_LIMIT&＃61;XXX’
[3]. check the manual that corresponds to your MySQL server version for the right syntax to use near
[4]. Hive删除表

Reference

[1]. [Hive]那些年我们踩过的Hive坑

[2]. beeline连接hiveserver2报错&＃xff1a;User: root is not allowed to impersonate root

[3]. hadoop组件—数据仓库(五)—通过JDBC连接hive的thrift或者hiveserver2

[4]. HIVE2 &＃xff1a;beeline连接设置用户名和密码注意问题

Hive设置连接用户名和密码