Query all data in DynamoDB without using primary partition key

Question

I am very new to DynamoDB. I want to query all data within certain time range.

column = "timerange" is primary sort key
column = "name" is primary partition key.

I want to get all data within 2 timerange.

This is my query.

from decimal import *
from boto3.dynamodb.conditions import Key, Attr
import boto3
import time
from datetime import datetime

dynamo = boto3.resource('dynamodb')
table = dynamo.Table('tablename')
a = time.mktime(datetime.strptime('2020-03-26 14:29:10','%Y-%m-%d %H:%M:%S').timetuple())
b = time.mktime(datetime.strptime('2020-03-26 14:30:10','%Y-%m-%d %H:%M:%S').timetuple())

response = table.query(
    KeyConditionExpression =
        Key('timerange').between(Decimal(a), Decimal(b)))

Which gives me an error ClientError: An error occurred (ValidationException) when calling the Query operation: Query condition missed key schema element: After searching the internet I found out that you need to have primary partition key inside your query so I tried the Contains method from https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Condition.html

response = table.query(
    KeyConditionExpression =
        Key('name').contains('a', 'b') &
        Key('timerange').between(Decimal(a), Decimal(b)))

Which I clearly do not understand fully.

How can I get all data within given timerange [a,b]?

I recommend watching a few NoSQL DynamoDB videos from AWS talking about how this is all done in DynamoDB. — Derrops
– Derrops, Commented Apr 20, 2020 at 10:42
In your first query, you are missing the partition key (name). In the second query, you cannot query on multiple partition key values in a single query -- the partition key condition is always equals, but the sort key can be a range. Recommended reading: dynamodbguide.com/querying — jarmod
– jarmod, Commented Apr 20, 2020 at 21:27
@jarmod Yes I understand that partition key needs to be equals. But I want to get all the names how can I eq([all names]) ? — haneulkim
– haneulkim, Commented Apr 21, 2020 at 1:24

jarmod · Accepted Answer · 2020-04-20 21:59:33Z

You cannot solve this kind of problem easily in DynamoDB, at least not in the general case that would allow you to make one query, and only one query, to get all records within an arbitrary date range, regardless of name (the partition key).

DynamoDB is a key/value database. Your queries are typically against individual keys, optionally with a range of values for a sort key. A query for name=A and timestamp between X and Y is perfect and can be queried very efficiently.

To do what you want, you would most typically create a Global Secondary Index whose primary key was a composite of:

YYMMDD of the timestamp
the timestamp

Now, you can query for items with timestamp in a certain range, regardless of name, but they must be on the same date. If you needed this query to work more broadly, say with ranges up to a month, then your GSI would have a primary key that was a composite of:

YYMM of the timestamp
the timestamp

And now you can query on all items within a given range of dates/times in the same month.

Here are a couple of useful resources:

Collectives™ on Stack Overflow

Query all data in DynamoDB without using primary partition key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related