4 ms·

We tried to implement an application on DynamoDB about 2 years ago. We really struggled with implementing adhoc queries/search. For e.g:- select * from employe

We tried to implement an application on DynamoDB about 2 years ago. We really struggled with implementing adhoc queries/search. For e.g:- select * from employees where name = X and city = Y. Any improvements in DynamoDB that make it easier to implement such queries?

garydevenay 5y ago
The most reliable way to build a system with DynamoDB is to plan queries upfront. Trying to use it like a SQL database and make Ad-Hoc queries won't work because it's not a SQL DB. Data should be stored in the fashion you wish for it to be read, and storing the same data in more than one configuration is acceptable. Good resource: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-general-nosql-design.html https://docs.aws.amazon.com/amazondynamodb/latest/developerg...
jon-wood 5y ago
This will sound flippant, but that's not what Dynamo is for. If you want to do freeform relational queries like that then put it in a relational database. Dynamo is primarily designed for high volume storage/querying on well understood data sets with a few query patterns. If you want to be able to query information on employees based on their name and city you'll need to build another index keyed on name and city (in practice Dynamo makes that reasonably simple by adding a secondary index).
- owenmarshall 5y ago
  Alternatively, practice single table design: structure your table keys in such a way that they can represent all (or at least most) of the queries you need to run. This is often easier said than done, but it can be far less expensive and more performant than adding an index for each search.
  - ellimilial 5y ago
    It's always great fun compounding new, manual 'indexes' when you discover you need another query.
- willcipriano 5y ago
  Amazon has a perfect use case for this. You click on a product in the search results, that url contains a UUID, that UUID is used to search Dynamo and returns an object that has all the information on the product, from that you build the page. If what you are trying to do looks more like "Give me all the customers that live in Cuba and have spent more than $10 and have green eyes", Dynamo isn't for you. You can query that way but after you put all the work in to get it up and running, you'd probably be better off with Postgres.
  - 8note 5y ago
    If that's one of 12 or less query patterns you need, I can write you a simple dynamo table for it. Dynamo's limitation is that it can only support n different query patterns, and you have to hand craft an index for each one(well, sometimes you can get multiple on one index)
unfunco 5y ago
I struggled at first but I watched Advanced Design Patterns for DynamoDB[0] a few times and it clicked. As other responses have suggested, generally you define your access patterns first and then structure the data later to fit those access patterns. [0]: https://www.youtube.com/watch?v=HaEPXoXVf2k https://www.youtube.com/watch?v=HaEPXoXVf2k
itsmemattchung 5y ago
With DynamoDB, you can now execute SQL queries using PartiQL: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html https://docs.aws.amazon.com/amazondynamodb/latest/developerg...
- augustl 5y ago
  Note that this is just a new syntax for the existing querying capabilities. If you query something that's not in the hash/sort key, you still need to filter on the "client" after the 1mb data set size limit etc.
- _hao 5y ago
  I'm really happy that Cosmos DB has this - https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-getting-started https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-que... I haven't used DynamoDB in a couple of years, so I'd be curious to know how querying compares if anyone can share some light that has used both Cosmos and Dynamo recently.
manigandham 5y ago
DynamoDB (and other dynamo-like systems like Cassandra, Bigtable) are just advanced key/value stores. They support multiple levels of keys->values but fundamentally you need the key to find the associated value. If you want to search by parameters that aren't keys then you need to store your data that way. Most of these systems have secondary indexes now, and that's basically what they do for you automatically in the backend, storing another copy of your records using a different key. If you need adhoc relational queries then you should use a relational database.
- ignoramous 5y ago
  > If you want to search by parameters that aren't keys then you need to store your data that way. Not that I recommend it, but by using space-filling curves, one could to index multiple dimensions onto DynamoDB's bi-dimensional (hash-key, range-key) primary-index: https://aws.amazon.com/blogs/database/z-order-indexing-for-multifaceted-queries-in-amazon-dynamodb-part-1/ https://aws.amazon.com/blogs/database/z-order-indexing-for-m... and https://web.archive.org/web/20220120151929/https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.144.505&rep=rep1&type=pdf https://web.archive.org/web/20220120151929/https://citeseerx...
Jupe 5y ago
DynamoDB is not meant for ad-hoc query patterns; as others have said, plan your indexes around your access patterns. However, so long as you add a global secondary index (GSI) with name, city as the key, you can certainly do such things. But be aware for large-scale solutions: 1. There's a limit of 20 GSIs per table. You can increase with a call to AWS support. 2. GSIs are latently updated; read-after write is not guaranteed, and there is no "consistent read" option on a GSI like there is with tables. 3. WCUs on GSIs should match (or surpass) the WCUs on the original table, else throughput limit exceeded exceptions will occur. So, 3 GSIs on a table means you pay 4x+ in WCU costs. 4. The keys of the GSI should be evenly distributed, just like the PK on a main table. If not, there is additional opportunity for hot partitions on write. Ref: https://aws.amazon.com/premiumsupport/knowledge-center/dynamodb-gsi-throttling-table/ https://aws.amazon.com/premiumsupport/knowledge-center/dynam...
deleted 5y ago
[deleted]
jugg1es 5y ago
That's not what DynamoDB is for. If you need to run queries like that, you should be using RDBMS. DynamoDB should only really be used for use cases where the queries are known up-front. There are ways to design your data model in Dynamo so that you could actually run queries like that, but you would have had to that work from day 1. You won't be able to retroactively support queries like that.

garydevenay 5y ago

The most reliable way to build a system with DynamoDB is to plan queries upfront. Trying to use it like a SQL database and make Ad-Hoc queries won't work because it's not a SQL DB. Data should be stored in the fashion you wish for it to be read, and storing the same data in more than one configuration is acceptable. Good resource: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-general-nosql-design.html https://docs.aws.amazon.com/amazondynamodb/latest/developerg...

jon-wood 5y ago

This will sound flippant, but that's not what Dynamo is for. If you want to do freeform relational queries like that then put it in a relational database. Dynamo is primarily designed for high volume storage/querying on well understood data sets with a few query patterns. If you want to be able to query information on employees based on their name and city you'll need to build another index keyed on name and city (in practice Dynamo makes that reasonably simple by adding a secondary index).

owenmarshall 5y ago
Alternatively, practice single table design: structure your table keys in such a way that they can represent all (or at least most) of the queries you need to run. This is often easier said than done, but it can be far less expensive and more performant than adding an index for each search.
- ellimilial 5y ago
  It's always great fun compounding new, manual 'indexes' when you discover you need another query.
willcipriano 5y ago
Amazon has a perfect use case for this. You click on a product in the search results, that url contains a UUID, that UUID is used to search Dynamo and returns an object that has all the information on the product, from that you build the page. If what you are trying to do looks more like "Give me all the customers that live in Cuba and have spent more than $10 and have green eyes", Dynamo isn't for you. You can query that way but after you put all the work in to get it up and running, you'd probably be better off with Postgres.
- 8note 5y ago
  If that's one of 12 or less query patterns you need, I can write you a simple dynamo table for it. Dynamo's limitation is that it can only support n different query patterns, and you have to hand craft an index for each one(well, sometimes you can get multiple on one index)

unfunco 5y ago

I struggled at first but I watched Advanced Design Patterns for DynamoDB[0] a few times and it clicked. As other responses have suggested, generally you define your access patterns first and then structure the data later to fit those access patterns. [0]: https://www.youtube.com/watch?v=HaEPXoXVf2k https://www.youtube.com/watch?v=HaEPXoXVf2k

itsmemattchung 5y ago

With DynamoDB, you can now execute SQL queries using PartiQL: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html https://docs.aws.amazon.com/amazondynamodb/latest/developerg...

augustl 5y ago
Note that this is just a new syntax for the existing querying capabilities. If you query something that's not in the hash/sort key, you still need to filter on the "client" after the 1mb data set size limit etc.
_hao 5y ago
I'm really happy that Cosmos DB has this - https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-query-getting-started https://docs.microsoft.com/en-us/azure/cosmos-db/sql/sql-que... I haven't used DynamoDB in a couple of years, so I'd be curious to know how querying compares if anyone can share some light that has used both Cosmos and Dynamo recently.

manigandham 5y ago

DynamoDB (and other dynamo-like systems like Cassandra, Bigtable) are just advanced key/value stores. They support multiple levels of keys->values but fundamentally you need the key to find the associated value. If you want to search by parameters that aren't keys then you need to store your data that way. Most of these systems have secondary indexes now, and that's basically what they do for you automatically in the backend, storing another copy of your records using a different key. If you need adhoc relational queries then you should use a relational database.

ignoramous 5y ago
> If you want to search by parameters that aren't keys then you need to store your data that way. Not that I recommend it, but by using space-filling curves, one could to index multiple dimensions onto DynamoDB's bi-dimensional (hash-key, range-key) primary-index: https://aws.amazon.com/blogs/database/z-order-indexing-for-multifaceted-queries-in-amazon-dynamodb-part-1/ https://aws.amazon.com/blogs/database/z-order-indexing-for-m... and https://web.archive.org/web/20220120151929/https://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.144.505&rep=rep1&type=pdf https://web.archive.org/web/20220120151929/https://citeseerx...

Jupe 5y ago

DynamoDB is not meant for ad-hoc query patterns; as others have said, plan your indexes around your access patterns. However, so long as you add a global secondary index (GSI) with name, city as the key, you can certainly do such things. But be aware for large-scale solutions: 1. There's a limit of 20 GSIs per table. You can increase with a call to AWS support. 2. GSIs are latently updated; read-after write is not guaranteed, and there is no "consistent read" option on a GSI like there is with tables. 3. WCUs on GSIs should match (or surpass) the WCUs on the original table, else throughput limit exceeded exceptions will occur. So, 3 GSIs on a table means you pay 4x+ in WCU costs. 4. The keys of the GSI should be evenly distributed, just like the PK on a main table. If not, there is additional opportunity for hot partitions on write. Ref: https://aws.amazon.com/premiumsupport/knowledge-center/dynamodb-gsi-throttling-table/ https://aws.amazon.com/premiumsupport/knowledge-center/dynam...

deleted 5y ago

[deleted]

jugg1es 5y ago

That's not what DynamoDB is for. If you need to run queries like that, you should be using RDBMS. DynamoDB should only really be used for use cases where the queries are known up-front. There are ways to design your data model in Dynamo so that you could actually run queries like that, but you would have had to that work from day 1. You won't be able to retroactively support queries like that.