SQLPASS Session about Windows Azure Table Storage

Thursday, September 19, 2013 by Rainer Stropek

Today I will do a session in a SQLPASS community meeting in Vienna about Windows Azure Table Storage. The audience mainly consists of SQL experts and I have been invited to describe similarities and differences of SQL Azure and Table Storage. In this article I want to summarize important links that people who are interested in the details could use. Additionally I publish the source code of the sample that I am going to show.

Note that the sample is built with the hot and new 2.1 release of Azure's .NET Storage Client. Therefore it can use nice features like async API, building queries with IQueryable, Shared Access Signatures for table storage, etc.

Here are some photos from the event (click to enlarge). You can view the photos in full resolution in my Flickr album.

2013-09-19 SQLPASS Vienna 1 2013-09-19 SQLPASS Vienna 1
Martina Grom, MVP for Office365, speaking about latest developments in the Office365 space.
2013-09-19 SQLPASS Vienna 2 2013-09-19 SQLPASS Vienna 2
Martina Grom, MVP for Office365, speaking about latest developments in the Office365 space.
2013-09-19 SQLPASS Vienna 3 2013-09-19 SQLPASS Vienna 3
Martin Pöckl (Microsoft Austria), one of the organizers of the SQLPASS user group meetings in Austria.
2013-09-19 SQLPASS Vienna 4 2013-09-19 SQLPASS Vienna 4 2013-09-19 SQLPASS Vienna 5 2013-09-19 SQLPASS Vienna 5 2013-09-19 SQLPASS Vienna 6 2013-09-19 SQLPASS Vienna 6 2013-09-19 SQLPASS Vienna 7 2013-09-19 SQLPASS Vienna 7 2013-09-19 SQLPASS Vienna 8 2013-09-19 SQLPASS Vienna 8 2013-09-19 SQLPASS Vienna 9 2013-09-19 SQLPASS Vienna 9

The Basics

Pricing

Deep Dive Content

Important 3rd Party Tools

Other Related Links

Source Code of the Sample

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Auth;
using Microsoft.WindowsAzure.Storage.RetryPolicies;
using Microsoft.WindowsAzure.Storage.Table;
using Microsoft.WindowsAzure.Storage.Table.Queryable;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Threading.Tasks;

namespace SqlPassTableStorageSample
{
    public class Order : TableEntity
    {
        public Order()
        {
            this.OrderDateTimeUtc = DateTimeOffset.UtcNow;
        }

        public Order(string partitionKey, string rowKey, DateTimeOffset timestamp, IDictionary<string, EntityProperty> props, string etag)
            : this()
        {
            this.PartitionKey = partitionKey;
            this.RowKey = rowKey;
            this.Timestamp = timestamp;
            this.ETag = etag;
            this.ReadEntity(props, null);
        }

        public string CustomerCode { get { return this.PartitionKey; } }
        public DateTimeOffset OrderDateTimeUtc { get; set; }
        public int TotalPrice { get; set; }
        public string EntityType { get { return this.RowKey.Substring(0, 1); } }

        public static string BuildRowKey(string orderId)
        {
            // Note that the generated row key for order header starts with the char "O".
            // Therefore we can easily filter for all order headers by checking the first
            // letter of the row key.
            return string.Format("O{0}_H", orderId);
        }
    }

    public class OrderLine : TableEntity
    {
        public OrderLine()
        {
        }

        public OrderLine(string partitionKey, string rowKey, DateTimeOffset timestamp, IDictionary<string, EntityProperty> props, string etag)
            : this()
        {
            this.PartitionKey = partitionKey;
            this.RowKey = rowKey;
            this.Timestamp = timestamp;
            this.ETag = etag;
            this.ReadEntity(props, null);
        }

        public string Product { get; set; }
        public int Amount { get; set; }
        public int ItemPrice { get; set; }
        public int TotalPrice { get; set; }
        public string EntityType { get { return this.RowKey.Substring(0, 1); } }

        public static string BuildRowKey(string orderId, int lineNumber)
        {
            // Note that the generated row key for order header starts with the char "L".
            // Therefore we can easily filter for all order lines by checking the first
            // letter of the row key.
            return string.Format("L{0}_{1:0000}", orderId, lineNumber);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            // Use the following code to connect to dev storage WITHOUT Fiddler.
            ////var account = CloudStorageAccount.DevelopmentStorageAccount;

            // Use the following code to connect to dev storage WITH Fiddler.
            var account = new CloudStorageAccount(
                new StorageCredentials("devstoreaccount1", "Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw=="),
                new Uri("http://ipv4.fiddler:10000/devstoreaccount1/", UriKind.Absolute),
                new Uri("http://ipv4.fiddler:10001/devstoreaccount1/", UriKind.Absolute),
                new Uri("http://ipv4.fiddler:10002/devstoreaccount1/", UriKind.Absolute));

            // Use the following code to connect to storage account in the cloud
            // and read credentials from app.config.
            ////var credentials = new StorageCredentials(
            ////    ConfigurationManager.AppSettings["AccountName"],
            ////    ConfigurationManager.AppSettings["AccountPassword"]);
            ////var account = new CloudStorageAccount(credentials, true);
            
            // Create and configure table client 
            var tableClient = account.CreateCloudTableClient();
            tableClient.RetryPolicy = new ExponentialRetry(TimeSpan.FromSeconds(1), 5);

            // Run demo scenarios
            ////DropTableAsync(tableClient).Wait();
            ////CreateTableWithBasicDataManipulationAsync(tableClient).Wait();
            ////CreateTableWithBatchOperationsAsync(tableClient).Wait();
            ////QueryScenariosAsync(tableClient).Wait();
            SharedAccessSignature(tableClient);

            // And now we are done
            Console.WriteLine("Done!");
            Console.ReadKey();
        }

        public static async Task DropTableAsync(CloudTableClient tableClient)
        {
            Console.WriteLine("\nScenario: Drop table");

            var table = tableClient.GetTableReference("Orders");
            if (await table.DeleteIfExistsAsync())
            {
                Console.WriteLine("Table deleted");
            }
        }

        public static async Task CreateTableWithBasicDataManipulationAsync(CloudTableClient tableClient)
        {
            Console.WriteLine("\nScenario: Create table with basic data manipulation");

            // Create a table.
            var table = tableClient.GetTableReference("Orders");
            await table.CreateIfNotExistsAsync();

            // Set some demo values
            var customerCode = "Rainer";
            var orderId = Guid.NewGuid().ToString();

            // Add a row with sample data
            var order = new Order()
            {
                PartitionKey = customerCode,                // we partition by customer
                RowKey = Order.BuildRowKey(orderId),    // guid order number
                TotalPrice = 100
            };
            await table.ExecuteAsync(TableOperation.Insert(order));
            Console.WriteLine("New order has been created");

            // Retrieve a specific row by partition key and row key
            var result = await table.ExecuteAsync(
                TableOperation.Retrieve<Order>(customerCode, Order.BuildRowKey(orderId)));
            var readOrder = (Order)result.Result;           // remember retrieved order, we will 
                                                            // need it later again
            Console.WriteLine("Retrieve successful; Order ID = {0}", readOrder.RowKey);

            // Update a row (this works)
            order.TotalPrice += 10;
            await table.ExecuteAsync(TableOperation.Replace(order));
            Console.WriteLine("Order has been updated");

            // Update a row (only works if ETag is specified as shown).
            // This demos optimistic concurrency. If you do not specify the etag *,
            // table storage will throw an exception as the underlying data row has
            // been altered since reading the order in readOrder.
            readOrder.TotalPrice += 20;
            readOrder.ETag = order.ETag = "*";
            await table.ExecuteAsync(TableOperation.Replace(readOrder));

            // Delete a row
            await table.ExecuteAsync(TableOperation.Delete(order));
            Console.WriteLine("Order has been deleted");
        }

        public static async Task CreateTableWithBatchOperationsAsync(CloudTableClient tableClient)
        {
            Console.WriteLine("\nScenario: Create table and execute a batch operation");

            // Create a table.
            // Note that we store order headers and order lines in the SAME table.
            // We even store all order data (headers and lines) or a customer in the
            // same partition. This makes it fast and easy to retrieve all order data
            // (headers and lines) of a single customer. Remember: Designing a NoSQL
            // database is all about designing for certain data access scenarios. Here
            // we design for a scenario in which it is common to read all order data
            // for a single customer knowing the customer's code (=partition key).
            var table = tableClient.GetTableReference("Orders");
            await table.CreateIfNotExistsAsync();

            // Set some demo values
            var customerCode = "Rainer";
            var orderId = Guid.NewGuid().ToString();
            var totalPrice = 0;

            // Add some order lines. Note that the order lines are not written to the 
            // table immediately. They are collected in a batch operation instead.
            // Later we can transfer the entire batch in a single REST request to
            // table storage. That saves money (lower number of transactions) and leads
            // to better performance (lower latency).
            var batch = new TableBatchOperation();
            for (int lineCount = 1; lineCount <= 10; totalPrice += lineCount * 5, lineCount++)
            {
                batch.Insert(new OrderLine()
                {
                    Product = "Bike",
                    Amount = lineCount,
                    ItemPrice = 5,
                    TotalPrice = lineCount * 5,
                    PartitionKey = customerCode,            // we partition by customer
                    RowKey = OrderLine.BuildRowKey(orderId, lineCount)
                                                            // build row key from order id and line number
                });
            }

            // Add order header
            batch.Insert(new Order()
            {
                TotalPrice = totalPrice,
                PartitionKey = customerCode,
                RowKey = Order.BuildRowKey(orderId)
            });

            // Note that the batch is executed in an atomic transaction
            await table.ExecuteBatchAsync(batch);

            Console.WriteLine("New order with order lines has been created");
        }

        public static async Task QueryScenariosAsync(CloudTableClient tableClient)
        {
            Console.WriteLine("\nScenario: Querying data (with new IQueryable feature from storage library 2.1)");

            // Check if table exists
            var table = tableClient.GetTableReference("Orders");
            if (!await table.ExistsAsync())
            {
                return;
            }

            // Create and execute a simple Linq query. Try running Fiddler to see the
            // REST requests resulting from that query. You will see that filtering is
            // done on the server, not on the client. Note that it does NOT
            // use async API. It blocks the calling thread.
            (from ol in table.CreateQuery<OrderLine>()
             where ol.PartitionKey == "Rainer" 
                && ol.RowKey.CompareTo("L") > 0 && ol.RowKey.CompareTo("LZ") < 0
             select ol)
                .AsTableQuery()
                .Execute()
                .ToList()
                .ForEach(ol => Console.WriteLine("Order Line {0} (Product: '{1}')", ol.RowKey, ol.Product));

            #region Async segmented query 
            // The following Linq query is executed in segments (i.e. result is delivered in
            // segments if result set is large). Segmented queries support async execution.
            var orderQuery = (from o in table.CreateQuery<Order>()
                              where o.PartitionKey == "Rainer"
                                && o.RowKey.CompareTo("O") > 0 && o.RowKey.CompareTo("OZ") < 0
                              select o).AsTableQuery();

            // Loop over all segments
            var continuationToken = new TableContinuationToken();
            do
            {
                // Get the next segment
                var segment = await orderQuery.ExecuteSegmentedAsync(continuationToken);

                // Iterate over all items in current segment
                foreach (var item in segment)
                {
                    Console.WriteLine("Order {0} (Customer: {1})", item.RowKey, item.CustomerCode);
                }

                // Store continuation token for retrieving the next segment
                continuationToken = segment.ContinuationToken;
            }
            while (continuationToken != null);
            #endregion

            // The following query is a dynamic query. It retrieves all order data
            // (order header and lines) of a customer. The helper function 
            // IterateResultAsync analyzes the result and generates Order or OrderLine
            // objects depending on the type of entity.
            var query = table.CreateQuery<DynamicTableEntity>()
                .Where(e => e.PartitionKey == "Rainer")
                .AsTableQuery();
            foreach (var item in await IterateResultAsync(query))
            {
                Console.WriteLine("Entity: {0}, CLR Type: {1}", item.RowKey, item.GetType().Name);
            }
        }

        private static async Task<IEnumerable<ITableEntity>> IterateResultAsync(TableQuery<DynamicTableEntity> query)
        {
            var result = new List<ITableEntity>();
            var continuationToken = new TableContinuationToken();

            // Add resolver to query in order to dynamically resolve result type
            var newQuery = query
                    .Resolve<DynamicTableEntity, ITableEntity>((pk, rk, ts, props, etag) =>
                    {
                        if (rk.StartsWith("L"))
                        {
                            // Generate order line entity
                            return new OrderLine(pk, rk, ts, props, etag);
                        }
                        else
                        {
                            // Generate order entity
                            return new Order(pk, rk, ts, props, etag);
                        }
                    });

            // Loop over all segments
            do
            {
                // Get the next segment
                var segment = await newQuery.ExecuteSegmentedAsync(continuationToken);
                
                // Iterate over all items in current segment
                foreach (var item in segment)
                {
                    result.Add(item);
                }

                // Store continuation token for retrieving the next segment
                continuationToken = segment.ContinuationToken;
            }
            while (continuationToken != null);

            return result;
        }

        public static void SharedAccessSignature(CloudTableClient tableClient)
        {
            Console.WriteLine("\nScenario: Querying data (with new IQueryable feature from storage library 2.1)");

            // Check if table exists
            var table = tableClient.GetTableReference("Orders");
            if (!table.Exists())
            {
                return;
            }

            // Setup a policy that allows querying in the next minute
            var policy = new SharedAccessTablePolicy()
            {
                SharedAccessStartTime = DateTimeOffset.UtcNow,
                SharedAccessExpiryTime = DateTimeOffset.UtcNow.AddMinutes(1),
                Permissions = SharedAccessTablePermissions.Query
            };

            // Limit access to order (not order lines) for customer Rainer
            var token = table.GetSharedAccessSignature(policy, null, "Rainer", "O", "Rainer", "OZ");
            Console.WriteLine("Token: {0}", token);

            // Read all order data for customer Rainer -> order lines must not be returned
            var restrictedClient = new CloudTableClient(
                tableClient.BaseUri,
                new StorageCredentials(token));
            var restrictedTable = restrictedClient.GetTableReference("Orders");
            foreach (var item in restrictedTable.ExecuteQuery(new TableQuery<DynamicTableEntity>())
                .GroupBy(e => e.RowKey.Substring(0, 1)))
            {
                Console.WriteLine("Type: {0}, Number of items: {1}", item.Key, item.Count());
            }
        }
    }
}
comments powered by Disqus

Rainer Stropek

Rainer Stropek

Co-founder, architect, developer

Bio

I am co-founder and CEO of the company software architects and have been serving this role since 2008. At software architects my team and I are developing the award-winning SaaS solution time cockpit. Previously, I founded and led IT consulting firms that worked in the area of developing software solutions based on the Microsoft technology stack.

In my work I focus on .NET development and software architecture. I have written some books and articles on C#, database development, Windows Azure, Windows 8 development, WPF, and Silverlight. Regularly I speak at conferences, do workshops and conduct trainings in Europe and the US. Since 2010 I have been MVP for Windows Azure.

I graduated the Higher Technical School Leonding (AT) for MIS with honors and hold a BSc (Hons) Computer Studies of the University of Derby (UK).

Contact

Twitter: @rstropek
Facebook
Google+
Xing
LinkedIn

Authors