You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Kanagha <er...@gmail.com> on 2016/10/03 20:38:00 UTC
Re: Question regarding designing row keys
+ user@phoenix.apache.org
Kanagha
On Mon, Oct 3, 2016 at 1:31 PM, Kanagha <er...@gmail.com> wrote:
> Hi,
>
> We have designed an metric_table, for ex:
>
> metric_type,
> timestamp,
> metricId
>
> in HBase using Apache Phoenix. And there is no indexing available on this
> table yet.
>
> Our access patterns are usually
>
> select * from metric_table where metric_type='x' and timestamp >
> 'start_date' and timestamp < 'end_date'.
>
> MetricId is not used in select statement.
>
> So, how does HBase scanning work in this scenario. I read that each region
> would hold the start and end rowkey for efficient scanning.
> In this case, since the rowkey is combined with metricId, will the select
> statement do a full scan of HBase table?
>
> Appreciate any inputs on this.
>
> Thanks
> Kanagha
>
Re: Question regarding designing row keys
Posted by Kanagha <er...@gmail.com>.
Thanks for the responses. The table is created with salting enabled.
Kanagha
On Tue, Oct 4, 2016 at 2:39 PM, Ciureanu Constantin <
ciureanu.constantin@gmail.com> wrote:
> Sure, but he should create the Phoenix table already salted.
>
> În Mar, 4 oct. 2016, 17:21 Mujtaba Chohan, <mu...@apache.org> a scris:
>
>> If you lead with timestamp key, you might want to consider experimenting
>> with salting <http://phoenix.apache.org/salted.html> as writing data
>> would hotspot on single region if keys are monotonically increasing.
>>
>> On Tue, Oct 4, 2016 at 8:04 AM, Ciureanu Constantin <
>> ciureanu.constantin@gmail.com> wrote:
>>
>> select * from metric_table where metric_type='x'
>> -- so far so good
>>
>> and timestamp > 'start_date' and timestamp < 'end_date'.
>> -- here in case the timestamp is long (BIGINT in Phoenix) - it should
>> work fine!
>> Try also with "timestamp between (x and y)"
>>
>> Anyway - my proposal would be to reverse the key parts - have timestamp
>> first, then metric type, then other parts in the key.
>>
>> Using the timestamp it would define the start+stop of the scan range
>> (that's a must, step 1) - then it would filter locally the metric type with
>> Skips when it's not the searched value then some other parts of the key
>> with lower importance (if any of them are part of the where clause).
>>
>> Note: This new key proposal would solve your current use-case / but
>> wouldn't be perfect for potential new use-case - then you would need
>> indexes or duplicated data in other tables ...
>>
>> 2016-10-04 6:03 GMT+02:00 Krishna <re...@gmail.com>:
>>
>> You have two options:
>> - Modify your primary key to include metric_type & timestamp as leading
>> columns.
>> - Create an index on metric_type & timestamp
>>
>> On Monday, October 3, 2016, Kanagha <er...@gmail.com> wrote:
>>
>> Sorry for the confusion.
>>
>> metric_type,
>> timestamp,
>> metricId is defined as the primary key via Phoenix for metric_table.
>>
>> Thanks
>>
>> Kanagha
>>
>> On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister <
>> mmcallister@homeaway.com> wrote:
>>
>> >
>>
>> there is no indexing available on this table yet.
>>
>> >
>>
>>
>>
>> So you haven’t defined a primary key constraint? Can you share your table
>> creation DDL?
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
>> | webex: https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Kanagha <er...@gmail.com>
>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Date: *Monday, October 3, 2016 at 5:32 PM
>> *To: *"user@hbase.apache.org" <us...@hbase.apache.org>, "
>> user@phoenix.apache.org" <us...@phoenix.apache.org>
>> *Subject: *Re: Question regarding designing row keys
>>
>>
>>
>> there is no indexing available on this table yet.
>>
>>
>>
>>
>>
Re: Question regarding designing row keys
Posted by Ciureanu Constantin <ci...@gmail.com>.
Sure, but he should create the Phoenix table already salted.
În Mar, 4 oct. 2016, 17:21 Mujtaba Chohan, <mu...@apache.org> a scris:
> If you lead with timestamp key, you might want to consider experimenting
> with salting <http://phoenix.apache.org/salted.html> as writing data
> would hotspot on single region if keys are monotonically increasing.
>
> On Tue, Oct 4, 2016 at 8:04 AM, Ciureanu Constantin <
> ciureanu.constantin@gmail.com> wrote:
>
> select * from metric_table where metric_type='x'
> -- so far so good
>
> and timestamp > 'start_date' and timestamp < 'end_date'.
> -- here in case the timestamp is long (BIGINT in Phoenix) - it should work
> fine!
> Try also with "timestamp between (x and y)"
>
> Anyway - my proposal would be to reverse the key parts - have timestamp
> first, then metric type, then other parts in the key.
>
> Using the timestamp it would define the start+stop of the scan range
> (that's a must, step 1) - then it would filter locally the metric type with
> Skips when it's not the searched value then some other parts of the key
> with lower importance (if any of them are part of the where clause).
>
> Note: This new key proposal would solve your current use-case / but
> wouldn't be perfect for potential new use-case - then you would need
> indexes or duplicated data in other tables ...
>
> 2016-10-04 6:03 GMT+02:00 Krishna <re...@gmail.com>:
>
> You have two options:
> - Modify your primary key to include metric_type & timestamp as leading
> columns.
> - Create an index on metric_type & timestamp
>
> On Monday, October 3, 2016, Kanagha <er...@gmail.com> wrote:
>
> Sorry for the confusion.
>
> metric_type,
> timestamp,
> metricId is defined as the primary key via Phoenix for metric_table.
>
> Thanks
>
> Kanagha
>
> On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister <
> mmcallister@homeaway.com> wrote:
>
> >
>
> there is no indexing available on this table yet.
>
> >
>
>
>
> So you haven’t defined a primary key constraint? Can you share your table
> creation DDL?
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha |
> webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Kanagha <er...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Monday, October 3, 2016 at 5:32 PM
> *To: *"user@hbase.apache.org" <us...@hbase.apache.org>, "
> user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Question regarding designing row keys
>
>
>
> there is no indexing available on this table yet.
>
>
>
>
>
Re: Question regarding designing row keys
Posted by Mujtaba Chohan <mu...@apache.org>.
If you lead with timestamp key, you might want to consider experimenting
with salting <http://phoenix.apache.org/salted.html> as writing data would
hotspot on single region if keys are monotonically increasing.
On Tue, Oct 4, 2016 at 8:04 AM, Ciureanu Constantin <
ciureanu.constantin@gmail.com> wrote:
> select * from metric_table where metric_type='x'
> -- so far so good
>
> and timestamp > 'start_date' and timestamp < 'end_date'.
> -- here in case the timestamp is long (BIGINT in Phoenix) - it should work
> fine!
> Try also with "timestamp between (x and y)"
>
> Anyway - my proposal would be to reverse the key parts - have timestamp
> first, then metric type, then other parts in the key.
>
> Using the timestamp it would define the start+stop of the scan range
> (that's a must, step 1) - then it would filter locally the metric type with
> Skips when it's not the searched value then some other parts of the key
> with lower importance (if any of them are part of the where clause).
>
> Note: This new key proposal would solve your current use-case / but
> wouldn't be perfect for potential new use-case - then you would need
> indexes or duplicated data in other tables ...
>
> 2016-10-04 6:03 GMT+02:00 Krishna <re...@gmail.com>:
>
>> You have two options:
>> - Modify your primary key to include metric_type & timestamp as leading
>> columns.
>> - Create an index on metric_type & timestamp
>>
>> On Monday, October 3, 2016, Kanagha <er...@gmail.com> wrote:
>>
>>> Sorry for the confusion.
>>>
>>> metric_type,
>>> timestamp,
>>> metricId is defined as the primary key via Phoenix for metric_table.
>>>
>>> Thanks
>>>
>>> Kanagha
>>>
>>> On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister <
>>> mmcallister@homeaway.com> wrote:
>>>
>>>> >
>>>>
>>>> there is no indexing available on this table yet.
>>>>
>>>> >
>>>>
>>>>
>>>>
>>>> So you haven’t defined a primary key constraint? Can you share your
>>>> table creation DDL?
>>>>
>>>>
>>>>
>>>> Michael McAllister
>>>>
>>>> Staff Data Warehouse Engineer | Decision Systems
>>>>
>>>> mmcallister@HomeAway.com | C: 512.423.7447 | skype:
>>>> michael.mcallister.ha | webex: https://h.a/mikewebex
>>>>
>>>> This electronic communication (including any attachment) is
>>>> confidential. If you are not an intended recipient of this communication,
>>>> please be advised that any disclosure, dissemination, distribution, copying
>>>> or other use of this communication or any attachment is strictly
>>>> prohibited. If you have received this communication in error, please
>>>> notify the sender immediately by reply e-mail and promptly destroy all
>>>> electronic and printed copies of this communication and any attachment.
>>>>
>>>>
>>>>
>>>> *From: *Kanagha <er...@gmail.com>
>>>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>>>> *Date: *Monday, October 3, 2016 at 5:32 PM
>>>> *To: *"user@hbase.apache.org" <us...@hbase.apache.org>, "
>>>> user@phoenix.apache.org" <us...@phoenix.apache.org>
>>>> *Subject: *Re: Question regarding designing row keys
>>>>
>>>>
>>>>
>>>> there is no indexing available on this table yet.
>>>>
>>>
>>>
>
Re: Question regarding designing row keys
Posted by Ciureanu Constantin <ci...@gmail.com>.
select * from metric_table where metric_type='x'
-- so far so good
and timestamp > 'start_date' and timestamp < 'end_date'.
-- here in case the timestamp is long (BIGINT in Phoenix) - it should work
fine!
Try also with "timestamp between (x and y)"
Anyway - my proposal would be to reverse the key parts - have timestamp
first, then metric type, then other parts in the key.
Using the timestamp it would define the start+stop of the scan range
(that's a must, step 1) - then it would filter locally the metric type with
Skips when it's not the searched value then some other parts of the key
with lower importance (if any of them are part of the where clause).
Note: This new key proposal would solve your current use-case / but
wouldn't be perfect for potential new use-case - then you would need
indexes or duplicated data in other tables ...
2016-10-04 6:03 GMT+02:00 Krishna <re...@gmail.com>:
> You have two options:
> - Modify your primary key to include metric_type & timestamp as leading
> columns.
> - Create an index on metric_type & timestamp
>
> On Monday, October 3, 2016, Kanagha <er...@gmail.com> wrote:
>
>> Sorry for the confusion.
>>
>> metric_type,
>> timestamp,
>> metricId is defined as the primary key via Phoenix for metric_table.
>>
>> Thanks
>>
>> Kanagha
>>
>> On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister <
>> mmcallister@homeaway.com> wrote:
>>
>>> >
>>>
>>> there is no indexing available on this table yet.
>>>
>>> >
>>>
>>>
>>>
>>> So you haven’t defined a primary key constraint? Can you share your
>>> table creation DDL?
>>>
>>>
>>>
>>> Michael McAllister
>>>
>>> Staff Data Warehouse Engineer | Decision Systems
>>>
>>> mmcallister@HomeAway.com | C: 512.423.7447 | skype:
>>> michael.mcallister.ha | webex: https://h.a/mikewebex
>>>
>>> This electronic communication (including any attachment) is
>>> confidential. If you are not an intended recipient of this communication,
>>> please be advised that any disclosure, dissemination, distribution, copying
>>> or other use of this communication or any attachment is strictly
>>> prohibited. If you have received this communication in error, please
>>> notify the sender immediately by reply e-mail and promptly destroy all
>>> electronic and printed copies of this communication and any attachment.
>>>
>>>
>>>
>>> *From: *Kanagha <er...@gmail.com>
>>> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
>>> *Date: *Monday, October 3, 2016 at 5:32 PM
>>> *To: *"user@hbase.apache.org" <us...@hbase.apache.org>, "
>>> user@phoenix.apache.org" <us...@phoenix.apache.org>
>>> *Subject: *Re: Question regarding designing row keys
>>>
>>>
>>>
>>> there is no indexing available on this table yet.
>>>
>>
>>
Re: Question regarding designing row keys
Posted by Krishna <re...@gmail.com>.
You have two options:
- Modify your primary key to include metric_type & timestamp as leading
columns.
- Create an index on metric_type & timestamp
On Monday, October 3, 2016, Kanagha <er...@gmail.com> wrote:
> Sorry for the confusion.
>
> metric_type,
> timestamp,
> metricId is defined as the primary key via Phoenix for metric_table.
>
> Thanks
>
> Kanagha
>
> On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister <
> mmcallister@homeaway.com
> <javascript:_e(%7B%7D,'cvml','mmcallister@homeaway.com');>> wrote:
>
>> >
>>
>> there is no indexing available on this table yet.
>>
>> >
>>
>>
>>
>> So you haven’t defined a primary key constraint? Can you share your table
>> creation DDL?
>>
>>
>>
>> Michael McAllister
>>
>> Staff Data Warehouse Engineer | Decision Systems
>>
>> mmcallister@HomeAway.com
>> <javascript:_e(%7B%7D,'cvml','mmcallister@HomeAway.com');> | C:
>> 512.423.7447 | skype: michael.mcallister.ha
>> <javascript:_e(%7B%7D,'cvml','zimmkate@hotmail.com');> | webex:
>> https://h.a/mikewebex
>>
>> This electronic communication (including any attachment) is
>> confidential. If you are not an intended recipient of this communication,
>> please be advised that any disclosure, dissemination, distribution, copying
>> or other use of this communication or any attachment is strictly
>> prohibited. If you have received this communication in error, please
>> notify the sender immediately by reply e-mail and promptly destroy all
>> electronic and printed copies of this communication and any attachment.
>>
>>
>>
>> *From: *Kanagha <er.kanagha@gmail.com
>> <javascript:_e(%7B%7D,'cvml','er.kanagha@gmail.com');>>
>> *Reply-To: *"user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>" <
>> user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>>
>> *Date: *Monday, October 3, 2016 at 5:32 PM
>> *To: *"user@hbase.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@hbase.apache.org');>" <
>> user@hbase.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@hbase.apache.org');>>, "
>> user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>" <
>> user@phoenix.apache.org
>> <javascript:_e(%7B%7D,'cvml','user@phoenix.apache.org');>>
>> *Subject: *Re: Question regarding designing row keys
>>
>>
>>
>> there is no indexing available on this table yet.
>>
>
>
Re: Question regarding designing row keys
Posted by Kanagha <er...@gmail.com>.
Sorry for the confusion.
metric_type,
timestamp,
metricId is defined as the primary key via Phoenix for metric_table.
Thanks
Kanagha
On Mon, Oct 3, 2016 at 3:41 PM, Michael McAllister <mmcallister@homeaway.com
> wrote:
> >
>
> there is no indexing available on this table yet.
>
> >
>
>
>
> So you haven’t defined a primary key constraint? Can you share your table
> creation DDL?
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>
>
>
> *From: *Kanagha <er...@gmail.com>
> *Reply-To: *"user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Date: *Monday, October 3, 2016 at 5:32 PM
> *To: *"user@hbase.apache.org" <us...@hbase.apache.org>, "
> user@phoenix.apache.org" <us...@phoenix.apache.org>
> *Subject: *Re: Question regarding designing row keys
>
>
>
> there is no indexing available on this table yet.
>
Re: Question regarding designing row keys
Posted by Michael McAllister <mm...@homeaway.com>.
>
there is no indexing available on this table yet.
>
So you haven’t defined a primary key constraint? Can you share your table creation DDL?
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D21D9D.4D94BCF0]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: Kanagha <er...@gmail.com>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Monday, October 3, 2016 at 5:32 PM
To: "user@hbase.apache.org" <us...@hbase.apache.org>, "user@phoenix.apache.org" <us...@phoenix.apache.org>
Subject: Re: Question regarding designing row keys
there is no indexing available on this table yet.
Re: Question regarding designing row keys
Posted by Kanagha <er...@gmail.com>.
I did an explain plan in Phoenix and it returned as
"Parallel 1-way round robin full scan over metric_table". A full scan is
done.
Thanks
Kanagha
On Mon, Oct 3, 2016 at 1:38 PM, Kanagha <er...@gmail.com> wrote:
> + user@phoenix.apache.org
>
> Kanagha
>
> On Mon, Oct 3, 2016 at 1:31 PM, Kanagha <er...@gmail.com> wrote:
>
>> Hi,
>>
>> We have designed an metric_table, for ex:
>>
>> metric_type,
>> timestamp,
>> metricId
>>
>> in HBase using Apache Phoenix. And there is no indexing available on this
>> table yet.
>>
>> Our access patterns are usually
>>
>> select * from metric_table where metric_type='x' and timestamp >
>> 'start_date' and timestamp < 'end_date'.
>>
>> MetricId is not used in select statement.
>>
>> So, how does HBase scanning work in this scenario. I read that each
>> region would hold the start and end rowkey for efficient scanning.
>> In this case, since the rowkey is combined with metricId, will the select
>> statement do a full scan of HBase table?
>>
>> Appreciate any inputs on this.
>>
>> Thanks
>> Kanagha
>>
>
>
Re: Question regarding designing row keys
Posted by Kanagha <er...@gmail.com>.
I did an explain plan in Phoenix and it returned as
"Parallel 1-way round robin full scan over metric_table". A full scan is
done.
Thanks
Kanagha
On Mon, Oct 3, 2016 at 1:38 PM, Kanagha <er...@gmail.com> wrote:
> + user@phoenix.apache.org
>
> Kanagha
>
> On Mon, Oct 3, 2016 at 1:31 PM, Kanagha <er...@gmail.com> wrote:
>
>> Hi,
>>
>> We have designed an metric_table, for ex:
>>
>> metric_type,
>> timestamp,
>> metricId
>>
>> in HBase using Apache Phoenix. And there is no indexing available on this
>> table yet.
>>
>> Our access patterns are usually
>>
>> select * from metric_table where metric_type='x' and timestamp >
>> 'start_date' and timestamp < 'end_date'.
>>
>> MetricId is not used in select statement.
>>
>> So, how does HBase scanning work in this scenario. I read that each
>> region would hold the start and end rowkey for efficient scanning.
>> In this case, since the rowkey is combined with metricId, will the select
>> statement do a full scan of HBase table?
>>
>> Appreciate any inputs on this.
>>
>> Thanks
>> Kanagha
>>
>
>