1

I have a table with two keys KeyA and KeyB on AWS Keyspaces.

I open a CQL editor on AWS Console and run the query:

SELECT fields FROM table WHERE KeyA = 'value' AND KeyB = 'value' LIMIT 10 ALLOW FILTERING

It returns 10 rows, I copy the query to my node.js project and run the same query, also returns 10 rows.

Now I want to filter by only ONE key, I open a CQL editor on AWS Console and run the query:

SELECT fields FROM table WHERE KeyA = 'value' LIMIT 10 ALLOW FILTERING

and returns 10 fields, now I copy and paste the same query to my node project but this time returns 0 rows.

I believe I'm missing some configuration on my node.js? it's the library issue? AWS issue?

I'm using Node v14.16.1, cassandra-driver v4.6.1

Miguel Angel Acevedo
  • 299
  • 5
  • 14

1 Answers1

3

When using allow filtering you should also implement paging even though you are using a limit of 10.

Amazon Keyspaces paginates results based on the number of rows that it reads to process a request, not the number of rows returned in the result set. As a result, some pages might contain fewer rows than you specify in PAGE SIZE for filtered queries. In addition, Amazon Keyspaces paginates results automatically after reading 1 MB of data to provide customers with consistent, single-digit millisecond read performance.

Because Amazon Keyspaces paginates results based on the number of rows read to process a request and not the number of rows returned in the result set, some pages may not contain any rows if you are running filtered queries.

https://docs.aws.amazon.com/keyspaces/latest/devguide/working-with-queries.html#paginating-results

client.eachRow(query, parameters, { prepare: true, autoPage : true }, function(n, row) {
   // Invoked per each row in all the pages
}, callback);

In the end, a full scan is not a typical Cassandra access pattern and its recommended that you always access data based on fully qualified partition key.

MikeJPR
  • 764
  • 3
  • 14
  • thanks, this solves my issue, I know this is not how cassandra means to work but is a requirement I was ask for and the possible values for KeyB are not available. – Miguel Angel Acevedo Nov 15 '21 at 06:38
  • it may be possible to have KeyA as partition key and KeyB as Clustering value. This way you can support both access patterns. – MikeJPR Nov 17 '21 at 22:11