You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Poonam Ligade <po...@gmail.com> on 2014/10/10 10:18:58 UTC
Phoenix/hbase part of composite rowkey retrieval
Hi,
I have phoenix table with below schema,
CREATE TABLE IF NOT EXISTS TEST (
ID BIGINT NOT NULL,
Date1 char(22) NOT NULL,
StID INTEGER NOT NULL,
....
..........
CONSTRAINT PK PRIMARY KEY (ID,Date1,StID)
);
I have dates in below format
01/02/2013 12:00:00 AM
02/07/2013 12:00:00 AM
03/25/2013 12:00:00 AM
4/12/2013 12:00:00 AM
5/16/2013 12:00:00 AM
I have over 200+milion rows of size 110GB in this table.
I frequently need to retrieve rows based on Date1.
To get data for January month, I am doing
select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
TO_DATE('02/01/2013','MM/dd/yyyy');
or
select * from TEST where substr(Date1,0,2)='01';
or to get week range
select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
TO_DATE('03/18/2013','MM/dd/yyyy') and TO_DATE(Date1,'MM/dd/yyyy') >
TO_DATE('03/10/2013','MM/dd/yyyy')
But these queries are slow,
Can you suggest more performant queries, to fetch data based on date range
in phoenix.
I am using Hortonworks hadoop 2.1, hbase-0.98 and phoenix 4.1
Regards,
Poonam.
Re: Phoenix/hbase part of composite rowkey retrieval
Posted by Nick Dimiduk <nd...@gmail.com>.
Hi Poonam,
Date/time format parsing is a cesspool. Might I recommend you frontline the
Phoenix ingest with a data cleansing step that normalizes all the dates?
This is probably easier done in Java/Scala/Clojure/Perl/Python/Ruby/C# than
SQL.
-n
---
Thanks,
Nick
n10k.com
hbaseinaction.com
On Fri, Oct 10, 2014 at 9:17 AM, Poonam Ligade <po...@gmail.com>
wrote:
> Hi Krishna,
>
> Ya definitely. We tried that initially a lot. But while storing data in
> Date type we were getting lot of formatting exceptions.
>
> ERROR util.CSVCommonsLoader: Error upserting record [5/20/2013 12:00:00
> AM]: java.text.ParseException: Unparseable date: "5/20/2013 12:00:00 AM"
>
> so we had to choose fixed size Char.
> and we cant revert it now.
>
> Regards,
> Poonam.
>
> On Fri, Oct 10, 2014 at 9:24 PM, Krishna <re...@gmail.com> wrote:
>
>> Hi Poonam, You should consider storing date fields in DATE/TIME
>> datatypes instead of Char(22).
>>
>>
>> On Friday, October 10, 2014, Poonam Ligade <po...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> I have phoenix table with below schema,
>>> CREATE TABLE IF NOT EXISTS TEST (
>>> ID BIGINT NOT NULL,
>>> Date1 char(22) NOT NULL,
>>> StID INTEGER NOT NULL,
>>> ....
>>> ..........
>>> CONSTRAINT PK PRIMARY KEY (ID,Date1,StID)
>>> );
>>>
>>> I have dates in below format
>>> 01/02/2013 12:00:00 AM
>>> 02/07/2013 12:00:00 AM
>>> 03/25/2013 12:00:00 AM
>>> 4/12/2013 12:00:00 AM
>>> 5/16/2013 12:00:00 AM
>>>
>>> I have over 200+milion rows of size 110GB in this table.
>>>
>>> I frequently need to retrieve rows based on Date1.
>>> To get data for January month, I am doing
>>> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
>>> TO_DATE('02/01/2013','MM/dd/yyyy');
>>> or
>>> select * from TEST where substr(Date1,0,2)='01';
>>>
>>> or to get week range
>>> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
>>> TO_DATE('03/18/2013','MM/dd/yyyy') and TO_DATE(Date1,'MM/dd/yyyy') >
>>> TO_DATE('03/10/2013','MM/dd/yyyy')
>>>
>>> But these queries are slow,
>>> Can you suggest more performant queries, to fetch data based on date
>>> range in phoenix.
>>>
>>> I am using Hortonworks hadoop 2.1, hbase-0.98 and phoenix 4.1
>>>
>>> Regards,
>>> Poonam.
>>>
>>
>
Re: Phoenix/hbase part of composite rowkey retrieval
Posted by James Taylor <ja...@apache.org>.
Krishna is correct. You'll need to revisit that decision if you want
good performance. You can specify your own parse format string for
your dates.
Maybe you can create a new Phoenix table with Date/Time types and do
an UPSERT SELECT from the old table to the new table?
Regards,
James
On Fri, Oct 10, 2014 at 9:17 AM, Poonam Ligade
<po...@gmail.com> wrote:
> Hi Krishna,
>
> Ya definitely. We tried that initially a lot. But while storing data in Date
> type we were getting lot of formatting exceptions.
>
> ERROR util.CSVCommonsLoader: Error upserting record [5/20/2013 12:00:00 AM]:
> java.text.ParseException: Unparseable date: "5/20/2013 12:00:00 AM"
>
> so we had to choose fixed size Char.
> and we cant revert it now.
>
> Regards,
> Poonam.
>
> On Fri, Oct 10, 2014 at 9:24 PM, Krishna <re...@gmail.com> wrote:
>>
>> Hi Poonam, You should consider storing date fields in DATE/TIME datatypes
>> instead of Char(22).
>>
>>
>> On Friday, October 10, 2014, Poonam Ligade <po...@gmail.com>
>> wrote:
>>>
>>> Hi,
>>>
>>> I have phoenix table with below schema,
>>> CREATE TABLE IF NOT EXISTS TEST (
>>> ID BIGINT NOT NULL,
>>> Date1 char(22) NOT NULL,
>>> StID INTEGER NOT NULL,
>>> ....
>>> ..........
>>> CONSTRAINT PK PRIMARY KEY (ID,Date1,StID)
>>> );
>>>
>>> I have dates in below format
>>> 01/02/2013 12:00:00 AM
>>> 02/07/2013 12:00:00 AM
>>> 03/25/2013 12:00:00 AM
>>> 4/12/2013 12:00:00 AM
>>> 5/16/2013 12:00:00 AM
>>>
>>> I have over 200+milion rows of size 110GB in this table.
>>>
>>> I frequently need to retrieve rows based on Date1.
>>> To get data for January month, I am doing
>>> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
>>> TO_DATE('02/01/2013','MM/dd/yyyy');
>>> or
>>> select * from TEST where substr(Date1,0,2)='01';
>>>
>>> or to get week range
>>> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
>>> TO_DATE('03/18/2013','MM/dd/yyyy') and TO_DATE(Date1,'MM/dd/yyyy') >
>>> TO_DATE('03/10/2013','MM/dd/yyyy')
>>>
>>> But these queries are slow,
>>> Can you suggest more performant queries, to fetch data based on date
>>> range in phoenix.
>>>
>>> I am using Hortonworks hadoop 2.1, hbase-0.98 and phoenix 4.1
>>>
>>> Regards,
>>> Poonam.
>
>
Re: Phoenix/hbase part of composite rowkey retrieval
Posted by Poonam Ligade <po...@gmail.com>.
Hi Krishna,
Ya definitely. We tried that initially a lot. But while storing data in
Date type we were getting lot of formatting exceptions.
ERROR util.CSVCommonsLoader: Error upserting record [5/20/2013 12:00:00
AM]: java.text.ParseException: Unparseable date: "5/20/2013 12:00:00 AM"
so we had to choose fixed size Char.
and we cant revert it now.
Regards,
Poonam.
On Fri, Oct 10, 2014 at 9:24 PM, Krishna <re...@gmail.com> wrote:
> Hi Poonam, You should consider storing date fields in DATE/TIME datatypes
> instead of Char(22).
>
>
> On Friday, October 10, 2014, Poonam Ligade <po...@gmail.com>
> wrote:
>
>> Hi,
>>
>> I have phoenix table with below schema,
>> CREATE TABLE IF NOT EXISTS TEST (
>> ID BIGINT NOT NULL,
>> Date1 char(22) NOT NULL,
>> StID INTEGER NOT NULL,
>> ....
>> ..........
>> CONSTRAINT PK PRIMARY KEY (ID,Date1,StID)
>> );
>>
>> I have dates in below format
>> 01/02/2013 12:00:00 AM
>> 02/07/2013 12:00:00 AM
>> 03/25/2013 12:00:00 AM
>> 4/12/2013 12:00:00 AM
>> 5/16/2013 12:00:00 AM
>>
>> I have over 200+milion rows of size 110GB in this table.
>>
>> I frequently need to retrieve rows based on Date1.
>> To get data for January month, I am doing
>> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
>> TO_DATE('02/01/2013','MM/dd/yyyy');
>> or
>> select * from TEST where substr(Date1,0,2)='01';
>>
>> or to get week range
>> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
>> TO_DATE('03/18/2013','MM/dd/yyyy') and TO_DATE(Date1,'MM/dd/yyyy') >
>> TO_DATE('03/10/2013','MM/dd/yyyy')
>>
>> But these queries are slow,
>> Can you suggest more performant queries, to fetch data based on date
>> range in phoenix.
>>
>> I am using Hortonworks hadoop 2.1, hbase-0.98 and phoenix 4.1
>>
>> Regards,
>> Poonam.
>>
>
Re: Phoenix/hbase part of composite rowkey retrieval
Posted by Krishna <re...@gmail.com>.
Hi Poonam, You should consider storing date fields in DATE/TIME datatypes
instead of Char(22).
On Friday, October 10, 2014, Poonam Ligade <po...@gmail.com>
wrote:
> Hi,
>
> I have phoenix table with below schema,
> CREATE TABLE IF NOT EXISTS TEST (
> ID BIGINT NOT NULL,
> Date1 char(22) NOT NULL,
> StID INTEGER NOT NULL,
> ....
> ..........
> CONSTRAINT PK PRIMARY KEY (ID,Date1,StID)
> );
>
> I have dates in below format
> 01/02/2013 12:00:00 AM
> 02/07/2013 12:00:00 AM
> 03/25/2013 12:00:00 AM
> 4/12/2013 12:00:00 AM
> 5/16/2013 12:00:00 AM
>
> I have over 200+milion rows of size 110GB in this table.
>
> I frequently need to retrieve rows based on Date1.
> To get data for January month, I am doing
> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
> TO_DATE('02/01/2013','MM/dd/yyyy');
> or
> select * from TEST where substr(Date1,0,2)='01';
>
> or to get week range
> select * from TEST where TO_DATE(Date1,'MM/dd/yyyy') <
> TO_DATE('03/18/2013','MM/dd/yyyy') and TO_DATE(Date1,'MM/dd/yyyy') >
> TO_DATE('03/10/2013','MM/dd/yyyy')
>
> But these queries are slow,
> Can you suggest more performant queries, to fetch data based on date range
> in phoenix.
>
> I am using Hortonworks hadoop 2.1, hbase-0.98 and phoenix 4.1
>
> Regards,
> Poonam.
>