hive近期相關問題清單

這是最近有碰到hive的新問題，再次記錄下來，後面查找解決方案，報錯資訊是：

Unable to fetch table t_calc_mms_base

Table 'hivemeta.DELETEME1343943738172' doesn't exist

 Another instance of Derby may have already booted the database /home/hadoop/ccms_analyse_stat/bin/TempStatsStore

下面是日志截取部分：

2012-08-03 05:42:18,593 [Main Thread] ERROR parse.SemanticAnalyzer (SemanticAnalyzer.java:getMetaData(1128)) - org.apache.hadoop.hive.ql.metadata.HiveException: Unable to fetch table t_calc_mms_base

        at org.apache.hadoop.hive.ql.metadata.Hive.getTable(Hive.java:896)

        at org.apache.hadoop.hive.ql.metadata.Hive.getTable(Hive.java:830)

        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:954)

        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:7525)

        at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:244)

        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:432)

        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:336)

        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:909)

        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.processReader(CliDriver.java:439)

        at org.apache.hadoop.hive.cli.CliDriver.processFile(CliDriver.java:449)

        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:647)

        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:558)

        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.util.RunJar.main(RunJar.java:156)

Caused by: javax.jdo.JDODataStoreException: Exception thrown obtaining schema column information from datastore

NestedThrowables:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'hivemeta.DELETEME1343943738172' doesn't exist

        at org.datanucleus.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:313)

        at org.datanucleus.ObjectManagerImpl.getExtent(ObjectManagerImpl.java:4154)

        at org.datanucleus.store.rdbms.query.legacy.JDOQLQueryCompiler.compileCandidates(JDOQLQueryCompiler.java:411)

        at org.datanucleus.store.rdbms.query.legacy.QueryCompiler.executionCompile(QueryCompiler.java:312)

        at org.datanucleus.store.rdbms.query.legacy.JDOQLQueryCompiler.compile(JDOQLQueryCompiler.java:225)

        at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.compileInternal(JDOQLQuery.java:175)

        at org.datanucleus.store.query.Query.executeQuery(Query.java:1628)

        at org.datanucleus.store.rdbms.query.legacy.JDOQLQuery.executeQuery(JDOQLQuery.java:245)

        at org.datanucleus.store.query.Query.executeWithArray(Query.java:1499)

        at org.datanucleus.jdo.JDOQuery.execute(JDOQuery.java:243)

        at org.apache.hadoop.hive.metastore.ObjectStore.getMDatabase(ObjectStore.java:389)

        at org.apache.hadoop.hive.metastore.ObjectStore.getDatabase(ObjectStore.java:408)

        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.hive.metastore.RetryingRawStore.invoke(RetryingRawStore.java:111)

        at $Proxy5.getDatabase(Unknown Source)

        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB_core(HiveMetaStore.java:351)

        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:371)

        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:278)

        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:248)

        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:114)

        at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2092)

        at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2102)

        at org.apache.hadoop.hive.ql.metadata.Hive.getTable(Hive.java:888)

        at org.apache.hadoop.hive.ql.metadata.Hive.getTable(Hive.java:830)

        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.getMetaData(SemanticAnalyzer.java:954)

        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:7524)

        at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:243)

        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:431)

        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:336)

        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:909)

        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.processReader(CliDriver.java:439)

        at org.apache.hadoop.hive.cli.CliDriver.processFile(CliDriver.java:449)

        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:647)

        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:557)

        ... 5 more

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'hivemeta.DELETEME1343943738172' doesn't exist

        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.sqlQueryDirect(MysqlIO.java:1665)

        at com.mysql.jdbc.Connection.execSQL(Connection.java:3118)

        at com.mysql.jdbc.Connection.execSQL(Connection.java:3047)

        at com.mysql.jdbc.Statement.executeQuery(Statement.java:1166)

        at com.mysql.jdbc.DatabaseMetaData$2.forEach(DatabaseMetaData.java:2046)

        at com.mysql.jdbc.DatabaseMetaData$IterateBlock.doForAll(DatabaseMetaData.java:76)

        at com.mysql.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:1920)

        at org.apache.commons.dbcp.DelegatingDatabaseMetaData.getColumns(DelegatingDatabaseMetaData.java:218)

        at org.datanucleus.store.rdbms.adapter.DatabaseAdapter.getColumns(DatabaseAdapter.java:1460)

        at org.datanucleus.store.rdbms.schema.RDBMSSchemaHandler.refreshTableData(RDBMSSchemaHandler.java:924)

        at org.datanucleus.store.rdbms.schema.RDBMSSchemaHandler.getRDBMSTableInfoForTable(RDBMSSchemaHandler.java:823)

        at org.datanucleus.store.rdbms.schema.RDBMSSchemaHandler.getRDBMSTableInfoForTable(RDBMSSchemaHandler.java:772)

        at org.datanucleus.store.rdbms.schema.RDBMSSchemaHandler.getSchemaData(RDBMSSchemaHandler.java:207)

        at org.datanucleus.store.rdbms.RDBMSStoreManager.getColumnInfoForTable(RDBMSStoreManager.java:1699)

        at org.datanucleus.store.rdbms.table.TableImpl.validateColumns(TableImpl.java:218)

        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.performTablesValidation(RDBMSStoreManager.java:2702)

        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.addClassTablesAndValidate(RDBMSStoreManager.java:2503)

        at org.datanucleus.store.rdbms.RDBMSStoreManager$ClassAdder.run(RDBMSStoreManager.java:2148)

        at org.datanucleus.store.rdbms.AbstractSchemaTransaction.execute(AbstractSchemaTransaction.java:113)

        at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:986)

        at org.datanucleus.store.rdbms.RDBMSStoreManager.addClasses(RDBMSStoreManager.java:952)

        at org.datanucleus.store.AbstractStoreManager.addClass(AbstractStoreManager.java:919)

        at org.datanucleus.store.mapped.MappedStoreManager.getDatastoreClass(MappedStoreManager.java:356)

        at org.datanucleus.store.rdbms.query.legacy.ExtentHelper.getExtent(ExtentHelper.java:48)

        at org.datanucleus.store.rdbms.RDBMSStoreManager.getExtent(RDBMSStoreManager.java:1332)

        at org.datanucleus.ObjectManagerImpl.getExtent(ObjectManagerImpl.java:4149)

        ... 44 more

Caused by: java.sql.SQLException: Failed to start database 'TempStatsStore', see the next exception for details.

        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)

        ... 33 more

Caused by: java.sql.SQLException: Another instance of Derby may have already booted the database /home/hadoop/ccms_analyse_stat/bin/TempStatsStore.

        at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)

        at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)

        at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)

        ... 30 more

Caused by: ERROR XSDB6: Another instance of Derby may have already booted the database /home/hadoop/ccms_analyse_stat/bin/TempStatsStore.

        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

        at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.privGetJBMSLockOnDB(Unknown Source)

        at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.run(Unknown Source)

        at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.getJBMSLockOnDB(Unknown Source)

        at org.apache.derby.impl.store.raw.data.BaseDataFileFactory.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.TopService.bootModule(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.startModule(Unknown Source)

        at org.apache.derby.iapi.services.monitor.Monitor.bootServiceModule(Unknown Source)

        at org.apache.derby.impl.store.raw.RawStore.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.TopService.bootModule(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.startModule(Unknown Source)

        at org.apache.derby.iapi.services.monitor.Monitor.bootServiceModule(Unknown Source)

        at org.apache.derby.impl.store.access.RAMAccessManager.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.TopService.bootModule(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.startModule(Unknown Source)

        at org.apache.derby.iapi.services.monitor.Monitor.bootServiceModule(Unknown Source)

        at org.apache.derby.impl.db.BasicDatabase.bootStore(Unknown Source)

        at org.apache.derby.impl.db.BasicDatabase.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.boot(Unknown Source)

        at org.apache.derby.impl.services.monitor.TopService.bootModule(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.bootService(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.startProviderService(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.findProviderAndStartService(Unknown Source)

        at org.apache.derby.impl.services.monitor.BaseMonitor.startPersistentService(Unknown Source)

        at org.apache.derby.iapi.services.monitor.Monitor.startPersistentService(Unknown Source)

解決方式：

不使用預設的内嵌資料庫derby，采用mysql作為統計的存儲資訊。

修改相關配置資訊（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://localhost:3306/TempStatsStore</value>

</property>

修改完成儲存。

另外後面還有一個步驟就是要在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)