踩坑｜Hive 无法连接hive FAILED: HiveException java.lang.RuntimeException:

Unable to instantiate org.apache.hadoop.hive.ql.me

问题点：[ mysql没有远程权限 ]、[ 没有metastore数据库 ]

云上贵猪：20240408

一、原因

在Hive 执行show databases;时出现如下报错

FAILED: HiveException java.lang.RuntimeException: 
Unable to instantiate org.apache.hadoop.hive.ql.me

尝试重新格式化失败

原因是因为服务器上的Mysql数据库root用户未开启权限。

二、解决

2.1、进入数据库中

mysql -u root -p

2.2、切换到mysql

use mysql;

2.3、设置root用户的连接权限

%的意思是允许所有IP连接数据库

update user set host = '%' where user ='root';

2.4、刷新权限

flush privileges; 

三、再次重新格式化

3.1、在hive的bin目录格式化

当前目录：/opt/module/hive-3.1.3/bin
schematool -dbType mysql -initSchema

接着提示说我们没有找到名为'metastore'的数据库，hive初始化失败

查看了下数据库确实没有：

show databases;

如果有则要删了，在新建一个

drop databases metastore;

create database metastore;

3.2、在mysql建立metastore数据库

登录数据库并建立库

mysql -uroot -p123456789
CREATE DATABASE metastore;

3.3、再次格式化hive

cd /opt/module/hive-3.1.3/bin
schematool -initSchema -dbType mysql

成功的标志

四、启动hive

启动的前提，确保hadoop、mysql都启动起来了

环境变量都配置好了

4.1、hive启动

ps：要打开两个Xshell ,都在hadoop21运行

第一窗口运行，不要关闭：

hive --service metastore

第二窗口运行hive

hive

hive> show databases;
OK
default
Time taken: 0.603 seconds, Fetched: 1 row(s)

成功解决

查看及启动信息

[root@hadoop21 ~]# jpsall 
=============== hadoop21 ===============
2688 DataNode
6416 RunJar
2977 NodeManager
3158 JobHistoryServer
2522 NameNode
7918 Jps
=============== hadoop22 ===============
3523 Jps
2013 DataNode
2205 ResourceManager
2526 NodeManager
=============== hadoop23 ===============
2032 DataNode
2219 NodeManager
3659 Jps
2141 SecondaryNameNode
[root@hadoop21 ~]# hive
which: no hbase in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/opt/module/jdk1.8.0_121/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/mysql-8.0.32/bin:/opt/module/jdk1.8.0_121/bin:/opt/module/hadoop-3.1.3/bin:/opt/module/hadoop-3.1.3/sbin:/opt/module/hive-3.1.3/bin:/root/bin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hive-3.1.3/lib/log4j-slf4j-impl-2.17.1.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.apache.logging.slf4j.Log4jLoggerFactory]
Hive Session ID = 98448c32-2794-4a1a-ab06-ecd2015e4538

Logging initialized using configuration in jar:file:/opt/module/hive-3.1.3/lib/hive-common-3.1.3.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 Session ID = 94d18717-35fa-4d04-8e3b-b060c2575890
hive> show databases;
OK
default
Time taken: 0.603 seconds, Fetched: 1 row(s)