external table creation in hive linking apache phoenix

[#1]

I am using HDP2.6

I want to create a hive external table I am following this.

I have created a table in phoenix using the below command

CREATE TABLE IF NOT EXISTS tmp (
    cola varchar,
        colb varchar,
    constraint my_cons primary key(cola)
);

Now after opening the hive console I do the following

hive> add jar /usr/hdp/2.6.0.3-8/phoenix/phoenix-hive.jar;
hive> create external table tmp (  cola string, colb string) STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler' TBLPROPERTIES (   "phoenix.zookeeper.quorum" = "sandbox.hortonworks.com",  "phoenix.zookeeper.znode.parent" = "hbase-unsecure",  "phoenix.zookeeper.client.port" = "2181",'autocreate'='true','autodrop'='true',"phoenix.rowkeys" = "cola", "phoenix.column.mapping" = "cola:cola,colb:colb" );

The table gets created but when I run

select * from tmp;

I get the following exception

Failed with exception java.io.IOException:java.lang.RuntimeException: org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined column. columnName=cola

Though the exception seems obvious i am not able to resolve this. Please help

[#2]

We have experienced the same problem, with a similar case for us. Is this a known bug (fixed in a later release of Hive and/or Phoenix for an upcoming HDP release), or is there a known fix? As a temporary workaround, we built the table using Hive, which automatically creates the Phoenix table. However, it would be our preference to define the table first using Phoenix (so that if the external table in Hive is dropped the Phoenix one is left unaltered) and then reference it as an external table.

[#3]

Hi @Aman Verma and @Jerome Soller, PhD, I was able to resolve this issue on HDP 2.6.2 by using case sensitive names for the Phoenix Table.
For example:

My Phoenix table name is T1 and its schema name is TEST. So when I create an external table in HIVE, I use the following syntax:

create external table t1_test (
  id int, name string
)
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
TBLPROPERTIES (
  "phoenix.table.name" = "TEST.T1",
  "phoenix.zookeeper.quorum" = "XXXXX",
  "phoenix.zookeeper.znode.parent" = "/hbase-secure",
  "phoenix.zookeeper.client.port" = "2181",
  "phoenix.rowkeys" = "id",
  "phoenix.column.mapping" = "id:ID,name:NAME"
);

[#4]

@ Aman Varma

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 🙂

[#5]

Aman Verma

1. You are missing "phoenix.table.name" in your hive external table create command.

2. Also "phoenix.zookeeper.znode.parent" =/hbase-unsecure is missing "/" before hbase-unsecure.

3. Add the following before hive external table creation in hive cli / beeline;

add jar /usr/hdp/current/phoenix-client/phoenix-client.jar;

add jar /usr/hdp/current/phoenix-client/phoenix-hive.jar;

add jar /usr/hdp/current/phoenix-client/phoenix-server.jar;

add jar /usr/hdp/current/phoenix-client/lib/phoenix-core-4.7.0.2.5.0.50-18.jar;

Example syntax for Hive external table for the phoenix schema table "test_phoenix.test_table":-

CREATE external TABLE test( pk string, a1 bigint, a2 bigint)

STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'

WITH SERDEPROPERTIES ( 'serialization.format'='1')

TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',

'phoenix.column.mapping'="pk:PK,a1:A1,a2:A2",

'phoenix.rowkeys'='pk',

'phoenix.table.name'='test_phoenix.test_table',

'phoenix.zookeeper.client.port'='2181',

'phoenix.zookeeper.quorum'='node1',

'phoenix.zookeeper.znode.parent'='/hbase-unsecure' );