Querying DynamoDB

Question

I've got a DynamoDB table with a an alpha-numeric string as a hash key (e.g. "d4ed6962-3ec2-4312-a480-96ecbb48c9da"). I need to query the table based on another field in the table, hence I need my query to select all the keys such as my field x is between dat x and date y.

I know I need a condition on the hash key and another on a range key, however I struggle to compose a hash key condition that does not bind my query to specific IDs.

I thought I could get away with a redundant condition based on the ID being NOT_NULL, but when I use it I get the error:

Query key condition not supported

Below is the conditions I am using, any idea how to achieve this goal?

 Condition hashKeyCondition = new Condition()
 .withComparisonOperator(ComparisonOperator.NOT_NULL.toString());

Condition rangeCondition = new Condition()
.withComparisonOperator(ComparisonOperator.BETWEEN.toString())
.withAttributeValueList(new AttributeValue().withS(dateFormatter.print(lastScanTime())), 
new AttributeValue().withS(dateFormatter.print(currentScanTime)));

Map<String, Condition> keyConditions = new HashMap<String, Condition>();
keyConditions.put("userId", hashKeyCondition);
keyConditions.put("lastAccesTime", rangeCondition);

Thanks in advance to everyone helping.

Solution 1

In DynamoDB you can get items with 3 api:

. Scan (flexible but expensive),

. Query (less flexible: you have to specify an hash, but less expensive)

. GetItem (by Hash and, if your table has one, by range)

The only way to achieve what you want is by either:

1. Use Scan, and be slow or expensive.

2. Use another table (B) as an index to the previous one (A) like:

   B.HASH = 'VALUES' B.RANGE = userid
   B.lastAccesTime = lastAccesTime (with a secondary index)

Now you have to maintain that index on writes, but you can use it with the Query operation, to get your userIds. Query B: hash='VALUES', lastaccessTime between x and y, select userid.

Hope this helps.

Solution 2

The NOT_NULL comparison operator is not valid for the hash key condition. The only valid operator for the Hash key condition on a query is EQ. More information can be found here: http://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html

And what this means is that a query will not work, at least as your table is currently constructed. You can either use a Scan operation or you can create a separate table that stores the data by Date (hash) and User ID (range).

Good luck!

Solution 3

I ended up scanning the table and enforcing a filter.

Thanks to everyone taking time for helping out!

Comments

[bikeman868]

If you need to do this then DynamoDB is not the right solution for your situation. If you have a small number of records then a relational database will work much better for you. If you have a very large number of records this full table scan will be extremely inefficient and expensive.