Getting data from Azure Storage

• Download source code - 16.2 KB

Introduction

I was working on POC and trying to figure out how to get aggregate data from Azure Storage using a REST call. My manager asked me to get aggregated data using LINQ. I was shocked. How could I apply a LINQ query in order to get data from Azure Storage? REST gives response using a Web Service. I din’t want to involve myself in lame arguments, so I decided to work on it. After doing a lot of research about this, I found that I could optimize data access from Azure Storage using a filter on a table.

Let’s start from scratch.

The solution

Step 1:  I created a simple Windows Forms based application for UI purposes. It looks like below:

I divided the form in 3 sections: Data Input, Validate to Azure, and Result.

Step 2:  The Submit button will not only validate the URI and time using REST, but will also parse the result from the REST response.

But before digging into the code, let us see what I am doing exactly.

I have a sample table in Azure storage, and in my test table, there are two properties: Key and Value. Suppose we have key= "CPU" and value = 10, key ="Memory" and value = 90, and so on (similar key) ... When I call using REST, it responds with all data of CPU and Memory in XML, but I want aggregate data from Azure storage when I request using REST. I don’t want to first get all the data locally and then perform an aggregate. I want response like Key = "CPU" and Value = 1450 (the aggregate sum of all values corresponding to CPU). (That is what I have to do, and eventually I found this is not possible right now because Azure does not allow us to play with the server data using REST. So I put this concept together as a a great Windows Azure idea.)

The only solution is to reduce the overhead of a large table response by filtering the table.

Step 3: Now, how do we call Azure storage using REST? For more info on REST, go to this link.

I am not going to describe the REST architecture here. All storage services are accessible via REST APIs. Storage services may be accessed from within a service running in Windows Azure, or directly over the Internet from any application that can send an HTTP/HTTPS request and receive an HTTP/HTTPS response. You can access three services from REST (Blob service, Queue Service, and Table service). For my purpose, I am using the Table service.

Table service provides structured storage in the form of tables. Table service supports a REST API that is compliant with the ADO.NET Data Services REST API. Developers may also use the .NET Client Library for ADO.NET Data Services to access the Table service. In Table service within a storage account, a developer may create named tables. Tables store data as entities. An entity is a collection of named properties and their values, similar to a row. Tables are partitioned to support load balancing across storage nodes. Each table has as its first property a partition key that specifies the partition an entity belongs to. The second property is a row key that identifies an entity within a given partition. The combination of the partition key and the row key forms a primary key that identifies each entity uniquely within the table.

Step 4: Enough discussion on theory, let dig into the code.

First declare the fields for input data (in my form based application):

private string _Account;
private string _Table;
private string _Secret;
private List<string> result;
private List<string> value;
public string key = string.Empty;

• _Account is used for get the input of user's Azure storage account.
• _Table is used for get the input of the sample table which already exists in the Azure storage account.
• _Secret Key is used for authentication purpose. This is an important filed because we will further use this key for signing the header in the REST call.

Step 5: Inputs are validated by the Submit button.

private void btnSubmit_Click(object sender, EventArgs e)
{
    _Secret = tbSharedKey.Text;
    _Account = tbAccountName.Text;
    _Table = tbTblName.Text;
    
    string xml = string.Empty;
    ListTables(key); // this function will show entitis of table
}

Step 6: Now we are ready to jump into REST. We will now call the REST API in order to get the table data from the Azure storage.

The Table service API is compliant with the REST API provided by the ADO.NET Data Services. In Table services, different REST API operations can be performed (Query Tables, Create Tables, Merge Entities, Delete Tables, Insert Entities, Update Entities, and Delete Entities). I am using Query Entities here. For details of other operations, you can use this link.

The Query Entities request may be constructed as follows. Replace myaccount with the name of your storage account and Tables with your sample table which you use as input: http://myaccount.table.core.windows.net/Tables.

This URI is used to create a request in GET format along with Request Header. So my URI will look like:

uri = @"https://" + _Account + ".table.core.windows.net/" + Resource;
HttpWebRequest request= (HttpWebRequest)HttpWebRequest.Create(uri);
            
request.Method = "GET";
request.ContentLength = 0;

But we need to add a header for calling the REST API. Request header parameters are shown in the table:

So, let's add the required parameter in the header:

request.Headers.Add("x-ms-date", DateTime.UtcNow.ToString("R", 
        System.Globalization.CultureInfo.InvariantCulture));

Note down: “x-ms-date” specifies the date in UTC format (mandatory).

Now I will sign the header in the code for authentication purposes.

// Add the Authorization header to the request
request.Headers.Add("Authorization", authH);

Code of authorization is:

string authH = "SharedKey " + _Account + ":" + 
  System.Convert.ToBase64String(hasher.ComputeHash(
  System.Text.Encoding.UTF8.GetBytes(signature)));

Signature is in a well defined format, keep that in mind.

// Now sign the request
string signature = "GET\n";

// Content-MD5
signature += "\n";

// Content-Type
signature += "\n";

// Date
signature += request.Headers["x-ms-date"] + "\n";

// Canonicalized Resource
// remove the query string
int q = Resource.IndexOf("?");
if (q > 0) Resource = Resource.Substring(0, q);

// Format is /{0}/{1} where 0 is name
// of the account and 1 is resources URI path
signature += "/" + _Account + "/" + Resource;

Now, use the request to get the response from Azure storage.

HttpWebResponse response = (HttpWebResponse)request.GetResponse()

You can now save this response into a StreamReader object. REST will respond with all data in XML format by default, but you can also get data in ATOM (similar to XML).

The XML response body looks like:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="http://myaccount.tables.core.windows.net/" 
      xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
      xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
      xmlns="http://www.w3.org/2005/Atom">
  <title type="text">Customers</title>
  <id>http://myaccount.tables.core.windows.net/Customers</id>
  <updated>2008-10-01T15:26:13Z</updated>
  <link rel="self" title="Customers" href="Customers" />
  <entry m:etag="W/"datetime'2008-10-01T15%3A26%3A04.6812774Z'"">
  <id>http://myaccount.tables.core.windows.net/Customers(
                       PartitionKey='Customer03',RowKey='')</id>
    <title type="text"></title>
    <updated>2008-10-01T15:26:13Z</updated>
    <author>
      <name />
    </author>
    <link rel="edit" title="Customers" 
       href="Customers (PartitionKey='Customer03',RowKey='')" />
    <category term="myaccount.Customers" 
       scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <content type="application/xml">
      <m:properties>
        <d:PartitionKey>Customer03</d:PartitionKey>
        <d:RowKey></d:RowKey>
        <d:Timestamp m:type="Edm.DateTime">2008-10-01T15:26:04.6812774Z</d:Timestamp>
        <d:Address>123 Lakeview Blvd, Redmond WA 98052</d:Address>
        <d:CustomerSince m:type="Edm.DateTime">2008-10-01T15:25:05.2852025Z</d:CustomerSince>
        <d:Discount m:type="Edm.Double">10</d:Discount>
        <d:Rating m:type="Edm.Int32">3</d:Rating>
      </m:properties>
    </content>
  </entry>
</feed>

That’s it from my side. Once you get the response data at the local end, you can play with it and display data.

But wait, I have still not given you the solution for the problem. If there is a large amount of data in Azure storage, then it will create an overhead in the response of the table data.

Till now, REST API did not permit getting aggregate data by any mechanism, but you can filter table data using a specific URI for filtration. Filtration will help to get data in filtered form. That means, if I want to get any specific data locally, then I do not need to request all data form the Azure storage table. Azure storage provides Query syntax for URIs.

For example: to return a single named table, specify the table as follows: http://myaccount.table.core.windows.net/Tables('MyTable').

For my purpose, I used a filter query syntax: http://myaccount.table.core.windows.net/Customers()?$filter=LastName%20eq%20'Smith'%20and%20FirstName%20eq%20'John'.

To get details on more query syntax, use this link.

Here is how I applied a filter in my code:

if (Key == "")
{
    uri = @"https://" + _Account + ".table.core.windows.net/" + Resource;
}
else
{
    uri = @"https://" + _Account + ".table.core.windows.net/" + 
          Resource +"?$filter=Key%20eq%20"+"'"+Key+"'";
}

It is simple !! Well, there is one more thing I want to discuss: how I read the XML and parsed it into the relevant answer section.

XDocument xdoc = XDocument.Parse(xml);
doc.LoadXml(xml);
XmlNodeList nodes = doc.GetElementsByTagName("d:Key");// Response XML attrib
List<string> la = new List<string>();
foreach (XmlNode n in nodes)
{
    la.Add(n.InnerText);
}

I bind the list with a data source. If there is repetitive key as I stated earlier in the problem statement, then I read the list and remove the duplicate items in the list and bind to the combobox. This is again an interesting subject, and my roommate suggested using a hashset, but I found it more interesting to remove the duplicate items in the list.

static List<string> removeDuplicates(List<string> la)
{
    Dictionary<string, int> uniqueStore = new Dictionary<string, int>();
    List<string> finalList = new List<string>();

    foreach (string currValue in la)
    {

        if (!uniqueStore.ContainsKey(currValue))
        {

            uniqueStore.Add(currValue, 0);
            finalList.Add(currValue);

        }

    }
    return finalList;
}

Finally, I bind this list and remove the duplicate items in the list.

Conclusion

My problem is still not solved, but I get a little help from the filter query syntax. This is not enough to solve my problem, but I am looking forward to some new things from Azure storage.