Thanks a bunch Samarth and James, I will switch our scripts over to use the correct way!
Jeff From: James Taylor [mailto:[email protected]] Sent: September-15-15 7:22 PM To: user <[email protected]> Subject: Re: Can't add views on HBase tables after upgrade Correct, you must use the CREATE VIEW <table> (<column> <type>) syntax when you're creating a view directly over HBase tables. Even through this syntax, you must also specify the primary key (which is not currently required - I filed PHOENIX-2265 for this). The reason is that you're telling Phoenix the structure of your table: the PK column(s), the other columns, and all of their types so that Phoenix knows how to deserialize and query the data. HBase does not have this information, hence the need to provide it. The CREATE VIEW <table> AS SELECT * FROM <table> does not provide this information so it's not supported. Once you create a view this way, you can use the CREATE VIEW AS SELECT syntax to create more views on top of this view, though. Thanks, James On Tue, Sep 15, 2015 at 11:48 AM, Samarth Jain <[email protected]<mailto:[email protected]>> wrote: Yes, I don't think creating views that way on existing HBase tables is supported. Maybe James can confirm? FWIW, I tried the following on 4.4.1 version of Phoenix: hbase shell: create T2, 'f1', 'f2', 'f3' create T1 'f1', f2' sqlline: create view T2_VIEW AS SELECT * FROM T2; This fails: at org.apache.phoenix.query.ConnectionQueryServicesImpl.generateTableDescriptor(ConnectionQueryServicesImpl.java:632) at org.apache.phoenix.query.ConnectionQueryServicesImpl.ensureTableCreated(ConnectionQueryServicesImpl.java:827) at org.apache.phoenix.query.ConnectionQueryServicesImpl.createTable(ConnectionQueryServicesImpl.java:1215) at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1912) at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:744) at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:186) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:302) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:294) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:292) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1199) 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) sqlline: CREATE VIEW T1 ("f1".COL1 INTEGER); This works. On Tue, Sep 15, 2015 at 5:56 AM, Jeffrey Lyons <[email protected]<mailto:[email protected]>> wrote: Hey Samarth, Thanks for looking into that for me! I can give you a bit more information about what I’m doing. We have view creation scripts that we use to create our schema when we spin up development environments. These scripts also get run on upgrades to our main cluster if we need to add new views. The views are created directly on our existing HBase tables. These view creation scripts work fine in 4.4-HBase-0.98, but they use the unsupported method you describe below, basically creating a view with a select statement directly over an HBase table. When I upgraded to the new version they fail. I guess if someone could either confirm that “CREATE VIEW T_VIEW AS SELECT * FROM T;” is unsupported, or point me to some docs that reiterate that, it would help my case to refactor all the scripts to fit the supported format. Thanks, Jeff From: Samarth Jain [mailto:[email protected]<mailto:[email protected]>] Sent: September-14-15 7:32 PM To: [email protected]<mailto:[email protected]> Subject: Re: Can't add views on HBase tables after upgrade Jeffrey, Can you tell us how are creating your view over the existing HBase table? For example this works for me: HBase shell: create 'T', 'f1', 'f2', 'f3' Phoenix sql line: create view T ("f1".col1 INTEGER) Note that HBase shell is case sensitive. So we need to put the family name f1 in double quotes. The below however isn't supported: CREATE VIEW T_VIEW AS SELECT * FROM T; On Sat, Sep 12, 2015 at 8:00 PM, James Taylor <[email protected]<mailto:[email protected]>> wrote: If we've broken views over HBase tables, we'll need to -1 the RC and get a fix IMO. Thanks in advance for offering to look into it, Samarth. On Sat, Sep 12, 2015 at 11:22 AM, Samarth Jain <[email protected]<mailto:[email protected]>> wrote: Jeffrey, I will look into this and get back to you. - Samarth On Thu, Sep 10, 2015 at 8:44 AM, Jeffrey Lyons <[email protected]<mailto:[email protected]>> wrote: Hey all, I have recently tried upgrading my Phoenix version from 4.4-HBase-0.98 to build 835 on 4.x-HBase-0.98 to get some of the new changes. After the upgrade it seems that I can’t add new views directly over raw HBase tables, though it seems to work fine over Phoenix tables or views. The error it gives when I try to add a view is: Error: ERROR 1012 (42M03): Table undefined. tableName=<myTable> (state=42M03,code=1012) org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table undefined. tableName=<myTable> at org.apache.phoenix.schema.PMetaDataImpl.getTable(PMetaDataImpl.java:260) at org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:1655) at org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:751) at org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:186) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:315) at org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:307) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:305) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1375) 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) (myTable is an Hbase table name in this case) Any advice, or could this just be an issue with the current state of the code? Thanks, Jeff
