Created 08-16-2018 01:02 AM
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://
Created 08-16-2018 03:28 AM
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:
And if you see, the Jira ticket it's still opened.
https://issues.apache.org/jira/browse/PHOENIX-1507
Hope this helps
Created 08-16-2018 04:43 AM
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
Created 08-16-2018 06:24 PM
Created 08-16-2018 06:27 PM
And also, could you share the create table statement?
Thanks
Created 08-16-2018 07:27 PM
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>