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.

Views on existing HBase namespace tables

Solved Go to solution
Highlighted

Views on existing HBase namespace tables

New Contributor

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)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Views on existing HBase namespace tables

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.

View solution in original post

3 REPLIES 3
Highlighted

Re: Views on existing HBase namespace tables

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.

View solution in original post

Highlighted

Re: Views on existing HBase namespace tables

New Contributor

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.

Highlighted

Re: Views on existing HBase namespace tables

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.

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