Tables Full of Cheese: An Intro to Azure Table Service and Xamarin

In our last post on Azure Storage we looked at how to save large amounts of binary data to a BLOB from a Xamarin Forms app. We also took a quick look at what Azure Storage’s offering all provided, how to create a service account, and then how to access the BLOB service portion of it via the WindowsAzure.Storage NuGet package.

Today we’re going to turn our attention to another offering inside Azure Storage – that of Table Storage. We’re going to answer the question… what exactly is Table storage? Then we’ll look at why would somebody want to use Table Storage within a mobile app. Finally, we’ll explore ways in which a developer can integrate Table Storage into an Xamarin app.

So without further delay…

What Is Azure Table Storage?

Table Storage is a key/value store that can be used to store massive amounts of structured data within Azure. It is not, however, a relational database. Each “row” (or entity as we’ll refer to it from now on) in a table can have different columns from one another. In other words, it’s a schema-less NoSQL database.

Let’s take a closer look of the structure of Table Storage.

• Storage Account – This is the top-level entry point for all of Azure Storage’s services.
• Table – A storage account can contain one or many tables. Tables, obviously, are a means to store and group data.
• Entity – Akin to an individual row of a table. An entity will be identified by 2 columns, a partition key and a row id (and those columns must exist). An entity also will contain a last modified date (again this is a system column and must exist, and in fact cannot be changed by code). The partition key and row id serve as the “Key” portion of the key/value pair storage. The value portion is made up of a collection of properties and can be whatever we want them to be – as long as it can be serialized to JSON or XML. (And the individual properties can differ from one entity to another within the same table).

It is worth noting that the “partition key” and the “row id” portion of the entity must be unique across the table in which they exist. (That makes sense considering they do make up the “key” of the key/value pair). But since they are unique, that means we’ll be able to use them to query and obtain results in a speedy manner. Just querying off the “partition key” will return all the entities within that table partition. Whereas querying for both parts of the key will zero in on the entity exactly. (It’s also possible to query off the properties within the value portion as well, but obviously that won’t be as performant).

Pricing

Table Storage, for the most part, does not cost a whole lot. The pricing is based off of how much data you’re storing, how many requests you’re making against it, the type of replication, and then how often data is transferred outside of the region in which it is stored.

As a practical example – in the Central US – to store 1 GB of data against 100,000 requests / month has a cost of … $0.07. That’s right – 7 cents. Not too bad. (Then again SQL server would run $4.98 for roughly the same amount, not terrible and not apples to apples, but definitely more than 7 cents.

Why Use Table Storage In A Mobile App?

There are several reasons one might consider using Table Storage instead of a regular relational database within a mobile app. We won’t get into the full relational vs. NoSQL debate here, but let’s just say in some situations it can be very useful to be able to store different types of object schemas within a single table without having to modify the database schema at all.

Some of the other benefits that Azure Table storage gives us are, as mentioned above, the amount of storage for the price paid is very good. Table Storage also provides optimistic concurrency – meaning that multiple clients will be able to edit the same entity at once, but the last one to commit the changes will get an exception automatically from the server – we don’t have to do the checking.

Table Storage is also very performant – if the tables are designed appropriately, queries are able to be run across millions of entities very quickly. Also Table Storage gives us the ability to batch multiple operations up together to run at once. This way we can support offline editing and then send everything up to the cloud at one time instead of piecemeal.

Some of the downsides though are that the client library does not support on device storage. So we’re going to have to design our own solution. If supporting offline editing, we will also need to manually keep track of what transactions need to be sync’d to the cloud. (Azure Mobile Services kind of spoils us in handling it all).

All in all – if you’re considering a NoSQL storage option for your app and you need the ability to have access to a massive amount of storage for a very reasonable price – Azure Table Storage is something you’ll want to consider.

Integrating Into A Xamarin App

Now to the part we’ve all been waiting for – integrating Table Storage into a Xamarin app! The simple app that we’re going to build will retrieve reviews of cheeses based on email address. (It’s always an app around cheese with me, isn’t it)? The app will also allow you to enter a new review and have that persisted in table storage. The idea is that there will be hundreds and thousands of reviews – and we need an easy and cheap way to store them … so Table Storage is the way to go.

You can find all of the example code and a working Xamarin Forms app in my CheesedTable GitHub repo.

Installing

The very first thing that needs to be done is to setup a storage account within Azure. See the last post on BLOB storage (under Setup) for the instructions.

The next thing to do is add the NuGet to our Xamarin project. In the NuGet package manager search for WindowsAzure.Storage and make sure you have pre-release selected. As of this post, the latest version is 6.2.2-preview.

One particular thing to note is that this package is PCL profile 111 – that means if you’re going to use it within a PCL project – it will need to be compatible with profile 111 as well.Ok – the NuGet package is installed, all we need is the URL and username & password and we can start querying data, right? Not quite … first we need to handle some security.

Shared Access Signature

The Shared Access Signature, or SAS is one means by which access is granted to objects within Azure’s Storage Account. As with BLOBs, we need to generate a token that provides a certain level of access (read, add, update, delete) to certain objects, that is valid for a certain time period. Then with this token we’ll be able to connect to Table Storage from the device.

The following is a snippet of node.js code that will generate a sas for table storage. One thing to note is this code is based off the “azure” v0.10.6 package. This code will not work as is in a default installation of the legacy Azure Mobile Services. (Can you tell I was bit by that once)?

// This is a "restify" get listener - what matters is the code in the middle
server.get('/sas', function (req, res) {
    var azure = require('azure');
 
    // First 2 parameters are account name and key for your service
    var tableService = azure.createTableService('', '', 'https://xxx.table.core.windows.net/');
 
    // creating the table we want the token for - on the off chance it's not there yet
    tableService.createTableIfNotExists('reviews', function (err, result, response) {
        if (!err) {
            var startDate = new Date();
            var expiryDate = new Date(startDate);
            expiryDate.setMinutes(startDate.getMinutes() + 100);
            startDate.setMinutes(startDate.getMinutes() - 100);
 
            var sharedAccessPolicy = {
                AccessPolicy: {
                    Permissions: 'raud', // requesting read, add, update and delete
                    Start: startDate,
                    Expiry: expiryDate
                },
            };
 
            // "reviews" is the table name
            var tableSAS = tableService.generateSharedAccessSignature('reviews', sharedAccessPolicy);
 
            // just returning it - not important, just get it back to the client
            res.writeHead(200, { 'Content-Type': 'text/json' });
            res.write(tableSAS);
            res.end();
        }  
    });
});

Ok .. that little snippet of code will get us a token that will authenticate us for the rest of our adventure!

Modeling The Entities

If there’s a motto for the things that you can put into Azure Table Storage it should be …

… if you can serialize it, you can store it …

Remember that we somehow need to transfer our objects up the Azure cloud – which means they need to get serialized to XML or JSON along the way. So as long as the properties on our objects are serializable, then we’ll be able to store them in Table Storage.

Otherwise the classes we want to put into Table Storage are plain old POCO classes – deriving from TableEntity.

The biggest thing you’ll need to remember is to set the PartitionKey and RowKey properties. Combined those essentially form the primary key of the entity and are required.

The Cloud Client and Cloud Tables.

The gateway to the clouds, so to speak, goes through an object called the CloudTableClient. The main purpose of this object is to take the table storage’s URL, the credentials we generate from the token above, and then provide access to various tables through the GetTableReference function.

The GetTableReference returns a CloudTable object … and it’s with that we can start doing interesting things with.

Reading Data

There are 2 ways we can get entities out of Table Storage – we can either query it out and get a result set, or we can go after a single entity exactly. Let’s talk about querying first.

Querying Tables

Unfortunately getting at the data stored within the Table Storage isn’t as simple as just writing a SQL query or using LINQ against the CloudTable. Instead we have to create and populate a TableQuery object and then have our CloudTable execute that.

As you may suspect – the TableQuery<t></t> object gives us a means by which to specify a where clause. But the where clause isn’t the normal SQL where syntax … in fact it’s just a string and looks suspiciously like OData, but there are helper functions available so we don’t have to remember that syntax.

TableQuery.GenerateFilterCondition and several of its strongly typed related cousins (such as TableQuery.GenerateFilterConditionForBool) exist to help us.

These functions take 3 parameters, the first being the property to apply the condition to, the second being the operator (equal, less than, etc) enumerated by the QueryComparisons class, and the final being the value.

Using that, you’ll end up with something of the following:

string whereClause = TableQuery.GenerateFilterCondition ("PartitionKey", QueryComparisons.Equal, emailAddress);

That’s the filter portion of the query … but how do we execute it? We need to use the CloudTable.ExecuteQuerySegmentedAsync function. This function will execute the passed in TableQuery<t></t>, and will only return 1000 entities at a time.

If you need to get more than 1000 entities out, that function also takes a TableContinuationToken object. The return from the execute query contains another TableContinuationToken, and you can continually pass the new result in to grab the next 1000 rows as needed.

When put all together a query against an Azure Storage Table looks like this:

public async Task<List<CheeseReviewEntity>> SearchCheeseReviewsAsync (string emailAddress)
{
    List<CheeseReviewEntity> results = new List<CheeseReviewEntity> ();
 
    try {
        if (client == null)
            await InitializeCloudClientAsync ();
                
        var table = client.GetTableReference (reviewTable); // reviewTable is just the table's name
 
        // Generate a query
        var query = new TableQuery<CheeseReviewEntity> ();
                            
        // Looking for an exact match
        query.Where (TableQuery.GenerateFilterCondition ("PartitionKey", QueryComparisons.Equal, emailAddress));
 
        // This version of the Table Storage NuGet only has the option
        // of returning a subset of the entire entity set at a time. 
        // We could use the TableContinuation token along with a loop to get
        // everything out - but we'll just go with the first return
        TableContinuationToken continueToken = null;
 
        // Execute the query
        var s = await table.ExecuteQuerySegmentedAsync (query, continueToken);
 
        continueToken = s.ContinuationToken;
 
        var searchResults = s.Results;
 
        results.AddRange(s.Results);
 
    } catch (Exception ex) {
        var exMsg = ex.ToString ();
    }
 
    return results;
}

Loading A Single Entity

Know exactly what you’re looking for? Then you don’t have to bother with the query operations and their (somewhat convoluted) syntax at all! Instead we can use a TableOperation instead. (We’ll see more of these when we start to write data).

There are several different types of TableOperation‘s we can choose from – and one just happens to be Retrieve. The Retrieve will zero in on the exact entity we want and return it. All we need to do is specify the unique identifier for the table – if you remember the partition key and the row key.

From there we just invoke the ExecuteAsync function on the CloudTable and the entity we want will be returned.

An example of getting at a single entity looks like:

public async Task<CheeseReviewEntity> GetCheeseReview (string emailAddress, Guid rowKey)
{
    CheeseReviewEntity returnCheese = null;
 
    try {
        if (client == null)
            await InitializeCloudClientAsync ();
 
        // Define a table operation that grabs the exact partition & row key
        var op = TableOperation.Retrieve<CheeseReviewEntity> (emailAddress, rowKey.ToString ());
 
        // Get a table reference
        var table = client.GetTableReference (reviewTable);
 
        // Execute the table operation
        var result = await table.ExecuteAsync (op);
 
        // Parse the return - will need to cast it
        if (result.Result != null)
            returnCheese = (CheeseReviewEntity)result.Result;
 
    } catch (Exception ex) {
        returnCheese = null;
    }
 
    return returnCheese;
}

We’re on a roll now … but what good is getting data out if there’s nothing in Table Storage to begin with?

Writing Data

As mentioned above the TableOperation object plays a role in writing data to Table Storage. There are several static methods to create TableOperation objects used for data modification. All of these take a TableEntity or our model class, as input.

Once a TableOperation is obtained, all we need to do is use the ExecuteAsync function of the CloudTable and we’re good to go.

An example would look like the following:

public async Task<bool> SaveReviewAsync (CheeseReviewEntity entity)
{
    try {                           
        if (client == null)
            await InitializeCloudClientAsync ();
            
        // Define the insert operation
        var operation = TableOperation.InsertOrReplace (entity);
 
        // Get a reference to the review table
        var table = client.GetTableReference (reviewTable);
 
        // Execute the insert against the table
        var result = await table.ExecuteAsync (operation);
 
        return true;
 
    } catch (Exception ex) {
        return false;
    }
}

We can send more than one operation up in a single request as well. In order to do so we would create the TableOperation‘s as above, but add them to a TableBatchOperation object. TableBatchOperation works just like an IEnumerable as far as adding things goes … then just have the CloudTable.ExecuteAsync work against the TableBatchOperation.

Design Considerations

We know how to get a security token … We can query … we can write … what else can be involved with Azure Table Storage?

Offline persistence? Ugh…

Luckily we have something at our fingertips which gives us a pretty darn good offline persistence layer for Table Storage, and it goes hand in hand with the key/value storage that Azure Tables does as well … that thing? Akavache!

We can use Akavache as an offline repository of our entities. Then within those entities we just need to keep track of what’s been sync’d with Azure and what hasn’t been. At appropriate times we sync – update Akavache – and off we go.

I’m making it sound easier than what it is – but Akavache really does give us a huge head start at implementing our own offline persistence without starting from scratch.

The other design considerations is to make sure our objects are serializable so they can be transmitted via JSON/XML, and to watch for areas where we can batch operations together.

Then there’s the best practices of table design … my blog posts are long and cover a lot of ground … but I won’t even attempt to cover that. This article from Microsoft does a great job.

Summary

The whirlwind tour of Azure Table Storage has come to an end. We covered a lot of ground in this post, including defining what Table Storage is and identifying its constituent parts. We then covered why we’d want to consider using a NoSQL solution that can hold massive amounts of data in a mobile application.

With the theory out of the way, we dove into how to actually use the WindowsAzure.Storage NuGet package from within a Xamarin solution. First up was how to download the package itself (remember PCL profile 111)! Then we looked at a way to generate a token to grant access to the table storage. After that we found out some ways to query and return entities. Finally we talked about the many ways to write entities back to Table Storage.

To wrap it all up, we touched a bit on how to handle offline data persistence with the little library that could … Akavache.

All in all, Table Storage is pretty cool, and not something that immediately comes to mind when thinking about NoSQL databases on devices – but it warrants a look.

Cover image via Wiki Media Commons: https://commons.wikimedia.org/wiki/File:Parmigiano_reggiano_factory.jpg