Creation of Hive-Phoenix integrated table fails if schema name added with Table name.

[#1]

PROBLEM: While creating an external Hive table using Hive Storage Handler for Phoenix, the table creation fails if schema name is added along with Phoenix table name.

STEPS TO REPRODUCE:

1. Hive DDL for the table:

create external table test.hive_e_phoenix1 (
scd string,
scddesc string
)
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
TBLPROPERTIES (
'phoenix.table.name'='myschema.mytbl',
'phoenix.zookeeper.quorum'='node1,node2,node3',
'phoenix.rowkeys'='scd'
);

2. Following exceptions are seen.

2016-07-17 16:02:25,898 ERROR [HiveServer2-Background-Pool: Thread-202]: operation.Operation (SQLOperation.java:run(209)) - Error running hive query: 
org.apache.hive.service.cli.HiveSQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Phoenix table bigstats.bigstats_cd doesn't exist)
	at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:315)
	at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:156)
	at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:71)
	at org.apache.hive.service.cli.operation.SQLOperation$1$1.run(SQLOperation.java:206)
	at java.security.AccessController.doPrivileged(Native Method)
	at javax.security.auth.Subject.doAs(Subject.java:422)
	at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1657)
	at org.apache.hive.service.cli.operation.SQLOperation$1.run(SQLOperation.java:218)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Phoenix table myschema.mytbl doesn't exist)
	at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:720)
	at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4135)
	at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:306)
	at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:160)
	at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:88)
	at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1653)
	at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1412)
	at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1195)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1059)
	at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1054)
	at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:154)
	... 11 more
Caused by: MetaException(message:Phoenix table myschema.mytbl doesn't exist)
	at org.apache.phoenix.hive.PhoenixMetaHook.preCreateTable(PhoenixMetaHook.java:63)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:664)
	at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createTable(HiveMetaStoreClient.java:657)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:156)
	at com.sun.proxy.$Proxy6.createTable(Unknown Source)
	at org.apache.hadoop.hive.ql.metadata.Hive.createTable(Hive.java:714)
	... 21 more

ROOT CAUSE: This is a known issue with Phoenix 4.8 discussed in Apache JIRA PHOENIX-3078

SOLUTION : Please log a case with HWX and request for a hotfix for HDP 2.5 backported from HDP 2.6

REFERENCE:

https://issues.apache.org/jira/browse/PHOENIX-3078

[#2]

@gsharma

I was facing same kind of issue. I have resolve this issue by using following steps:-

1) Edit Ambari->Hive->Configs->Advanced->Custom hive-site->Add Property..., add the following properties based on your HBase configurations(you can search in Ambari->HBase->Configs): custom hive-site.xml

hbase.zookeeper.quorum=xyz (find this property value from hbase )

zookeeper.znode.parent=/hbase-unsecure (find this property value from hbase )

phoenix.schema.mapSystemTablesToNamespace=true

phoenix.schema.isNamespaceMappingEnabled=true

2) Copy jar to /usr/hdp/current/hive-server2/auxlib from

/usr/hdp/2.5.6.0-40/phoenix/phoenix-4.7.0.2.5.6.0-40-hive.jar

/usr/hdp/2.5.6.0-40/phoenix/phoenix-hive-4.7.0.2.5.6.0-40-sources.jar If he jar is not working for you then just try to get following jar phoenix-hive-4.7.0.2.5.3.0-37.jar and copy this to /usr/hdp/current/hive-server2/auxlib

3) add property to custom-hive-env

HIVE_AUX_JARS_PATH=/usr/hdp/current/hive-server2/auxlib/4) Add follwoing property to custom-hbase-site.xmlphoenix.schema.mapSystemTablesToNamespace=true phoenix.schema.isNamespaceMappingEnabled=true

5) Also run following command

1) jar uf /usr/hdp/current/hive-server2/auxlib/phoenix-4.7.0.2.5.6.0-40-client.jar /etc/hive/conf/hive-site.xml

2) jar uf /usr/hdp/current/hive-server2/auxlib/phoenix-4.7.0.2.5.6.0-40-client.jar /etc/hbase/conf/hbase-site.xml

And I hope my solution will work for you 🙂