Unable to view phoenix table primary keys via phoenix shell

Question

We are having a CDP 7.1.7 private cluster with phoenix setup.

We had created some phoenix tables long back with certain primary keys, however now when we are trying to check those primary keys on a table via phoenix shell, we are getting empty results.

I have tried below commands and no information.

!primarykeys <tablename>

!column <tablename>

!describe <tablename>

select column_name from system.catalog where table_name='tablename'

Phoenix version is 5.0.0

Please help.

smdas · Accepted Answer

Hello @SagarCapG

Confirmed that Phoenix v5.1.0 has the Fix for "!primarykeys" to show the Primary Key linked with a Phoenix Table. Upon checking our Product Documentation, CDP v7.1.6 introduces Phoenix v5.1.0 [1]. As such, I am surprised your Team has Phoenix v5.0.0 with CDP v7.1.7, wherein Official v7.1.7 Doc [2] says Phoenix v5.1.1.7.1.7.0-551 is used.

Since the Issue is fixed in Phoenix v5.1.x & CDP v7.1.6 onwards ship Phoenix v5.1.x, Kindly engage Cloudera Support to allow Support to review your Cluster for identifying the reasoning for CDP v7.1.7 using Phoenix v5.0.0. Or, Upgrade to Phoenix v5.1.x (If Customer is managing Phoenix outside of CDP) to use "!primarykeys" functionality.

Regards, Smarak

[1] What's New in Apache Phoenix | CDP Private Cloud (cloudera.com)

[2] Cloudera Runtime component versions | CDP Private Cloud

smdas · Answer

Hello @SagarCapG

Thanks for using Cloudera Community. While I haven't tested the above Use-Case in Phoenix v5.0.0, I tested in Phoenix v5.1.0 (Used in CDP v7.1.7) & the same shows the Primary Key correctly [1]. As such, I believe the above ask is fixed in CDP v7.1.8 atleast.

I reviewed Upstream JIRA & it appears the same is actually fixed in Phoenix v5.1.x as per comments in https://issues.apache.org/jira/browse/PHOENIX-6651. As such, Kindly plan for CDP v7.1.8 Upgrade for obtaining the concerned functionality of identifying PK via "!primarykeys".

Regards, Smarak

[1]

0: jdbc:phoenix:> CREATE TABLE Employee (EmpId Integer Not Null Primary Key, Ename Varchar(50));
0: jdbc:phoenix:> !primarykeys Employee;
+-----------+-------------+------------+-------------+---------+---------+-------------+-----------+-----------+-------------+---------+---------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | KEY_SEQ | PK_NAME | ASC_OR_DESC | DATA_TYPE | TYPE_NAME | COLUMN_SIZE | TYPE_ID | VIEW_CONSTANT |
+-----------+-------------+------------+-------------+---------+---------+-------------+-----------+-----------+-------------+---------+---------------+
|           |             | EMPLOYEE   | EMPID       | 1       |         | A        | 4         | INTEGER   | null        | 4       |               |
+-----------+-------------+------------+-------------+---------+---------+-------------+-----------+-----------+-------------+---------+---------------+

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.