query dynamoDB for the latest entry of every hash key

user_10270333 0
Viewed 4281

I have a dynamoDB table where I store sensor events.

  • HASH KEY: sensor id

  • RANGE KEY: timestamp

  • sensor info

I now need a query for the latest event of every sensor.

The only solution I can come up with is to query the latest event for each sensor id. But that would be a lot of queries with 2000+ sensors. I don't want to scan the whole table to sort it out afterwards either since the table can grow quite fast.

Any ideas?

dynamodb-queries amazon-dynamodb aws-lambda python
2 Answers

You have to decide what is important to you and design your table(s) to match your use cases.

You say you want to query the last value for every sensor and that there are 2000+ sensors. What will you do with these 2000+ values? How often do you need these values and can the values be slightly out of date?

One solution would be to have two tables: one where you append historical values (time series data) and another table where you always update the most recent reading for each sensor. When you need the most recent sensor data, just scan this second table to get all your sensors’ most recent values. It's as efficient as it gets for reads. For writes, it means you have to write twice for each sensor update.

The other potential solution would be to write your time series data partitioned by time, as opposed to the sensor ids. Assuming all sensors are updated at each time point, with a single query you can get the value of all sensors. This works but only if you update the vales of all sensors every time, and only if you do it with regular cadence.

However, if you update all sensors at once, then further optimizations may be had by combining multiple sensor readings into a single item, therefore requiring less writes to update all 2000 of them.

user_630740

Since you have a range_key defined you can get the latest item using

Query(hash_key=HASH_KEY, ScanIndexForward=True, Limit=1)
user_102495580
Related