Hi Arjun, this is strange. You should be able to use a range query on a column that is part of the clustering key, as long as all columns in the clustering key left to this column are set to fixed values.
So, given the table definition that you specified, your query should work (I just tested it with C* 4.1.3). In contrast, doing a range query for the “id” column without specifying “date_id” would not work. On a side note, I have two suggestions: 1. If you are not going to make range queries on the msisdn column, you might want to consider making it part of the partition key instead of the clustering key. 2. Doing something like “auto-increment” is notoriously hard in a distributed environment, which is why C* does not have built-in support for it. Instead of using a counter, you might want to consider using UUIDs. Cheers, Sebastian > Am 14.12.2023 um 06:19 schrieb arjun s <arjunjoice...@gmail.com>: > > Hello team, I'm new to Cassandra and I've created a table using the following > command: > > CREATE TABLE instant_cdr ( > rowid int, > msisdn varchar, > id bigint, > date_id int, > channel varchar, > usage_type varchar, > ac_type varchar, > voice_amt int, > sms_amt int, > data_amt int, > field1 varchar, > field2 varchar, > field3 varchar, > field4 varchar, > field5 varchar, > field6 varchar, > field7 varchar, > field8 varchar, > field9 varchar, > field10 varchar, > field11 varchar, > field12 varchar, > field13 varchar, > field14 varchar, > field15 varchar, > field16 varchar, > field17 varchar, > field18 varchar, > field19 varchar, > field20 varchar, > s_date date, > date_idp int, > PRIMARY KEY (rowid, msisdn, date_id, id) > ); > > > > In this table, rowid, msisdn, and date_id may have repeated values, so I > added an id column with an auto-incremental value for unique entries. > However, my concern is that queries will be based on the date_id. For example: > > SELECT VOICE_AMT, SMS_AMT, DATA_AMT FROM instant_cdr WHERE rowid = 0 and > msisdn = '8010000000' and date_id >= 231202 AND date_id <= 231204; > > > > The issue is that I can only apply >= and <= on the id column, which is not > used in the query. Is there a method to execute the query without mentioning > ALLOW FILTER in the query, or what would be the best approach to design the > table for this use case? > > Regards, > Arjun
smime.p7s
Description: S/MIME cryptographic signature
