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.