phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Poonam Ligade <poonam.v.lig...@gmail.com>
Subject Phoenix/hbase part of composite rowkey retrieval
Date Fri, 10 Oct 2014 08:18:58 GMT
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.

Mime
View raw message