Looking for cassandra expert's recommendation on above usecase, please reply.
On Mon, Apr 17, 2017 at 7:37 PM, Naresh Yadav <nyadav....@gmail.com> wrote: > Hi all, > > This is my existing table configured on apache-cassandra-3.0.9: > > CREATE TABLE report_id1 ( > mc_id text, > tag_id text, > e_date timestamp. > value text > PRIMARY KEY ((mc_id, tag_id), e_date) > } > > I create table dynamically for each report from application. Need to > support upto 1000 reports means 1000 such tables. > unique mc_id will be in range of 5 to 100 in a report. > For a mc_id there will be unique tag_id in range of 100 to 1 million in a > report. > For a mc_id, tag_id there will be unique e_date values in range of 10 to > 5000. > > Current queries to answer : > 1)SELECT * FROM report_id1 WHERE mc_id='x' AND tag_id IN('a','b','c') AND > e_date='16Apr2017 23:59:59'; > 2)SELECT * FROM report_id1 WHERE mc_id='x' AND tag_id IN('a','b','c') AND > e_date >='01Apr2017 00:00:00' AND e_date <='16Apr2017 23:59:59; > > 3)SELECT * FROM report_id1 WHERE mc_id='x' AND e_date='16Apr2017 23:59:59'; > Current design this works with ALLOW FILTERING ONLY > 4)SELECT * FROM report_id1 WHERE mc_id='x' AND e_date >='01Apr2017 > 00:00:00' AND e_date <='16Apr2017 23:59:59'; > Current design this works with ALLOW FILTERING ONLY > > Looking for better design for this case, keeping in mind dynamic tables > usecase and queries listed. > > Thanks in advance, > Naresh > >