初始化hive报错总结,翻遍网络,终于摸索出来万能解决方法

root用户下 service mysqld start

初始化：$HIVE_HOME/bin/schematool -dbType derby -initSchema

这是嵌入式启动hive

前提条件在cd ～/bigdata/apache-hive-2.3.0-bin/conf下有个 hive-site.xml

记得初始化derby的 hive-site.xml 里面是

<configuration>

</configuration>

如果里面全空没东西，否则报以下错误：

[hadoop-twq@master apache-hive-2.3.3-bin]$ $HIVE_HOME/bin/schematool -dbType derby -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/hadoop-2.7.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]
[Fatal Error] hive-site.xml:2:1: A well-formed document requires a root element.
Exception in thread “main” java.lang.RuntimeException: org.xml.sax.SAXParseException; systemId: file:/home/hadoop-twq/bigdata/apache-hive-2.3.3-bin/conf/hive-site.xml; lineNumber: 2; columnNumber: 1; A well-formed document requires a root element.
at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:2728)
at org.apache.hadoop.conf.Configuration.loadResources(Configuration.java:2574)
at org.apache.hadoop.conf.Configuration.getProps(Configuration.java:2477)
at org.apache.hadoop.conf.Configuration.get(Configuration.java:1302)
at org.apache.hadoop.hive.conf.HiveConf.getVar(HiveConf.java:3904)
at org.apache.hadoop.hive.conf.HiveConf.getVar(HiveConf.java:3973)
at org.apache.hadoop.hive.conf.HiveConf.initialize(HiveConf.java:4060)
at org.apache.hadoop.hive.conf.HiveConf.(HiveConf.java:4008)
at org.apache.hive.beeline.HiveSchemaTool.(HiveSchemaTool.java:82)
at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:1117)
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:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: org.xml.sax.SAXParseException; systemId: file:/home/hadoop-twq/bigdata/apache-hive-2.3.3-bin/conf/hive-site.xml; lineNumber: 2; columnNumber: 1; A well-formed document requires a root element.
at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)
at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source)
at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:150)
at org.apache.hadoop.conf.Configuration.parse(Configuration.java:2552)
at org.apache.hadoop.conf.Configuration.parse(Configuration.java:2540)
at org.apache.hadoop.conf.Configuration.loadResource(Configuration.java:2611)
… 15 more

然后很多人想再次初始化呢，没错可以这样先连上mysql ，

drop database hive；

然后在metastore_db路径下，删除元数据 rm -fr metastore_db/

但是，想想前面说过 hive-site.xml 要想 derby初始化，不要乱加东西

重点地方：

很多人把hive-site.xml里面加了其他内容用来hive 作为本地Local MetaStore启动，然后又执行嵌入式derby初始化schematool -dbType derby -initSchema，这样是不行的。只能选一种哦。

注意首次初始化 可以用 derby ，再次执行就会失败？ 这是错觉，而是你在hive-site.xml加了其他东西，变成本地启动，而不是嵌入式Embedded MetaStore启动hive，所以不管你执行多少次derby方式都会报错

[hadoop-twq@master derby]$ $HIVE_HOME/bin/schematool -dbType derby -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/hadoop-2.7.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://master:3306/hive?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.derby.sql
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '“APP”.“NUCLEUS_ASCII” (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE ’ at line 1 (state=42000,code=1064)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
Underlying cause: java.io.IOException : Schema script failed, errorcode 2
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

以上的原因是你的hive-site.xml有除了其他东西，比如

<configuration>

	<property>
	<name>javax.jdo.option.ConnectionURL</name>
	<value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true</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>root</value>
</property>
<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>hdfs://master:9999/user/hive/warehouse</value>
</property>

</configuration>

解决方法：在hive的conf下找到hive-site.xml,清空hive-site.xml里面除了<configuration></configuration>其他东西

连上mysql ，drop database hive；

然后在metastore_db路径下，删除元数据 rm -fr metastore_db/

在执行 $HIVE_HOME/bin/schematool -dbType derby -initSchema

完美，请看

[hadoop-twq@master apache-hive-2.3.3-bin]$ $HIVE_HOME/bin/schematool -dbType derby -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/hadoop-2.7.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:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver :	 org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:	 APP
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.derby.sql
Initialization script completed
schemaTool completed

列出其他报错解决思路一样：

hadoop-twq@master derby]$ $HIVE_HOME/bin/schematool -dbType derby -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/apache-hive-2.3.3-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/hadoop-twq/bigdata/hadoop-2.7.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://master:3306/hive?createDatabaseIfNotExist=true
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.CommunicationsException : Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
SQL Error code: 0
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

原因是没有启动mysql

root用户下 service mysqld start

然后清hive-site.xml本地化启动的内容

连上mysql ，drop database hive；

然后在metastore_db路径下，删除元数据 rm -fr metastore_db/

在执行 $HIVE_HOME/bin/schematool -dbType derby -initSchema

嵌入式启动完美

最后还要说说 $HIVE_HOME/bin/schematool -dbType mysql -initSchema 与$HIVE_HOME/bin/schematool -dbType derby -initSchema 不同地方，很多人都分不清

有derby的是嵌入式初始化，会生成在你执行那条初始化命令下生成一个叫metastore_db的目录，再次强调

hive-site.xml里面必须保持干净，否则你就会报以上错，然后网上说什么将derby改为mysql，其实那不是嵌入式初始化了，而是hive的另一种启动，local metastore ，但这个必须hive-site.xml配置好。

如果你想用local metastore 方式启动，先配置hive-site.xml增加内容

<configuration>

	<property>
	<name>javax.jdo.option.ConnectionURL</name>
	<value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true</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>root</value>
</property>
<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>hdfs://master:9999/user/hive/warehouse</value>
</property>

</configuration>

执行$HIVE_HOME/bin/schematool -dbType mysql -initSchema
如果这步出错，
失败Error: Duplicate key name ‘PCS_STATS_IDX’ (state=42000,code=1061)，这时你就要root在master下启动service mysqld start
进去mysql， drop database hive;
，然后在普通用户下在metastore_db路径下，删除元数据生成文件 rm -fr metastore_db/，再次$HIVE_HOME/bin/schematool -dbType mysql -initSchema