【已解决】Hive常见错误解决方案

发表于2021-04-10更新于2022-06-05字数统计2.8k阅读时长31分

在学习和工作过程中，遇到的一些Hive常见错误，在此记录下来。

文章目录

错误1：guava包冲突错误

Hive3启动报错，Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditio

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[wxler@wxler1 hive]$ bin/schematool -dbType derby -initSchema
Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V
	at org.apache.hadoop.conf.Configuration.set(Configuration.java:1357)
	at org.apache.hadoop.conf.Configuration.set(Configuration.java:1338)
	at org.apache.hadoop.mapred.JobConf.setJar(JobConf.java:518)
	at org.apache.hadoop.mapred.JobConf.setJarByClass(JobConf.java:536)
	at org.apache.hadoop.mapred.JobConf.<init>(JobConf.java:430)
	at org.apache.hadoop.hive.conf.HiveConf.initialize(HiveConf.java:5141)
	at org.apache.hadoop.hive.conf.HiveConf.<init>(HiveConf.java:5104)
	at org.apache.hive.beeline.HiveSchemaTool.<init>(HiveSchemaTool.java:96)
	at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1473)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:318)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:232)

解决方法：

原因是hadoop的guava包与hive的guava包版本不一致，比较两者的jar包，一般将最新版的拷贝进去，老版的删除

1
2
3
4
5
6
7
8
9
10
[wxler@wxler1 lib]$ ls | grep "guava*"
guava-19.0.jar
jersey-guava-2.25.1.jar
[wxler@wxler1 lib]$ pwd
/opt/module/hive/lib


[wxler@wxler1 hadoop-3.1.3]$ find ./ -name "guava*.jar"
./share/hadoop/common/lib/guava-27.0-jre.jar
./share/hadoop/hdfs/lib/guava-27.0-jre.jar

可以看到，/opt/module/hive/lib目录下的guava包比较旧，将其删除，然后将hadoop/common/lib/guava-27.0-jre.jar拷贝到hive的lib下即可。

错误2：Hive启动报错

启动Hive报错，错误信息如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
[wxler@wxler1 hive]$ bin/hive
which: no hbase in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/module/jdk1.8.0_212/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/home/wxler/.local/bin:/home/wxler/bin:/opt/module/jdk1.8.0_212/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/hive/bin)
Hive Session ID = 3e41a7fd-b559-4eee-9ffb-cfb75d6f911b

Logging initialized using configuration in jar:file:/opt/module/hive/lib/hive-common-3.1.2.jar!/hive-log4j2.properties Async: true
Exception in thread "main" java.lang.RuntimeException: java.net.ConnectException: Call From wxler1/192.168.218.71 to wxler1:8020 failed on connection exception: java.net.ConnectException: 拒绝连接; For more details see:  http://wiki.apache.org/hadoop/ConnectionRefused
	at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:651)
	at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:591)
	at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:747)
	at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:683)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.util.RunJar.run(RunJar.java:318)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:232)
Caused by: java.net.ConnectException: Call From wxler1/192.168.218.71 to wxler1:8020 failed on connection exception: java.net.ConnectException: 拒绝连接; For more details see:  http://wiki.apache.org/hadoop/ConnectionRefused
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at org.apache.hadoop.net.NetUtils.wrapWithMessage(NetUtils.java:831)
	at org.apache.hadoop.net.NetUtils.wrapException(NetUtils.java:755)
	at org.apache.hadoop.ipc.Client.getRpcResponse(Client.java:1549)
	at org.apache.hadoop.ipc.Client.call(Client.java:1491)
	at org.apache.hadoop.ipc.Client.call(Client.java:1388)
	at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:233)
	at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:118)
	at com.sun.proxy.$Proxy28.getFileInfo(Unknown Source)
	at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.getFileInfo(ClientNamenodeProtocolTranslatorPB.java:904)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:422)
	at org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeMethod(RetryInvocationHandler.java:165)
	at org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invoke(RetryInvocationHandler.java:157)
	at org.apache.hadoop.io.retry.RetryInvocationHandler$Call.invokeOnce(RetryInvocationHandler.java:95)
	at org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:359)
	at com.sun.proxy.$Proxy29.getFileInfo(Unknown Source)
	at org.apache.hadoop.hdfs.DFSClient.getFileInfo(DFSClient.java:1661)
	at org.apache.hadoop.hdfs.DistributedFileSystem$29.doCall(DistributedFileSystem.java:1577)
	at org.apache.hadoop.hdfs.DistributedFileSystem$29.doCall(DistributedFileSystem.java:1574)
	at org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)
	at org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus(DistributedFileSystem.java:1589)
	at org.apache.hadoop.fs.FileSystem.exists(FileSystem.java:1683)
	at org.apache.hadoop.hive.ql.exec.Utilities.ensurePathIsWritable(Utilities.java:4486)
	at org.apache.hadoop.hive.ql.session.SessionState.createRootHDFSDir(SessionState.java:760)
	at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:701)
	at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:627)
	... 9 more
Caused by: java.net.ConnectException: 拒绝连接
	at sun.nio.ch.SocketChannelImpl.checkConnect(Native Method)
	at sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:717)
	at org.apache.hadoop.net.SocketIOWithTimeout.connect(SocketIOWithTimeout.java:206)
	at org.apache.hadoop.net.NetUtils.connect(NetUtils.java:531)
	at org.apache.hadoop.ipc.Client$Connection.setupConnection(Client.java:700)
	at org.apache.hadoop.ipc.Client$Connection.setupIOstreams(Client.java:804)
	at org.apache.hadoop.ipc.Client$Connection.access$3800(Client.java:421)
	at org.apache.hadoop.ipc.Client.getConnection(Client.java:1606)
	at org.apache.hadoop.ipc.Client.call(Client.java:1435)
	... 34 more

解决方法：

先启动hadoop集群，然后再启动hive

1
myhadoop.sh start

错误3：执行Hive命令报错

执行show databases报错如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
hive> show databases;
FAILED: HiveException java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
hive> [wxler@wxler1 hive]$ bin/schematool -dbType derby -initSchema
Metastore connection URL:	 jdbc:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver :	 org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:	 APP
Starting metastore schema initialization to 3.1.0
Initialization script hive-schema-3.1.0.derby.sql

 
Error: FUNCTION 'NUCLEUS_ASCII' already exists. (state=X0Y68,code=30000)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
Underlying cause: java.io.IOException : Schema script failed, errorcode 2
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

解决方法：
将hive下的metastore_db更名为：metastore_db.tmp

或者直接删除：rm -rf metastore_db

错误4：使用beeline启动报错

执行bin/beeline -u jdbc:hive2://wxler1:10000 -n wxler错误信息如下：

1
2
3
4
5
[wxler@wxler1 hive]$ bin/beeline -u jdbc:hive2://wxler1:10000 -n wxler
Connecting to jdbc:hive2://wxler1:10000
21/04/11 16:25:28 [main]: WARN jdbc.HiveConnection: Failed to connect to wxler1:10000
Error: Could not open client transport with JDBC Uri: jdbc:hive2://wxler1:10000: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: wxler is not allowed to impersonate wxler (state=08S01,code=0)
Beeline version 3.1.2 by Apache Hive

解决方法：

在hadoop的core-site.xml添加

1
2
3
4
5
6
7
8
<property>
       <name>hadoop.proxyuser.wxler.hosts</name>
       <value>*</value>
   </property>
   <property>   
       <name>hadoop.proxyuser.wxler.groups</name>
       <value>*</value>
</property>

分发配置到其它hadoop结点上，然后重启hadoop

错误5：Hive配置tez报错

Hive配置tez启动不起来，报错信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
[2021-04-22 14:38:11.639]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hadoop-3.1.3/data/nm-local-dir/filecache/10/tez-0.10.1-SNAPSHOT.tar.gz/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-3.1.3/share/hadoop/common/lib/slf4j-log4j12-1.7.25.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.slf4j.impl.Log4jLoggerFactory]


For more detailed output, check the application tracking page: http://wxler2:8088/cluster/app/application_1619073432538_0001 Then click on links to logs of each attempt.
. Failing the application.
	at java.util.concurrent.FutureTask.report(FutureTask.java:122) ~[?:1.8.0_212]
	at java.util.concurrent.FutureTask.get(FutureTask.java:206) ~[?:1.8.0_212]
	at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.isOpen(TezSessionState.java:209) ~[hive-exec-3.1.2.jar:3.1.2]
	... 22 more
Caused by: org.apache.tez.dag.api.SessionNotRunning: TezSession has already shutdown. Application application_1619073432538_0001 failed 2 times due to AM Container for appattempt_1619073432538_0001_000002 exited with  exitCode: 1
Failing this attempt.Diagnostics: [2021-04-22 14:38:11.628]Exception from container-launch.
Container id: container_1619073432538_0001_02_000001
Exit code: 1

[2021-04-22 14:38:11.636]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hadoop-3.1.3/data/nm-local-dir/filecache/10/tez-0.10.1-SNAPSHOT.tar.gz/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-3.1.3/share/hadoop/common/lib/slf4j-log4j12-1.7.25.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.slf4j.impl.Log4jLoggerFactory]


[2021-04-22 14:38:11.639]Container exited with a non-zero exit code 1. Error file: prelaunch.err.
Last 4096 bytes of prelaunch.err :
Last 4096 bytes of stderr :
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hadoop-3.1.3/data/nm-local-dir/filecache/10/tez-0.10.1-SNAPSHOT.tar.gz/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-3.1.3/share/hadoop/common/lib/slf4j-log4j12-1.7.25.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.slf4j.impl.Log4jLoggerFactory]


For more detailed output, check the application tracking page: http://wxler2:8088/cluster/app/application_1619073432538_0001 Then click on links to logs of each attempt.
. Failing the application.
	at org.apache.tez.client.TezClient.waitTillReady(TezClient.java:1013) ~[tez-api-0.10.1-SNAPSHOT.jar:0.10.1-SNAPSHOT]
	at org.apache.tez.client.TezClient.waitTillReady(TezClient.java:982) ~[tez-api-0.10.1-SNAPSHOT.jar:0.10.1-SNAPSHOT]
	at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.startSessionAndContainers(TezSessionState.java:453) ~[hive-exec-3.1.2.jar:3.1.2]
	at org.apache.hadoop.hive.ql.exec.tez.TezSessionState.access$100(TezSessionState.java:101) ~[hive-exec-3.1.2.jar:3.1.2]
	at org.apache.hadoop.hive.ql.exec.tez.TezSessionState$1.call(TezSessionState.java:376) ~[hive-exec-3.1.2.jar:3.1.2]
	at org.apache.hadoop.hive.ql.exec.tez.TezSessionState$1.call(TezSessionState.java:371) ~[hive-exec-3.1.2.jar:3.1.2]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_212]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_212]
2021-04-22T14:45:08,376  INFO [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] reexec.ReOptimizePlugin: ReOptimization: retryPossible: false
2021-04-22T14:45:08,376 ERROR [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] ql.Driver: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.tez.TezTask
2021-04-22T14:45:08,376  INFO [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] ql.Driver: Completed executing command(queryId=wxler_20210422144507_049f3acb-18e0-4a2c-8144-a6d9c5f3a8f3); Time taken: 0.001 seconds
2021-04-22T14:45:08,376  INFO [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] ql.Driver: Concurrency mode is disabled, not creating a lock manager
2021-04-22T14:45:08,376  INFO [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] exec.ListSinkOperator: RECORDS_OUT_INTERMEDIATE:0, RECORDS_OUT_OPERATOR_LIST_SINK_12:0, 
2021-04-22T14:45:08,404  INFO [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] conf.HiveConf: Using the default value passed in for log id: 7ab6cc19-cf77-4f0b-94f2-6234123f8a30
2021-04-22T14:45:08,404  INFO [7ab6cc19-cf77-4f0b-94f2-6234123f8a30 main] session.SessionState: Resetting thread name to  main

参考网上的博客，我做了下面的更改，还是不行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#执行
find /opt/ -name "slf4j*.jar"

#输出
/opt/module/hadoop-3.1.3/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar
/opt/module/hadoop-3.1.3/share/hadoop/common/lib/slf4j-api-1.7.25.jar
/opt/module/hadoop-3.1.3/data/nm-local-dir/filecache/10/tez-0.10.1-SNAPSHOT.tar.gz/lib/slf4j-api-1.7.10.jar
/opt/module/hadoop-3.1.3/data/nm-local-dir/filecache/10/tez-0.10.1-SNAPSHOT.tar.gz/lib/slf4j-log4j12-1.7.10.jar
/opt/module/tez/lib/slf4j-api-1.7.10.jar

#执行
cd /opt/module/hadoop-3.1.3/data/nm-local-dir/filecache/10/tez-0.10.1-SNAPSHOT.tar.gz/lib/

#第一步
mv slf4j-log4j12-1.7.10.jar slf4j-log4j12-1.7.10.jar.bak
#第二步
rm /opt/module/tez/lib/slf4j-log4j12-1.7.10.jar
#第三步
关闭检查虚拟内容

最后解决不了，放弃了。

错误6：Invalid table alias or column reference

在执行hql时报如下的错误：

1
FAILED: SemanticException [Error 10004]: Line 6:32 Invalid table alias or column reference 'b': (possible column names are: _col0, _col1)

我执行的hql如下：

1
2
3
4
5
6
7
SELECT count（*）TotalCount，b.region_code 
 from XXX a 
 INNER JOIN YYY b 
 ON a.uid = b.uid 
 where a.dt ='2015-04-15'
 group by b.region_code
 order by b.region_code

看起来很简单的一个语句，但是为什么会出错呢？

根据网上的解释：Hive在执行完成group by之后其实已经默认把以上所有的语句当成了一条，也就是说

1
2
3
4
5
6
SELECT count（*）TotalCount，b.region_code 
 from XXX a 
 INNER JOIN YYY b 
 ON a.uid = b.uid 
 where a.dt ='2015-04-15'
 group by b.region_code

在执行完成后，Hive把它当成一个语句，只认得TotalCount和region_code，自然b就不认得了，所以改成下列语句就行了

1
2
3
4
5
6
7
SELECT count（* ）as TotalCount，b.region_code 
 from XXX a INNER JOIN 
 YYY b 
 ON a.ui = b.uid 
 where a.dt ='2015-04-15' 
 group by b.region_code 
 order by region_code