DynamoDB.SQL 1.2.1 – now supports Local Secondary Index

A couple of weeks earlier, Amazon announced support for Local Secondary Indexes (LSI) for DynamoDB. You can now perform fast, efficient queries against DynamoDB tables using attributes that are not part of the existing Hash and Range key model without resorting to the use of scans.

As a result to the new feature the DynamoDB query API has also gone through some changes, as did the AWSSDK for .Net. From version 1.5.18.0 onwards, there’s a new top level namespace Amazon.DynamoDBv2 which contains a mirror set of types to those under the original Amazon.DynamoDB namespace, albeit with minor changes to support the new LSI feature.

Query syntax change

Due to the changes in the underlying AWSSDK, I have decided to make some changes to the query syntax supported by DynamoDB.SQL too – namely, to remove the need for the special keywords @HashKey and @RangeKey and instead allow you to use the attributes names for your hash and range keys.

For example, given a table like the one outlined in the DynamoDB docs:

image

To write a query to find all subjects starting with “a” in the “S3” forum, you would previously write:

SELECT * FROM Thread WHERE @HashKey = \”S3\” AND @RangeKey BEGINS WITH \”a\”

In the new version of DynamoDB.SQL, you would write the following instead:

SELECT * FROM Thread WHERE ForumName = \”S3\” AND Subject BEGINS WITH \”a\”

This syntax change only applies to the extension methods for the AmazonDynamoDBClient and DynamoDBContext types under the new Amazon.DynamoDBv2 namespace. The extension methods themselves are only available under a new namespace DynamoDbV2.SQL.Execution in the DynamoDb.SQL.dll.

The syntax for scans on the other hand, has remained the same in both the new and the old API.

Local Secondary Index support

You can specify that a query should use a Local Secondary Index (LSI) by using the Index option in the WITH clause.

For example, given a Thread table and an index LastPostIndex, as outlined in the DynamoDB docs:

image

To find all the posts in the “S3” forum since the 1st May 2013, you can write the query as following:

SELECT * FROM Thread WHERE ForumName = \”S3\” AND LastPostDateTime >= \”2013-05-01\”

WITH (Index(LastPostIndex, true))

The WITH clause is where you specify optional query parameters, such as NoConsistentRead, and PageSize. (please refer to the Getting Started guide on available query parameters).

The Index option allows you to specify the name of the index, in this case that’s “LastPostIndex”, and a boolean flag to specify whether or not all attributes should be returned.

For the above query, because we’re asking for all attributes to be sent back with *, and that attributes such as Replies are not projected into the index, they will be fetched (automatically performed by DynamoDB) from the main table at additional consumed capacity units.

 

On the other hand, if you want only the projected attributes back from the index, we can tweak the query slightly:

SELECT * FROM Thread WHERE ForumName = \”S3\” AND LastPostDateTime >= \”2013-05-01\”

WITH (Index(LastPostIndex, false))

In which case, only ForumName, LastPostDateTime and Subject will be returned by the query.

 

Finally, if you are interested in a specific set of attributes, you can also specify them in the SELECT clause:

SELECT ForumName, Subject FROM Thread

WHERE ForumName = \”S3\” AND LastPostDateTime >= \“2013-05-01\”

WITH     (Index(LastPostIndex, false))

 

Some reference links:

AWS announces Local Secondary Index support for DynamoDB

DynamoDB docs on Local Secondary Indexes

DynamoDB docs on Query

Querying with an Index attribute in DynamoDB.SQL

Getting started with DynamoDB.SQL

DynamoDB.SQL – version 1.1.0 released

Just a quick note to say that another minor update to DynamoDB.SQL has been release, you can view the release notes here.

 

The latest update adds support for a TSQL style WITH keyword for specifying optional parameters for tweaking the query/scan operation. For queries, you can specify the NoConsistentRead and PageSize options to use eventually consistent read and throttle the number of items returned per request respectively. Similarly for scans, you can use the PageSize option for throttling your scan requests too, but the DynamoDB scans does not support strong consistency.

 

According to DynamoDB best practices, you should avoid sudden bursts of read activity, using the new PageSize option you can make sure that your query/scan does not consume too many read capacity unit in a short burst and end up causing more critical reads to be throttled.

 

For example, a query which returns 10 items per request using eventually consistent read will look something like this:

image

whereas a scan will look like:

image

For more details about the full syntax, please refer to the Getting Started document, which has been updated to include the new WITH keyword.

 

Enjoy!

DynamoDB.SQL – version 1.0.7 released

Just a quick note to say that I have made some minor changes to DynamoDb.SQL and released version 1.0.7 of the library to Nuget, here’s a list of the changes:

  • fixed a bug with LIMIT when there is insufficient number of elements using the DynamoDBContext.
  • added support for counting the number of matching items with a query or scan (see below)

image

image

The Getting Started guide has also been updated to include details on how to write a Count query.

DynamoDB.SQL – minor updates

Just a quick note to say that I have made some minor changes to DynamoDb.SQL to:

  • Add support for specifying the ScanIndexForward option(see DynamoDB API doc here for detail) in a Query operation using an optional “ORDER” clause

image

  • Fixed a bug where when querying using the extension methods on the DynamoDBContext class, the LIMIT clause is not being respected. This was due to lazy-loading of results in the DynamoDBContext class, see this thread for more info.

 

The latest build can be found on Nuget as version 1.0.5.

DynamoDb.SQL – a SQL-like external DSL for Amazon DynamoDB

Amazon’s DynamoDB is a wonderful product – scalable, durable, fast, with predictable latency numbers unlike SimpleDB. However, the only gripe I have with DynamoDB is that there is no built-in support for a query language, which makes life rather difficult when you want to perform a query or a scan against the data you have in DynamoDB.

Whilst the standard AWS SDK for .Net provides a number of different ways to perform queries and scans:

  • using the low-level AmazonDynamoDBClient
  • using the Table helper class
  • using the DynamoDBContext ORM

none of these ways are easy to use and the few attempts to use them in our codebase left a bad taste in my mouth and an external DSL is desperately needed to make it easier to express the query we’d like to perform against data stored in DynamoDB.

Introducing DynamoDb.SQL

It is because of these limitations that I decided to add a SQL-like external DSL on top of existing functionalities to make it easier for .Net developers to work with DynamoDB.

Having spent a couple of weekends I have put together a simple library called DynamoDb.SQL, which you can download and try it yourself from Nuget here. This library adds an external DSL on top of the existing functionalities of the .Net AWS SDK and allows you to query and scan DynamoDB using natural, SQL-like syntax.

Using this syntax, a query can be expressed with the general format:

image

where @HashKey and @RangeKey are special keywords to mean the hash and range key in your table, and operator can be one of the allowed comparison operators for a query request :

=, >=, >, <=, <, BEGINS WITH and BETWEEN .. AND ..

Similarly, a scan can be expressed with the general format:

image

where operator1 to operatorN can be one of the allowed comparison operators in a scan request :

=, !=, >=, >, <=, <, CONTAINS, NOT CONTAINS, BEGINS WITH, IS NULL, IS NOT NULL, BETWEEN .. AND .., and IN (…)

To learn more about the syntax and how to use DynamoDb.SQL, take a look at the Getting Started guide here.

 

Links:

DynamoDB API – Querying and Scanning

DynamoDB – Querying and Scanning using low-level AmazonDynamoDBClient

DynamoDB – Querying and Scanning using Table helper class

DynamoDB – Querying and Scanning using high-level DynamoDBContext