DynamoDB.SQL 1.2.1 – now supports Local Secondary Index

A cou­ple of weeks ear­li­er, Ama­zon announced sup­port for Local Sec­ondary Index­es (LSI) for DynamoDB. You can now per­form fast, effi­cient queries against DynamoDB tables using attrib­ut­es that are not part of the exist­ing Hash and Range key mod­el with­out resort­ing to the use of scans.

As a result to the new fea­ture the DynamoDB query API has also gone through some changes, as did the AWSSDK for .Net. From ver­sion 1.5.18.0 onwards, there’s a new top lev­el name­space Amazon.DynamoDBv2 which con­tains a mir­ror set of types to those under the orig­i­nal Amazon.DynamoDB name­space, albeit with minor changes to sup­port the new LSI fea­ture.

Query syntax change

Due to the changes in the under­ly­ing AWSSDK, I have decid­ed to make some changes to the query syn­tax sup­port­ed by DynamoDB.SQL too – name­ly, to remove the need for the spe­cial key­words @HashKey and @RangeKey and instead allow you to use the attrib­ut­es names for your hash and range keys.

For exam­ple, giv­en a table like the one out­lined in the DynamoDB docs:

image

To write a query to find all sub­jects start­ing with “a” in the “S3” forum, you would pre­vi­ous­ly write:

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

In the new ver­sion of DynamoDB.SQL, you would write the fol­low­ing instead:

SELECT * FROM Thread WHERE Forum­Name = \”S3\” AND Sub­ject BEGINS WITH \”a\”

This syn­tax change only applies to the exten­sion meth­ods for the Ama­zon­Dy­namoD­B­Client and DynamoD­B­Con­text types under the new Amazon.DynamoDBv2 name­space. The exten­sion meth­ods them­selves are only avail­able under a new name­space DynamoDbV2.SQL.Execution in the DynamoDb.SQL.dll.

The syn­tax for scans on the oth­er hand, has remained the same in both the new and the old API.

Local Secondary Index support

You can spec­i­fy that a query should use a Local Sec­ondary Index (LSI) by using the Index option in the WITH clause.

For exam­ple, giv­en a Thread table and an index Last­PostIn­dex, as out­lined 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 fol­low­ing:

SELECT * FROM Thread WHERE Forum­Name = \”S3\” AND Last­Post­Date­Time >= \”2013–05-01\”

WITH (Index(LastPostIndex, true))

The WITH clause is where you spec­i­fy option­al query para­me­ters, such as NoCon­sis­ten­tRead, and Page­Size. (please refer to the Get­ting Start­ed guide on avail­able query para­me­ters).

The Index option allows you to spec­i­fy the name of the index, in this case that’s “Last­PostIn­dex”, and a boolean flag to spec­i­fy whether or not all attrib­ut­es should be returned.

For the above query, because we’re ask­ing for all attrib­ut­es to be sent back with *, and that attrib­ut­es such as Replies are not pro­ject­ed into the index, they will be fetched (auto­mat­i­cal­ly per­formed by DynamoDB) from the main table at addi­tion­al con­sumed capac­i­ty units.

 

On the oth­er hand, if you want only the pro­ject­ed attrib­ut­es back from the index, we can tweak the query slight­ly:

SELECT * FROM Thread WHERE Forum­Name = \”S3\” AND Last­Post­Date­Time >= \”2013–05-01\”

WITH (Index(LastPostIndex, false))

In which case, only Forum­Name, Last­Post­Date­Time and Sub­ject will be returned by the query.

 

Final­ly, if you are inter­est­ed in a spe­cif­ic set of attrib­ut­es, you can also spec­i­fy them in the SELECT clause:

SELECT Forum­Name, Sub­ject FROM Thread

WHERE Forum­Name = \”S3\” AND Last­Post­Date­Time >= \“2013–05-01\”

WITH     (Index(LastPostIndex, false))

 

Some ref­er­ence links:

AWS announces Local Sec­ondary Index sup­port for DynamoDB

DynamoDB docs on Local Sec­ondary Index­es

DynamoDB docs on Query

Query­ing with an Index attribute in DynamoDB.SQL

Get­ting start­ed with DynamoDB.SQL