Re: Secondary index question

[Miles Spielberg]

I believe this is the same bug we encountered with local indexes where Phoenix 
4.13.1 failed to join back to the main table if the local index is not covering 
for all columns referenced in the WHERE.

> On Feb 27, 2018, at 9:36 AM, James Taylor  wrote:
> 
> Please file a JIRA and include the Phoenix and HBase version. Sounds like 
> you’ve found a bug.
> 
>> On Tue, Feb 27, 2018 at 9:21 AM Jonathan Leech  wrote:
>> I’ve done what you’re looking for by selecting the pk from the index in a 
>> nested query and filtering the other column separately.
>> 
>> > On Feb 27, 2018, at 6:39 AM, Alexey Karpov  wrote:
>> >
>> > Thanks for quick answer, but my case is a slightly different. I've seen 
>> > these
>> > links and already use local index. All cases, described in faq, index_usage
>> > and any other, I've found in this user list, are about SELECT clause. In
>> > WHERE clause there is always field from the index.
>> >
>> > In my case in WHERE clause I have one field from the index and one not from
>> > the index, combined with AND operator:
>> > SELECT * from test WHERE name = 'a' *AND description = 'b'*
>> > name - from the index
>> > description - not from the index
>> >
>> > Without filter on description (only on name) Phoenix uses index, as 
>> > expected
>> > for local index. But with additional filter Phoenix decides to do a full
>> > scan. And my question is: Is there any way to make Phoenix use index in 
>> > such
>> > types of queries, without include all fields in index ?
>> >
>> > Hint does not help:
>> > SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
>> > description= 'b'
>> > ColumnNotFoundException: ERROR 504 (42703): Undefined column.
>> > columnName=DESCRIPTION.
>> >
>> > Thanks,
>> > Alexey.
>> >
>> >
>> >
>> > --
>> > Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/

Re: Secondary index question

[James Taylor]

Please file a JIRA and include the Phoenix and HBase version. Sounds like
you’ve found a bug.

On Tue, Feb 27, 2018 at 9:21 AM Jonathan Leech  wrote:

> I’ve done what you’re looking for by selecting the pk from the index in a
> nested query and filtering the other column separately.
>
> > On Feb 27, 2018, at 6:39 AM, Alexey Karpov  wrote:
> >
> > Thanks for quick answer, but my case is a slightly different. I've seen
> these
> > links and already use local index. All cases, described in faq,
> index_usage
> > and any other, I've found in this user list, are about SELECT clause. In
> > WHERE clause there is always field from the index.
> >
> > In my case in WHERE clause I have one field from the index and one not
> from
> > the index, combined with AND operator:
> > SELECT * from test WHERE name = 'a' *AND description = 'b'*
> > name - from the index
> > description - not from the index
> >
> > Without filter on description (only on name) Phoenix uses index, as
> expected
> > for local index. But with additional filter Phoenix decides to do a full
> > scan. And my question is: Is there any way to make Phoenix use index in
> such
> > types of queries, without include all fields in index ?
> >
> > Hint does not help:
> > SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a'
> AND
> > description= 'b'
> > ColumnNotFoundException: ERROR 504 (42703): Undefined column.
> > columnName=DESCRIPTION.
> >
> > Thanks,
> > Alexey.
> >
> >
> >
> > --
> > Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
>

Re: Secondary index question

[Jonathan Leech]

I’ve done what you’re looking for by selecting the pk from the index in a 
nested query and filtering the other column separately.

> On Feb 27, 2018, at 6:39 AM, Alexey Karpov  wrote:
> 
> Thanks for quick answer, but my case is a slightly different. I've seen these
> links and already use local index. All cases, described in faq, index_usage
> and any other, I've found in this user list, are about SELECT clause. In
> WHERE clause there is always field from the index.
> 
> In my case in WHERE clause I have one field from the index and one not from
> the index, combined with AND operator:
> SELECT * from test WHERE name = 'a' *AND description = 'b'*
> name - from the index
> description - not from the index
> 
> Without filter on description (only on name) Phoenix uses index, as expected
> for local index. But with additional filter Phoenix decides to do a full
> scan. And my question is: Is there any way to make Phoenix use index in such
> types of queries, without include all fields in index ?
> 
> Hint does not help:
> SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
> description= 'b'
> ColumnNotFoundException: ERROR 504 (42703): Undefined column.
> columnName=DESCRIPTION.
> 
> Thanks,
> Alexey.
> 
> 
> 
> --
> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/

Re: Secondary index question

[Alexey Karpov]

Thanks for quick answer, but my case is a slightly different. I've seen these
links and already use local index. All cases, described in faq, index_usage
and any other, I've found in this user list, are about SELECT clause. In
WHERE clause there is always field from the index.

In my case in WHERE clause I have one field from the index and one not from
the index, combined with AND operator:
SELECT * from test WHERE name = 'a' *AND description = 'b'*
name - from the index
description - not from the index

Without filter on description (only on name) Phoenix uses index, as expected
for local index. But with additional filter Phoenix decides to do a full
scan. And my question is: Is there any way to make Phoenix use index in such
types of queries, without include all fields in index ?

Hint does not help:
SELECT /*+ INDEX(test ix_test_name) */ name FROM test WHERE name = 'a' AND
description= 'b'
ColumnNotFoundException: ERROR 504 (42703): Undefined column.
columnName=DESCRIPTION.

Thanks,
Alexey.



--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/

Re: Secondary index question

[Josh Elser]

Thanks for the pointer, James. I looked quickly but didn't find it :)

Let us know if that explains it, Alexey.

On 2/26/18 1:41 PM, James Taylor wrote:
See https://phoenix.apache.org/secondary_indexing.html#Index_Usage. We 
get this question a fair amount. We have an FAQ, here [1], but it's not 
a very complete answer (as it doesn't mention hinting or local indexes), 
so it'd be good if it was updated.


Thanks,
James

[1] 
https://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used


On Mon, Feb 26, 2018 at 7:43 AM, Josh Elser > wrote:


IIRC, Phoenix will only choose to use an index when all columns are
covered (either the index is on the columns or the columns are
explicitly configured to be covered in the DDL).



On 2/26/18 6:45 AM, Alexey Karpov wrote:

Hi.

Let’s say I have a table CREATE TABLE test (id integer NOT NULL
PRIMARY KEY,
name VARCHAR, description VARCHAR)
with local index CREATE LOCAL INDEX ix_test_name ON test(name)

For the query SELECT * FROM test WHERE name = 'a'
it’s all right, Phoenix uses index.

But for the query SELECT * FROM test WHERE name = 'a' AND
description = 'b'
It makes full scan over the table. Is there any way to make
Phoenix use
index in such queries(when one field is in index and another is
not),
without including another field into the index ?

I use HDP 2.6.2 and Phoenix 4.7.

Best regards,
Alexey



--
Sent from:
http://apache-phoenix-user-list.1124778.n5.nabble.com/

Re: Secondary index question

[James Taylor]

See https://phoenix.apache.org/secondary_indexing.html#Index_Usage. We get
this question a fair amount. We have an FAQ, here [1], but it's not a very
complete answer (as it doesn't mention hinting or local indexes), so it'd
be good if it was updated.

Thanks,
James

[1]
https://phoenix.apache.org/faq.html#Why_isnt_my_secondary_index_being_used

On Mon, Feb 26, 2018 at 7:43 AM, Josh Elser  wrote:

> IIRC, Phoenix will only choose to use an index when all columns are
> covered (either the index is on the columns or the columns are explicitly
> configured to be covered in the DDL).
>
>
>
> On 2/26/18 6:45 AM, Alexey Karpov wrote:
>
>> Hi.
>>
>> Let’s say I have a table CREATE TABLE test (id integer NOT NULL PRIMARY
>> KEY,
>> name VARCHAR, description VARCHAR)
>> with local index CREATE LOCAL INDEX ix_test_name ON test(name)
>>
>> For the query SELECT * FROM test WHERE name = 'a'
>> it’s all right, Phoenix uses index.
>>
>> But for the query SELECT * FROM test WHERE name = 'a' AND description =
>> 'b'
>> It makes full scan over the table. Is there any way to make Phoenix use
>> index in such queries(when one field is in index and another is not),
>> without including another field into the index ?
>>
>> I use HDP 2.6.2 and Phoenix 4.7.
>>
>> Best regards,
>> Alexey
>>
>>
>>
>> --
>> Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/
>>
>>

Re: Secondary index question

[Josh Elser]

IIRC, Phoenix will only choose to use an index when all columns are 
covered (either the index is on the columns or the columns are 
explicitly configured to be covered in the DDL).



On 2/26/18 6:45 AM, Alexey Karpov wrote:

Hi.

Let’s say I have a table CREATE TABLE test (id integer NOT NULL PRIMARY KEY,
name VARCHAR, description VARCHAR)
with local index CREATE LOCAL INDEX ix_test_name ON test(name)

For the query SELECT * FROM test WHERE name = 'a'
it’s all right, Phoenix uses index.

But for the query SELECT * FROM test WHERE name = 'a' AND description = 'b'
It makes full scan over the table. Is there any way to make Phoenix use
index in such queries(when one field is in index and another is not),
without including another field into the index ?

I use HDP 2.6.2 and Phoenix 4.7.

Best regards,
Alexey



--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/

Secondary index question

[Alexey Karpov]

Hi.

Let’s say I have a table CREATE TABLE test (id integer NOT NULL PRIMARY KEY,
name VARCHAR, description VARCHAR)
with local index CREATE LOCAL INDEX ix_test_name ON test(name)

For the query SELECT * FROM test WHERE name = 'a'
it’s all right, Phoenix uses index.

But for the query SELECT * FROM test WHERE name = 'a' AND description = 'b'
It makes full scan over the table. Is there any way to make Phoenix use
index in such queries(when one field is in index and another is not),
without including another field into the index ?

I use HDP 2.6.2 and Phoenix 4.7.

Best regards,
Alexey



--
Sent from: http://apache-phoenix-user-list.1124778.n5.nabble.com/

Re: Phoenix Secondary index question

[Jeremy Huffman]

Udit once they have been persisted Phoenix tables and indexes are just
normal rows in HBase tables. HBase backups and replication would work just
fine. It may be helpful to make a small table with index, upsert a few rows
and look at the raw entries with the HBase shell to understand what Phoenix
is doing for you behind the scenes.


As for Snapshot functionality, Phoenix has explicit support through
the CurrentSCN
property; usage is demonstrated in the FAQ.

http://phoenix.apache.org/faq.html#Can_phoenix_work_on_tables_with_arbitrary_timestamp_as_flexible_as_HBase_API


On Wed, Jan 4, 2017 at 2:47 PM Udit Mehta  wrote:

> Thanks James for the reply.
>
> Can you also tell me how to go about taking backups/snapshots in Phoenix
> similar to what Hbase has. I assume i cant use the snapshot feature Hbase
> offers for the same reason you cited above.
>
> Thanks again,
> Udit
>
> On Tue, Jan 3, 2017 at 5:26 PM, James Taylor 
> wrote:
>
> Hi Udit,
> You'll need to use Phoenix APIs when you update your data if you want
> Phoenix to keep you index in sync with your table.
> Thanks,
> James
>
> On Tue, Jan 3, 2017 at 1:32 PM, Udit Mehta  wrote:
>
> Hi All,
>
> I am facing an issue while working with secondary indexes on a Phoenix
> view/table that is created on top of an existing Hbase table.
>
> I basically have an existing Hbase table with a few rows and I then build
> a Phoenix view on this table. This works fine and any updates directly to
> the Hbase table are reflected in the Phoenix view. Now the problem arises
> the moment I create a secondary index on the phoenix table and then update
> the underlying Hbase table. I dont see the updates being reflected in
> either of the Phoenix tables ie the original one and the indexed one.
>
> Is this behavior or am I missing something?
>
> Also is it a good idea to have such a design where we update the Hbase
> table directly with a Phoenix table/view created on top of it? We had to
> choose this design due to the backup/snapshot features Hbase provides. I
> could not find any such mechanism in Phoenix so not sure what the best
> approach is here.
>
> Looking forward to your ideas.
>
> Thanks in advance,
> Udit
>
>
>
>
>
>
>
>
>

Re: Phoenix Secondary index question

[James Taylor]

Hi Udit,
You'll need to use Phoenix APIs when you update your data if you want
Phoenix to keep you index in sync with your table.
Thanks,
James

On Tue, Jan 3, 2017 at 1:32 PM, Udit Mehta  wrote:

> Hi All,
>
> I am facing an issue while working with secondary indexes on a Phoenix
> view/table that is created on top of an existing Hbase table.
>
> I basically have an existing Hbase table with a few rows and I then build
> a Phoenix view on this table. This works fine and any updates directly to
> the Hbase table are reflected in the Phoenix view. Now the problem arises
> the moment I create a secondary index on the phoenix table and then update
> the underlying Hbase table. I dont see the updates being reflected in
> either of the Phoenix tables ie the original one and the indexed one.
>
> Is this behavior or am I missing something?
>
> Also is it a good idea to have such a design where we update the Hbase
> table directly with a Phoenix table/view created on top of it? We had to
> choose this design due to the backup/snapshot features Hbase provides. I
> could not find any such mechanism in Phoenix so not sure what the best
> approach is here.
>
> Looking forward to your ideas.
>
> Thanks in advance,
> Udit
>

Phoenix Secondary index question

[Udit Mehta]

Hi All,

I am facing an issue while working with secondary indexes on a Phoenix
view/table that is created on top of an existing Hbase table.

I basically have an existing Hbase table with a few rows and I then build a
Phoenix view on this table. This works fine and any updates directly to the
Hbase table are reflected in the Phoenix view. Now the problem arises the
moment I create a secondary index on the phoenix table and then update the
underlying Hbase table. I dont see the updates being reflected in either of
the Phoenix tables ie the original one and the indexed one.

Is this behavior or am I missing something?

Also is it a good idea to have such a design where we update the Hbase
table directly with a Phoenix table/view created on top of it? We had to
choose this design due to the backup/snapshot features Hbase provides. I
could not find any such mechanism in Phoenix so not sure what the best
approach is here.

Looking forward to your ideas.

Thanks in advance,
Udit