1

I'm new to Dynamodb. I want to query all records from Dynamodb along with pagination. So I want only 100 records of recent rows. In my table, I have created data which contains the date and time. If I create the GSI for the created, then I have to provide the partition key as the current date or any date. But I don't know the time which is present in the table. Is there any other way to do pagination?

Table structure:

---------------------------------------------
|id    |    created      |   action_type    |
---------------------------------------------
|hash  | 21:22 20/10/2018|   some_type      |
|hash  | 10:12 10/11/2018|   some_type      |
Dinesh
  • 1,135
  • 2
  • 10
  • 15
  • Take a look at this related question: https://stackoverflow.com/questions/9097708/how-to-implement-pagination-when-using-amazon-dynamo-db-in-rails – Matthew Pope Nov 21 '18 at 20:18
  • Also, to ensure that your date sort is correct, you should use ISO-8601 timestamp strings or Unix epoch timestamps. – Matthew Pope Nov 21 '18 at 20:20
  • I have tried with `action_type` as partition key and `created` as a sort key. When I query along with type and date from `was-console` it shows the result of current date results but not in sorted order. I have changed the `ascending` and `descending` radio buttons. But no change in result. Can you explain to me why it is not sorted by `created` data? – Dinesh Nov 22 '18 at 17:52
  • Does your query cross a day boundary? Did you fix your date format? – Matthew Pope Nov 22 '18 at 18:12
  • Dynamo _must_ sort by the sort key, so I suspect it's just a mistake. In the AWS Console make sure you've selected [Query] | [name-of-index]. It will then show all items matching a the specific value of `action_type` ordered by `created`. Remember, as we've mentioned- `created` will be sorted _alphabetically_ so if you've still got the data as shown there's a chance you're getting it sorted, just not as you expected. If you've checked both of these, could you share a screenshot of the console with the query expanded and a few rows out of order? – thomasmichaelwallace Nov 22 '18 at 18:23
  • @thomasmichaelwallace I have added the screenshots of the table. Can you check now? – Dinesh Nov 26 '18 at 14:02
  • @MatthewPope The query does not exceed the day boundry. But it was not in sorted order. – Dinesh Nov 26 '18 at 14:04
  • OK- that's actually the DynamoDB console interface getting in your way. If you do it programatically you'll be in order. The dynamo db "web interface" allows you to sort _the information shown on the page_ (see that black triangle on the column you've cropped on the left?) - if you click on the column you want to sort, it'll sort the data by that column. – thomasmichaelwallace Nov 26 '18 at 14:06

1 Answers1

4

The design of DynamoDB means that it is not particularly good at returning all items.

Returning all items is a SCAN, which although it allows you to impose limits on the returned items, is always unsorted.

The only way to get a sorted response is if you QUERY on a table with a partition and sort key. In the case you can query on partition and it will return the results ordered by sort (ascending/descending is supported).

There's another question which gives a larger discussion about fixes you can do to sort a scan, but effectively it breaks down into:

  • Have an attribute that is set to be the same in every item (let's call it scannable and it'll be set to scan_me)
  • Create a Global Secondary Index with partition scannable and sort created keys
  • Query scannable == "scan_me" and that will return all the data in your table (up to limit in the string order of created.

This is not a scalable solution, but it'll "work" for small amounts of data. Note that as @matthew-pope points out, your created key does not sort (or, more specifically it sorts by hour-minute-day-month-year) which means you would also need to replace your created key with either a epoch-seconds or ISO datestring (as these both sort with default comparators).

Finally if TOP N is going to be important to you, then there is a probably a solution using a dynamodb -> dynamodb streams -> lambda -> another dynamodb table pattern to maintain a materialised view. (This pattern is often the suggested solution to supporting more complex "queries" in dynamo.)

thomasmichaelwallace
  • 7,926
  • 1
  • 27
  • 33
  • I have tried with action_type as partition key and created as a sort key. When I query along with type and date from was-console it shows the result of current date results but not in sorted order. I have changed the ascending and descending radio buttons. But no change in result. Can you explain to me why it is not sorted by created data? – Dinesh Nov 22 '18 at 18:09