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.
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.
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;
@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
@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.
no mapping is required for PK columns as all PK columns are concatenated together to form a row key.