Apache Phoenix view creation error - Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1

[#1]

Hello experts,

I am using Phoenix on AWS EMR v5.16.0 9 (Hbase 1.4.4, phoenix 4.14.0)

When trying to create a phoenix view, I see below error.

Is this expected behaviour Is there a way we can map correct datatypes in phoenix to Hbase?

Did anyone encounter this error? Can you please guide me on this issue?

Steps to reproduce the issue :

Hbase - create 'AP:TEST', 'B'
put 'AP:TEST', '1', 'B:MESSAGE', 'Hello'

Phoenix - CREATE VIEW AP.TEST(ID VARCHAR PRIMARY KEY,"b".MESSAGE VARCHAR)
CREATE VIEW AP.TEST1 (ID INTEGER,MESSAGE VARCHAR) AS SELECT TO_NUMBER(ID),MESSAGE FROM AP.TEST;

jdbc:phoenix:thin:url=http://localhost:876> CREATE VIEW AP.TEST1(ID INTEGER,MESSAGE VARCHAR) AS SELECT TO_NUMBER(ID),MESSAGE FROM AP.TEST;
Error: Error -1 (00000) : Error while executing SQL "CREATE VIEW AP.TEST1(ID INTEGER,MESSAGE VARCHAR) AS SELECT TO_NUMBER(ID),MESSAGE FROM AP.TEST": Remote driver error: RuntimeException: org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1, column 60. -> PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1, column 60. -> MismatchedTokenException: (null exception message) (state=00000,code=-1)
org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : Error while executing SQL "CREATE VIEW AP.TEST1(ID INTEGER,MESSAGE VARCHAR) AS SELECT TO_NUMBER(ID),MESSAGE FROM AP.TEST": Remote driver error: RuntimeException: org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1, column 60. -> PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1, column 60. -> MismatchedTokenException: (null exception message)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:54)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.Helper.createException(Helper.java:41)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:156)
        at org.apache.phoenix.shaded.org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:209)
        at sqlline.Commands.execute(Commands.java:822)
        at sqlline.Commands.sql(Commands.java:732)
        at sqlline.SqlLine.dispatch(SqlLine.java:813)
        at sqlline.SqlLine.begin(SqlLine.java:686)
        at sqlline.SqlLine.start(SqlLine.java:398)
        at sqlline.SqlLine.main(SqlLine.java:291)
        at org.apache.phoenix.queryserver.client.SqllineWrapper.main(SqllineWrapper.java:93)
java.lang.RuntimeException: org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1, column 60.
        at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:683)
        at org.apache.calcite.avatica.jdbc.JdbcMeta.prepareAndExecute(JdbcMeta.java:748)
        at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:206)
        at org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:928)
        at org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:880)
        at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
        at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
        at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:127)
        at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
        at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
        at org.apache.phoenix.shaded.org.eclipse.jetty.server.Server.handle(Server.java:499)
        at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
        at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
        at org.apache.phoenix.shaded.org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
        at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
        at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.phoenix.exception.PhoenixParserException: ERROR 604 (42P00): Syntax error. Mismatched input. Expecting "ASTERISK", got "TO_NUMBER" at line 1, column 60.
        at org.apache.phoenix.exception.PhoenixParserException.newException(PhoenixParserException.java:33)
        at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:111)
        at org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementParser.parseStatement(PhoenixStatement.java:1644)
        at org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1727)
        at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1819)
        at org.apache.calcite.avatica.jdbc.JdbcMeta.prepareAndExecute(JdbcMeta.java:730)
        ... 15 more
Caused by: MismatchedTokenException(108!=13)
        at org.apache.phoenix.parse.PhoenixSQLParser.recoverFromMismatchedToken(PhoenixSQLParser.java:376)
        at org.apache.phoenix.shaded.org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
        at org.apache.phoenix.parse.PhoenixSQLParser.create_view_node(PhoenixSQLParser.java:1761)
        at org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:886)
        at org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:524)
        at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
        ... 19 more


0: jdbc:phoenix:thin:url=http://

[#2]

Hello @cskbhatt!
Looking at the documentation seems it's a limitation from Phoenix when you need to project all columns to the view creation.

https://phoenix.apache.org/views.html

Here's the part telling about the issue listed above:

1. All columns must be projected into a VIEW when it’s created (i.e. only CREATE VIEW … AS SELECT * is supported). Note, however, you may drop non primary key columns inherited from the base table in a VIEW after it is created through the ALTER VIEW command. Providing a subset of columns and or expressions in the SELECT clause will be supported in a future release (PHOENIX-1507).

And if you see, the Jira ticket it's still opened.

https://issues.apache.org/jira/browse/PHOENIX-1507

Hope this helps

[#3]

Thanks @Vinicius Higa Murakami for pointing to the JIRA

Actually I am trying to change the datatype of a column in phoenix table mapped to Hbase by creating a VIEW ( SELECT TO_NUMBER(ID),MESSAGE FROM AP.TEST)

Now when I create a phoenix table mapping to Hbase table, the datatypes do not map correctly.

I even set below properties in hbase-site.xml but no luck

<property> <name>phoenix.schema.isNamespaceMappingEnabled</name> <value>true</value>

</property> <property> <name>phoenix.schema.mapSystemTablesToNamespace</name> <value>true</value> </property>

phoenix0: jdbc:phoenix:thin:url=http://localhost:876> select * from AP.TEST;
+-----+----------+
| ID  | MESSAGE  |
+-----+----------++-----+----------+
No rows selected (0.031 seconds)

hbase(main):002:0> scan 'AP:TEST'
ROW                            COLUMN+CELL 
1                             column=B:MESSAGE, timestamp=1533062047409, value=Hello
2                             column=B:MESSAGE, timestamp=1533062316879, value=World
2 row(s) in 0.0860 seconds

[#4]

Got it @cskbhatt.

Are you able to count the table?

select count(*) from "AP"."TEST";

[#5]

And also, could you share the create table statement?

Thanks

[#6]

select count(*) from AP.TEST shows 1 row but the select returns nothing

Below is how I ran the test

Hbase
create 'AP:TEST', 'B'
put 'AP:TEST', '1', 'B:MESSAGE', 'Hello'
put 'AP:TEST', '2', 'B:MESSAGE', 'World'<br>

Phoenix 
CREATE TABLE AP.TEST(ID VARCHAR PRIMARY KEY,"b"."message" VARCHAR) or CREATE TABLE AP.TEST(ID BIGINT PRIMARY KEY,"b"."message" VARCHAR)
jdbc:phoenix:thin:url=http://localhost:876> select count(*) from AP.TEST;
+-----------+
| COUNT(1)  |
+-----------+
| 1         |
+-----------+
1 row selected (0.013 seconds)
0: jdbc:phoenix:thin:url=http://localhost:876> select * from AP.TEST;
+-----+----------+
| ID  | MESSAGE  |
+-----+----------+
+-----+----------+
No rows selected (0.012 seconds)
0: jdbc:phoenix:thin:url=http://localhost:876>