HiveServer2 Failed to Start With “Table/View ‘DBS’ does not exist”

Recently I was dealing with an issue that after user upgraded CDH to 5.12.x, HiveServer2 was unable to start up. Examining the HiveServer2 log we noticed that for some reason HiveServer2 was try to access HiveMetaStore database and returned table does not exist error. See below full stacktrace:

2017-12-01 20:17:15,419 WARN  org.apache.hadoop.hive.metastore.MetaStoreDirectSql: [Thread-13]: 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:451)
        at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:230)
        at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.runTestQuery(MetaStoreDirectSql.java:226)
        at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.(MetaStoreDirectSql.java:134)
        at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:347)
        at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:298)
        at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73)
        at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.(RawStoreProxy.java:60)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:69)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:682)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:660)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:713)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:508)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.(HiveMetaStore.java:461)
        at org.apache.hive.service.auth.HiveAuthFactory.(HiveAuthFactory.java:118)
        at org.apache.hive.service.cli.thrift.ThriftBinaryCLIService.run(ThriftBinaryCLIService.java:58)
        at java.lang.Thread.run(Thread.java:745)
NestedThrowablesStackTrace:
java.sql.SQLSyntaxErrorException: Table/View 'DBS' does not exist.
        at org.apache.derby.impl.jdbc.SQLExceptionFactory.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.EmbedPreparedStatement.(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedPreparedStatement42.(Unknown Source)
        at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
        at com.jolbox.bonecp.ConnectionHandle.prepareStatement(ConnectionHandle.java:1193)
        at org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:350)
        at org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:194)
        at org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:267)
        at org.datanucleus.store.query.Query.executeQuery(Query.java:1786)
        at org.datanucleus.store.query.AbstractSQLQuery.executeWithArray(AbstractSQLQuery.java:339)
        at org.datanucleus.store.query.Query.execute(Query.java:1654)
        at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:221)
        at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.runTestQuery(MetaStoreDirectSql.java:226)
        at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.(MetaStoreDirectSql.java:134)
        at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:347)
        at org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:298)
        at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:73)
        at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:133)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.(RawStoreProxy.java:60)
        at org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:69)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStore(HiveMetaStore.java:682)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:660)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:713)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:508)
        at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.(HiveMetaStore.java:461)
        at org.apache.hive.service.auth.HiveAuthFactory.(HiveAuthFactory.java:118)
        at org.apache.hive.service.cli.thrift.ThriftBinaryCLIService.run(ThriftBinaryCLIService.java:58)
        at java.lang.Thread.run(Thread.java:745)
Caused by: ERROR 42X05: Table/View 'DBS' does not exist.
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown Source)
        at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
        at org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown Source)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown Source)
        at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
        at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
        at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
        at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
        ... 29 more

If you look closely at the stacktrace, you will notice that HiveServer2 was trying to access Derby database for those tables and hence failed with error. This does not make sense because: 1. The backend database was configured to use MySQL, not Derby 2. The database access should happen at HiveMetaStore service, not HiveServer2 After researching, I found out that this was actually caused by the following: – A known issue in Cloudera Manager, that when you chose DBTokenStore as the HiveMetaStore Delegation Token Store, Cloudera Manager will also HiveServer2 to use DBTokenStore – HiveServer2 does not support DBTokenStore until CDH 5.13.0 (introduced by HIVE-12270) – When DBTokenStore was used by HiveServer2, the behaviour for HiveServer2 is that it will use Derby as the backend for the DBTokenStore and somehow it will try to query the tables that supposed to be used by HiveMetaStore (I have not figured out why yet) Since user had HiveMetaStore HA, roll back to MemoryTokenStore was not an option, so the solution is to use ZookeeperTokenStore instead, which is supported by both HiveMetaStore as well as HiveServer2. So simply go to CM > Hive > Configuration > “Hive Metastore Delegation Token Store” and select “org.apache.hadoop.hive.thrift.ZooKeeperTokenStore“, see below screenshot: Save and then restart Hive services.