phoenix create table with schema.

[#1]

Hi,

I am using HDP 2.5.3.0 version.

I have created schema in phoenix and under that schema I have created table.

After creating table in phoenix, I have tried to created external table on hive. But while creating table I am getting following error.

Failed with exception java.io.IOException:java.lang.RuntimeException: org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table undefined. tableName=ACCOUNT1

DDL:-

CREATE EXTERNAL TABLE account1 ( acct_no Int , acct_descr string ) ROW FORMAT SERDE 'org.apache.phoenix.hive.PhoenixSerDe' STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler' TBLPROPERTIES ( "phoenix.table.name" = "account1", "phoenix.zookeeper.quorum"="<ZKNODE>", "phoenix.zookeeper.znode.parent" = "/hbase-unsecure", "phoenix.zookeeper.client.port" = "2181", "phoenix.rowkeys" = "acct_no", "phoenix.column.mapping" = "acct_no:acct_no, acct_descr:acct_descr");

Tried following steps also to resolve the issue.

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

3) jar uf /usr/hdp/current/hive-server2/auxlib/phoenix-hive-4.7.0.2.5.3.0-37.jar /etc/hbase/conf/hbase-site.xml

4) jar uf /usr/hdp/current/hive-server2/auxlib/phoenix-hive-4.7.0.2.5.3.0-37.jar /etc/hive/conf/hive-site.xml

5) jar -uf /usr/hdp/2.5.6.0-40/phoenix/phoenix-4.7.0.2.5.6.0-40-client.jar /etc/hive/conf/hive-site.xml

6) jar -uf /usr/hdp/2.5.6.0-40/phoenix/phoenix-4.7.0.2.5.6.0-40-client.jar /etc/hbase/conf/hbase-site.xml

7) 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= ZKNODEt zookeeper.znode.parent=/hbase-unsecure

phoenix.schema.mapSystemTablesToNamespace=true

phoenix.schema.isNamespaceMappingEnabled=true

😎 Restart Hive via Ambari

9) 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 download jar phoenix-hive-4.7.0.2.5.3.0-37.jar HIVE_AUX_JARS_PATH=/usr/hdp/current/hive-server2/auxlib

10)phoenix.schema.mapSystemTablesToNamespace=true

phoenix.schema.isNamespaceMappingEnabled=true

Please help on this. Need urgent help.

[#2]

@Ashnee Sharma

Hive Phoenix handler is not a supported feature in HDP 2.5.x versions. This feature is available as Tech Preview feature starting HDP 2.6.2. Below is the document with list of TP features in HDP 2.6.2:

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.2/bk_release-notes/content/tech_previews.html

[#3]

@Sindhu

Thanks for the info.

when production release is coming for the same?

[#4]

@Ashnee Sharma

Usually, it should be GA in next major release, but cannot confirm on that.

[#5]

@Sindhu

After deploying HDP 2.6.2 I am getting follwoing error whicle doing select * from statemnet.

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

Please suggest any configuration needed.

[#6]

@Sindhu

Please guide me. Needed urgent help.

[#7 · Accepted solution]

@Ashnee Sharma

The issue could be due to mismatch in the column cases of the Hive table columns being mapped to Phoenix table.

Match the case of columns under phoenix.column.mapping for example,

CREATE external db.test(
teste_hive decimal(38,10)
,teste1_hive decimal(38,10)
)
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
WITH SERDEPROPERTIES ( 'serialization.format'='1')
TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}',
'phoenix.column.mapping’=“teste_hive:TESTE,teste1_hive:TESTE1”,
'phoenix.rowkeys’=’TESTE1’,
'phoenix.table.name'='TEST_PHOENIX_TABLE’,
'phoenix.zookeeper.client.port'='2181',
'phoenix.zookeeper.quorum'='x.com',
'phoenix.zookeeper.znode.parent'='/hbase-unsecure' );

[#8]

@Sindhu

The issue is resolved. We were trying to create table in phoenix then trying to create in hive. Now we are creating tables directly into the hive and also able to see tables in phoenix.

Just need to know am I doing right method?

[#9]

@Ashnee SharmaFor INTERNAL tables, Hive manages the lifecycle of the table and data. When a Hive table is created, a corresponding Phoenix table is also created. Once the Hive table is dropped, the Phoenix table is also deleted.

For EXTERNAL tables, Hive works with an existing Phoenix table and manages only Hive metadata. Dropping an EXTERNAL table from Hive deletes only Hive metadata but does not delete the Phoenix table.

Refer https://phoenix.apache.org/hive_storage_handler.html

[#10]

@Sindhu

Thanks for the information.