业精于勤而荒于嬉，行成于思而毁于随

Error executing SQL query "select "DB_ID" from "DBS"".異常

Posted on Jan 25, 2018 By Jack Sun

執行hive -hiveconf hive.root.logger=DEBUG,console報錯

Error executing SQL query “select “DB_ID” from “DBS”“.異常

下列堆棧信息

2018-01-25T03:05:37,579 DEBUG [main] bonecp.BoneCPDataSource: JDBC URL = jdbc:derby:;databaseName=metastore_db;create=true, Username = APP, partitions = 1, max (per partition) = 10, min (per partition) = 0, idle max age = 60 min, idle test period = 240 min, strategy = DEFAULT 2018-01-25T03:05:37,608 WARN [main] metastore.MetaStoreDirectSql: Self-test query [select "DB_ID" from "DBS"] failed; direct SQL is disabled javax.jdo.JDODataStoreException: Error executing SQL query "select "DB_ID" from "DBS"". at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543) ~[datanucleus-api-jdo-4.2.1.jar:?] at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:388) ~[datanucleus-api-jdo-4.2.1.jar:?] at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:213) ~[datanucleus-api-jdo-4.2.1.jar:?] at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.runTestQuery(MetaStoreDirectSql.java:241) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.(MetaStoreDirectSql.java:144) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.ObjectStore.initializeHelper(ObjectStore.java:404) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:336) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:297) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:76) ~[hadoop-common-2.7.1.jar:?] at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136) ~[hadoop-common-2.7.1.jar:?] at org.apache.hadoop.hive.metastore.RawStoreProxy.(RawStoreProxy.java:58) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:67) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:617) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:582) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:644) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:424) ~[hive-exec-2.2.0.jar:2.2.0] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_95] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_95] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_95] at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_95] at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:148) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:107) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RetryingHMSHandler.(RetryingHMSHandler.java:79) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:92) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:6608) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.(HiveMetaStoreClient.java:239) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.(SessionHiveMetaStoreClient.java:70) ~[hive-exec-2.2.0.jar:2.2.0] at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.7.0_95] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[?:1.7.0_95] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_95] at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_95] at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1652) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.(RetryingMetaStoreClient.java:83) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:133) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:104) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3496) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3548) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3528) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3790) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.reloadFunctions(Hive.java:244) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:227) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.(Hive.java:384) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.create(Hive.java:328) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.getInternal(Hive.java:308) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.metadata.Hive.get(Hive.java:284) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:581) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:549) ~[hive-exec-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:750) ~[hive-cli-2.2.0.jar:2.2.0] at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686) ~[hive-cli-2.2.0.jar:2.2.0] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_95] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_95] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_95] at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_95] at org.apache.hadoop.util.RunJar.run(RunJar.java:221) ~[hadoop-common-2.7.1.jar:?] at org.apache.hadoop.util.RunJar.main(RunJar.java:136) ~[hadoop-common-2.7.1.jar:?] Caused by: java.sql.SQLSyntaxErrorException: Table/View 'DBS' does not exist. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:1193) ~[bonecp-0.8.0.RELEASE.jar:?] at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:345) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:211) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:633) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.query.Query.executeQuery(Query.java:1844) ~[datanucleus-core-4.1.6.jar:?] at org.datanucleus.store.rdbms.query.SQLQuery.executeWithArray(SQLQuery.java:807) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.query.Query.execute(Query.java:1715) ~[datanucleus-core-4.1.6.jar:?] at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:371) ~[datanucleus-api-jdo-4.2.1.jar:?] ... 53 more Caused by: org.apache.derby.impl.jdbc.EmbedSQLException: Table/View 'DBS' does not exist. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:1193) ~[bonecp-0.8.0.RELEASE.jar:?] at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:345) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:211) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:633) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.query.Query.executeQuery(Query.java:1844) ~[datanucleus-core-4.1.6.jar:?] at org.datanucleus.store.rdbms.query.SQLQuery.executeWithArray(SQLQuery.java:807) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.query.Query.execute(Query.java:1715) ~[datanucleus-core-4.1.6.jar:?] at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:371) ~[datanucleus-api-jdo-4.2.1.jar:?] ... 53 more Caused by: org.apache.derby.iapi.error.StandardException: Table/View 'DBS' does not exist. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) ~[derby-10.10.2.0.jar:?] at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:1193) ~[bonecp-0.8.0.RELEASE.jar:?] at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:345) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:211) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:633) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.query.Query.executeQuery(Query.java:1844) ~[datanucleus-core-4.1.6.jar:?] at org.datanucleus.store.rdbms.query.SQLQuery.executeWithArray(SQLQuery.java:807) ~[datanucleus-rdbms-4.1.7.jar:?] at org.datanucleus.store.query.Query.execute(Query.java:1715) ~[datanucleus-core-4.1.6.jar:?] at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:371) ~[datanucleus-api-jdo-4.2.1.jar:?] ... 53 more</code>

解決方法 vim hive-site.xml hive.metastore.try.direct.sql 設置為false