How to filter data using Rest from Azure Storage

• Tweet
• Email

• Tweet
• Email

How to filter data using Rest from Azure Storage

Yesterday I was working on POC, How to get aggregate data from Azure Storage using REST call. My manager was silly and asked to get aggregated data using LINQ. I was shocked, how I can apply LINQ query in order to get data from Azure Storage, however Rest gives response using web service. I don’t want to involve myself into lame argument so I decided let work on it to optimization. After working lot on research about this, I found I can optimize data access from Azure Storage using filter on table.

Let’s start from scratch

Step 1: I created simple windows form based application for UI purpose. Looks like below

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

Step2: Submit button will not only validate URI and time using REST also parse the result from REST response.

But before digging into code let you know what I am exactly doing?

I have a sample Table in Azure storage and in my Test table there are two properties Key and Value and suppose key have key= “CPU” and value = 10; or key =”Memory” and value = 90 and so on (similar key) …so when i call using REST it response all data of CPU and Memory in XML but i want aggregate data from azure storage when i request call using REST. I don’t want that first get all data locally then perform aggregation. i want response like Key = “CPU” and Value = 1450 (aggregate sum of all value of corresponding CPU). (That is what I have to do and eventually I got this could not be possible right now because Azure did not allowed us to play there server Data using REST. So I put it this concept into your great Windows Azure idea)

The only way is to reduce overhead of large table response by filtering table.

Step 3: Now how to call azure storage using REST. For REST understanding, go to this link.

Because I am not going to describe, what is Rest Architecture. Anyway 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 the rest (Blob service, Queue Service and Table service). For my purpose I am using Table service.

The Table service provides structured storage in the form of tables. The 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 on code.

So first declare filed 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 azure storage account.

_Table is used for get the input of Sample table which already exist in azure storage account.

_Secret Key is used for authentication purpose. This is important filed because we will further use this key for signing the header in REST call.

Step 5: Inputs are validating by on 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 ready to jump in REST because now I will call REST API in order to get table data from the azure storage.

The Table service API is compliant with the REST API provided by ADO.NET Data Services. In table services, different Rest API operation can be performed (Query Tables, Create Tables, Merge Entities, Delete Tables, Insert Entities, Update Entities and Delete Entities). I am using Query Entities. 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 asked as input:

http://myaccount.table.core.windows.net/Tables

This URI is used to create 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 header for calling REST API. Request header parameters are below in table.

Request header	Description
Authorization	Required. Specifies the authentication scheme, account name, and signature. For more information, see Authentication Schemes.
Date or x-ms-date	Required. Specifies the Coordinated Universal Time (UTC) for the request. For more information, see Authentication Schemes.
x-ms-version	Optional. Specifies the version of the operation to use for this request. For more information, see Storage Service Versioning.

So let add required parameter in 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 code for the authentication purpose.

// 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)));

And Signature is in well defined format, so keep it 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 request to get the response from the azure storage.

HttpWebResponse response = (HttpWebResponse)request.GetResponse()

Now you can save this response into StreamReader object. REST will response all data into the XML format by default but you can also get data into the ATOM (similar to XML).

XML Response body is 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/&quot;datetime’2008-10-01T15%3A26%3A04.6812774Z’&quot;”>

<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, now once you get the response data at local end, you can play and show data.

But wait still I am not giving you solution about my problem. If there is large amount of data in Azure storage then it will make overhead in response of table data.

Till now REST API does not permit to get aggregate data by any mechanism but you can filter table data using specific URI to filtration. Filtration will help to get data in filtered form that mean if I want to perform any specific data on local then we do not need of request all data form azure storage table. Azure storage provides Query Syntax in URI.

Example: To return a single named table, specify that table as follows:

http://myaccount.table.core.windows.net/Tables(‘MyTable’)

For my purpose I used 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 used this link

So how I did apply 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 one more thing I want to discuss how I read XML and parse it into 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);

}

and bind this list with datasource. If there is repetative key as I stated earlier in problem statement then I thought to read this list and remove duplicate items in list and bind it to combobox. This is again a intresting subject and my roommate told me to do this by hashset but I found more intresting code to remove duplicate items in 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 duplicate items in list.

Conclusion: My problem is not solved but I get little help from filter query syntax. Anyway this is not enough to reduce my problem and looking forward some new things from Azure storage.

Popularity: 4%