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 are the primary keys in Phoenix are converted as row keys in hbase

How are the primary keys in Phoenix are converted as row keys in hbase

New Contributor

Phoenix Query:

CREATE TABLE STORE.DETAILS (Market_Key UNSIGNED_INT NOT NULL, Product_Key UNSIGNED_INT NOT NULL, Period_Key UNSIGNED_INT NOT NULL, Units double CONSTRAINT pk PRIMARY KEY (Market_Key, Product_Key, Period_Key)) 

In hbase i am having two columns i hope Primary key combination is converted to row key. can you please let me know how the primary keys are combined and converted to rowkeys

6 REPLIES 6

Re: How are the primary keys in Phoenix are converted as row keys in hbase

Hello Santosh

When creating a Phoenix table through the DSL, as you are doing, phoenix will handle all the magic before pushing using Hbase as a store. In this scenario you will get a complex rowkey in the order you have written it: Market_key-Product_Key-Period-Key so the order in which you declare in your statement is important as it will be the order of your complex rowkey. Furthermore to separate the values Phoenix will use a 0 byte in between each element of the key, or use size encoding info if applicable. So, for example if you have (varchar,u_long, varchar) primary key, the rowkey for values like 'X',1,'Y'will be : ('X', 0x00) (0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x01), ('Y')

Lastly the Primary Key elements becoming the rowkey they will not be Hbase columns of your table so keep that in mind will designing it if has importance.

Highlighted

Re: How are the primary keys in Phoenix are converted as row keys in hbase

New Contributor

Hi Maillard,

Thank you for your reply basically i am trying to convert a phoenix query to hbase query

Phoenix query:

SELECT PRODUCT_KEY, STORE_KEY, PERIOD_KEY , U_PER_STORE_WEEKS_PROJ FROM FG.FCT FCT where

PRODUCT_KEY in ( SELECT PRODUCT_KEY FROM FG.PRDC ) AND

STORE_KEY in ( SELECT STORE_KEY FROM FG.STR WHERE STORE IN ( '03441' ) ) AND

PERIOD_KEY in ( SELECT PERIOD_KEY FROM FG.PRD WHERE PERIOD_SHORT_DESC IN ('Super Bowl 2 W/E 02/07/15') );

Can this be done and can you let me know on how to start with for the conversion

Re: How are the primary keys in Phoenix are converted as row keys in hbase

Mentor

A great source of information is the Apache Phoenix FAQ, your question has been answered there https://phoenix.apache.org/faq.html#How_I_map_Phoenix_table_to_an_existing_HBase_table

I won't copy the whole answer

Our composite row keys are formed by simply concatenating the values together, with a zero byte character used as a separator after a variable length type.

Re: How are the primary keys in Phoenix are converted as row keys in hbase

New Contributor

Hey @Artem Ervits can you please give me an example of making a composite rowkey in Phoenix and accessing it? It'd be of great help to me.

Re: How are the primary keys in Phoenix are converted as row keys in hbase

New Contributor

I am also looking for an example which illustrate creation of a Hbase put statement corresponding to a upsert query in Phoenix which has a composite row key (primary key) similar to what is mentioned above. If someone can give an example of that it would be great. Not sure how can I add this Zero byte character in Hbase put statement.

Re: How are the primary keys in Phoenix are converted as row keys in hbase

New Contributor

If you look at how Phoenix handles in its code, you will get an Idea.

Specifically, org.apache.phoenix.query.QueryConstants.java defines zero byte separator as below

public static final byte SEPARATOR_BYTE = (byte) 0; 
public static final byte[] SEPARATOR_BYTE_ARRAY = new byte[] {SEPARATOR_BYTE};

You can use Bytes.add(byte[], byte[]) to append separator byte array to form a composite row key.

Let me know,How it works...

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