Create view in Apache Phoenix: Error 505

Question

I'm not able to run in phoenix the command

CREATE VIEW "t1" ( pk VARCHAR PRIMARY KEY, "f1".val VARCHAR )

after that i have executed the in hbase command

create 't1', {NAME => 'f1', VERSIONS => 5}

I get the following error

org.apache.phoenix.schema.ReadOnlyTableException: ERROR 505 (42000): Table is read only.
    at org.apache.phoenix.query.ConnectionQueryServicesImpl.ensureTableCreated(ConnectionQueryServicesImpl.java:815)
    at org.apache.phoenix.query.ConnectionQueryServicesImpl.createTable(ConnectionQueryServicesImpl.java:1174)
    at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1974)
    at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:770)
    at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:186)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:305)
    at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:297)
    at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
    at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:295)
    at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1255)
    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)

These are commands that are showed in https://phoenix.apache.org/faq.html under the section

How I map Phoenix table to an existing HBase table?

You need to create the table before the view. That is obvious. Provided that, your commands work perfectly if you run them one after the other in the right order without tampering the HBase table content. If you alter the schema by adding new columns or so you might experience mapping problems — Mehdi LAMRANI

acg acg · Accepted Answer · 2017-05-17T10:10:14

You wanted to map phoenix table to existing HBase table. However in issue description you have created Phoenix view first and after that you are trying to create HBase table with same name. Hence you are getting org.apache.phoenix.schema.ReadOnlyTableException: ERROR 505 (42000): Table is read only. exception.

Instead of that create HBase table first and then create Phoenix view which maps to existing HBase table.

Example: Creating HBase table:

create 't1', {NAME => 'f1', VERSIONS => 5}

Creating phoenix view pointing to same HBase table:

CREATE VIEW "t1" ( pk VARCHAR PRIMARY KEY, "f1".val VARCHAR )

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

Create view in Apache Phoenix: Error 505

2 Answers