You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by KARTHICK Duraisamy Soundararaj <ks...@groupon.com> on 2016/12/07 00:18:37 UTC
Secondary index breaks all queries
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
Re: Secondary index breaks all queries
Posted by KARTHICK Duraisamy Soundararaj <ks...@groupon.com>.
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 <ja...@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
>>>>
>>>>
>>>
>>
>
Re: Secondary index breaks all queries
Posted by KARTHICK Duraisamy Soundararaj <ks...@groupon.com>.
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 <ja...@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
>>>
>>>
>>
>
Re: Secondary index breaks all queries
Posted by James Taylor <ja...@apache.org>.
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", "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
>>
>>
>
Re: Secondary index breaks all queries
Posted by KARTHICK Duraisamy Soundararaj <ks...@groupon.com>.
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", "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
>
>