Azure Storage Table design patterns

Azure table Storeage

• Core component of Azure cloud
• Designed for storing petabytes of data
• NoSQL key/attribute storage
• Schemaless
• Relatively cheap(€0,0591 per GB) and €0,000304 per 10 000 transactions

Querying Table Storage

• Point Query(PartitionKey + RowKey: $filter=(PartitionKey eq 'Sales') and (RowKey eq '2'))
• Range Query(PartitionKey + range of RowKeys: $filter=PartitionKey eq 'Sales' and RowKey ge 'S' and RowKey lt 'T'
• Partition Scan(PartitionKey + non-key property)
• Table Scan(without PartitionKey)

EGTs or entity group transactions

• In-built mechanism for performing atomic operations across multiple entities
• Max of 100 entities per transaction
• Only within a single partition

egts example

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "people" table.
CloudTable table = tableClient.GetTableReference("people");

// Create the batch operation.
TableBatchOperation batchOperation = new TableBatchOperation();

// Create a customer entity and add it to the table.
CustomerEntity customer1 = new CustomerEntity("Smith", "Jeff");
customer1.Email = "Jeff@contoso.com";
customer1.PhoneNumber = "425-555-0104";

// Create another customer entity and add it to the table.
CustomerEntity customer2 = new CustomerEntity("Smith", "Ben");
customer2.Email = "Ben@contoso.com";
customer2.PhoneNumber = "425-555-0102";

// Add both customer entities to the batch insert operation.
batchOperation.Insert(customer1);
batchOperation.Insert(customer2);

// Execute the batch operation.
table.ExecuteBatch(batchOperation);

intra-partition secondary index(IPSI)

• Store multiple copies of each entity using different RowKey values in the same table
• Optimized for querying/sorting
• Uses the same partition(so EGTs are possible)

inter-partition secondary index(IPSIx)

• Store multiple copies of each entity using different RowKey values
• Optimized for querying/sorting
• Uses different partitions
• EGTs are not possible

log tail pattern

• Retrieve the n entities most recently added to a partition by using a RowKey value that sorts in reverse date and time order
• Optimized for sorting

log tail example

// Prepare RowKey
string invertedTicks = string.Format("{0:D19}", DateTime.MaxValue.Ticks - DateTime.UtcNow.Ticks);
DateTime dt = new DateTime(DateTime.MaxValue.Ticks - Int64.Parse(invertedTicks));

// Insert entity
(...)

// Retrieve N recent entities
https://myaccount.table.core.windows.net/EmployeeExpense(PartitionKey='empid')?$top=10

Compound key pattern

• enable client to retrieve data with a single query
• designed for frequently queried entities

High volume delete pattern

• designed for quickly deleting large volumes of data
• avoids performing a table scan
• introduces some throttling so it may be insufficient when you create new tables frequently

Data series pattern

• retrieving data with a single query
• allows for merging data
• limited to 252 properties
• requires ETag for optimistic concurrency

eventually consistent transactions pattern