Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to get HBase column qualifier name of a table created through phoenix

how to get HBase column qualifier name of a table created through phoenix

New Contributor

I have a created a phoenix table test with the following schema

CREATE TABLE test

(

 tag varchar(10) NOT NULL,

 ts DATE NOT NULL,

 val INTEGER

 CONSTRAINT pk PRIMARY KEY (tag, ts)

)

Now I want to know how these "tag", "ts", "val" fields are stored in HBase and with what name. Would it be something like "columnFamily:qualifier" i.e "0:tag".

I am using apache-phoenix-4.11.0-HBase-1.3-bin.

6 REPLIES 6

Re: how to get HBase column qualifier name of a table created through phoenix

Super Guru

@Himanshu Mishra

Go to HBase shell, and run "describe test". This will show you how table was created in HBase.

A better way to create tables in Phoenix is to follow the following convention:

CREATE TABLE TEST (MYKEY VARCHAR NOT NULL PRIMARY KEY, A.COL1 VARCHAR, A.COL2 VARCHAR, B.COL3 VARCHAR)

Above statement will create two column families "A" and "B" with qualifies "Col1" and "Col2" in column family A and "col3" in column family B. When you create tables from Phoenix, it also adds an empty key-value for each row so queries work in a SQL like fashion without requiring you to write all projections in your query.

Following link describes how columns are mapped from Phoenix to HBase.

https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_tablention

Re: how to get HBase column qualifier name of a table created through phoenix

It seems you are using column mapping feature(Though it is default from 4.11), which encodes column name in some encoded form of qualifier while storing in hbase , this is to save space and for better performance.

Currently, we don't provide any API(as there is no standard JDBC API which exposes the details of the storage) to give you such mapping.

if your application requires using the HBase qualifier directly, then I would suggest creating a table having column encoding disabled so that the column name will be used for HBase qualifier as well.

CREATE TABLE test
( tag varchar(10) NOT NULL, 
ts DATE NOT NULL,
val INTEGER CONSTRAINT pk PRIMARY KEY (tag, ts)
)COLUMN_ENCODED_BYTES=0;

Re: how to get HBase column qualifier name of a table created through phoenix

New Contributor

@Ankit Singhal Do we have a way for getting this column mapping from any System table. I am using two HBase clients somehow, one for creating tables and ingesting data through (Apache Phoenix), which converts all column qualifiers to encoded form.

And While retrieving data from HBase , I am using HappyBase through Thrift, which actually needs exact column qualifiers. Looking forward to hear from you. Thanks

Re: how to get HBase column qualifier name of a table created through phoenix

Check if columns "COLUMN_QUALIFIER, COLUMN_NAME" of SYSTEM.CATALOG can help.

Re: how to get HBase column qualifier name of a table created through phoenix

New Contributor

@Ankit Singhal Thank you. I was able to obtain column mapping for column qualifiers in "SYSTEM.CATALOG" table in the column "COLUMN_QUALIFIER" except for column qualifiers which are declared as PK in the DDL through Apache Phoenix.

For those column qualifiers which were declared as PK, their corresponding column mapping in "SYSTEM.CATALOG" does not have any column "COLUMN_QUALIFIER", instead it has "KEY_SEQ", which mostly maps to integer value and does not seem to have the value for column qualifier encoding.

Highlighted

Re: how to get HBase column qualifier name of a table created through phoenix

no mapping is required for PK columns as all PK columns are concatenated together to form a row key.

Don't have an account?
Coming from Hortonworks? Activate your account here