phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From KARTHICK Duraisamy Soundararaj <ksoundara...@groupon.com>
Subject Re: Secondary index breaks all queries
Date Wed, 07 Dec 2016 23:00:30 GMT
Thanks for the response James. Rised PHOENIX-3523
<https://issues.apache.org/jira/browse/PHOENIX-3523>.

On Wed, Dec 7, 2016 at 12:57 PM, James Taylor <jamestaylor@apache.org>
wrote:

> Thanks for narrowing down the cause, Karthick. Please file a JIRA with
> this information.
>
>     James
>
> On Wed, Dec 7, 2016 at 12:45 PM, KARTHICK Duraisamy Soundararaj <
> ksoundararaj@groupon.com> wrote:
>
>> 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 <
>> ksoundararaj@groupon.com> 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 <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>",
"merchant.feature_country" from "m3.merchants" limit 1;
>>> +---------------------------------------+-------------------+---------------------------+
>>> |                primary                |   merchant.name <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>
  | merchant.feature_country  |
>>> +---------------------------------------+-------------------+---------------------------+
>>> | 00001860-00259060b612  | XXXXX                 | US                       
|
>>> +---------------------------------------+-------------------+---------------------------+
>>>
>>> ​
>>>
>>> 2. I create a secondary index on "m3.merchants" for "merchant.name
>>> <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>".
>>> 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
<https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>");
>>> 1,660,274 rows affected (36.341 seconds)
>>>
>>> 0: jdbc:phoenix:dev.snc1> select "primary", "merchant.name <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>",
"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 <https://urldefense.proofpoint.com/v2/url?u=http-3A__merchant.name&d=DgMFaQ&c=LNdz7nrxyGFUIUTz2qIULQ&r=ibZuebyjpgOGN8_Vls6nU-h1Je4eG1upPUO9sIpBAqg&m=z4sZBYWqUAJal5N46_o7jV0WCQ8_s-eAFfCIIeUkReY&s=h4Rm0OI0gYjcVUMHyxcuA2hZnMjcDsbz9W04LFmkHgM&e=>",
"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
>>>
>>>
>>
>

Mime
View raw message