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:41 GMT
raised*

On Wed, Dec 7, 2016 at 3:00 PM, KARTHICK Duraisamy Soundararaj <
ksoundararaj@groupon.com> wrote:

> 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