On this publish I discover the best way to assist analytical queries with out encountering prohibitive scan prices, by leveraging secondary indexes in DynamoDB. I additionally consider the professionals and cons of this method in distinction to extracting information to a different system like Athena, Spark or Elastic.
Rockset not too long ago added assist for DynamoDB – which principally means you possibly can run quick SQL on DynamoDB tables with none ETL. As I spoke to our customers, I got here throughout alternative ways by which world secondary indexes (GSI) are used for analytical queries.
DynamoDB shops information beneath the hood by partitioning it over numerous nodes primarily based on a user-specified partition key discipline current in every merchandise. This user-specified partition key could be optionally mixed with a form key to characterize a major key. The first key acts as an index, making question operations on it cheap. A question operation can do equality comparability (=) on the partition key and comparative operations (>, <, =, BETWEEN) on the type key if specified. Performing operations that aren’t coated by the above scheme requires the usage of a scan operation, which is usually executed by scanning over your complete DynamoDB desk in parallel. These scans could be gradual and costly by way of Learn Capability Models (RCUs) as a result of they require a full learn of your complete desk. Scans additionally are likely to decelerate when the desk measurement grows as there may be extra information to scan to provide outcomes.
If we wish to assist analytical queries with out encountering prohibitive scan prices, we are able to leverage secondary indexes in DynamoDB. Secondary indexes additionally consist of making partition keys and non-compulsory kind keys over fields that we wish to question over in a lot the identical method as the first key. Secondary indexes are sometimes used to enhance software efficiency by indexing fields that are queried fairly often. Question operations on secondary indexes can be used to energy particular options by analytic queries which have clearly outlined necessities—like computing a leaderboard in a recreation. One clear benefit of this method of performing analytical queries is that there is no such thing as a want for every other system.
Nonetheless, it’s infeasible to make use of this method for a wider vary of analytical queries due to the restricted varieties of queries it helps. The total gamut of analytics requires filtering on a number of fields, grouping, ordering, becoming a member of information between information units, and many others., which can’t be achieved merely by secondary indexes. Secondary indexes that may be created are additionally restricted in quantity and require some planning to make sure that they scale properly with the information. A badly chosen partition key can worsen efficiency and enhance prices considerably. Information in DynamoDB can have a nested construction together with arrays and objects, however indexes can solely be constructed on sure primitive varieties. This may power denormalizing of the information to flatten nested objects and arrays so as to construct secondary indexes, which might doubtlessly explode the variety of writes carried out and related prices. Aside from value and suppleness, there are additionally safety and efficiency issues with regards to supporting analytic use instances on an operational information retailer in a manufacturing surroundings.
Benefits
- No further setup exterior DynamoDB
- Quick and scalable serving for primary analytical queries over listed fields
Disadvantages
- Costly when queries require scans over DynamoDB
- Very restricted assist for analytical queries over indexes; no SQL queries, grouping, or joins
- Can not arrange indexes on nested fields with out denormalizing information and exploding out writes
- Safety and efficiency implications of working analytical queries on an operational database
This method could also be appropriate if we’ve got an software that requires a selected characteristic that’s easy sufficient to be realized utilizing a question over an index. The elevated storage and I/O value and the restricted question means make it unsuitable for the broader vary of analytical queries in any other case. Subsequently, for a majority of analytic use instances, it’s value efficient to export the information from DynamoDB into a special system that enables us to question with greater constancy.
In case you are contemplating extracting information to a different system, there are a number of totally different choices for real-time analytics:
- DynamoDB + Glue + S3 + Athena
- DynamoDB + Hive/Spark
- DynamoDB + AWS Lambda + Elasticsearch
- DynamoDB + Rockset
I evaluate every of those by way of ease of setup, upkeep, question functionality, latency in my different weblog publish Analytics on DynamoDB: Evaluating Athena, Spark and Elastic, the place I additionally consider which use instances every of them are greatest suited to.
Different DynamoDB assets:
