Querying DynamoDB in sorted order
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 |
amazon-web-services amazon-dynamodb
asked Nov 21 '18 at 10:30
Dinesh
156
|
show 3 more comments
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 |
amazon-web-services amazon-dynamodb
asked Nov 21 '18 at 10:30
Dinesh
156
Take a look at this related question: stackoverflow.com/questions/9097708/…
– 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 withaction_typeas partition key andcreatedas a sort key. When I query along with type and date fromwas-consoleit shows the result of current date results but not in sorted order. I have changed theascendinganddescendingradio buttons. But no change in result. Can you explain to me why it is not sorted bycreateddata?
– 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 ofaction_typeordered bycreated. Remember, as we've mentioned-createdwill 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
|
show 3 more comments
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 |
amazon-web-services amazon-dynamodb
asked Nov 21 '18 at 10:30
Dinesh
156
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 |
amazon-web-services amazon-dynamodb
amazon-web-services amazon-dynamodb
asked Nov 21 '18 at 10:30
Dinesh
156
asked Nov 21 '18 at 10:30
Dinesh
156
edited Nov 28 '18 at 9:31
asked Nov 21 '18 at 10:30
Dinesh
156
asked Nov 21 '18 at 10:30
Dinesh
156
asked Nov 21 '18 at 10:30
Dinesh
156
156
Take a look at this related question: stackoverflow.com/questions/9097708/…
– 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 withaction_typeas partition key andcreatedas a sort key. When I query along with type and date fromwas-consoleit shows the result of current date results but not in sorted order. I have changed theascendinganddescendingradio buttons. But no change in result. Can you explain to me why it is not sorted bycreateddata?
– 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 ofaction_typeordered bycreated. Remember, as we've mentioned-createdwill 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
|
show 3 more comments
Take a look at this related question: stackoverflow.com/questions/9097708/…
– 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 withaction_typeas partition key andcreatedas a sort key. When I query along with type and date fromwas-consoleit shows the result of current date results but not in sorted order. I have changed theascendinganddescendingradio buttons. But no change in result. Can you explain to me why it is not sorted bycreateddata?
– 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 ofaction_typeordered bycreated. Remember, as we've mentioned-createdwill 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
Take a look at this related question: stackoverflow.com/questions/9097708/…
– Matthew Pope
Nov 21 '18 at 20:18
Take a look at this related question: stackoverflow.com/questions/9097708/…
– 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
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
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
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
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
|
show 3 more comments
1 Answer
1
active
oldest
votes
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
scannableand it'll be set toscan_me) - Create a Global Secondary Index with partition
scannableand sortcreatedkeys - Query
scannable == "scan_me"and that will return all the data in your table (up tolimitin the string order ofcreated.
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.)
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
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
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410098%2fquerying-dynamodb-in-sorted-order%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
scannableand it'll be set toscan_me) - Create a Global Secondary Index with partition
scannableand sortcreatedkeys - Query
scannable == "scan_me"and that will return all the data in your table (up tolimitin the string order ofcreated.
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.)
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
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
add a comment |
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
scannableand it'll be set toscan_me) - Create a Global Secondary Index with partition
scannableand sortcreatedkeys - Query
scannable == "scan_me"and that will return all the data in your table (up tolimitin the string order ofcreated.
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.)
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
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
add a comment |
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
scannableand it'll be set toscan_me) - Create a Global Secondary Index with partition
scannableand sortcreatedkeys - Query
scannable == "scan_me"and that will return all the data in your table (up tolimitin the string order ofcreated.
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.)
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
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
scannableand it'll be set toscan_me) - Create a Global Secondary Index with partition
scannableand sortcreatedkeys - Query
scannable == "scan_me"and that will return all the data in your table (up tolimitin the string order ofcreated.
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.)
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
answered Nov 22 '18 at 12:28
thomasmichaelwallace
2,5251817
2,5251817
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
add a comment |
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
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
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
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410098%2fquerying-dynamodb-in-sorted-order%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Take a look at this related question: stackoverflow.com/questions/9097708/…
– 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_typeas partition key andcreatedas a sort key. When I query along with type and date fromwas-consoleit shows the result of current date results but not in sorted order. I have changed theascendinganddescendingradio buttons. But no change in result. Can you explain to me why it is not sorted bycreateddata?– 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_typeordered bycreated. Remember, as we've mentioned-createdwill 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