CODEWITHSUNDEEP
time-seriesamazon-dynamodbdynamodb-queries
Brice Frisco
Brice Frisco

Reputation: 450

DynamoDB Timeseries: Querying large timespans of data

I have a simple timeseries table:

{
  "n": "EXAMPLE", # Name, Hash Key
  "t": 1640893628, # Unix Timestamp, Range Key
  "v": 10 # Value being stored
}

Every 15 minutes I will poll data and insert into the table. If I want to query values between a 24-hour period, this works well - this would equate to a total of 96 records.

Now, say I want to query a larger timespan - 1 or 2 years. This is now tens of thousands of records, and (in my opinion) impractical to do regularly. This will require multiple queries to retrieve larger time ranges which would negatively impact response times as well as being much more costly.

I have thought of a couple of potential solutions to this problem:

1. Replicate data in another table, with larger increments. A table with a single record every 6 hours, for example.

2. Have another table to store common query results, such as records for "EXAMPLE" for the past week, month, and year (respectively). I would periodically update records in the new table to hold every N'th record in the main table (a total of 100). Something like:

{
  "n": "EXAMPLE#WEEKLY",
  "v": [
     {
       "t": 1640893628,
       "v": 10
     },
     {
       "t": 1640993628,
       "v": 15
     },
     ... 98 more.
  ]
}

I believe #2 is a solid approach. It seems to me like this would be a common enough problem, so I would love to hear about how other people have approached it.

Upvotes: 1

Views: 783

Answers (2)

fedonev
fedonev

Reputation: 25669

More options present themselves if you can convert your unix timestamps into ISO 8601-type strings like 2021-12-31T09:27:58+00:00.

If so, DynamoDB's begins_with key condition expression lets us query for discrete calendar time buckets. December 2021, for example, is queryable using n = id1 AND begins_with(t, "2021-12"). Same for days and hours. We can take this one step further by adding other periods in indexes. Some rolling windows are possible, too: n = id1 AND t > [24 hours ago] gives us last 24h.

n (PK)        t (SK)                   hour_bucket (LSI1 SK)  week (LSI2 SK)
id1           2021-12-31T10:45         2021-12-31T09-12       2021-52
id1           2021-12-31T13:00         2021-12-31T13-15       2021-52
id1           2022-06-01T22:00         2022-06-01T22-24       2022-01

If you are looking for arbitrary time-series queries, you might consider Athena, as the other answer suggested, or AWS's serverless Timestream, which is a "purpose-built time series database that makes it easy to store and analyze trillions of time series data points per day. "

Upvotes: 2

hunterhacker
hunterhacker

Reputation: 7132

You could export the table to Amazon S3 and run Amazon Athena on the exported data. Here’s a blog post describing the process: https://aws.amazon.com/blogs/aws/new-export-amazon-dynamodb-table-data-to-data-lake-amazon-s3/

Upvotes: 0

Related Questions