Using Amazon DynamoDb with the AWS .NET API Part 6: queries

Introduction

In the previous post we looked at how to delete and update records in Amazon DynamoDb. In this post we’ll investigate how to run queries against the data set in a DynamoDb table. Like in the two posts before this we’ll look at the document and data models separately.

We’ll extend the DynamoDbDemo console application in Visual Studio.

I’ve inserted a couple more records into the demo DynamoDb table in preparation for the queries:

Records in DynamoDb ready for queries

There’s an important distinction between a “query” and a “scan” in DynamoDb. A query is a search by ID. All query-searches where you omit the ID parameter and try to run a search by other parameters will result in an exception. Searches based on parameters other than the ID are scan operations. An ID-based query is a lot faster as each record is guaranteed to have non-null ID field(s) and the IDs are indexed. As there’s no enforced schema in DynamoDb a search based on any property must inspect – or scan – each record and check if it has that property. You can read more about querying and scanning here.

Searches in the document model

Let’s first see how to retrieve all items from the target table. The following code example shows how to achieve that using the document model and the scan operation:

public void SearchRecordsByDocumentModel(string tableName)
{
	try
	{
		using (IAmazonDynamoDB client = GetDynamoDbClient())
		{
			Table peopleTable = Table.LoadTable(client, tableName);

			//get all records
			ScanFilter scanFilter = new ScanFilter();
			Search getAllItems = peopleTable.Scan(scanFilter);					
			List<Document> allItems = getAllItems.GetRemaining();					
			foreach (Document item in allItems)
			{
				foreach (string key in item.Keys)
				{
					DynamoDBEntry dbEntry = item[key];
					string val = dbEntry.ToString();
					if (key.ToLower() == "neighbours")
					{
						List<string> neighbours = dbEntry.AsListOfString();
						StringBuilder valueBuilder = new StringBuilder();
						foreach (string neighbour in neighbours)
						{
							valueBuilder.Append(neighbour).Append(", ");
						}
						val = valueBuilder.ToString();
					}							
					Console.WriteLine(string.Format("Property: {0}, value: {1}", key, val));
				}
			}			
		}
	}
	catch (AmazonDynamoDBException exception)
	{
		Debug.WriteLine(string.Concat("Exception while filtering records in DynamoDb table: {0}", exception.Message));
		Debug.WriteLine(String.Concat("Error code: {0}, error type: {1}", exception.ErrorCode, exception.ErrorType));
	}
}

First we load the referenced table. An empty ScanFilter object passed into the Scan method will ensure that there’s no filter on the search. The Scan method returns a Search object which we can call upon to return all documents. Each document is a row in the DynamoDb table and each row will have a number of properties and values. So for each document we want to iterate through the available keys which represent the property names, like “Name”. Each entry in the property-dictionary has a value of type DynamoDbEntry which can store one of several types: number, string, list of strings, float etc. Often the ToString method will yield the string representation of the value, like “Niklas” and “32”. However, in the case of e.g. a list of strings, such as the Neighbours property ToString is not enough, it will simply provide the fully qualified name of the type. Hence you’ll need to know at least something about the data structure in the DynamoDb table so that you can convert the DynamoDbEntry object into its underlying type. DynamoDBEntry has a list of methods starting with “As” which help you convert the contained value into a .NET object: AsBoolean, AsByte, AsByteArray, AsGuid and a whole lot more. In our case we know the Neighbours is a list of integers hence the AsListOfString method is called upon each DynamoDBEntry extracted from the People table. This example also shows how to extract values from a Document object, it won’t be repeated in the other code examples.

You need to be careful with scanning large tables. There’s a scan limit of about 12k records. The GetRemaining() method will at most return that many documents.

Calling the above method from Main…

DynamoDbDemoService service = new DynamoDbDemoService();
service.SearchRecordsByDocumentModel("People");

…will print all 7 records:

Extract all records from DynamoDb table Document model

Let’s see how to retrieve an item by its ID. As we have a composite ID we’ll populate a Dictionary with the ID values and call the GetItem value of Table:

IDictionary<string, DynamoDBEntry> keys = new Dictionary<string, DynamoDBEntry>();
keys["Name"] = "Niklas";
keys["Birthdate"] = new DateTime(1975, 03, 12);
Document singleDocumentByKey = peopleTable.GetItem(keys);

This will obviously find Niklas. An alternative way is to provide the hash key “Niklas” and a QueryFilter for the range key coupled with the Equal enumeration value of QueryOperator. Note the word “query” as we’re now searching by ID:

Search alternativeIdSearch = peopleTable.Query("Niklas", new QueryFilter("Birthdate", QueryOperator.Equal, new DateTime(1975, 03, 12)));
List<Document> alternativeIdSearchDocuments = alternativeIdSearch.GetRemaining();

The resulting list will only have one item. You can use the QueryFilter to specify other operators such as GreaterThan:

Search alternativeIdSearch = peopleTable.Query("Niklas", new QueryFilter("Age", QueryOperator.GreaterThan, 45));
List<Document> alternativeIdSearchDocuments = alternativeIdSearch.GetRemaining();

This query will obviously not find any document.

You can also retrieve items in batches using the QueryOperationConfig config:

QueryFilter queryFilter = new QueryFilter();
queryFilter.AddCondition("Name", QueryOperator.Equal, "Niklas");
queryFilter.AddCondition("Birthdate", QueryOperator.Between, new DateTime(1974, 03, 12), new DateTime(1976, 03, 12));
QueryOperationConfig queryConfig = new QueryOperationConfig
{
	Filter = queryFilter,
	Limit = 1
};
Search queryFilterSearch = peopleTable.Query(queryConfig);
while (!queryFilterSearch.IsDone)
{
	List<Document> set = queryFilterSearch.GetNextSet();
}

We’re looking for all “Niklas” whose birthdate lies between the given dates and we want to extract one record in each batch. There’ll be only one match as expected.

Finally here are two examples of providing non-ID based searches where the Scan method is the way to go:

scanFilter = new ScanFilter();
scanFilter.AddCondition("Age", ScanOperator.Equal, 42);
Search ageSearch = peopleTable.Scan(scanFilter);
List<Document> ageSearchItems = ageSearch.GetRemaining();

//get multiple items based on multiple criteria
scanFilter = new ScanFilter();
scanFilter.AddCondition("Age", ScanOperator.GreaterThanOrEqual, 30);
scanFilter.AddCondition("Age", ScanOperator.LessThan, 50);
Search multiplAgeSearch = peopleTable.Scan(scanFilter);
List<Document> multipleAgeSearchItems = multiplAgeSearch.GetRemaining();

The examples show how to add multiple criteria to the ScanFilter object to refine your search.

The data model

Querying is very similar in the data model but the returned objects will be strictly typed. The AWS library will take care of translating the values in the database to the proper objects. Even the Address will be converted into a proper Address object using the AddressConverter Here are a couple of examples that should be easy to follow based on the Scan and Query methods we saw above:

public void SearchRecordsByDataModel(string tableName)
{
	try
	{
		using (IAmazonDynamoDB client = GetDynamoDbClient())
		{
			DynamoDBContext context = new DynamoDBContext(client);
			//get by id
			Person niklas = context.Load<Person>("Niklas", new DateTime(1975, 03, 12));

			//get all Person objects
			List<Person> allPersons = context.Scan<Person>().ToList();

			//get all Person objects with age  between 35 and 50
			//Scan method accepts a params array of ScanCondition objects that will let you refine
			//your search criteria
			List<Person> allBetweenAges = context.Scan<Person>(
				new ScanCondition("Age", ScanOperator.Between, 35, 50)).ToList();

			List<Person> queryExample = context.Query<Person>("Niklas", QueryOperator.Between, new DateTime(1974, 03, 12), new DateTime(1976, 03, 12)).ToList();					
		}
	}
	catch (AmazonDynamoDBException exception)
	{
		Debug.WriteLine(string.Concat("Exception while filtering records in DynamoDb table: {0}", exception.Message));
		Debug.WriteLine(String.Concat("Error code: {0}, error type: {1}", exception.ErrorCode, exception.ErrorType));
	}
}

There are many other methods available for both the document and data models that haven’t been covered here: other batching options, asynchronous reads and writes, multiple overloads of Scan, Query and Load methods etc. We’ve gone through the basics which should be enough for you to get started.

In the next and final post on DynamoDb we’ll look at its potential role in an AWS Big Data architecture.

View all posts related to Amazon Web Services and Big Data here.

Advertisements

About Andras Nemes
I'm a .NET/Java developer living and working in Stockholm, Sweden.

One Response to Using Amazon DynamoDb with the AWS .NET API Part 6: queries

  1. Brian Dead Rift Webb says:

    Reblogged this on Brian By Experience.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

ultimatemindsettoday

A great WordPress.com site

iReadable { }

.NET Tips & Tricks

Robin Sedlaczek's Blog

Developer on Microsoft Technologies

HarsH ReaLiTy

A Good Blog is Hard to Find

Softwarearchitektur in der Praxis

Wissenswertes zu Webentwicklung, Domain-Driven Design und Microservices

the software architecture

thoughts, ideas, diagrams,enterprise code, design pattern , solution designs

Technology Talks

on Microsoft technologies, Web, Android and others

Software Engineering

Web development

Disparate Opinions

Various tidbits

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Cyber Matters

Bite-size insight on Cyber Security for the not too technical.

Guru N Guns's

OneSolution To dOTnET.

Johnny Zraiby

Measuring programming progress by lines of code is like measuring aircraft building progress by weight.

%d bloggers like this: