hive使用中常见错误

最新推荐文章于 2024-02-06 09:55:48 发布

starsky20

最新推荐文章于 2024-02-06 09:55:48 发布

阅读量3.5w

点赞数 14

分类专栏： hive 大数据-hadoop

本文链接：https://blog.csdn.net/seashouwang/article/details/77867134

版权

大数据-hadoop 同时被 2 个专栏收录

24 篇文章 1 订阅

订阅专栏

hive

3 篇文章 0 订阅

订阅专栏

1.1. hive错误

1.1.1. hive2.3初始化mysql不起作用

[root@localhost65bin]# schematool -initSchema -dbType mysql--verbose

Metastore connectionURL: jdbc:derby:;databaseName=metastore_db;create=true

Metastore ConnectionDriver : org.apache.derby.jdbc.EmbeddedDriver

Metastore connectionUser: APP

Starting metastoreschema initialization to 2.3.0

Initialization scripthive-schema-2.3.0.mysql.sql

Connecting tojdbc:derby:;databaseName=metastore_db;create=true

Connected to: ApacheDerby (version 10.10.2.0 - (1582446))

Driver: Apache DerbyEmbedded JDBC Driver (version 10.10.2.0 - (1582446))

Transaction isolation:TRANSACTION_READ_COMMITTED

0: jdbc:derby:>!autocommit on

Autocommit status: true

0: jdbc:derby:> /*!40101SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */

Error: Syntax error:Encountered "<EOF>" at line 1, column 64.(state=42X01,code=30000)

Closing: 0:jdbc:derby:;databaseName=metastore_db;create=true

org.apache.hadoop.hive.metastore.HiveMetaException:Schema initialization FAILED! Metastore state would be inconsistent !!

Underlying cause:java.io.IOException : Schema script failed, errorcode 2

org.apache.hadoop.hive.metastore.HiveMetaException:Schema initialization FAILED! Metastore state would be inconsistent !!

atorg.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:590)

atorg.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:563)

at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1145)

atsun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

atjava.lang.reflect.Method.invoke(Method.java:497)

atorg.apache.hadoop.util.RunJar.run(RunJar.java:221)

atorg.apache.hadoop.util.RunJar.main(RunJar.java:136)

Caused by:java.io.IOException: Schema script failed, errorcode 2

at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:980)

atorg.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:959)

atorg.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:586)

... 8 more

*** schemaTool failed***

[root@localhost65bin]#

hive2.3初始化mysql不起作用，主要原因是安装包自身有问题。删除该解压文件，使用其他版本。

1.1.2. org.apache.hadoop.hive.ql.exec.DDLTask

hive> show databases;

FAILED: Error in metadata: javax.jdo.JDOFatalInternalException: Unexpected exception caught.

NestedThrowables:

java.lang.reflect.InvocationTargetException

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

解决办法：

将hadoop_home路径下的build目录移除，然后再启动hive

1.1.3. Duplicate key name'PCS_STATS_IDX

No rows affected(0.004 seconds)

0: jdbc:mysql://192.168.3.65:3306/hive>CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS(DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME) USING BTREE

Error: Duplicate key name'PCS_STATS_IDX' (state=42000,code=1061)

Closing: 0:jdbc:mysql://192.168.3.65:3306/hive?createDatabaseIfNotExist=true&characterEncoding=UTF-8&useSSL=false

org.apache.hadoop.hive.metastore.HiveMetaException:Schema initialization FAILED! Metastore state would be inconsistent !!

Underlying cause:java.io.IOException : Schema script failed, errorcode 2

org.apache.hadoop.hive.metastore.HiveMetaException:Schema initialization FAILED! Metastore state would be inconsistent !!

atorg.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:291)

atorg.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:264)

atorg.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:505)

atsun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

atjava.lang.reflect.Method.invoke(Method.java:497)

atorg.apache.hadoop.util.RunJar.run(RunJar.java:221)

atorg.apache.hadoop.util.RunJar.main(RunJar.java:136)

Caused by: java.io.IOException:Schema script failed, errorcode 2

atorg.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:390)

atorg.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:347)

at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:287)

... 8 more

*** schemaTool failed***

[root@localhost65apache-hive-2.1.1-bin]#

解决办法1：

“Error: Duplicate keyname 'PCS_STATS_IDX'”

这是由于之前曾经格式化一次，或者有表未导入，mysql中的hive库中有残留的数据，残留的表，将mysql中的hive库删掉重新创建，或者删掉hive中的表；再次格式化

解决办法2：

将hive 所在节点上的/usr/local/hive/script/metastore/upgrade/msql/hive-schema-1.2.1000.mysql.sql拷贝到HIVE所连接的数据库，

然后中HIVE库中执行这个脚本就OK 了。

1.1.4. Error: Syntax error:Encountered

安装好MySQL后，使用初始化命令 schematool -dbType mysql -initSchema对Hive初始化时，出现以下异常：Error: Syntax error:Encountered “” at line 1, colume64.(state=42x01,code=30000)

解决办法:

上述截图中，可以看到hive的数据库驱动还是derby，因此需要将hive-site.xml中的hive 连接数据库的驱动修改一下。打开hive-site.xml，找到如下区域。

按图片改正即可。需要注意，在hive的lib下，需要有mysql-connector-Java-5.x.x-bin.jar这个文件。还有在这个配置文件中，hiveConnectionDriverName 可能不止一个，因此需要删除其余的，保留这一个才行。

1.1.5. org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

[root@localhost65apache-hive-1.2.2-bin]# hive

SLF4J: Class pathcontains multiple SLF4J bindings.

SLF4J: Found bindingin[jar:file:/usr/local/apache-hive-2.1.1-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found bindingin[jar:file:/usr/local/hadoop-2.6.2/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Seehttp://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual bindingis of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initializedusing configuration in file:/usr/local/apache-hive-2.1.1-bin/conf/hive-log4j2.propertiesAsync: true

Exception in thread"main" java.lang.RuntimeException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException:Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

atorg.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:591)

atorg.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:531)

atorg.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:705)

at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641)

atsun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

atjava.lang.reflect.Method.invoke(Method.java:497)

atorg.apache.hadoop.util.RunJar.run(RunJar.java:221)

atorg.apache.hadoop.util.RunJar.main(RunJar.java:136)

Caused by:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException:Unable to instantiateorg.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

atorg.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:226)

atorg.apache.hadoop.hive.ql.metadata.Hive.<init>(Hive.java:366)

atorg.apache.hadoop.hive.ql.metadata.Hive.create(Hive.java:310)

atorg.apache.hadoop.hive.ql.metadata.Hive.getInternal(Hive.java:290)

atorg.apache.hadoop.hive.ql.metadata.Hive.get(Hive.java:266)

at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:558)

... 9 more

Caused by:java.lang.RuntimeException: Unable to instantiateorg.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1654)

atorg.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:80)

atorg.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:130)

at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:101)

atorg.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3367)

atorg.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3406)

at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3386)

atorg.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3640)

atorg.apache.hadoop.hive.ql.metadata.Hive.reloadFunctions(Hive.java:236)

atorg.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:221)

... 14 more

Caused by:java.lang.reflect.InvocationTargetException

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(NativeMethod)

atsun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

atsun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

atjava.lang.reflect.Constructor.newInstance(Constructor.java:422)

atorg.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1652)

... 23 more

Caused by:MetaException(message:Version information not found in metastore. )

atorg.apache.hadoop.hive.metastore.ObjectStore.checkSchema(ObjectStore.java:7753)

atorg.apache.hadoop.hive.metastore.ObjectStore.verifySchema(ObjectStore.java:7731)

atsun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

atjava.lang.reflect.Method.invoke(Method.java:497)

atorg.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:101)

atcom.sun.proxy.$Proxy21.verifySchema(Unknown Source)

at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:565)

atorg.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:626)

atorg.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:416)

atorg.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:78)

atorg.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:84)

atorg.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:6490)

atorg.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:238)

atorg.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:70)

... 28 more

[root@localhost65apache-hive-1.2.2-bin]#

[root@localhost65apache-hive-1.2.2-bin]# ls

bin conf examples hcatalog lib LICENSE NOTICE README.txt RELEASE_NOTES.txt scripts

[root@localhost65 apache-hive-1.2.2-bin]#

原因分析：

是由于没有初始化数据库导致，执行名称初始化数据库即可。schematool -dbType mysql -initSchema

1.1.6. SLF4J: Found binding in log4j-slf4j-impl-2.4.1.jar

[root@localhost65apache-hive-2.1.1-bin]# bin/hive

SLF4J: Class pathcontains multiple SLF4J bindings.

SLF4J: Found binding in[jar:file:/usr/local/apache-hive-2.1.1-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Found bindingin[jar:file:/usr/local/hadoop-2.6.2/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]

SLF4J: Seehttp://www.slf4j.org/codes.html#multiple_bindings for an explanation.

SLF4J: Actual bindingis of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initializedusing configuration in file:/usr/local/apache-hive-2.1.1-bin/conf/hive-log4j2.propertiesAsync: true

atorg.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:591)

。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。

The last packet sentsuccessfully to the server was 0 milliseconds ago. The

问题原因：

由于hive中的日志和hadoop中日志包冲突导致。

解决办法：

将hive中的日志包去除即可(不要删除，修改名称即可)。如下;

[root@localhost65apache-hive-2.1.1-bin]# mv/usr/local/apache-hive-2.1.1-bin/lib/log4j-slf4j-impl-2.4.1.jar/usr/local/apache-hive-2.1.1-bin/lib/log4j-slf4j-impl-2.4.1.jar.bak

[root@localhost65apache-hive-2.1.1-bin]#

1.1.7. javax.jdo.JDOFatalDataStoreException:Unable to open a test connection

[root@localhost65bin]# hive

Logging initializedusing configuration infile:/usr/local/apache-hive-2.1.1-bin/conf/hive-log4j2.properties Async: true

Exception in thread"main" java.lang.RuntimeException:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException:Unable to instantiateorg.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:591)

atorg.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:531)

atorg.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:705)

at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641)

atsun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

atjava.lang.reflect.Method.invoke(Method.java:497)

atorg.apache.hadoop.util.RunJar.run(RunJar.java:221)

atorg.apache.hadoop.util.RunJar.main(RunJar.java:136)

Caused by:org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException:Unable to instantiateorg.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

atorg.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:226)

atorg.apache.hadoop.hive.ql.metadata.Hive.<init>(Hive.java:366)

atorg.apache.hadoop.hive.ql.metadata.Hive.create(Hive.java:310)

atorg.apache.hadoop.hive.ql.metadata.Hive.getInternal(Hive.java:290)

atorg.apache.hadoop.hive.ql.metadata.Hive.get(Hive.java:266)

at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:558)

... 9 more

Caused by:java.lang.RuntimeException: Unable to instantiateorg.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

atorg.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1654)