Apache Phoenix - Create view on existing phoenix table with additional calculated field

Question

I want to create a view in apache phoenix on an existing phoenix table and with an additional column. The following create view statements are throwing error:

CREATE VIEW  SCHEMA01.OSLE_VW (EVENTTIMESTAMP TIMESTAMP) AS SELECT *, CAST(TO_NUMBER("EVENTTIME") AS TIMESTAMP) EVENTTIMESTAMP FROM SCHEMA01.OSLE;
CREATE VIEW  SCHEMA01.OSLE_VW AS SELECT *, CAST(TO_NUMBER("EVENTTIME") AS TIMESTAMP) EVENTTIMESTAMP FROM SCHEMA01.OSLE;

Error: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "FROM", got "," at line 1, column 70 (state=42P00,code=604) org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "FROM", got "," at line 1, column 70.

Please suggest if it is possible to achieve this in phoenix.

Answer 1

After going over the documentation and Phoenix pro book, I have come to understand that Phoenix does not allow additional computed fields in the view. A view can have additional fields other than the underlying table. The values of these fields need to be provided while adding data via the view upsert statements. The additional fields cannot be computed based on the existing table data. Thus the additional fields in Phoenix act very differently from the commonly used relational database systems.