Views on existing HBase namespace tables

[#1]

Dear Experts,

We have HDP 2.5 (Phoenix 4.7.0.2.5.0.0-1245) - does it support View creation on an HBase table created in a namespace using HBase shell (i.e. not created through Phoenix Grammar)?

I have run knit,

kinit -k -t /etc/security/keytabs/hbase.headless.keytab hbase-cluster1

In Hbase shell,

create 'durham:TEST', 'b'
put 'durham:TEST', '1', 'b:message', 'Hello'
put 'durham:TEST', '2', 'b:message', 'World'
hbase(main):026:0> scan 'durham:TEST'
ROW                         COLUMN+CELL                                                                 
 1                          column=b:message, timestamp=1478511785906, value=Hello                      
 2                          column=b:message, timestamp=1478511794551, value=World 

then sqlline,

0: jdbc:phoenix:localhost:2181/hbase-secure> select DISTINCT TABLE_SCHEM from SYSTEM.CATALOG;
+--------------+
| TABLE_SCHEM  |
+--------------+
| DURHAM       |
| SYSTEM       |
+--------------+

create view gives ,

use "durham";
CREATE VIEW "TEST" (
  pk VARCHAR PRIMARY KEY
 ,"b"."message"  VARCHAR
 )
 AS SELECT * FROM "TEST";

create view error ,

Error: ERROR 505 (42000): Table is read only. (state=42000,code=505)
org.apache.phoenix.schema.ReadOnlyTableException: ERROR 505 (42000): Table is read only.
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.ensureTableCreated(ConnectionQueryServicesImpl.java:1032)
	at org.apache.phoenix.query.ConnectionQueryServicesImpl.createTable(ConnectionQueryServicesImpl.java:1415)
	at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:2180)
	at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:865)
	at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:194)
	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:343)
	at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:331)
	at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
	at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:329)
	at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1440)
	at sqlline.Commands.execute(Commands.java:822)
	at sqlline.Commands.sql(Commands.java:732)
	at sqlline.SqlLine.dispatch(SqlLine.java:808)
	at sqlline.SqlLine.begin(SqlLine.java:681)
	at sqlline.SqlLine.start(SqlLine.java:398)
	at sqlline.SqlLine.main(SqlLine.java:292)

[#2 · Accepted solution]

First you can map the table created in HBase to Phoenix with create table query as mentioned in "Mapping to an Existing HBase Table" at phoenix.apache.org and then create view on it.

[#3]

Rajesh,

Thank you, I didn't see that (it might help if that were in the Views documentation page).

This worked,

use "durham";
create table "TEST" (PK VARCHAR PRIMARY KEY, "b"."message" VARCHAR);

SELECT * FROM "TEST";
+-----+----------+
| PK  | message  |
+-----+----------+
| 1   | Hello    |
| 2   | World    |
+-----+----------+
2 rows selected (0.126 seconds)

using this approach (i.e. CREATE TABLE) I actually don't require a View, although it has a different issue:

CREATE VIEW "TEST" (  pk VARCHAR PRIMARY KEY ,"b"."message"  VARCHAR ) AS SELECT * FROM "TEST";

Error: ERROR 1036 (42J04): Cannot modify the primary key of a VIEW if last PK column of parent is variable length. columnName=durham.TEST.PK (state=42J04,code=1036)
java.sql.SQLException: ERROR 1036 (42J04): Cannot modify the primary key of a VIEW if last PK column of parent is variable length. columnName=durham.TEST.PK

are VARCHAR PRIMARY KEY's not supported on Views?

Chris.

[#4]

After creating the table you can create view like below

CREATE VIEW "TESTVIEW" AS SELECT * FROM "TEST"

or else if you want to map HBase table as view or read only you can just try without select query.

CREATE VIEW "TEST" (  pk VARCHAR PRIMARY KEY ,"b"."message"  VARCHAR ) 

If this solves the problem mark it as best answer.