Hi Jonathan,
Thanks for the response. To make this clearer, this is what we are doing:
create table sensorReadings (sensorUnitId int,
sensorId int,time timestamp,timeShard int,
readings blob,primary key((sensorUnitId, sensorId, timeShard), time);
where timeShard is a combination of year and week of year
This works exactly as you mentioned when we know what time range we are
querying.
The problem is that for those cases where we want to run through all the
readings for all timestamps, we don't know the first and last timeShard value
to use to constrain the query or iterate over each shard. Our understanding is
that updating another table with the maximum or minimum timeShard values on
every write to the above table would mean pounding a single row with updates
and running SELECT DISTINCT pulls all partition keys.
Hopefully this is clearer.
Again, any suggestions would be appreciated.
Thanks,
Jason
From: Jonathan Haddad <j...@jonhaddad.com>
To: user@cassandra.apache.org; Jason Kania <jason.ka...@ymail.com>
Sent: Thursday, March 10, 2016 11:21 AM
Subject: Re: Strategy for dividing wide rows beyond just adding to the
partition key
Have you considered making the date (or week, or whatever, some time component)
part of your partition key?
something like:
create table sensordata (sensor_id int,day date,ts datetime,reading int,primary
key((sensor_id, day), ts);
Then if you know you need data by a particular date range, just issue multiple
async queries for each day you need.
On Thu, Mar 10, 2016 at 5:57 AM Jason Kania <jason.ka...@ymail.com> wrote:
Hi,
We have sensor input that creates very wide rows and operations on these rows
have started to timeout regulary. We have been trying to find a solution to
dividing wide rows but keep hitting limitations that move the problem around
instead of solving it.
We have a partition key consisting of a sensorUnitId and a sensorId and use a
time field to access each column in the row. We tried adding a time based
entry, timeShardId, to the partition key that consists of the year and week of
year during which the reading was taken. This works for a number of queries but
for scanning all the readings against a particular sensorUnitId and sensorId
combination, we seem to be stuck.
We won't know the range of valid values of the timeShardId for a given
sensorUnitId and sensorId combination so would have to write to an additional
table to track the valid timeShardId. We suspect this would create tombstone
accumulation problems given the number of updates required to the same row so
haven't tried this option.
Alternatively, we hit a different bottleneck in the form of SELECT DISTINCT in
trying to directly access the partition keys. Since SELECT DISTINCT does not
allow for a where clause to filter on the partition key values, we have to
filter several hundred thousand partition keys just to find those related to
the relevant sensorUnitId and sensorId. This problem will only grow worse for
us.
Are there any other approaches that can be suggested? We have been looking
around, but haven't found any references beyond the initial suggestion to add
some sort of shard id to the partition key to handle wide rows.
Thanks,
Jason
