Uploading Entities for Storage in Azure Tables

One of the primary considerations when selecting a Platform as a Service (PaaS) cloud provider is the ease, speed, accuracy and reliability of uploading typical entities to persistent storage, as well as the cost of data ingress/egress and long-term storage.

Updated 4/8/2009 3:50 PM PDT: Performance and transaction topics.

Windows Azure, Amazon Web Service’s SimpleDB, and the Google App Engine provide low-cost, highly scalable storage in Entity-Attribute-Value (EAV) tables. To accommodate the widest range of platforms and programming languages for creating, querying and retrieving data from cloud storage, most cloud storage application programming interfaces (APIs) use Representational State Transfer (REST) methods with HTTP as the data transfer protocol and Atom Syndication Format (Atom), Atom Publication Protocol (APP, AtomPub) or a variation of these two Internet standards as the data format on the wire.

The following analysis is adapted from Chapter 7, “Optimizing the Scalability and Performance of Azure Tables,” of my forthcoming Cloud Computing with the Microsoft Azure Services Platform book for WROX.

ADO.NET Data Services HTTP Request and Response Messages in AtomPub Format

Azure Data Services use the HTTP POST method to insert entities in tables. Following is an example of the AtomPub POST Request message to add an entity from the Northwind sample database’s Orders table to an Azure OrderTable:

POST /OrderTable HTTP/1.1
User-Agent: Microsoft ADO.NET Data Services
x-ms-date: Wed, 08 Apr 2009 22:49:49 GMT
Authorization: SharedKeyLite oakleaf3:f3o42-----RedactedKey-----6qT6r9AiYnkRS80=
Accept: application/atom+xml,application/xml
Accept-Charset: UTF-8
DataServiceVersion: 1.0;NetFx
MaxDataServiceVersion: 1.0;NetFx
Content-Type: application/atom+xml
Host: oakleaf3.table.core.windows.net
Content-Length: 1383
Expect: 100-continue

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entry 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 />
  <updated>2009-04-08T22:49:49.8086948Z</updated>
  <author>
    <name />
  </author>
  <id />
  <content type="application/xml">
    <m:properties>
      <d:CustomerID>BSBEV</d:CustomerID>
      <d:EmployeeID m:type="Edm.Int32">4</d:EmployeeID>
      <d:Freight m:type="Edm.Double">2.17</d:Freight>
      <d:OrderDate m:type="Edm.DateTime">1998-03-11T00:00:00</d:OrderDate>
      <d:OrderID m:type="Edm.Int32">10943</d:OrderID>
      <d:PartitionKey>2147472704</d:PartitionKey>
      <d:RequiredDate m:type="Edm.DateTime">1998-04-08T00:00:00</d:RequiredDate>
      <d:RowKey m:null="false" />
      <d:ShipAddress>Fauntleroy Circus</d:ShipAddress>
      <d:ShipCity>London</d:ShipCity>
      <d:ShipCountry>UK</d:ShipCountry>
      <d:ShipName>B's Beverages</d:ShipName>
      <d:ShipPostalCode>EC2 5NT</d:ShipPostalCode>
      <d:ShipRegion m:null="true" />
      <d:ShipVia m:type="Edm.Int32">2</d:ShipVia>
      <d:ShippedDate m:type="Edm.DateTime">1998-03-19T00:00:00</d:ShippedDate>
      <d:Timestamp m:type="Edm.DateTime">0001-01-01T00:00:00</d:Timestamp>
    </m:properties>
  </content>
</entry>

Azure Tables support a simplified version of the ADO.NET Data Services (formerly the “Astoria Project”) API for create, retrieve, update, and delete (CRUD) operations by means of the Windows Azure SDK’s sample ClientServices class library. The create operation with the POST method returns a confirmation of the data inserted, such as that shown below:

HTTP/1.1 201 Created
Cache-Control: no-cache
Content-Type: application/atom+xml;charset=utf-8
ETag: W/"datetime'2009-04-08T22%3A48%3A37.9913381Z'"
Location: http://oakleaf3.table.core.windows.net/OrderTable(PartitionKey='2147472704',RowKey='')
Server: Table Service Version 1.0 Microsoft-HTTPAPI/2.0
x-ms-request-id: 05b91086-1747-42d6-98ed-1ad6c85537bd
Date: Wed, 08 Apr 2009 22:48:37 GMT
Content-Length: 1774

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entry xml:base="http://oakleaf3.table.core.windows.net/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:etag="W/&quot;datetime'2009-04-08T22%3A48%3A37.9913381Z'&quot;" xmlns="http://www.w3.org/2005/Atom">
  <id>http://oakleaf3.table.core.windows.net/OrderTable(PartitionKey='2147472704',RowKey='')</id>
  <title type="text"></title>
  <updated>2009-04-08T22:48:38Z</updated>
  <author>
    <name />
  </author>
  <link rel="edit" title="OrderTable" href="OrderTable(PartitionKey='2147472704',RowKey='')" />
  <category term="oakleaf3.OrderTable" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
  <content type="application/xml">
    <m:properties>
      <d:PartitionKey>2147472704</d:PartitionKey>
      <d:RowKey></d:RowKey>
      <d:Timestamp m:type="Edm.DateTime">2009-04-08T22:48:37.9913381Z</d:Timestamp>
      <d:CustomerID>BSBEV</d:CustomerID>
      <d:EmployeeID m:type="Edm.Int32">4</d:EmployeeID>
      <d:Freight m:type="Edm.Double">2.17</d:Freight>
      <d:OrderDate m:type="Edm.DateTime">1998-03-11T00:00:00</d:OrderDate>
      <d:OrderID m:type="Edm.Int32">10943</d:OrderID>
      <d:RequiredDate m:type="Edm.DateTime">1998-04-08T00:00:00</d:RequiredDate>
      <d:ShipAddress>Fauntleroy Circus</d:ShipAddress>
      <d:ShipCity>London</d:ShipCity>
      <d:ShipCountry>UK</d:ShipCountry>
      <d:ShipName>B's Beverages</d:ShipName>
      <d:ShipPostalCode>EC2 5NT</d:ShipPostalCode>
      <d:ShipVia m:type="Edm.Int32">2</d:ShipVia>
      <d:ShippedDate m:type="Edm.DateTime">1998-03-19T00:00:00</d:ShippedDate>
    </m:properties>
  </content>
</entry>

Message Bloat for Order Entities with the AtomPub Wire Format

Notice that the payload for the HTTP 201 Response message is 1.3 times the size of the Request message’s payload. The combined size of the two payloads is 3,157 plus 973 header bytes for a relational table record with a UTF-8 size of about 159 bytes. The update messages are (4130 – 159) * 100/4130 = 96.2% overhead, assuming that the return payload is discarded. In other words, the Request and Response messages are about 26 times the size of uncompressed relational data. The added overhead contributes significantly to increased data ingress charges.

Message Bloat for OrderDetail Entities with the AtomPub Wire Format

Following is an HTTP Request message for a typical OrderDetail entity:

POST /OrderDetailTable HTTP/1.1
User-Agent: Microsoft ADO.NET Data Services
x-ms-date: Thu, 09 Apr 2009 21:02:25 GMT
Authorization: SharedKeyLite oakleaf3:uG/e6awcKFfuy-----RedactedKey-----pp4k+j0msRX0=
Accept: application/atom+xml,application/xml
Accept-Charset: UTF-8
DataServiceVersion: 1.0;NetFx
MaxDataServiceVersion: 1.0;NetFx
Content-Type: application/atom+xml
Host: oakleaf3.table.core.windows.net
Content-Length: 886
Expect: 100-continue

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entry 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 />
  <updated>2009-04-09T21:02:25.7957487Z</updated>
  <author>
    <name />
  </author>
  <id />
  <content type="application/xml">
    <m:properties>
      <d:Discount m:type="Edm.Double">0.2</d:Discount>
      <d:OrderID m:type="Edm.Int32">11077</d:OrderID>
      <d:PartitionKey>2147472570</d:PartitionKey>
      <d:ProductID m:type="Edm.Int32">2</d:ProductID>
      <d:Quantity m:type="Edm.Int32">24</d:Quantity>
      <d:RowKey>00</d:RowKey>
      <d:Timestamp m:type="Edm.DateTime">0001-01-01T00:00:00</d:Timestamp>
      <d:UnitPrice m:type="Edm.Double">19</d:UnitPrice>
    </m:properties>
  </content>
</entry>

and the corresponding 201 Response message:

HTTP/1.1 201 Created
Cache-Control: no-cache
Content-Type: application/atom+xml;charset=utf-8
ETag: W/"datetime'2009-04-09T21%3A01%3A16.700813Z'"
Location: http://oakleaf3.table.core.windows.net/OrderDetailTable(PartitionKey='2147472570',RowKey='00')
Server: Table Service Version 1.0 Microsoft-HTTPAPI/2.0
x-ms-request-id: f2debcd6-1614-4380-8972-b76f54a73cd1
Date: Thu, 09 Apr 2009 21:01:16 GMT
Content-Length: 1347

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<entry xml:base="http://oakleaf3.table.core.windows.net/" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" m:etag="W/&quot;datetime'2009-04-09T21%3A01%3A16.700813Z'&quot;" xmlns="http://www.w3.org/2005/Atom">
  <id>http://oakleaf3.table.core.windows.net/OrderDetailTable(PartitionKey='2147472570',RowKey='00')</id>
  <title type="text"></title>
  <updated>2009-04-09T21:01:16Z</updated>
  <author>
    <name />
  </author>
  <link rel="edit" title="OrderDetailTable" href="OrderDetailTable(PartitionKey='2147472570',RowKey='00')" />
  <category term="oakleaf3.OrderDetailTable" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
  <content type="application/xml">
    <m:properties>
      <d:PartitionKey>2147472570</d:PartitionKey>
      <d:RowKey>00</d:RowKey>
      <d:Timestamp m:type="Edm.DateTime">2009-04-09T21:01:16.700813Z</d:Timestamp>
      <d:Discount m:type="Edm.Double">0.2</d:Discount>
      <d:OrderID m:type="Edm.Int32">11077</d:OrderID>
      <d:ProductID m:type="Edm.Int32">2</d:ProductID>
      <d:Quantity m:type="Edm.Int32">24</d:Quantity>
      <d:UnitPrice m:type="Edm.Double">19</d:UnitPrice>
    </m:properties>
  </content>
</entry>

The two payloads total 2,233 bytes and headers total 886 bytes. The numeric values occupy 47 bytes, which leads to an overhead of (3119 –47)*100/3119 = 98.5% or a message to payload ratio of 66:1. Providing an industry-standard API results in substantially increased data ingress charges.

The Affect of PartitionKey and RowKey Values on Scalability

For this example, each OrderTable entity has a unique PartitionKey value and RowKey zero-length string value to achieve maximum scalability by allowing distribution of entities over a potentially large number of Azure nodes. Default PartitionKey values for this example are 10-digit padded string representations of the maximum int value less the OrderID value. This combination produces the equivalent of a descending index on the primary key, which enables use of the LINQ Take(n) function to return the most recent n orders.

Associated OrderDetailsTable entities carry the parent order’s PartitionKey key value plus a zero-based line item number as the RowKey value to produce a unique object identifier (the equivalent of a relational table’s primary key.) Using the same PartitionKey value for Order and OrderDetail entities assures that a parent and its child entities are stored on the same nodes. (Currently, the original data and two replicas would be stored on three nodes in different failure domains.)

After Microsoft releases geo-location features for Azure, you’ll be able to specify the data center in which to store entities that are coded geographically, such as by state, province or region. The first center to support Azure is NorthwestUS (Quincy, WA). SouthwestUS (San Antonio (Westover Hills), TX) is expected online with Azure in 2009; it opened in September 2008.

Measuring Data Upload Performance with ADO.NET Data Services and AtomPub

Following is a screen capture of the form for conducting timing tests on create operations for OrderTable entities, with and without corresponding OrderDetailTable entities (click the image below for a full-size capture):

Loading the most recent 100 Orders with their 232 Order Details entities requires about 67.75 seconds. The time shown in the preceding screen capture is that for retrieving a single order with four line items. The following table shows the number of seconds to upload or delete 100 Orders, without and with line items (Order Details)

	Upload (Alone)	Delete(Alone)	Upload (with Details)	Delete (with Details)
HTTP (clear)	18.86	13.55	67.75	70.92
HTTPS (TLS)	20.80	13.57	69.18	74.40

What’s interesting about the above data is the longer time to delete than create Order entities with their OrderDetail entities. Client deletions execute a GET request for the last Order entity, which includes PrimaryKey and OrderID values. The client then issues a GET /OrderDetailTable()?$filter=PartitionKey%20eq%20'2147472570' HTTP/1.1 request to obtain associated OrderDetail entities, and follows with DELETE /OrderDetailTable(PartitionKey='2147472570',RowKey='0n') instructions for n = 0 to n OrderDetail entities. Even with the extra GET method calls, the total amount of data on the wire is considerably less for DELETE than POST operations. This implies that the data creation times are more dependent on Azure Table operations in the cloud than message transit times. 

Cross-Table Transactions for Azure Tables

The latest word on transactions for Azure Tables is from Microsoft’s Alex Gershaft in 3/24/2009 answer to Azure table storage and state management questions in the Windows Azure forum:

There is no current support for transactions, but it is something that is on our future feature list.

The question, of course, is “How far in the future?” I’m guessing quite a while because MSFT wants to maximize revenue from SDS surcharges for persistent storage with relational features.

There was discussion of using Azure Queues for Cross-Table Transactions in the early days of Azure tables (see Brad Calder’s answer of 10/29/2008 in the Transactions and Locking? Window Azure forum thread.) A couple of days later, Calder replied to the Design pattern for data structures thread:

Great point.   Yes, you can create a property called “Kind”/“Type” (or whatever you want) for all of the entities stored in your Test Table,  and then treat the entities within a partition as entities belonging to N different types/classes.  This can allow you to model within a partition basically several “mini-tables”, where you can think of each Kind/Type being a mini-table within the partition.    Then once we have Entity Group transactions exposed that will allow you to perform atomic transactions across all of the entities within the partition, which means you can perform atomic transactions across all of these Kind/Type entities (mini-tables) in that partition.

Not much has been heard about queue-based or Kind/Type-assisted transactions recently.

This post will be updated as additional tests under varying conditions are conducted.

Technorati Tags: Windows Azure,Azure Services Platform,Azure Storage Services,Azure Table Services,Cloud Computing,ADO.NET Data Services,Astoria Project