Hive格式化失败

初始化hive百度了一下是，输入后报错，如下：

[root@cluster-1 conf]# schematool -dbType mysql -initSchema
bash: schematool: 未找到命令...

本来以为只是没有安装schematool这么简单，于是我就通过yum命令安装。结果还是出错，报错如下：

[root@cluster-1 conf]# yum -y install schematool
已加载插件：fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * extras: mirrors.aliyun.com
 * updates: mirrors.tuna.tsinghua.edu.cn
base                                                                                                                                             | 3.6 kB  00:00:00     
extras                                                                                                                                           | 3.4 kB  00:00:00     
mysql-connectors-community                                                                                                                       | 2.5 kB  00:00:00     
mysql-tools-community                                                                                                                            | 2.5 kB  00:00:00     
mysql57-community                                                                                                                                | 2.5 kB  00:00:00     
updates                                                                                                                                          | 3.4 kB  00:00:00    
没有可用软件包 schematool。
错误：无须任何处理

后来百度发现是不该进入conf目录下进行，必须转到bin目录下执行hive初始化命令，但还是出错，错误如下：

[root@cluster-1 bin]# schematool -dbType mysql -initSchema
bash: schematool: 未找到命令...

还是出错，于是我就加个./试试，但是报其它错误了，如下所示：

[root@cluster-1 bin]# ./schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.4/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-2.8.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]
Metastore connection URL:	 jdbc:mysql://cluster-1:3306/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 root
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to load driver
Underlying cause: java.lang.ClassNotFoundException : com.mysql.jdbc.Driver
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

查看错误发现，是不能载入mysql驱动，需要下载一个mysql的驱动jar包，上传到hive的lib目录下

这里驱动下载可以从此网站https://dev.mysql.com/downloads/connector/j/5.1.html下载，下载完成后，进行解压缩，再上传到hive/lib目录下，再执行以上命令就可以了，如下所示：

[root@cluster-1 bin]# ./schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/apache-hive-2.3.4/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop-2.8.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]
Metastore connection URL:	 jdbc:mysql://cluster-1:3306/hive?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf8&iuseSSL=false
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 root
Sat Jan 26 16:31:14 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.mysql.sql
Sat Jan 26 16:31:15 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Initialization script completed
Sat Jan 26 16:31:22 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
schemaTool completed

如果已经上传完jar包，且版本正确，还出现下列问题：

在这里插入代码片

一定是忘记启动hadoop，（初学者容易遗忘的原因），hive是基于hadoop的数据仓库，首先要启动hadoop之后才能使用hive连接数据库，

之后重新启动即可。

如果启动完Hadoop出现了以下问题：

SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/SoftWare/Hive/hive-2.3.6/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/SoftWare/Hadoop/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://node03:3306/metastore?
createDatabaseIfNotExist=true&useSSL=false
Metastore Connection Driver :	 com.mysql.jdbc.Driver
Metastore connection User:	 root
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException : Unknown database 'metastore'
SQL Error code: 1049
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

说明了你的数据库中并没有一个名叫：***metastore***的数据库。

解决方法:

1.打开数据库：mysql -uroot -p…(数据库密码)；

创建名为：***metastore***的数据库；

重新格式化hive：schematool -dbType mysql -initSchema。

2.在hive/conf/hive-site.xml配置文件中修改***metastore***为你数据库中存在的数据库名。

<configuration>
  <property><!--数据库连接地址，使用MySQL存储元数据信息-->
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://node03:3306/metastore?
createDatabaseIfNotExist=true&amp;useSSL=false</value>

然后就真的没啥问题了。

[root@node01 hive-2.3.6]# schematool -initSchema -dbType mysql
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/SoftWare/Hive/hive-2.3.6/lib/log4j-slf4jimpl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/SoftWare/Hadoop/hadoop2.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://node03:3306/metastore?
createDatabaseIfNotExist=true&useSSL=false
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/conf/hive-site.xml文件内容，在hive2.3.6/conf目录下创建一个：

server端：

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
  <property><!--数据库连接地址，使用MySQL存储元数据信息-->
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://node03:3306/metastore?
createDatabaseIfNotExist=true&amp;useSSL=false</value>
  </property>
  <property><!--数据库驱动-->
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
  </property>
  <property><!--数据库用户名-->
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>
  <property><!--密码-->
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>123456</value>
  </property>
  <property><!--HDFS路径，用于存储不同 map/reduce 阶段的执行计划和这些阶段的中间输出结
果。-->
    <name>hive.exec.local.scratchdir</name>
    <value>/hive/tmp</value>
  </property>
  <property><!--HDFS路径，本地表的默认位置-->
    <name>hive.metastore.warehouse.dir</name>
    <value>/hive/warehouse</value>
  </property>
  <property><!--本地路径：Hive 查询日志所在的目录，如果该值为空，将不创建查询日志。-->
Client 端 node02 配置如下
    <name>hive.querylog.location</name>
    <value>/tmp/hive/logs</value>
  </property>
  
  <property><!--本地路径：Hive操作日志-->
    <name>hive.server2.logging.operation.log.location</name>
    <value>/tmp/hive/logs</value>
  </property>
  <property><!--本地路径：远程资源下载的临时目录-->
    <name>hive.downloaded.resources.dir</name>
    <value>/tmp/hive/resources</value>
  </property>
  <property>
    <!-- hiveserver2用户名 -->
    <name>beeline.hs2.connection.user</name>
    <value>root</value>
  </property>
  <property>
    <!-- hiveserver2密码 -->
    <name>beeline.hs2.connection.password</name>
    <value>123456</value>
  </property>
</configuration>

client端：

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
  <property><!--这里配置的是否启用本地服务器连接Hive，这里我们是客户端和服务端分开的一个
配置，所以这里设置为false.-->
    <name>hive.metastore.local</name>
    <value>false</value>
  </property>
  <property><!--HDFS路径，用于存储不同 map/reduce 阶段的执行计划和这些阶段的中间输出结
果。-->
    <name>hive.exec.local.scratchdir</name>
    <value>/hive/tmp</value>
  </property>
  <property><!--本地表的默认位置-->
    <name>hive.metastore.warehouse.dir</name>
    <value>/hive/warehouse</value>
  </property>
  <property><!--Hive连接到该URI请求远程元存储的元数据-->
    <name>hive.metastore.uris</name>
    <value>thrift://node01:9083</value>
  </property>
  <property><!--本地路径：Hive 查询日志所在的目录，如果该值为空，将不创建查询日志。-->
    <name>hive.querylog.location</name>
    <value>/tmp/hive/logs</value>
  </property>
  <property><!--本地路径：Hive操作日志-->
    <name>hive.server2.logging.operation.log.location</name>
    <value>/tmp/hive/logs</value>
  </property>
2.4.4 添加 MySQL 驱动
客⼾端和服务端都需要将 MySQL 的驱动 jar 包上传⾄ lib ⽬录。
2.4.5 修改⽇志配置
Hive 的 log 默认存放在 /tmp/root/hive.log⽬录下（当前⽤⼾名下），可以对其进⾏修改，两台节点均
需要配置
2.4.6 格式化 Hive
在 node01 上格式化 Hive
  <property><!--本地路径：远程资源下载的临时目录-->
    <name>hive.downloaded.resources.dir</name>
    <value>/tmp/hive/resources</value>
  </property>
  
  <property><!--显示当前所使用的数据库-->
    <name>hive.cli.print.current.db</name>
    <value>true</value>
  </property>
  
  <property><!--显示表头-->
    <name>hive.cli.print.header</name>
    <value>true</value>
  </property>
</configuration>