Hive on phoenix

1

I use Phoenix Storage Handler for Apache Hive

I got this error:
PLAN ERROR: Failure while retrieving metadata for table hive.phoenix.test.

Sql Query SELECT *
FROM hive.phoenix.test

(org.apache.hadoop.hive.ql.metadata.HiveException) Error in loading storage handler.org.apache.phoenix.hive.PhoenixStorageHandler
org.apache.hadoop.hive.ql.metadata.HiveUtils.getStorageHandler():315
com.dremio.exec.store.hive.DatasetBuilder.getInputFormatClass():785
com.dremio.exec.store.hive.DatasetBuilder.buildSplits():437
com.dremio.exec.store.hive.DatasetBuilder.buildIfNecessary():285
com.dremio.exec.store.hive.DatasetBuilder.getDataset():204
com.dremio.exec.store.SimpleSchema.getTableFromSource():377
com.dremio.exec.store.SimpleSchema.getTableWithRegistry():297
com.dremio.exec.store.SimpleSchema.getTable():415
org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitTable():67
org.apache.calcite.jdbc.CalciteSchema.getTable():219
org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom():117
org.apache.calcite.prepare.CalciteCatalogReader.getTable():106
org.apache.calcite.prepare.CalciteCatalogReader.getTable():73
org.apache.calcite.sql.validate.EmptyScope.getTableNamespace():71
org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace():189
org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():104
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2859
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2844
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3077
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():188
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():167
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():287
java.util.concurrent.ThreadPoolExecutor.runWorker():1142
java.util.concurrent.ThreadPoolExecutor$Worker.run():617
java.lang.Thread.run():745
Caused By (java.lang.ClassNotFoundException) org.apache.phoenix.hive.PhoenixStorageHandler
java.net.URLClassLoader.findClass():381
java.lang.ClassLoader.loadClass():424
sun.misc.Launcher$AppClassLoader.loadClass():331
java.lang.ClassLoader.loadClass():357
java.lang.Class.forName0():-2
java.lang.Class.forName():348
org.apache.hadoop.hive.ql.metadata.HiveUtils.getStorageHandler():309
com.dremio.exec.store.hive.DatasetBuilder.getInputFormatClass():785
com.dremio.exec.store.hive.DatasetBuilder.buildSplits():437
com.dremio.exec.store.hive.DatasetBuilder.buildIfNecessary():285
com.dremio.exec.store.hive.DatasetBuilder.getDataset():204
com.dremio.exec.store.SimpleSchema.getTableFromSource():377
com.dremio.exec.store.SimpleSchema.getTableWithRegistry():297
com.dremio.exec.store.SimpleSchema.getTable():415
org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitTable():67
org.apache.calcite.jdbc.CalciteSchema.getTable():219
org.apache.calcite.prepare.CalciteCatalogReader.getTableFrom():117
org.apache.calcite.prepare.CalciteCatalogReader.getTable():106
org.apache.calcite.prepare.CalciteCatalogReader.getTable():73
org.apache.calcite.sql.validate.EmptyScope.getTableNamespace():71
org.apache.calcite.sql.validate.DelegatingScope.getTableNamespace():189
org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():104
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2859
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2844
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3077
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():188
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():167
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():155
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():287
java.util.concurrent.ThreadPoolExecutor.runWorker():1142
java.util.concurrent.ThreadPoolExecutor$Worker.run():617
java.lang.Thread.run():745

Can you help me ?

I try to put the phoenix-hive jar in dremio 3rdparty directory but I have log4j error and dremio won’t start.

2

Hi @Pirion,

I’ll do some research on Hive on Phoenix setup and get back to you.

Thanks,
Danny

4

Hi @Pirion,

I’ve did some research on this and I recommend you to try the following steps:

  1. Add phoenix-version-hive.jar’s path to HIVE_AUX_JARS_PATH env variable in hive-env.sh file:

export HIVE_AUX_JARS_PATH=$HIVE_AUX_JARS_PATH:/usr/lib/phoenix/phoenix-hive.jar

(The above path should be the default location of the jar file)

  1. Add the following property in hive-site.xml:
<property>
  <name>hive.aux.jars.path</name>
  <value>file://<path></value>
</property>

Let me know if this works for you.

Thanks,
Danny

5

I already do that, I use hdp2.6 and dremio host as all client config.

6

Any update on this?
It’s almost an year and this question I think It’s worth answers.
I added phoenix-hive.jar in 3rdparty as @Pirion .
I have conflict due to org.apache.log4j.Log4jLoggerFactory.

The configs you @dbrody suggested are for Hive.
I am able to do query on the hive external table through storage handler.

Any tips around this topic?

Best regards and Happy New Year btw.

Rosario