执行SparkSQL脚本后hive出错 FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException

错误信息

hive> show databases;;
FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
hive> use test_1901;
FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

出现问题的原因

在某个节点执行SparkSQL脚本

19/06/30 14:49:23 INFO ui.SparkUI: Stopped Spark web UI at http://192.168.2.101:4040
19/06/30 14:49:23 INFO spark.MapOutputTrackerMasterEndpoint: MapOutputTrackerMasterEndpoint stopped!
19/06/30 14:49:23 INFO memory.MemoryStore: MemoryStore cleared
19/06/30 14:49:23 INFO storage.BlockManager: BlockManager stopped
19/06/30 14:49:23 INFO storage.BlockManagerMaster: BlockManagerMaster stopped
19/06/30 14:49:23 INFO scheduler.OutputCommitCoordinator$OutputCommitCoordinatorEndpoint: OutputCommitCoordinator stopped!
19/06/30 14:49:23 INFO spark.SparkContext: Successfully stopped SparkContext
19/06/30 14:49:23 INFO util.ShutdownHookManager: Shutdown hook called
19/06/30 14:49:23 INFO util.ShutdownHookManager: Deleting directory /tmp/spark-4d12f060-3efd-4cdc-a219-f9acd1696912

执行成功，已经打开的hive客户端可以查看到数据库和表
退出hive，再次重新进入hive客户端执行任何语句都报错。

hive> quit;
[hadoop01@hdoop01 ~]$ hive
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop01/apps/apache-hive-2.3.2-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop01/apps/hadoop-2.7.7/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]

Logging initialized using configuration in jar:file:/home/hadoop01/apps/apache-hive-2.3.2-bin/lib/hive-common-2.3.2.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> show databases;
FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException: 
java.lang.RuntimeException: Unable to instantiate
 org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient
hive> use test_1902;
FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.HiveException
: java.lang.RuntimeException: Unable to instantiate 
org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient

如何解决

在hive的配置文件hive-site.xml添加如下配置：

<property>
    <name>datanucleus.schema.autoCreateAll</name>
    <value>true</value>
  </property>

进入MySQL 删除hive对应的数据库

[hadoop01@hdoop01 ~]$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| azkaban            |
| bd1901             |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database bd1901;
Query OK, 57 rows affected (0.69 sec)

mysql> quit;
Bye

重新初始化hive元数据

[hadoop01@hdoop01 ~]$ schematool -dbType mysql -initSchema

显示如下信息代表成功

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop01/apps/apache-hive-2.3.2-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop01/apps/hadoop-2.7.7/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]
Metastore connection URL:        jdbc:mysql://192.168.2.101:3306/bd1901?createDatabaseIfNotExist=true
Metastore Connection Driver :    com.mysql.jdbc.Driver
Metastore connection User:       root
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.mysql.sql
Initialization script completed
schemaTool completed

成功

hive可以正常使用了

[hadoop01@hdoop01 ~]$ hive
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop01/apps/apache-hive-2.3.2-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop01/apps/hadoop-2.7.7/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]

Logging initialized using configuration in jar:file:/home/hadoop01/apps/apache-hive-2.3.2-bin/lib/hive-common-2.3.2.jar!/hive-log4j2.properties Async: true
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> show databases;
OK
default
Time taken: 7.12 seconds, Fetched: 1 row(s)
hive> 

后续问题

尴尬的是再次执行Spark SQL脚本后还会出现这样的问题。。
还需进一步深入查找真正的问题所在