hive java.sql.SQLException: Table/View 'PARTITION_STATS_V2' already exists in Schema 'APP'.

这个问题折腾了我很久，网上也鲜有这些资料，网上和官网都只说了要替换metastore为mysql但是都没有说 Tempstatsstore.下面是错误和我的解决办法，希望对大家能够有所帮助。

这是我在使用java jdbc 连接hive hiveserver2的时候所报的错误（多线程长期运行时）：

2013-05-23 10:45:56,364 ERROR org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher: Error during JDBC initialization. 
java.sql.SQLException: Table/View 'PARTITION_STATS_V2' already exists in Schema 'APP'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown Source)
at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:276)
at org.apache.hadoop.hive.ql.exec.ExecDriver.execute(ExecDriver.java:435)
at org.apache.hadoop.hive.ql.exec.MapRedTask.execute(MapRedTask.java:136)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:138)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1352)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1138)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:951)
at org.apache.hive.service.cli.operation.SQLOperation.run(SQLOperation.java:95)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatement(HiveSessionImpl.java:168)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:62)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:552)
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:57)
at $Proxy9.executeStatement(Unknown Source)
at org.apache.hive.service.cli.CLIService.executeStatement(CLIService.java:153)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:190)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1133)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1118)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hive.service.cli.thrift.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:38)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Table/View 'PARTITION_STATS_V2' already exists in Schema 'APP'.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 41 more
Caused by: ERROR X0Y32: Table/View 'PARTITION_STATS_V2' already exists in Schema 'APP'.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.duplicateDescriptorException(Unknown Source)
at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addDescriptor(Unknown Source)
at org.apache.derby.impl.sql.execute.CreateTableConstantAction.executeConstantAction(Unknown Source)
at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
... 35 more
2013-05-23 10:45:56,366 INFO org.apache.hadoop.hive.ql.exec.ExecDriver: Making Temp Directory: hdfs://iap-hadoop1:8020/tmp/hive-hive-clouderauser/hive_2013-05-23_10-45-44_969_3866685561440570084/-ext-10020
2013-05-23 10:45:56,367 WARN org.apache.hadoop.ipc.Client: interrupted waiting to send params to server
java.lang.InterruptedException
at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireSharedInterruptibly(AbstractQueuedSynchronizer.java:1279)
at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:218)
at java.util.concurrent.FutureTask.get(FutureTask.java:83)
at org.apache.hadoop.ipc.Client$Connection.sendParam(Client.java:913)
at org.apache.hadoop.ipc.Client.call(Client.java:1198)
at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:202)
at $Proxy17.mkdirs(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:164)
at org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:83)
at $Proxy17.mkdirs(Unknown Source)
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.mkdirs(ClientNamenodeProtocolTranslatorPB.java:425)
at org.apache.hadoop.hdfs.DFSClient.primitiveMkdir(DFSClient.java:2108)
at org.apache.hadoop.hdfs.DFSClient.mkdirs(DFSClient.java:2079)
at org.apache.hadoop.hdfs.DistributedFileSystem.mkdirs(DistributedFileSystem.java:543)
at org.apache.hadoop.fs.FileSystem.mkdirs(FileSystem.java:1867)
at org.apache.hadoop.hive.ql.exec.ExecDriver.createTmpDirs(ExecDriver.java:221)
at org.apache.hadoop.hive.ql.exec.ExecDriver.execute(ExecDriver.java:444)
at org.apache.hadoop.hive.ql.exec.MapRedTask.execute(MapRedTask.java:136)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:138)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1352)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1138)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:951)
at org.apache.hive.service.cli.operation.SQLOperation.run(SQLOperation.java:95)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatement(HiveSessionImpl.java:168)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:62)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:552)
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:57)
at $Proxy9.executeStatement(Unknown Source)
at org.apache.hive.service.cli.CLIService.executeStatement(CLIService.java:153)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:190)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1133)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1118)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hive.service.cli.thrift.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:38)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
2013-05-23 10:45:56,371 ERROR org.apache.hadoop.hive.ql.exec.Task: Job Submission failed with exception 'java.io.IOException(java.lang.InterruptedException)'
java.io.IOException: java.lang.InterruptedException
at org.apache.hadoop.ipc.Client.call(Client.java:1204)
at org.apache.hadoop.ipc.ProtobufRpcEngine$Invoker.invoke(ProtobufRpcEngine.java:202)
at $Proxy17.mkdirs(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hadoop.io.retry.RetryInvocationHandler.invokeMethod(RetryInvocationHandler.java:164)
at org.apache.hadoop.io.retry.RetryInvocationHandler.invoke(RetryInvocationHandler.java:83)
at $Proxy17.mkdirs(Unknown Source)
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolTranslatorPB.mkdirs(ClientNamenodeProtocolTranslatorPB.java:425)
at org.apache.hadoop.hdfs.DFSClient.primitiveMkdir(DFSClient.java:2108)
at org.apache.hadoop.hdfs.DFSClient.mkdirs(DFSClient.java:2079)
at org.apache.hadoop.hdfs.DistributedFileSystem.mkdirs(DistributedFileSystem.java:543)
at org.apache.hadoop.fs.FileSystem.mkdirs(FileSystem.java:1867)
at org.apache.hadoop.hive.ql.exec.ExecDriver.createTmpDirs(ExecDriver.java:221)
at org.apache.hadoop.hive.ql.exec.ExecDriver.execute(ExecDriver.java:444)
at org.apache.hadoop.hive.ql.exec.MapRedTask.execute(MapRedTask.java:136)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:138)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1352)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1138)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:951)
at org.apache.hive.service.cli.operation.SQLOperation.run(SQLOperation.java:95)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatement(HiveSessionImpl.java:168)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.hive.service.cli.session.HiveSessionProxy$1.run(HiveSessionProxy.java:62)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:396)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1408)
at org.apache.hadoop.hive.shims.HadoopShimsSecure.doAs(HadoopShimsSecure.java:552)
at org.apache.hive.service.cli.session.HiveSessionProxy.invoke(HiveSessionProxy.java:57)
at $Proxy9.executeStatement(Unknown Source)
at org.apache.hive.service.cli.CLIService.executeStatement(CLIService.java:153)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:190)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1133)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1118)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hive.service.cli.thrift.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:38)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:206)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.InterruptedException
at java.util.concurrent.locks.AbstractQueuedSynchronizer.acquireSharedInterruptibly(AbstractQueuedSynchronizer.java:1279)
at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:218)
at java.util.concurrent.FutureTask.get(FutureTask.java:83)
at org.apache.hadoop.ipc.Client$Connection.sendParam(Client.java:913)
at org.apache.hadoop.ipc.Client.call(Client.java:1198)
... 46 more

这个错误还是由于 hive 使用derby造成的。解决办法就是将hive的元数据库metastore和TempStatsStore都使用mysql。

hive完全使用mysql的方法如下：
a、创建一个metastore数据库（数据库名字自己起）：
eg:
$ mysql -u root -p
Enter password:
mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE /usr/lib/hive/scripts/metastore/upgrade/mysql/hive-schema-0.10.0.mysql.sql;


b、创建一个用户，并让用户拥有访问metastore的相关权限：
eg:
mysql> CREATE USER 'hive'@'metastorehost' IDENTIFIED BY 'mypassword';
...
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hive'@'metastorehost';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,EXECUTE ON metastore.* TO 'hive'@'metastorehost';
mysql> FLUSH PRIVILEGES;
mysql> quit;
c、修改 hive-site.xml文件，在该文件中添加如下代码：
<property>
 <name>javax.jdo.option.ConnectionURL</name>
 <value>jdbc:mysql://myhost/metastore</value>
 <description>the URL of the MySQL database</description>
</property>

<property>
 <name>javax.jdo.option.ConnectionDriverName</name>
 <value>com.mysql.jdbc.Driver</value>
</property>

<property>
 <name>javax.jdo.option.ConnectionUserName</name>
 <value>hive</value>
</property>

<property>
 <name>javax.jdo.option.ConnectionPassword</name>
 <value>mypassword</value>
</property>

<property>
 <name>datanucleus.autoCreateSchema</name>
 <value>false</value>
</property>

<property>
 <name>datanucleus.fixedDatastore</name>
 <value>true</value>
</property>

<property>
 <name>hive.metastore.uris</name>
 <value>thrift://<n.n.n.n>:9083</value>
 <description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property>


这一步可以详解Cloudera的官网介绍：http://www.cloudera.com/content/cloudera-content/cloudera-docs/CDH4/4.2.0/CDH4-Installation-Guide/cdh4ig_topic_18_4.html
到这一步元数据库已经替换成功了。但是到这里还没有结束，要完全替换derby还得修改一个库TempStatsStore。
下面这一步可以详见：http://blog.csdn.net/chenyi8888/article/details/7826032?reload
另外后面还有一个步骤就是要在mysql里创建TempStatsStore这个数据库（mysql里不会自动创建该库，在derby里会自动创建）
这里纠正下，还有个问题就是如何连接到这个TempStatsStore数据库，因为没有写用户名和密码，会有如下报错信息：
2012-08-10 04:56:59,696 [Main Thread] ERROR jdbc.JDBCStatsPublisher (JDBCStatsPublisher.java:init(276)) - Error during JDBC initialization. 
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Access denied for user ''@'localhost' to database 'TempStatsStore'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
        at com.mysql.jdbc.MysqlIO.changeDatabaseTo(MysqlIO.java:1265)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:2644)
        at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at org.apache.hadoop.hive.ql.stats.jdbc.JDBCStatsPublisher.init(JDBCStatsPublisher.java:260)
        at org.apache.hadoop.hive.ql.exec.ExecDriver.execute(ExecDriver.java:428)
        at org.apache.hadoop.hive.ql.exec.MapRedTask.execute(MapRedTask.java:137)
        at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:134)
        at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:57)
        at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1326)
        at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1118)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:951)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:258)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:215)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:406)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:341)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:642)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:557)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
 解决的办法就是下面的第 b 步骤即： mysql > GRANT all ON TempStatsStore.* TO ''@'iap-hadoop1';
     
a、在数据库中新建TempStatsStore：
$ mysql -u root -p
mysql > CREATE DATABASE TempStatsStore;


b、因为没有用户和密码，所以必须得给没有用户的权限,iap-hadoop1是hive所在机器的主机域名：
mysql > GRANT all ON TempStatsStore.* TO ''@'iap-hadoop1';


c、在hive-site.xml文件中配置：
<property>
      <name>hive.stats.dbclass</name>
      <value>jdbc:mysql</value>
</property>

<property>
      <name>hive.stats.jdbcdriver</name>
      <value>com.mysql.jdbc.Driver</value>
</property>

<property>
      <name>hive.stats.dbconnectionstring</name> 
      <value>jdbc:mysql://10.1.253.45:3306/TempStatsStore</value>
</property>

到这里我们的所以配置就都完成了，停止hive服务，部署客户端配置，重启服务即可。下面是我们这次添加到hive中添加的部分的完整代码：
  <!--解决：java.lang.RuntimeException: Error sending events to Navigator server-->
<property>
   <name>hive.metastore.event.listeners</name>
   <value></value>
</property>
<property>
   <name>hive.exec.post.hooks</name>
   <value></value>
</property>
<property>
 <name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://10.1.253.45:3306/metastore</value>
 <description>the URL of the MySQL database</description>
</property>

<!--hive替换derby 元数据库metastore为mysql-->
<property>
 <name>javax.jdo.option.ConnectionDriverName</name>
 <value>com.mysql.jdbc.Driver</value>
</property>
<property>
 <name>datanucleus.autoCreateSchema</name>
 <value>false</value>
</property>

<property>
 <name>datanucleus.fixedDatastore</name>
 <value>true</value>
</property>

<property>
 <name>hive.metastore.uris</name>
 <value>thrift://iap-hadoop1:9083</value>
 <description>IP address (or fully-qualified domain name) and port of the metastore host</description>
</property>


<!--替换hive的TempStatsStore 由derby为mysql-->
<property>
      <name>hive.stats.dbclass</name>
      <value>jdbc:mysql</value>
</property>

<property>
      <name>hive.stats.jdbcdriver</name>
      <value>com.mysql.jdbc.Driver</value>
</property>

<property>
      <name>hive.stats.dbconnectionstring</name>   
      <value>jdbc:mysql://10.1.253.45:3306/TempStatsStore</value>
</property>