CODEWITHSUNDEEP
javaamazon-web-servicesconditional-statementsamazon-dynamodb
Diferdin
Diferdin

Reputation: 1260

Querying DynamoDB

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.

Upvotes: 4

Views: 12652

Answers (5)

user8070240
user8070240

Reputation:

You need to create GSI if you want to query other than Partition Key. Scan is very expensive in terms of cost and performance.

Upvotes: 0

irriss
irriss

Reputation: 782

You could add Global Secondary Index with, for example, year and month of your date and make it your hash key, range key for that index would be your date then you could query any data range in a certain month. It will help you avoid expensive full scan.

E.g.

Global Secondary Index:
Hash key: month_and_year for example '2014 March'
Range key: full_date

Hope it helps!

Upvotes: 0

Diferdin
Diferdin

Reputation: 1260

I ended up scanning the table and enforcing a filter.

Thanks to everyone taking time for helping out!

Upvotes: 2

John Kelvie
John Kelvie

Reputation: 858

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!

Upvotes: 2

aaaristo
aaaristo

Reputation: 2169

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.

Upvotes: 6

Related Questions