Hi Stamatis,
Thanks for the input, I just tried using a memory database within derby but
it seems like it didn't address the core problem - Still getting errors
that the self test query is failing because the tables within the metastore
do not exist.
I took a look around your project, and clearly things are working for you
there. I had a thought that maybe some settings weren't making it into the
embedded hive server because they were not in a config file (I'm passing
all the configs into the embedded instance in a locally created HiveConf
object). I placed a hive-site.xml file with my configurations in it on the
class path, and now it does get past the initial test query. It's a little
frustrating that the configuration object passed into HiveServer2 is not
used consistently through the application (my guess is a fresh HiveConf is
being constructed somewhere).
I'm still getting exceptions in my output that it cannot read the current
tables, but it says right afterward that the exception doesn't indicate an
error. There are parts of the startup process that have been throwing quite
a few exceptions aside from this, but it seems that testing is not halted
outright because of it.
[2021-02-19T13:41:01,206][WARN
][org.apache.hadoop.hive.metastore.MetaStoreDirectSql] Failed to execute
[SELECT "TBLS"."TBL_NAME" FROM "TBLS" INNER JOIN "DBS" ON "TBLS"."DB_ID" =
"DBS"."DB_ID" WHERE "DBS"."NAME" = ? AND "DBS"."CTLG_NAME" = ? AND
"TBLS"."TBL_TYPE" = ? ] with parameters [default, hive, MATERIALIZED_VIEW]
javax.jdo.JDODataStoreException: Error executing SQL query "SELECT
"TBLS"."TBL_NAME" FROM "TBLS" INNER JOIN "DBS" ON "TBLS"."DB_ID" =
"DBS"."DB_ID" WHERE "DBS"."NAME" = ? AND "DBS"."CTLG_NAME" = ? AND
"TBLS"."TBL_TYPE" = ?".
at
org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:543)
~[datanucleus-api-jdo-4.2.4.jar:?]
at
org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:391)
~[datanucleus-api-jdo-4.2.4.jar:?]
at
org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:267)
~[datanucleus-api-jdo-4.2.4.jar:?]
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.executeWithArray(MetaStoreDirectSql.java:2003)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getTables(MetaStoreDirectSql.java:420)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:1640)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:1636)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:3577)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.ObjectStore.getTablesInternal(ObjectStore.java:1648)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.ObjectStore.getTables(ObjectStore.java:1534)
[hive-exec-3.1.2.jar:3.1.2]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[?:1.8.0_171]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[?:1.8.0_171]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
at
org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
[hive-exec-3.1.2.jar:3.1.2]
at com.sun.proxy.$Proxy43.getTables(Unknown Source) [?:?]
at
org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.get_tables_by_type(HiveMetaStore.java:5082)
[hive-exec-3.1.2.jar:3.1.2]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[?:1.8.0_171]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[?:1.8.0_171]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[?:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
at
org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:147)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:108)
[hive-exec-3.1.2.jar:3.1.2]
at com.sun.proxy.$Proxy45.get_tables_by_type(Unknown Source) [?:?]
at
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaStoreClient.java:1676)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTables(HiveMetaStoreClient.java:1665)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.ql.metadata.Hive.getTablesByType(Hive.java:1310)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.ql.metadata.Hive.getTableObjects(Hive.java:1222)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.ql.metadata.Hive.getAllMaterializedViewObjects(Hive.java:1217)
[hive-exec-3.1.2.jar:3.1.2]
at
org.apache.hadoop.hive.ql.metadata.HiveMaterializedViewsRegistry$Loader.run(HiveMaterializedViewsRegistry.java:166)
[hive-exec-3.1.2.jar:3.1.2]
at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
[?:1.8.0_171]
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
[?:1.8.0_171]
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
[?:1.8.0_171]
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
[?:1.8.0_171]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_171]
Caused by: java.sql.SQLSyntaxErrorException: Table/View 'TBLS' does not
exist.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement42.<init>(Unknown Source)
~[derby-10.14.1.0.jar:?]
at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:325)
~[HikariCP-2.6.1.jar:?]
at
com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
~[HikariCP-2.6.1.jar:?]
at
org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:345)
~[datanucleus-rdbms-4.1.19.jar:?]
at
org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:211)
~[datanucleus-rdbms-4.1.19.jar:?]
at
org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:633)
~[datanucleus-rdbms-4.1.19.jar:?]
at org.datanucleus.store.query.Query.executeQuery(Query.java:1855)
~[datanucleus-core-4.1.17.jar:?]
at
org.datanucleus.store.rdbms.query.SQLQuery.executeWithArray(SQLQuery.java:807)
~[datanucleus-rdbms-4.1.19.jar:?]
at
org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:368)
~[datanucleus-api-jdo-4.2.4.jar:?]
... 33 more
Caused by: org.apache.derby.iapi.error.StandardException: Table/View
'TBLS' does not exist.
at
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.compile.TableOperatorNode.bindNonVTITables(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement42.<init>(Unknown Source)
~[derby-10.14.1.0.jar:?]
at org.apache.derby.jdbc.Driver42.newEmbedPreparedStatement(Unknown
Source) ~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
~[derby-10.14.1.0.jar:?]
at
com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:325)
~[HikariCP-2.6.1.jar:?]
at
com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java)
~[HikariCP-2.6.1.jar:?]
at
org.datanucleus.store.rdbms.SQLController.getStatementForQuery(SQLController.java:345)
~[datanucleus-rdbms-4.1.19.jar:?]
at
org.datanucleus.store.rdbms.query.RDBMSQueryUtils.getPreparedStatementForQuery(RDBMSQueryUtils.java:211)
~[datanucleus-rdbms-4.1.19.jar:?]
at
org.datanucleus.store.rdbms.query.SQLQuery.performExecute(SQLQuery.java:633)
~[datanucleus-rdbms-4.1.19.jar:?]
at org.datanucleus.store.query.Query.executeQuery(Query.java:1855)
~[datanucleus-core-4.1.17.jar:?]
at
org.datanucleus.store.rdbms.query.SQLQuery.executeWithArray(SQLQuery.java:807)
~[datanucleus-rdbms-4.1.19.jar:?]
at
org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:368)
~[datanucleus-api-jdo-4.2.4.jar:?]
... 33 more
[2021-02-19T13:41:01,218][WARN
][org.apache.hadoop.hive.metastore.ObjectStore] Falling back to ORM path
due to direct SQL failure (this is not an error): See previous errors;
Error executing SQL query "SELECT "TBLS"."TBL_NAME" FROM "TBLS" INNER JOIN
"DBS" ON "TBLS"."DB_ID" = "DBS"."DB_ID" WHERE "DBS"."NAME" = ? AND
"DBS"."CTLG_NAME" = ? AND "TBLS"."TBL_TYPE" = ?". at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.executeWithArray(MetaStoreDirectSql.java:2015)
at
org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getTables(MetaStoreDirectSql.java:420)
at
org.apache.hadoop.hive.metastore.ObjectStore$5.getSqlResult(ObjectStore.java:1640)
On Fri, Feb 19, 2021 at 6:34 AM Stamatis Zampetakis <zabe...@gmail.com>
wrote:
> Hi James,
>
> I am doing something similar with the difference that everything runs on
> docker [1].
> I am using Hive 3.1 (HDP though) but things work fine at least with
> in-memory derby.
>
> <property>
> <name>javax.jdo.option.ConnectionURL</name>
> <value>jdbc:derby:memory:metastore;create=true</value>
> </property>
>
> Best,
> Stamatis
>
> [1] https://github.com/zabetak/hs2-embedded
>
> On Wed, Feb 17, 2021 at 10:55 PM James Baiera <james.bai...@elastic.co>
> wrote:
>
>> Hey folks,
>>
>> I have a project where I test with Hive using an embedded HiveServer2
>> instance within a JVM running integration tests. This has worked for Hive
>> 1.2.2 in the past, and I've been able to get it to work with Hive 2.3.8,
>> but have been having trouble getting it working on Hive 3.0+
>>
>> The error I keep running into is that the metastore tables are not
>> present in the local embedded metastore. I have enabled both
>> "hive.metastore.schema.verification" to be "false" and
>> "datanucleus.schema.autoCreateAll" to be "true", but it seems like the
>> latter setting is being ignored. Instead of starting up, the HiveServer2
>> fails while trying to read from the DBS table:
>>
>> 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.4.jar:?]
>> at
>> org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:391)
>> ~[datanucleus-api-jdo-4.2.4.jar:?]
>> at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:216)
>> ~[datanucleus-api-jdo-4.2.4.jar:?]
>> at
>> org.apache.hadoop.hive.metastore.MetaStoreDirectSql.runTestQuery(MetaStoreDirectSql.java:276)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.MetaStoreDirectSql.<init>(MetaStoreDirectSql.java:184)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.ObjectStore.initializeHelper(ObjectStore.java:498)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:420)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.ObjectStore.setConf(ObjectStore.java:375)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:77)
>> [hadoop-common-3.1.2.jar:?]
>> at
>> org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:137)
>> [hadoop-common-3.1.2.jar:?]
>> at
>> org.apache.hadoop.hive.metastore.RawStoreProxy.<init>(RawStoreProxy.java:59)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.RawStoreProxy.getProxy(RawStoreProxy.java:67)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.newRawStoreForConf(HiveMetaStore.java:718)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMSForConf(HiveMetaStore.java:696)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.getMS(HiveMetaStore.java:690)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultDB(HiveMetaStore.java:767)
>> [hive-exec-3.1.2.jar:3.1.2]
>> at
>> org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:538)
>> [hive-exec-3.1.2.jar:3.1.2]
>> <....>
>>
>> Looking into the documentation, it seems that many things mention using
>> the schematool to set up the metastore the first time, but this is an
>> embedded use case, and there is no Hive installation locally to use for
>> this.
>>
>> I've also tried using the HiveJDBC driver with "jdbc:hive2:///" as the
>> url to run on an embedded server, and I am getting the same errors.
>>
>> Is this use case not supported anymore in Hive 3? Am I missing something
>> here?
>>
>
