Data Model for Reporting over Windows Azure Tables

One of my colleague tried implemented a browser history mechanism for MVC. Based on this data he would like to generate two simple reports:

1. Top 5 web addresses accessed by a given user per day
2. Top 5 web addresses accessed by all user

When the data store is implemented using SQL Azure, this problem can be resolved very simple.  The question that appeared here is: Can we implement a data store model using Windows Azure Table Services?
I will try to propose a possible data model that is using Windows Azure Table.
In the default implementation (using SQL Azure), there were 3 kind of information that is stored in the SQL tables:

• URL visited
• User
• Date

Because we don’t have an order by, count or a max function in a query over Windows Azure Table we need to think at a model that would help us with this. We will start with the first requirement:

Top 5 web addresses accessed by a given user per day
To be able to solve this problem we need a data model that permit us to refer to URLs for a given day and user.
Theoretically, we can have an unlimited number of tables in Windows Azure Tables (and we don’t need to pay for each table in part). Because of this we can have a different table for each day. In this way the cleanup mechanism will be extremely simple. Also, when we want to access historical, selecting a specific day will be very easily.
We already know that each table from Windows Azure Tables contains two fields that play the role of keys: Partition Key and Row Key. Partition key can be used with success when we have different items types saved in the same table. Entities could be grouped based on the user, because of this we can save in the Partition Key the user id. In this way we will be able to specify a specific date and user.
In the row key we can store the visited URL. Another property (column) will be needed to store how many times the URL was visited in a specific day. On the server we can define a mechanism that will add or increment a visited URL.
The downside of this solution is that we will need to make two different transactions when we want to increment the history counter. One transaction that brings the current counter for the given user and URL (if exist) and another one that update (insert) the counter value.
When we need to generate the TOP 5 for a given day per user we will have to load all URLs for specific user and order this by the counter value. We don’t need to forget that in Windows Azure Tables we don’t have support for Order By and Top N functions of a query – because of this we need to retrieve all the URLs that a user visited in a given day.

Top 5 web addresses accessed by all user

Option 1
To full fit this request we need to create another Windows Azure Table that will store the URL and the counter for each URL. To be able to support Top 5 not only for URL but also for domains we will store in the Partition Key the URL domain and the Row Key the rest of the URL path.
Another solution would be to have in the Partition Key the same values for all rows and in the Row Key the full URL. We don’t want to have in the Partition Key the URL (and in the Row Key the counter) because in this case we risk having a table that is fragmented – when a Windows Azure Table is too big, Windows Azure can split our table based on the Partition Key and move our spited table on different machines (this is not visible for consumer – is an implementation detail).
The biggest downside of this solution in the moment when we need to retrieve the top 5 most visited URLs. We will need to retrieve all the content from our table and calculate the top 5.

Option 2
Another possible solution for this problem is to have more than one table. Based on how many a URL was visited we will be stored in a specific table. For example we would have

• VisitedUrls1to100
• VisitedUrls101to1000
• VisitedUrls1001to10000
• VisitedUrls10001to100000

For example when the URL is in the first table (VisitedUrls1to100) and the counter value will reach 101, that the entity will be moved to the next table and so on. In this implementation we will have a big problem to find a URL to increment the counter. To optimize this we would need to use another table that would store the URL and the table name where our URL counter can be found (VisitedUrls1to100).
The good part of this implementation is in the moment when we need to calculate top 5 and we can retrieve only a part of the URLs.

These were the possible implementation that I see using Windows Azure Tables. I think that this problem is not suitable for Windows Azure Table and a relational database is better for this case.
I didn’t forget about a problem that needs to be solved here – concurrency. I will come with a post tomorrow.
Part 2

Read More

Trace information to Windows Azure Azure Tables

I saw that there are a lot of people that use tracing infrastructure that is offered by .NET framework to trace information in Windows Azure Tables. Basically, after we configure the configuration file, the only thing that we need to do is to call the Trace class and write data to it.

Trace.WriteLine(“Some  trace data”);
Trace.TraceWarning(“Some worning information”);
Trace.TraceError(“An error that appeared in the application.”);

We can do a log of thinks with this class. It is not something new.
In the configuration file of our application we need to add a new trace listener that is able to write all the trace information to Azure Tables.

<system.diagnostics>
    <trace>
      <listeners>
        <add type="Microsoft.WindowsAzure.Diagnostics.DiagnosticMonitorTraceListener, Microsoft.WindowsAzure.Diagnostics”  name="DiagToAzureTables"></add>
      </listeners>
    </trace>
  </system.diagnostics>

Next step is to add the listener to the trace listener collection each time when your application start. At this step I prefer to set auto-flush to true. In this way all the content will be send automatically to the trace and the risk to lose data when the machine is instable and crash is very low.

System.Diagnostics.Trace.Listeners.Add(myListener);
System.Diagnostics.Trace.AutoFlush = true;

On the internet you will find a lot of implementation of trace listener. The one that I prefer to use is the most common one. One of the implementation can be found in the following location http://www.wou.edu/~rvitolo06/WATK/Labs/WindowsAzureDebugging/Source/Assets/CS/AzureDiagnostics/TableStorageTraceListener.cs
If we check the Azure Tables of our account we will see that a new table was created with the following structure:

• PartitionKey - D10 of event timestamp >> 30
• RowKey - D19 of event timestamp
• EventTickCount – event timestamp
• Level - event type
• EventId – event id,
• Pid - event process id
• Tid - event thead id
• Message - event message

After a time you will observe that the EventTickCount can have different values, that are not orders based on the timeline. This is happen because the event timestamp is based on Stopwatch.GetTimestamp() method. This method don’t guaranty to us that will get a higher value in time. The purpose of Stopwatch is to measure time interval and calling GetTimestamp method return the current value of the counter (this is not correlated with the current date of the system) -

is correlated to the time when the system/process have been started.

Remarks: Base on the hardware configuration we can have a frequency tick per second or per nanoseconds. We can determine what is the frequency using StopWatch.Frequency.
If we want to order events based on the event tick count, we need to be aware that this will be valid only for events that were generated by the same processor (Pid). For different processor on the same machine the EventTickCount can be different.
Never try to order all the event of a Trace table from Windows Azure based on the EventTickCount. You can use it in combination with Pid. Also, the TimeStamp column of Azure Table store the time when the message was written to Azure Table and not the moment when the event was generated.

Read More

How to use Shared Access Signature with tables from Windows Azure

Until now we talk about how to use Shared Access Signature on blobs and queues from Windows Azure. Today we will see how we can use Shared Access Signature with tables from Windows Azure.
Using this feature we can give a limited access to a consumer to Windows Azure tables. In the next part we will look over what are the restrictions that can be made using Shared Access Signature:

• The first limitation that we can add is the time range. Based on the time a user can have a limited access to a table. For example a user can have access to a table only from 1st of July until the end of the week.
• We can limit what actions can be done using a Shared Access Signature. The following actions can be specified: add, update, delete and query.
• We can give access to a table or to only a part of a table. This limitation can be done using the partition key and row key. For example we can limit a consumer to have access only to partions keys from pkStart to pkEnd and from row rwStart to rwEnd.
• The last feature is one of the most powerful features that we have on Azure Tables from Shared Access Signature. We can limit access to only to a part of items from a table. For example we can give access only to items from table that have the partition key equal with some value. In this way the user will not be able to see the rest of the content.

We saw until now, how we can use it in theory. Let see now in practice how the code should look likes.

SharedAccessTablePolicy tablePolicy = new SharedAccessTablePolicy()
{
    Permissions = SharedAccessTablePermissions.Query
            | SharedAccessTablePermissions.Add,
    SharedAccessExpiryTime = DateTime.UtcNow + TimeSpan.FromHours(1)
};

At this step we created the policy. We can specify the permissions right and how long the consumer will have access to our table. The partition key and row key limitation can be done only at the last step when we generate the access token.

TablePermissions tablePermissions = new TablePermissions();
tablePermissions.SharedAccessPolicies.Add(
    "Client1",
    tablePolicy);
myTable.SetPermissions(tablePermissions);

In the above code we create the permissions with the given policy and added to our table. At this step is important to know that we can add more than one access policies. Each access policy have a unique name (in my case is “Client1”). Using this name we can change or remove the permissions on a table.

tableToken = myTable.GetSharedAccessSignature(
    new SharedAccessTablePolicy(),
    "Client1",
    10,
    0,
    19,
    100);

This was last step. At this step we generated the table token. We had to specify the name of the policy that we want to use for that token. In this example I specify that the consumer will have access only from the partition key 10 to 19 and from row keys 0 to 100. If I don’t want to limit the access of the user to the table (to have full access to the table) we need to specify null to these values.

tableToken = myTable.GetSharedAccessSignature(
    new SharedAccessTablePolicy(),
    "Client1",
    null,
    null,
    null,
    null);

The use of this access token is very easy and is 1 to 1 to how we have done for the queues.
Until now we saw how to work with Shared Access Signature with blobs, quest and table. The next post will see how we can change or remove an access signature that was already provided to a client.
Tutorials about Shared Access Signature:

1. Overview
2. How to use Shared Access Signature with tables from Windows Azure
3. How to use Shared Access Signature with blobs from Windows Azure
4. How to use Shared Access Signature with queues from Windows Azure
5. How to remove or edit a Shared Access Signature from Windows Azure 
6. Some scenarios when we can use Shared Access Signature from Windows Azure

Read More

What are the limitation of Windows Azure Tables

I’m pretty sure that a lot of you had heard about Windows Azure Tables. I already described how we can work with this Windows Azure Tables. You can find a series of posts about them on my blog in this link.
Today I want to talk about some limitation that we can have on Windows Azure Tables if we don’t use properly. A row in a table can store any kind of data that is serializable and one table can have more than one entity type saved. For example in the same table we can save Student entities and in the same time Dog entity and also Car entity. Each entity that is saved has 3 properties that need to exist all the time:
Partition key – based on this property we can group items from a specific table based on this partition. It is used for load balancing across storage nodes.
Row key – this a unique identifiers used to identify a unique row in a partition (the combination between partition key and row key form a unique key in the table).
Timestamp – the last modified time for a row.
The partition key exists with a scope, but a lot of people don’t use it properly. Because of this when they do intensive work on a table from Windows Azure some performance issues could appear.
Why? In this moment (July 2012), it seems that the maxim number of rows that can be processes per partition in a table is 500.
What does it means? If we have a table with only one partition key we will be able to process only 500 rows per second. If we have a table with 10 partition keys and we will be able to access maxim 5000 rows per second.
Some people could say: “OH, only 500 rows per second!”. What we don’t need to forget is that all this information’s are usually access from internet, not only from the datacenter. Because of this sending for example 500 rows per second is a lot of information and even on our side (on the client that consume the table) we could have problems with our bandwidth.
Also, we can use partition key in such a way that we could distribute the rows in a table in a manner that this limitation will not be reached.
For example if we have a table where we store audit data, we could group them based on the type. For example each partition key would represent a different audit type. A better solution is to have each audit time in a different table and the partition key could be used to group the audit data based on the type (per hour, per day, depends on how many items are logged per hour/day). Also, don’t forget that we will pay the same amount of money if we have one Azure table with 1.000.000 or 100 Azure tables with 10.000 rows each.
Don’t be afraid to split information in more than one tables. Having data in more than one table will help us to work with it more easily. Also use partition key, whenever is possible. We have them with a scope.
In conclusion, we don’t need to forget that we have a limited number of rows that we can process per second. Now are 500 rows per partition key, in the future maybe will be 10.000. What is important for us is to know that some limit exist and when we use table intensive, we need to remember that we need to distribute data across table or partitions.

Read More

Cum sa configuram Azure development storage pentru a putea fi accesat de catre toata echipa de dezvoltare

In mod default, cand pornim storage-ul local, acesta accesează 127.0.0.1. Dar ce ne facem daca avem o echipa de dezvoltare care doreste sa lucreze pe un storage comun. Prima posibilitatea este sa ne configuram un storage sus pe cloud, dar asta înseamna bani in plus si un lag, in cazul in care nu avem o conexiune foarte rapida.
O alta varianta este s schimbam configurarea pe mașinile de development, astfel incat acestea sa bata spre aceiasi adresa. Pentru a putea face acest lucru trebuie sa deschidem fisierul DSService.exe.config din directorul ~Program Files\Windows Azure SDK\v1.4\bin\devstore\DSService.exe.config. Sub nodul services o sa gasim lista de adrese pentru blobs, queue si tables.
Fiecare adresa a serviciului poate sa fie modificata, ca in exemplul de mai jos:

<services>
  <service name="Blob" url="http://10.123.1.9:10000/"/>
  <service name="Queue" url="http://10.123.1.9:10001/"/>
  <service name="Table" url="http://10.123.1.9:10002/"/>
</services>

Dupa ce am facut aceste modificări este nevoie sa dam repornim storage-ul pentru development.

Read More

Cum se face paginare pe tabelele din Windows Azure

Daca lucram cu tabelele din Windows Azure o sa avem nevoie sa facem paginare. Default nu avem nici un mecanism de paginare, dar acesta este usor de implementat.
In postul anterior am descris modul prin care se pot obtine mai mult de 1000 de entitati dintr-un tabel. Pe baza mecanismului prezentat anterior putem sa parcurgem datele din tabel in format paginat.
Trebuie sa ne folosim de metoda Take din LINQ, care din fericire este suportata si de LINQ folosit pentru tabelele din Windows Azure( nu trebuie sa uitam ca doar o parte din LINQ este implementat pentru Windows Azure in acest moment). Apeland metoda Take putem sa obtinem doar o parte din entitati care ne sunt returnate de catre query.
Folosindu-ne de token-urile nextPartitionToken si nextRowToken putem sa luam urmatoarele x elemente incepand de pe o anumita pozitie.
Mai jos gasiti o implementare minimala a unui mecanism de paginare.

    public class Pagination<TItem>
        where TItem : class
    {
        private const string NoValue = "novalue";

        private string _nextPartitionToken;
        private string _nextRowToken;
        private DataServiceQuery<TItem> _dataServiceQuery;
        private List<TItem> _items;
        private int _pageSize;

        /// <summary>
        /// TRUE when another page exist.
        /// </summary>
        public bool HasNextPage { get { return _nextPartitionToken != NoValue && !string.IsNullOrEmpty(_nextPartitionToken); } }

        /// <summary>
        /// Gets all the items of the current page.
        /// </summary>
        public List<TItem> Items { get { return _items; } }

        /// <summary>
        /// The base contructor.
        /// </summary>
        /// <param name="dataServiceQuery">The query that is executed on Azure tables.</param>
        /// <param name="pageSize">The size of the page.</param>
        public Pagination(DataServiceQuery<TItem> dataServiceQuery, int pageSize)
        {
            _nextPartitionToken = NoValue;
            _pageSize = pageSize;
            _dataServiceQuery = dataServiceQuery.Take(_pageSize);
        }

        /// <summary>
        /// Load the next page.
        /// </summary>
        public void NextPage()
        {
            if(string.IsNullOrEmpty(_nextPartitionToken))
            {
                return;
            }

            if (_nextPartitionToken!=NoValue)
            {
                _dataServiceQuery.AddQueryOption("NextPartitionKey", _nextPartitionToken);
                _dataServiceQuery.AddQueryOption("NextRowKey", _nextRowToken);
            }

            //Get the result.
            var result = _dataServiceQuery.Execute();
            _items = result.ToList();

            //Get tokens for the next page.
            QueryOperationResponse queryOperationResponse = (QueryOperationResponse)result;
            queryOperationResponse.Headers.TryGetValue("x-ms-continuation-NextPartitionKey", out _nextPartitionToken);
            queryOperationResponse.Headers.TryGetValue("x-ms-continuation-NextRowKey", out _nextRowToken);
        }
    }

Ceea ce lipseste este calcularea numarului total de pagini. Pentru a putea face acest lucru este nevoie sa executam un query care sa returneze numarul total de entitati de pe un anumit tabel.
Pentru ca utilizatorul sa aiba optiunea de a putea accesa orice pagina este nevoie ca in constructor sa executam cate un query pentru fiecare pagina in parte si sa salvam token-urile.

Read More

Cum sa obtii mai mult de 1000 de entitati dintr-un query pe tabelele din Windows Azure

Pentru a putea obtine date din tabelele din Windows Azure putem sa folosim DataServiceQuery. Acesta suporta LINQ, astfel ne va fi foarte usor sa filtram continutul.

DataServiceQuery<Person> queryPersons =
     ApplicationServiceContext
     .PersonEntryTable
     .Where( p => p.Age >= 18 );
var result = queryPersons.Execute();

In variabila result o sa avem toate persoanele care au varsta mai mare sau egala cu 18.
Problema apare cand rezultatul contine mai mult de 1000 de entitati. Un query pe tabele din cloud va returna maxim 1000 de rezultate. In cazul in care acest numar este depasit acesta va contine doua token-uri pe baza carora se pot obtine si celelate entitati din query.
Cele doua token-uri reprezinta token-ul pentru Partition Key si Row Key. Se pot obtine din heather-ul rezultatului:

var resultQOR = (QueryOperationResponse)result;
string nextPartitionToken = null;
string nextRowToken = null;
resultQOR.Headers.TryGetValue("x-ms-continuation-NextPartitionKey", out nextPartitionToken);
resultQOR.Headers.TryGetValue("x-ms-continuation-NextRowKey", out nextRowToken);

Cele doua token-uri se pot trasmite mai departe la query pentru a obtine urmatorul set de rezultate.

queryPersons = queryPersons
            .AddQueryOption("NextPartitionKey", nextPartitionToken)
            .AddQueryOption("NextRowKey", nextRowToken);
var rezult2 = queryPersons.Execute();

In result2 am obtinut urmatoarele 1000 entitati din tabel.
Folosind acest mecanism putem sa implementam si un mecanism de paginare.

Read More

Propietati de tip array in tabelele din Windows Azure (part 2)

Partea 1 http://vunvulearadu.blogspot.com/2011/02/propietati-de-tip-vector-in-tabele-din.html
Continuare:
In ultimul meu post am descris trei variante prin care se poate persista o entitate care contine o propietate de tip array( lista) intr-un tabel. Ultima varianta, care la prima vedere pare cea mai buna era folosind DataServiceContext.
Aceasta solutie este viabila, atata timp cat nu trebe sa facem un query direct pe tabel. In acest moment tabelel din Windows Azure nu suporta pe query expresii de genu:

• Contains
• Count
• Select

Din aceasta cauza, daca avem o propietate de tip lista nu o sa putem avea un query de genul:

(item => item.ListaElemente.Contains(id)

Din aceasta cauza, pentru a putea face un astfel de query ar fi nevoie sa incarcam toate elementele din tabel si sa le procesam din cod, cea ce nu tocmai optim.
Dar nici prima varianta nu se poate folosii din pacate( lista sa fie stocata ca si un string cu un caracter despartitor), din cauza ca urmatorul query nu este suportat:

(item => item.ListaElementeSz.Contains(id.ToString())

Nu putem sa avem un query care sa foloseasca metoda Contains() pe string.

Singura varianta ramasa este a 2-a, folosirea unei tabele intermediare .

Read More

Propietati de tip array in tabelele din Windows Azure( part 1)

Am inceput sa folosesc din plin tabelele din Windows Azure. Orice fel de date se pot salva acolo, atata timp cat sunt serializabile.
Cu ajutorul lor am facut maparea unor entitati fara probleme. Am ajuns in momentul in care intre doua entitati A si B aveam o relatie n la m, iar intre alte doua entitati 1 la n. Fara nici o problema am scris:

public List<Guid> ItemIds { get; set; }

Am continuat sa scriu codul in continuare fara nici o problema, iar intr-un final am ajuns la unit-teste unde am avut o surpriza nu tocmai placuta. Cea ce am ignorat de la bun inceput a fost faptul ca tabele din Windows Azure nu sunt O/R, ele nu stiu sa stocheze in mod default o lista.
Greseala mea, fapta fiind deja comisa a trebuit sa caut solutii la aceasta problema. Am gasit urmatoarele solutii:

• o propietate de tip string care sa stocheze id-urile despartite printr-un caracter special. Solutia destul de viabila, daca tinem cont de faptul ca obiectele care se stocheaza pe tabele nu ajung sa fie folosite pe partea logica si/sau client side;

• o tabela intermediara care sa stocheze relatiile intre cele doua obiecte. Pare o solutie bunicica, dar ma sperie putin ideea de a crea aceste tabele intermediare;

• ultima solutie gasita care m-a incantat cel mai mult este prin folosirea lui DataServiceContext. Acesta ne permite sa controlam momentul in care o entitate se salveaza sau se incarca dintr-un tabel si sa modificam continutul care se salveaza.

O sa detaliez mai jos aceasta varianta. Windows Azure salveaza toate aceste obiecte din tabele in format Atom Feed. Ce inseamna asta pentru noi? Pai, noi putem controla cea ce se salveaza si sa modificam continutul care ajunge sa fie salvat pe "tabele". Asa cum putem controla serializarea si deserializarea, cu ajutorul la DataServiceContext putem controla valoarea care se persista in tabele. Avem la dispozitie doua "metode":

• ReadingEntity: pentru citirea din tabel;
• WritingEntity: pentru scrierea in tabel;

Un exemplu de implementare puteti sa gasiti aici:
http://convective.wordpress.com/2009/12/30/entities-in-azure-tables/
In momentul acesta am de ales intre ultimele doua variante. Cred ca o sa aleg ultima varianta din cauza si altor neajunsuri pe care le are in momentul de fata Windows Azure la capitolul tabele( modul in care se genereaza un query).
Partea a 2-a: http://vunvulearadu.blogspot.com/2011/02/propietati-de-tip-array-in-tabelele-din.html

Read More

Operatii CRUD pe tables in Windows Azure

Se da obiectul de tip Point, cu doua propietati de tip int X si Y si un id tot de tip int, unde point implementeaza TableServiceEntity.

Initalizare:

//Initializare cont (se poate incarca din fisierul de configurare).
StorageAccountInfo account = ...
TableStorage tableStorage = TableStorage.Create(account);
//Creare tabel cu un anumit nume.
tableStorage.TryCreateTable("Point");

Salvare:

Point point = new Point();
...
TableStorageDataServiceContext context = table.GetDataServiceContext();
context.AddObject(point);
//Doar pe save se salveaza in tabel obiectul nostru.
context.Save();

Interogare:

TableStorageDataServiceContext context = table.GetDataServiceContext();
//Trebuie specificat numele tabelului. Un tabel poate sa contina orice tip de obiecte, chiar si de tipuri diferite.
var query = context .CreateQuery("Point").Where(item => item.Id == 10);
IEnumerable resultList = new TableStorageDataServiceQuery((DataServiceQuery)query).ExecuteAll();

Stergere:

//Dupa ce prin avem obiectul de tip Point( ajunge sa fie un obiect de tip Point care sa aibe partition key si row key corespunzator.
TableStorageDataServiceContext context = table.GetDataServiceContext();
context.DeleteObject(point);
context.SaveChanges()

Read More