node.jsamazon-web-servicescassandraamazon-keyspaces

Amazon Keyspaces ALLOW FILTERING without all keys in Node JS

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

Solution

  • 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.