How to create a Phoenix Table / View over an Existing Hbase table

Question

I am new to Phoenix, Hbase. We have a requirement to issue SQL Like queries against Hbase Database. A decision was made to go with Apache Phoenix as the SQL Skin on top of an existing Hbase table which was already getting loaded with the Data that we want to retrieve.

I am having trouble creating the Phoenix table which maps to the existing Hbase table.

Here is my sample Hbase Table
hbase(main):033:0> describe 'esrmock'
Table esrmock is ENABLED
esrmock
COLUMN FAMILIES DESCRIPTION
{NAME => 'esrdata', DATA_BLOCK_ENCODING => 'NONE', BLOOMFILTER => 'ROW', 
REPLICATION_SCOPE => '0', VERSIONS => '1', MIN_VERSIONS => '0', TTL => 
'FOREVER', KEEP_DELETED_CELLS => 'FALSE', BLOCKSIZE => '65536', IN_MEMORY => 
'false', BLOCKCACHE => 'true'}
1 row(s) in 0.0360 seconds

Sample Data in the table
hbase(main):034:0> scan 'esrmock'
ROW                                                          COLUMN+CELL
21333444555-234234564666-5                                  
                           column=esrdata:value, timestamp=1450450438296,                               
                           value=sdfgsdfhfgjfgdfgfghfghe546e547ydfghfghe547e567udtyfghngj
21333444555-23423534654-5                                   
                            column=esrdata:value, timestamp=1450450446777, 
                            value=sdfgsdfhfgjfgdfgfghfghe546e547ydfghfghe547e567udtyfghngj
21333444555-23423534666-5                                   
                            column=esrdata:value, timestamp=1450450443184, 
                           value=sdfgsdfhfgjfgdfgfghfghe546e547ydfghfghe547e567udtyfghngj
3 row(s) in 0.0250 seconds

What I want to do is to have a phoenix table that has 2 columns.

id -> This should be the primary key corresponding to the Hbase row Key.

data -> This should be the contents of the esrdata:value column.

From what I have tried I could only create a phoenix table with 1 column which was the row key in hbase, but I couldnt create the other column which contains the data that I am looking for.

I am looking for the Phoenix DDL syntax which will result in the table and columns that I need

Answer 1

See Apache Phoenix F.A.Q. Section How I map Phoenix table to an existing HBase table?

This topic can be helpful too link.

Answer 2

Phoenix has very good feature that if we will create any table in phoenix then it will reflect automatically in Hbase but if we will create any table in Hbase then it will not reflect in Phoenix.

If you want to map an existing Hbase table to Phoenix then you have to create a View in the phoenix and that view will be read only means it can fetch data from Hbase table and we can only see that data, we can not modify or insert new data through phoenix.

In your case, your column family name is 'esrdata' so run the below query in the phoenix :

create view esrmock (pk VARCHAR PRIMARY KEY, esrdata.value VARCHAR)

you can not have data column in phoenix because it will map exact same column as in Hbase so if you want column name as data then you have to create column in Hbase table and create view for the same. like :

create view esrmock (pk VARCHAR PRIMARY KEY, esrdata.data VARCHAR)

The “pk” column declares that your row key is a VARCHAR (i.e. a string) while the “esrdata”.value column declares that your HBase table will contain KeyValues with a column family and column qualifier of “esrdata”:VALUE and that their value will be a VARCHAR.

Note : Phoenix automatically do uppercase the column name internally so your Hbase table column should be in uppercase. like :

column=esrdata:VALUE

then only it will fetch the data from Hbase tables.

Answer 3

If you want to map an exising hbase table, you can execute command below:

CREATE TABLE "esrmock"(
PK VARCHAR PRIMARY KEY, 
"esrdata"."value" VARCHAR
);