【CDH】问题解决：CDH5.16重新初始化hive 元数据信息

---

前言

之前在搭建CDH集群时，在进行到初始化hive 元数据库时，由于DBA没有放开库所有的权限，导致当时初始化hive元数据信息部分失败。 由于集群当时能够正常使用，于是就没有做过多的考虑，今天在创建hive udf时报错，排查下来是当时初始化表时，缺失了几张表。于是决定重新对hive 元数据库进行初始化。

---

一、重新初始化

初始化元数据需要使用cdh hive中的schematool 脚本工具进行初始化。

二、操作步骤

1.脚本位置

对于CDH集群，初始化脚本在：

cd /opt/cloudera/parcels/CDH/lib/hive/bin/

2.执行命令

执行初始化命令：

./schematool -initSchema -dbType mysql --verbose

3. 执行报错

报错内容如下：

ARNING: Use "yarn jar" to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/data/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLogger
Binder.class]SLF4J: Found binding in [jar:file:/data/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBi
nder.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:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver :	 org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:	 root
Starting metastore schema initialization to 2.1.1-cdh6.3.2
Initialization script hive-schema-2.1.1.mysql.sql
Connecting to jdbc:derby:;databaseName=metastore_db;create=true
Connected to: Apache Derby (version 10.14.1.0 - (1808820))
Driver: Apache Derby Embedded JDBC Driver (version 10.14.1.0 - (1808820))
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc:derby:> !autocommit on
Autocommit status: true
0: jdbc:derby:> /*!40101 SET @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 !!
	at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:568)
	at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:541)
	at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1137)
	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:313)
	at org.apache.hadoop.util.RunJar.main(RunJar.java:227)
Caused by: java.io.IOException: Schema script failed, errorcode 2
	at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:962)
	at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:941)
	at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:564)
	... 8 more
*** schemaTool failed ***

4. 问题原因：

根据错误提示可以看到，初始化过程中没有连接mysql数据库，而是去连接默认的derby数据库，于是打开/etc/hive/conf/hive-site.xml，我们可以看到它并没有相关mysql元数据库的配置，但是cdh页面中已经配置了mysql元数据库信息，如图：

5. 解决方案：

所以需要在/etc/hive/conf/hive-site.xml（cloudera/parcels/CDH/lib/hive/conf的软链实体）中手动添加元数据库配置，添加如下内容：

<property>
    <name>hive.metastore.db.type</name>
    <value>mysql</value>
    <description>元数据库类型指定为使用mysql</description>
</property>
<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>指定mysql的驱动</description>
</property>
<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
    <description>指定元数据库用户</description>
</property>
<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>root</value>
    <description>指定元数据库密码</description>
</property>
 <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://prod-bigdata-pc7/metastore?createDatabaseIfNotExist=true</value>
    <description>指定mysql连接串</description>
</property>

6. 重新初始化

再次重新初始化 hive 元数据库。

./schematool -initSchema -dbType mysql --verbose

---

转载：

• https://blog.csdn.net/qq_32068809/article/details/120880067