Alternative to IN clause not supported in Amazon Keyspaces

Question

I have en existing DSE 6 node cluster on AWS that performs very well. I would like to move the data to the "Cassandra compatible" Amazon keyspaces but after moving some data, I have found there is no "IN" clause.

I use the field mentioned in the "IN" clause as the sharding separator. The field is unique per day so if I want to search over a number of days I use "where data_bucket in (1,2,3,4,5)"

Does anyone know how I could approach this (or adapt the query) using Keyspaces that would be performant?

Answer 1

Keyspaces probably didn't fully caught up with C*. Why move if it performs well? Alternatively, you can move it to ScyllaDB, it does have IN clause.

Answer 2

Yes there are work arounds for the IN operator. The traditional one is to break the IN statement into multiple queries. This is what the cassandra coordinator does. Actually performing parallel queries will result in better response times (latency) where the coordinator will execute this IN statements synchronously.

If you don't want to make a code change. Open a ticket with the Amazon Keyspaces service they may be able to help you with this feature.