Created 05-28-2017 06:46 PM
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
Created 07-27-2017 02:08 AM
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.
Created 09-12-2017 02:14 PM
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" );
Created 09-13-2017 06:24 PM
@ 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 🙂
Created 09-14-2017 02:42 AM
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' );