I am able to create a secondary index on case-insensitive schema.table and everything works as expected. In other words, index table for a non-case sensitive table gets created in the appropriate namespace on HBase.
On Tue, Dec 6, 2016 at 4:18 PM, KARTHICK Duraisamy Soundararaj < [email protected]> wrote: > Hi Team, > I am not sure what I am missing but the secondary index > totally breaks all the phoenix queries. Following sequence of actions > illustrates my problem > > 1. On HBase, I have "m3:merchants". It is mapped to "m3.merchants" on > phoenix. As you can see below, I can query the table just fine. > > 0: jdbc:phoenix:dev.snc1> drop index "merchant_feature_country_idx" on > "m3.merchants"; > No rows affected (4.006 seconds) > 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name", > "merchant.feature_country" from "m3.merchants" limit 1; > +---------------------------------------+-------------------+---------------------------+ > | primary | merchant.name | > merchant.feature_country | > +---------------------------------------+-------------------+---------------------------+ > | 00001860-00259060b612 | XXXXX | US | > +---------------------------------------+-------------------+---------------------------+ > > > > 2. I create a secondary index on "m3.merchants" for "merchant.name". The > moment I do this, "m3.merchants" table is not usable anymore. > > 0: jdbc:phoenix:dev.snc1> create index "merchant_feature_country_idx" ON > "m3.merchants"("merchant.name"); > 1,660,274 rows affected (36.341 seconds) > > 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name", > "merchant.feature_country" from "m3.merchants" limit 1; > Error: ERROR 1012 (42M03): Table undefined. > tableName=m3.merchant_feature_country_idx (state=42M03,code=1012) > org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table > undefined. tableName=m3.merchant_feature_country_idx > at > org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:539) > at > org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:365) > at > org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:213) > at > org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:226) > at > org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:146) > at > org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94) > at > org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80) > at > org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:278) > at > org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266) > at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) > at > org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265) > at > org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1446) > at sqlline.Commands.execute(Commands.java:822) > at sqlline.Commands.sql(Commands.java:732) > at sqlline.SqlLine.dispatch(SqlLine.java:807) > at sqlline.SqlLine.begin(SqlLine.java:681) > at sqlline.SqlLine.start(SqlLine.java:398) > at sqlline.SqlLine.main(SqlLine.java:292) > > > > When I look at my HBase tables, I see that the index is created in the > "default" namespace > > hbase(main):006:0> list_namespace_tables "default" > TABLE > merchant_feature_country_idx > 1 row(s) in 0.0100 seconds > > hbase(main):007:0> list_namespace_tables "m3" > TABLE > merchants > 1 row(s) in 0.0080 seconds > > > > 3. I tried the following to force the hbase index table to be located > under "m3" namespace. But I get the following error. > > 0: jdbc:phoenix:dev.snc1> create index "m3.merchant_feature_country_idx" ON > "m3.merchants"("merchant.feature_country"); > 1,660,274 rows affected (32.14 seconds) > 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name", > "merchant.feature_country" from "m3.merchants" limit 1; > Error: ERROR 1012 (42M03): Table undefined. > tableName=m3.m3.merchant_feature_country_idx (state=42M03,code=1012) > org.apache.phoenix.schema.TableNotFoundException: ERROR 1012 (42M03): Table > undefined. tableName=m3.m3.merchant_feature_country_idx > at > org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.createTableRef(FromCompiler.java:539) > at > org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.<init>(FromCompiler.java:365) > at > org.apache.phoenix.compile.FromCompiler.getResolverForQuery(FromCompiler.java:213) > at > org.apache.phoenix.optimize.QueryOptimizer.addPlan(QueryOptimizer.java:226) > at > org.apache.phoenix.optimize.QueryOptimizer.getApplicablePlans(QueryOptimizer.java:146) > at > org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:94) > at > org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:80) > at > org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:278) > at > org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266) > at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) > at > org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:265) > at > org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1446) > at sqlline.Commands.execute(Commands.java:822) > at sqlline.Commands.sql(Commands.java:732) > at sqlline.SqlLine.dispatch(SqlLine.java:807) > at sqlline.SqlLine.begin(SqlLine.java:681) > at sqlline.SqlLine.start(SqlLine.java:398) > at sqlline.SqlLine.main(SqlLine.java:292) > > > > Below is the evidence that index table on hbase is located under "m3" > namespace > > hbase(main):008:0> list_namespace_tables "m3" > TABLE > merchant_feature_country_idx > merchants > 2 row(s) in 0.0100 seconds > > hbase(main):009:0> list_namespace_tables "default" > TABLE > 0 row(s) in 0.0030 seconds > > > > Not sure if this is a bug in the namespace mapping implementation or if I > am missing something. I would appreciate if someone could shed some light > on this. > > Environment: > HBase version : 1.1.2 > Phoenix version : 4.8.1 > > Context: > I am new to phoenix and playing around with various phoenix features. > > Thanks, > Karthick > >
