1) I created another table for Query#2/3. The partition Key was StartTime and clustering key was name. When I execute my queries, I get an exception saying that I need to ALLOW FILTERING.
*Primary key(startTime,name) - the only queries that can be answered by this model are: where startTime = , where startTime IN (value1, value2), where startTime = and name = . Clustering keys support =,<,<=,>,>= while partition key supports = and IN operators. * *Your query was with name first and then startTime so in this case Cassandra is telling you that cannot answer this unless you use Allow Filtering at the end of the query = which basically is a disaster for performance since will bring all data in the coordinator and perform local filtering of the data. So, the model is not good for this query. * 2) I created a table with Name as partitioning key and startTime as clustering key. This way I was able to order the data in descending order based on startTime. But the problem was that if a row with same "name" was inserted, it was overriding the previously inserted row. *In Cassandra the primary key has 2 main purposes: to answer the queries and to provide uniqueness for the entries. This means that every variation of ( name, startTime) should be unique otherwise Cassandra will overwrite existing values ( actually C* doesn't read before write by default) and write the new values. In your case name in combination with different starttimes should provide unicity to the entries. If it's likely to have 2 entries for 1 name and 1 startTime then you need to insert in the primary key another column that will provide the uniqueness. This column will be last clustering key and you will not need to involve it in queries - the role will be only for uniqueness. * Valentina On Thu, Mar 1, 2018 at 3:26 PM, Behroz Sikander <behro...@gmail.com> wrote: > Thank you for your response. > > I have been through the document and I have tried these techniques but I > failed to model my queries correctly. > > Forexample, I have already tried the following: > 1) I created another table for Query#2/3. The partition Key was StartTime > and clustering key was name. When I execute my queries, I get an exception > saying that I need to ALLOW FILTERING. > 2) I created a table with Name as partitioning key and startTime as > clustering key. This way I was able to order the data in descending order > based on startTime. But the problem was that if a row with same "name" was > inserted, it was overriding the previously inserted row. > > I am not sure how to model such queries. > > > On Thu, Mar 1, 2018 at 2:02 PM, Kyrylo Lebediev <kyrylo_lebed...@epam.com> > wrote: > >> Hi! >> >> >> Partition key (Id in your case) must be in WHERE cause if not using >> indexes (but indexes should be used carefully, not like in case of >> relational DB's). Also, only columns which belong to primary key ( = >> partition key + clustering key) can be used in WHERE in such cases. That's >> why 2nd and 3rd are failing. >> You might find this useful: http://cassandra.apache.org/do >> c/latest/cql/dml.html#the-where-clause >> >> There are several Cassandra handbooks available on Amazon, maybe it would >> be helpful for you to use some of them as starting point to understand >> aspects of Cassandra data[query] modeling. >> >> >> Regards, >> >> Kyrill >> ------------------------------ >> *From:* Behroz Sikander <behro...@gmail.com> >> *Sent:* Thursday, March 1, 2018 2:36:28 PM >> *To:* user@cassandra.apache.org >> *Subject:* Cassandra filter with ordering query modeling >> >> Hi,own vote >> favorite >> <https://stackoverflow.com/questions/49049760/cassandra-filter-with-ordering-query-modeling#> >> >> I am new to Cassandra and I am trying to model a table in Cassandra. My >> queries look like the following >> >> Query #1: select * from TableA where Id = "123"Query #2: select * from >> TableA where name="test" orderby startTime DESCQuery #3: select * from >> TableA where state="running" orderby startTime DESC >> >> I have been able to build the table for Query #1 which looks like >> >> val tableAStatement = SchemaBuilder.createTable("tableA").ifNotExists. >> addPartitionKey(Id, DataType.uuid). >> addColumn(Name, DataType.text). >> addColumn(StartTime, DataType.timestamp). >> addColumn(EndTime, DataType.timestamp). >> addColumn(State, DataType.text) >> >> session.execute(tableAStatement) >> >> but for Query#2 and 3, I have tried many different things but failed. >> Everytime, I get stuck in a different error from cassandra. >> >> Considering the above queries, what would be the right table model? What >> is the right way to model such queries. >> Regards, >> Behroz >> > >
