You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Vaghawan Ojha <va...@gmail.com> on 2018/02/13 10:42:47 UTC
Creating View Table Using the Date & Time
Hi,
I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions of
rows, but I don't need much of the old data, Let's say the frequent data I
need is data from 2 month back.
the query become slow when I read the table using timestamp. So query would
be like where date>some date and <some date.
I was thinking of creating a veiw table where I could put the latest two
months data, but the data there should consist only the latest two months.
The parent table is updated daily with the new data, so in my case whenever
new day's data comes in, the last one day's data should be removed from the
view, making sure that the view consists two month's data. (e.g it would
always hold last 60 days data)
I don't know if that is possible using create view. If it is how can I do
it?
Any suggestion would be appreciated.
Thanks
Vaghawan
Re: Creating View Table Using the Date & Time
Posted by Vaghawan Ojha <va...@gmail.com>.
Ok, Thank you very much for the help.
On Wed, Feb 14, 2018 at 12:43 PM, James Taylor <ja...@apache.org>
wrote:
> No, you’ll need to create a Phoenix table and use Phoenix APIs to write
> your data.
>
> On Tue, Feb 13, 2018 at 9:52 PM Vaghawan Ojha <va...@gmail.com>
> wrote:
>
>> Thank you James, my keys are something like this: 2018-02-01-BM50558-
>> 1517454912.0-5-1517548497.261604 . the first few chars are the date. and
>> these dates are stored in a seperate columns as BDATE as well. Do you think
>> I could implement the rowtimestamp in the BDATE column?
>>
>> Thanks
>> Vaghawan
>>
>> On Wed, Feb 14, 2018 at 7:47 AM, James Taylor <ja...@apache.org>
>> wrote:
>>
>>> If the date time column is part of your pk, then you’d be able to use
>>> the ROW_TIMESTAMP feature.
>>>
>>> On Tue, Feb 13, 2018 at 5:04 PM Vaghawan Ojha <va...@gmail.com>
>>> wrote:
>>>
>>>> Yes, the datetime column is part of my primary key, but primary key
>>>> also consists other strings.
>>>>
>>>> Thanks
>>>> Vaghawan
>>>>
>>>> On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <ja...@apache.org>
>>>> wrote:
>>>>
>>>>> The standard way of doing this is to add a TTL for your table [1]. You
>>>>> can do this through the ALTER TABLE call [2]. Is the date/time column part
>>>>> of your primary key? If so, you can improve performance by declaring this
>>>>> column as a ROW_TIMESTAMP [3].
>>>>>
>>>>> A view is not going to help you - it's not materialized.
>>>>>
>>>>> Thanks,
>>>>> James
>>>>>
>>>>> [1] http://hbase.apache.org/0.94/book/ttl.html
>>>>> [2] https://phoenix.apache.org/language/index.html#alter
>>>>> [3] https://phoenix.apache.org/rowtimestamp.html
>>>>>
>>>>> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <va...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few
>>>>>> millions of rows, but I don't need much of the old data, Let's say the
>>>>>> frequent data I need is data from 2 month back.
>>>>>>
>>>>>> the query become slow when I read the table using timestamp. So query
>>>>>> would be like where date>some date and <some date.
>>>>>>
>>>>>> I was thinking of creating a veiw table where I could put the latest
>>>>>> two months data, but the data there should consist only the latest two
>>>>>> months. The parent table is updated daily with the new data, so in my case
>>>>>> whenever new day's data comes in, the last one day's data should be removed
>>>>>> from the view, making sure that the view consists two month's data. (e.g it
>>>>>> would always hold last 60 days data)
>>>>>> I don't know if that is possible using create view. If it is how can
>>>>>> I do it?
>>>>>>
>>>>>> Any suggestion would be appreciated.
>>>>>>
>>>>>> Thanks
>>>>>> Vaghawan
>>>>>>
>>>>>
>>>>>
>>>>
>>
Re: Creating View Table Using the Date & Time
Posted by James Taylor <ja...@apache.org>.
No, you’ll need to create a Phoenix table and use Phoenix APIs to write
your data.
On Tue, Feb 13, 2018 at 9:52 PM Vaghawan Ojha <va...@gmail.com> wrote:
> Thank you James, my keys are something like
> this: 2018-02-01-BM50558-1517454912.0-5-1517548497.261604 . the first few
> chars are the date. and these dates are stored in a seperate columns as
> BDATE as well. Do you think I could implement the rowtimestamp in the BDATE
> column?
>
> Thanks
> Vaghawan
>
> On Wed, Feb 14, 2018 at 7:47 AM, James Taylor <ja...@apache.org>
> wrote:
>
>> If the date time column is part of your pk, then you’d be able to use the
>> ROW_TIMESTAMP feature.
>>
>> On Tue, Feb 13, 2018 at 5:04 PM Vaghawan Ojha <va...@gmail.com>
>> wrote:
>>
>>> Yes, the datetime column is part of my primary key, but primary key also
>>> consists other strings.
>>>
>>> Thanks
>>> Vaghawan
>>>
>>> On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <ja...@apache.org>
>>> wrote:
>>>
>>>> The standard way of doing this is to add a TTL for your table [1]. You
>>>> can do this through the ALTER TABLE call [2]. Is the date/time column part
>>>> of your primary key? If so, you can improve performance by declaring this
>>>> column as a ROW_TIMESTAMP [3].
>>>>
>>>> A view is not going to help you - it's not materialized.
>>>>
>>>> Thanks,
>>>> James
>>>>
>>>> [1] http://hbase.apache.org/0.94/book/ttl.html
>>>> [2] https://phoenix.apache.org/language/index.html#alter
>>>> [3] https://phoenix.apache.org/rowtimestamp.html
>>>>
>>>> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <va...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few
>>>>> millions of rows, but I don't need much of the old data, Let's say the
>>>>> frequent data I need is data from 2 month back.
>>>>>
>>>>> the query become slow when I read the table using timestamp. So query
>>>>> would be like where date>some date and <some date.
>>>>>
>>>>> I was thinking of creating a veiw table where I could put the latest
>>>>> two months data, but the data there should consist only the latest two
>>>>> months. The parent table is updated daily with the new data, so in my case
>>>>> whenever new day's data comes in, the last one day's data should be removed
>>>>> from the view, making sure that the view consists two month's data. (e.g it
>>>>> would always hold last 60 days data)
>>>>> I don't know if that is possible using create view. If it is how can I
>>>>> do it?
>>>>>
>>>>> Any suggestion would be appreciated.
>>>>>
>>>>> Thanks
>>>>> Vaghawan
>>>>>
>>>>
>>>>
>>>
>
Re: Creating View Table Using the Date & Time
Posted by Vaghawan Ojha <va...@gmail.com>.
Thank you James, my keys are something like
this: 2018-02-01-BM50558-1517454912.0-5-1517548497.261604 . the first few
chars are the date. and these dates are stored in a seperate columns as
BDATE as well. Do you think I could implement the rowtimestamp in the BDATE
column?
Thanks
Vaghawan
On Wed, Feb 14, 2018 at 7:47 AM, James Taylor <ja...@apache.org>
wrote:
> If the date time column is part of your pk, then you’d be able to use the
> ROW_TIMESTAMP feature.
>
> On Tue, Feb 13, 2018 at 5:04 PM Vaghawan Ojha <va...@gmail.com>
> wrote:
>
>> Yes, the datetime column is part of my primary key, but primary key also
>> consists other strings.
>>
>> Thanks
>> Vaghawan
>>
>> On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <ja...@apache.org>
>> wrote:
>>
>>> The standard way of doing this is to add a TTL for your table [1]. You
>>> can do this through the ALTER TABLE call [2]. Is the date/time column part
>>> of your primary key? If so, you can improve performance by declaring this
>>> column as a ROW_TIMESTAMP [3].
>>>
>>> A view is not going to help you - it's not materialized.
>>>
>>> Thanks,
>>> James
>>>
>>> [1] http://hbase.apache.org/0.94/book/ttl.html
>>> [2] https://phoenix.apache.org/language/index.html#alter
>>> [3] https://phoenix.apache.org/rowtimestamp.html
>>>
>>> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <va...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions
>>>> of rows, but I don't need much of the old data, Let's say the frequent data
>>>> I need is data from 2 month back.
>>>>
>>>> the query become slow when I read the table using timestamp. So query
>>>> would be like where date>some date and <some date.
>>>>
>>>> I was thinking of creating a veiw table where I could put the latest
>>>> two months data, but the data there should consist only the latest two
>>>> months. The parent table is updated daily with the new data, so in my case
>>>> whenever new day's data comes in, the last one day's data should be removed
>>>> from the view, making sure that the view consists two month's data. (e.g it
>>>> would always hold last 60 days data)
>>>> I don't know if that is possible using create view. If it is how can I
>>>> do it?
>>>>
>>>> Any suggestion would be appreciated.
>>>>
>>>> Thanks
>>>> Vaghawan
>>>>
>>>
>>>
>>
Re: Creating View Table Using the Date & Time
Posted by James Taylor <ja...@apache.org>.
If the date time column is part of your pk, then you’d be able to use the
ROW_TIMESTAMP feature.
On Tue, Feb 13, 2018 at 5:04 PM Vaghawan Ojha <va...@gmail.com> wrote:
> Yes, the datetime column is part of my primary key, but primary key also
> consists other strings.
>
> Thanks
> Vaghawan
>
> On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <ja...@apache.org>
> wrote:
>
>> The standard way of doing this is to add a TTL for your table [1]. You
>> can do this through the ALTER TABLE call [2]. Is the date/time column part
>> of your primary key? If so, you can improve performance by declaring this
>> column as a ROW_TIMESTAMP [3].
>>
>> A view is not going to help you - it's not materialized.
>>
>> Thanks,
>> James
>>
>> [1] http://hbase.apache.org/0.94/book/ttl.html
>> [2] https://phoenix.apache.org/language/index.html#alter
>> [3] https://phoenix.apache.org/rowtimestamp.html
>>
>> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <va...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions
>>> of rows, but I don't need much of the old data, Let's say the frequent data
>>> I need is data from 2 month back.
>>>
>>> the query become slow when I read the table using timestamp. So query
>>> would be like where date>some date and <some date.
>>>
>>> I was thinking of creating a veiw table where I could put the latest two
>>> months data, but the data there should consist only the latest two months.
>>> The parent table is updated daily with the new data, so in my case whenever
>>> new day's data comes in, the last one day's data should be removed from the
>>> view, making sure that the view consists two month's data. (e.g it would
>>> always hold last 60 days data)
>>> I don't know if that is possible using create view. If it is how can I
>>> do it?
>>>
>>> Any suggestion would be appreciated.
>>>
>>> Thanks
>>> Vaghawan
>>>
>>
>>
>
Re: Creating View Table Using the Date & Time
Posted by Vaghawan Ojha <va...@gmail.com>.
Yes, the datetime column is part of my primary key, but primary key also
consists other strings.
Thanks
Vaghawan
On Tue, Feb 13, 2018 at 11:05 PM, James Taylor <ja...@apache.org>
wrote:
> The standard way of doing this is to add a TTL for your table [1]. You can
> do this through the ALTER TABLE call [2]. Is the date/time column part of
> your primary key? If so, you can improve performance by declaring this
> column as a ROW_TIMESTAMP [3].
>
> A view is not going to help you - it's not materialized.
>
> Thanks,
> James
>
> [1] http://hbase.apache.org/0.94/book/ttl.html
> [2] https://phoenix.apache.org/language/index.html#alter
> [3] https://phoenix.apache.org/rowtimestamp.html
>
> On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <va...@gmail.com>
> wrote:
>
>> Hi,
>>
>> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions
>> of rows, but I don't need much of the old data, Let's say the frequent data
>> I need is data from 2 month back.
>>
>> the query become slow when I read the table using timestamp. So query
>> would be like where date>some date and <some date.
>>
>> I was thinking of creating a veiw table where I could put the latest two
>> months data, but the data there should consist only the latest two months.
>> The parent table is updated daily with the new data, so in my case whenever
>> new day's data comes in, the last one day's data should be removed from the
>> view, making sure that the view consists two month's data. (e.g it would
>> always hold last 60 days data)
>> I don't know if that is possible using create view. If it is how can I do
>> it?
>>
>> Any suggestion would be appreciated.
>>
>> Thanks
>> Vaghawan
>>
>
>
Re: Creating View Table Using the Date & Time
Posted by James Taylor <ja...@apache.org>.
The standard way of doing this is to add a TTL for your table [1]. You can
do this through the ALTER TABLE call [2]. Is the date/time column part of
your primary key? If so, you can improve performance by declaring this
column as a ROW_TIMESTAMP [3].
A view is not going to help you - it's not materialized.
Thanks,
James
[1] http://hbase.apache.org/0.94/book/ttl.html
[2] https://phoenix.apache.org/language/index.html#alter
[3] https://phoenix.apache.org/rowtimestamp.html
On Tue, Feb 13, 2018 at 2:42 AM, Vaghawan Ojha <va...@gmail.com>
wrote:
> Hi,
>
> I'm using phoenix 4.12 with hbase 1.2.0, I've a table with few millions of
> rows, but I don't need much of the old data, Let's say the frequent data I
> need is data from 2 month back.
>
> the query become slow when I read the table using timestamp. So query
> would be like where date>some date and <some date.
>
> I was thinking of creating a veiw table where I could put the latest two
> months data, but the data there should consist only the latest two months.
> The parent table is updated daily with the new data, so in my case whenever
> new day's data comes in, the last one day's data should be removed from the
> view, making sure that the view consists two month's data. (e.g it would
> always hold last 60 days data)
> I don't know if that is possible using create view. If it is how can I do
> it?
>
> Any suggestion would be appreciated.
>
> Thanks
> Vaghawan
>