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 <[email protected]> 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 <[email protected]> wrote: >> >> Hi Poonam, You should consider storing date fields in DATE/TIME datatypes >> instead of Char(22). >> >> >> On Friday, October 10, 2014, Poonam Ligade <[email protected]> >> 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. > >
