I have a table like given below.
CREATE TEST(
HOURLYTIME TIMESTAMP,
FULLTIME TIMESTAMP,
DATA TEXT,
PRIMARY KEY(HOURLYTIME,FULLTIME)
)
I inserted the record (2014-12-12 00:00:00,2014-12-12 00:00:01,'Hello World')
I would like to search based on date time range in HOURLYTIME field which holds hourly records.When i tried with token() like
select * from TEST where token(HOURLYTIME)=token('2014-12-12')
to get all the records for that date it returns only for one hour record i.e for
2014-12-12 **00:00:00**
If i add date range
select * from TEST where token(HOURLYTIME)>=token('2014-12-12') AND token(HOURLYTIME)<=token('2014-12-14');
It gives the error : More than one restriction was found for the start bound.
How to resolve this issue.
I am able to scan using FULLTIME but i need to provide ALLOW FILTERING which will scan whole records & inefficient.
You are not allowed to restrict the primary key by a range without explicitly demanding it with allow filterting . This prevents queries which require a full table scan which as you note are slow and will not scale for true big data sizes. The reason for this is that the primary key values are randomly hashed so specifying a range of primary key values is basically the same as providing two loosely coupled random numbers. For example in your case dates most likely are not monotonically hashed. This means saying you want dates that hash to a value less that the hash of another value will return a completely random set of data.
The issue here is that your table setup does not allow the queries that you actually want to perform. You need to model your tables so that the information you want can be obtained from a single partition.