Utilizing DynamoDB Single-Table Style with Rockset


The single table style for DynamoDB streamlines the architecture needed for saving information in DynamoDB. Rather of having several tables for each record type you can integrate the various kinds of information into a single table. This works since DynamoDB has the ability to keep extremely broad tables with differing schema. DynamoDB likewise supports embedded items. This enables users to integrate PK as the partition secret, SK as the sort secret with the mix of the 2 ending up being a composite main secret. Typical columns can be utilized throughout record types like an outcomes column or information column that shops embedded JSON. Or the various record types can have absolutely various columns. DynamoDB supports both designs, or perhaps a mix of shared columns and diverse columns. Often users following the single table design will utilize the PK as a main essential within an SK which works as a namespace. An example of this:


Notification that the PK is the exact same for both records, however the SK is various. You might picture a 2 table design like the following:




While neither of these information designs is in fact a fine example of appropriate information modeling, the example still represents the concept. The single table design utilizes PK as a main Secret within the namespace of an SK.

How to utilize the single table design in Rockset

Rockset is a real-time analytics database that is typically utilized in combination with DynamoDB. It synchronizes with information in DynamoDB to use a simple method to carry out inquiries for which DynamoDB is less matched. Discover more in Alex DeBrie’s blog site on DynamoDB Filtering and Aggregation Inquiries Utilizing SQL on Rockset

Rockset has 2 methods of producing combinations with DynamoDB. The very first is to utilize RCUs to scan the DynamoDB table, and when the preliminary scan is total Rockset tails DynamoDB streams. The other approach uses DynamoDB export to S3 to very first export the DynamoDB table to S3, carry out a bulk intake from S3 and after that, after export, Rockset will begin trailing the DynamoDB streams. The very first approach is utilized for when tables are extremely little, < < 5GB, and the 2nd is a lot more performant and works for bigger DynamoDB tables. Either approach is proper for the single table approach.

Tip: Rollups can not be utilized on DDB.

Once the combination is established you have a couple of choices to think about when setting up the Rockset collections.

Technique 1: Collection and Views

The very first and easiest is to consume all of the table into a single collection and carry out views on top of Rockset. So in the above example you would have a SQL change that appears like:

-- new_collection
choose i. * from _ input i.

And you would develop 2 views on top of the collection.

-- user view.
Select c. * from new_collection c where c.SK='User';.


-- class view.
choose c. * from new_collection c where c.SK=' Class';

This is the easiest method and needs the least quantity of understanding about the tables, table schema, sizes, gain access to patterns, and so on. Usually for smaller sized tables, we begin here. Tip: views are syntactic sugar and will not emerge information, so they need to be processed like they belong to the question for every single execution of the question.

Technique 2: Clustered Collection and Views

This approach is extremely comparable to the very first approach, other than that we will carry out clustering when making the collection. Without this, when an inquiry that utilizes Rockset’s column index is run, the whole collection needs to be scanned since there is no real separation of information in the column index. Clustering will have no influence on the inverted index.

The SQL change will appear like:

-- clustered_collection.
choose i. * from _ input i cluster by i.SK.

The caution here is that clustering does take in more resources for intake, so CPU usage will be greater for clustered collections vs non-clustered collections. The benefit is inquiries can be much quicker.

The views will look the like prior to:

-- user view.
Select c. * from new_collection c where c.SK='User';.


-- class view.
choose c. * from new_collection c where c.SK=' Class';

Technique 3: Different Collections

Another approach to think about when developing collections in Rockset from a DynamoDB single table design is to develop several collections. This approach needs more setup upfront than the previous 2 approaches however offers significant efficiency advantages. Here we will utilize the where provision of our SQL change to separate the SKs from DynamoDB into different collections. This enables us to run inquiries without executing clustering, or carry out clustering inside a specific SK.

-- User collection.
Select i. * from _ input i where i.SK=' User';


-- Class collection.
Select i. * from _ input i where i.SK=' Class';

This approach does not need views since the information is emerged into specific collections. This is truly practical when splitting out huge tables where inquiries will utilize blends of Rockset’s inverted index and column index. The constraint here is that we are going to need to do a different export and stream from DynamoDB for each collection you wish to develop.

Technique 4: Mix of Different Collections and Clustering

The last approach to go over is the mix of the previous approaches. Here you would break out big SKs into different collections and utilize clustering and a combined table with views for the smaller sized SKs.

Take this dataset:


You can develop 2 collections here:

-- user_collection.
choose i. * from _ input i where i.SK=' User';


-- combined_collection.
choose i. * from _ input i where i.SK!= 'User' Cluster By SK;.

And after that 2 views on top of combined_collection:

-- class_view.
choose * from combined_collection where SK=' Class';


-- transportation_view.
choose * from combined_collection where SK=' Transport';

This offers you the advantages of separating out the big collections from the little collections, while keeping your collection size smaller sized, permitting other smaller sized SKs to be contributed to the DynamoDB table without needing to recreate and re-ingest the collections. It likewise enables one of the most versatility for question efficiency. This alternative does feature the most functional overhead to setup, display, and keep.


Single table style is a popular information modeling method in DynamoDB. Having actually supported various DynamoDB users through the advancement and productionization of their real-time analytics applications, we have actually detailed numerous approaches for arranging your DynamoDB single table design in Rockset, so you can choose the style that works finest for your particular usage case.

Like this post? Please share to your friends:
Leave a Reply

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: