Phoenix view over existing hbase table works but Phoenix table doesn't

Question

I'm new to HBase and Phoenix. I have problem mapping an existing HBase table into phoenix. My ultimate intent here is that I should be able to update existing rows of the HBase table as well as insert new rows into it using Phoenix SQL. My table in HBASE looks like this (it has five columns under the column family CASEDETAILS):

CASES
COLUMN FAMILIES DESCRIPTION
{NAME => 'CASEDETAILS', BLOOMFILTER => 'NONE', VERSIONS => '2', IN_MEMORY => 
'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', TTL 
=> 'FOREVER',COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 
'false', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}

When I create a view over this table using the command

create view CASES( pk VARCHAR PRIMARY KEY,
                    CASEDETAILS.CASEID VARCHAR,
                    CASEDETAILS.CREATED VARCHAR,
                    CASEDETAILS.CREATEDBY VARCHAR,
                    CASEDETAILS.MBI VARCHAR,
                    CASEDETAILS.MEMBER VARCHAR);

it works and I'm able to fetch the data from the table with the select query but I'm not able to update any rows nor able to insert new rows. It gives an error "Table is Read-Only"

But when in the above Phoenix create command I replace "view" with "table" i.e. I'm trying to create a table with same name in Phoenix; table creation is successful but select statement doesn't work.

So I have few questions here:

1) Why the view over existing Hbase table works but table doesn't?

2) Is there a way to update existing rows in HBase table using Phoenix SQL interface?

3) How important it is to maintain the order of column names and column names case when mapping an existing table in Phoenix?

Note: I have explored existing database for similar questions and reached to the point to realise that my view was working but not table. However, none of the questions were able to resolve my end problem which is to update existing rows of existing HBase table through Phoenix

Nishu Tayal Nishu Tayal · Accepted Answer · 2018-11-19T16:27:12

Here are the answers:

1.Views may also be defined with more complex WHERE clauses, but in that case you cannot issue DML against them as you’ll get a ReadOnlyException. You are still allowed to query through them and their WHERE clauses will be in effect as with standard SQL views.
As expected, you may create a VIEW on another VIEW as well to further filter the data set. The same rules as above apply: if only simple equality expressions are used in the VIEW and its parent VIEW(s), the new view is updatable as well, otherwise it’s read-only.

You can find more details about view here : https://phoenix.apache.org/views.html

2.Yes, you can update/insert the values in phoenix table that updates the underlying HBase table as well.

UPSERT INTO TEST(NAME,ID) VALUES('foo',123);

It will insert the record in the table if not exists otherwise update the existing value.

3.It is not necessary to maintain the order of column Name. Column Names are mapped with the names. By default it takes all names as uppercased, but if you want, you can specify within quotes in lowercase. https://phoenix.apache.org/language/index.html#create_table

Phoenix view over existing hbase table works but Phoenix table doesn't

1 Answers