Tutorial - Using Table Storage

Author: Maarten Balliauw

Date: Friday, March 12, 2010, 12:00:00 AM

Tags: Tutorial, Storage

Table of Contents

Note:This article pertains to the CodePlex SDK initially released late 2009. The Windows Azure team has since then released a newer version of the Azure SDK for PHP on Github. Please refer to the Windows Azure PHP Developer Center for documentation on this more recent version of the SDK.

Please stay tuned and come back here regularly as we are working on refreshing the tutorials to deliver up to date and useful content for our PHP developers.

Recommended Reading


This tutorial will show the fundamentals of Windows Azure Table storage and provide a walk through that creates a working guest book application that can be deployed directly to Windows Azure.

Sample Files

This tutorial uses the GuestBookUsingTables sample application from the Windows Azure Sample Kit 4 PHP

Place a copy of the GuestBookUsingTables files in C:\temp for the remainder of this tutorial.

The two files from GuestBookUsingTable which will be focused on in this tutorial are index.php and GuestBookEntry.class.php.

Windows Azure table storage fundamentals

The Table storage service offers structured storage in the form of tables. Tables can contain entities with different properties and sizes which implies that table storage is a schema-less data store. Entities in Table Storage are limited to 1MB each with no more than 255 properties (3 of which are required partition key, row key, and timestamp).

Addressing schema

Tables are available on their specific HTTP(S) endpoint:

http://<account>.table.core.windows.net/ <table>

This endpoint is specific to the production Windows Azure environment. When working locally one can install the Windows Azure SDK and have access to a local, simulated storage environment. The endpoint for this environment is the following:<table>

Access to the storage endpoint is granted based on an endpoint that you choose and a generated account key. For the development storage environment the following account is the default one:

Account name (endpoint): devstoreaccount1
Account key: Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==

There is no need to remember the above: the Windows Azure SDK for PHP uses the above account name and account key by default when connecting to development storage.


Figure 1 - Windows Azure table structure

Choosing between table storage and SQL Azure

The choice between table storage and SQL Azure can be reduced to one simple question: do you require data processing (aggregates, grouping, …) or do you require fast, scalable access to large datasets? To illustrate this further, here is a citation by Tony Petrossian, Principal Program Manager, Windows Azure:

SQL Azure and other relational databases usually provide data-processing capabilities on top of a storage system. Generally, RDBMS users are more interested in data processing than the raw storage and retrieval aspects of a database.

For example, if you want to find out the total revenue for the company in a given period, you might have to scan hundreds of megabytes of sales data and calculate a SUM. In a database, you can send a single query (a few bytes) to the database that will cause the database to retrieve the data (possibly many gigabytes) from disk into memory, filter the data based on the appropriate time range (down to several hundred megabytes in common scenarios), calculate the sum of the sales figures and return the number to the client application (a few bytes).

To do this with a pure storage system requires the machine running the application code to retrieve all of the raw data over the network from the storage system, and then the developer has to write the code to execute a SUM on the data. Moving a lot of data from storage to the app for data processing tends to be very expensive and slow.

SQL Azure provides data-processing capabilities through queries, transactions and stored procedures that are executed on the server side, and only the results are returned to the app. If you have an application that requires data processing over large data sets, then SQL Azure is a good choice. If you have an app that stores and retrieves (scans/filters) large datasets but does not require data processing, then Windows Azure Table Storage is a superior choice.

-Tony Petrossian, Principal Program Manager, Windows Azure

More information on this topic is available in an excellent article by Joseph Fultz on MSDN magazine: http://msdn.microsoft.com/en-us/magazine/gg309178.aspx

Partition keys and row keys

Each table storage entity contains a partition key and row key property. The partition key supports scalability of Azure Tables. Entities with the same partition key comprise a partition and are stored together and handled by the same partition server. The Azure Storage Service can move partitions to different partition servers if it detects that a particular partition server is getting hot, i.e. too much traffic. The allocation of partition keys should be predicated on the scalability needs of the application.

See the article How to get the most out of Windows Azure Tables by the Windows Azure team for more information on scaling tables using partition keys.

Creating table entities

To demonstrate working with tables a sample scenario will be used. This sample will allow you to insert and delete a collection of guestbook entries in a table.

Defining entities for table storage

Before diving into the sample and its code, it is important to know about a specific characteristic of the Windows Azure SDK for PHP. Tables store data as collections of entities. Entities are similar to rows. An entity has a primary key and a set of properties. A property is a name, typed-value pair, similar to a column.

The Table service does not enforce any schema for tables, so two entities in the same table may have different sets of properties. Developers may choose to enforce a schema on the client side. A table may contain any number of entities.

Windows Azure SDK for PHP provides two ways of working with entities:

  • Enforced schema
  • No enforced schema

Windows Azure SDK for PHP automatically works without schema enforcement if no specific class name is passed into the various table storage client methods.

Working with a client-side enforced schema

To enforce a schema on the client side using Windows Azure SDK for PHP, you can create a class which inherits Microsoft_WindowsAzure_Storage_TableEntity. This class provides some basic functionality for the Windows Azure SDK for PHP to work with a client-side schema.

Base properties provided by Microsoft_WindowsAzure_Storage_TableEntity are:

  • PartitionKey (exposed through getPartitionKey() and setPartitionKey())
  • RowKey (exposed through getRowKey() and setRowKey())
  • Timestamp (exposed through getTimestamp() and setTimestamp())
  • Etag value (exposed through getEtag() and setEtag())

When opening the GuestBookEntry.class.php file, a schema-enforced class is defined:

class GuestBookEntry extends Microsoft_WindowsAzure_Storage_TableEntity {
     * @azure Message
    public $Message;

     * @azure GuestName
    public $GuestName;

    function __construct() {
        //Set the partition key to today's date in "mmddyyyy" format
        $nowDT = new DateTime('now', new DateTimeZone('UTC'));
        $partitionKey = $nowDT->format("mdY");
        $this->_partitionKey = $partitionKey;
        $this->_rowKey = trim(com_create_guid(), '{}');

The Windows Azure SDK for PHP will map any class inherited from Microsoft_WindowsAzure_Storage_TableEntity to Windows Azure table storage entities with the correct data type and property name. All there is to storing a property in Windows Azure is adding a docblock comment to a public property or public getter/setter. (A PHPDoc docblock is an adaptation of Javadoc for the PHP programming language. It is a formal standard for commenting PHP code, (http://en.wikipedia.org/wiki/PHPDoc )

The following format should be used when working with the Windows Azure SDK for PHP:

* @azure <property name in Windows Azure table> [optional property type] 
public $<property name in PHP>

Note: A property does not necessarily have to be named the same on Windows Azure table storage.

The Windows Azure table storage property name can be defined as well as the type. The following data types are supported:

  • Edm.Binary - An array of bytes up to 64 KB in size.
  • Edm.Boolean - A boolean value.
  • Edm.DateTime - A 64-bit value expressed as Coordinated Universal Time (UTC). The supported DateTime range begins from 12:00 midnight, January 1, 1601 A.D. (C.E.), Coordinated Universal Time (UTC). The range ends at December 31st, 9999.
  • Edm.Double - A 64-bit floating point value.
  • Edm.Guid - A 128-bit globally unique identifier.
  • Edm.Int32 - A 32-bit integer.
  • Edm.Int64 - A 64-bit integer.
  • Edm.String - A UTF-16-encoded value. String values may be up to 64 KB in size.

Working without an enforced schema

Note: This section of code is not in the sample and is included for reference only.

To use Windows Azure SDK for PHP without defining a schema, you can make use of the Microsoft_WindowsAzure_Storage_DynamicTableEntity class. This class inherits Microsoft_WindowsAzure_Storage_TableEntity like an enforced schema class does, but contains additional logic to make it dynamic and not bound to a schema.

Base properties provided by Microsoft_WindowsAzure_Storage_DynamicTableEntity are:

  • PartitionKey (exposed through getPartitionKey() and setPartitionKey())
  • RowKey (exposed through getRowKey() and setRowKey())
  • Timestamp (exposed through getTimestamp() and setTimestamp())
  • Etag value (exposed through getEtag() and setEtag())

All other properties are created at runtime. The following code snippet illustrates how this works:

$entry = new Microsoft_WindowsAzure_Storage_DynamiceTableEntity(<partition_key>, <row_key>); 
// Add "GuestName" property 
$entry->GuestName = "Guest Name";

// Add "Message" property 
$entry->Message = "Message from me!";

Connecting to Windows Azure table storage

The Windows Azure SDK for PHP supports connecting to table storage both in the Windows Azure cloud and locally for development purposes.

Local storage

The Windows Azure development environment includes a local version of the Windows Azure storage server. When the Microsoft_WindowsAzure_Storage_Table object is not provided connection details in the constructor it will automatically assume the developer wishes to connect to the local storage account. The following code is used to connect to the local storage table.

$table = new Microsoft_WindowsAzure_Storage_Table();

Windows Azure cloud storage

When using the Windows Azure cloud storage the Microsoft_WindowsAzure_Storage_Table object accepts three parameters in order as follows:

  1. Address to the storage table server
  2. Storage account (endpoint)
  3. Storage account key

The following code will allow you to connect to a Windows Azure table storage account

$table = new Microsoft_WindowsAzure_Storage_Table( 
    <storage account (endpoint)>, 
    <storage account key> 

Ensuring a table exists before use

If an operation is performed on a table that does not exist a fatal exception will be thrown and your application will perform unexpectedly, therefore it is always good practice to ensure that the table in question exists. This can be done quite easily with the following single line of code:

$table->createTableIfNotExists('<name of table>');

In the case of our sample the name of the table is guestbook.

Insert a new table entity

Creating a new table entity is a two-step process that involves

  1. Instantiating an entity object
  2. Inserting that object into a table

The first step is to create a new object from the entity class created earlier, GuestBookEntry.

$g = new GuestBookEntry();

Next you have to set the values in $g to the values the user provided via a form.

$g->GuestName = $_POST['NameTextBox']; 
$g->Message = $_POST['MessageTextBox'];

And finally, insert the entity, $g, into the guestbook table.

$table->insertEntity('guestbook', $g);

Update a table entity

To update a table entity you first need the entity partition and row key to retrieve the entity. Then you make the changes and call update.

$g = $table->retrieveEntityById('guestbook', <partition_key>, <row_key>); 
$g->Message = "This is a new message"; 
$table->updateEntity('guestbook', $g);

Delete a table entity

To delete a table entity you first need the entity partition and row key to retrieve the entity. Then you can call the delete function.

$g = $table->retrieveEntityById('guestbook', <partition_key>, <row_key>); 
$table->deleteEntity('guestbook', $g);

Retrieving entities

Single entity

To retrieve a single entity you must know both the partition key and the row key. The retrieve method returns an object containing the properties which are specified by the column name and type.

$entity = $table->retrieveEntityById(<table_name>, <partition_key>, <row_key>);

All entities in a table

To retrieve all entities in a table simply pass the table name to the retrieveEntities method

$entities = $table->retrieveEntities(<table_name>);

In this sample <table_name> would be guestbook.

Run the sample

If you have been following along in the sample code you will have noticed that most of the methods listed in the tutorial have been implemented in order to present you with a complete sample that is ready to be run in your local Windows Azure development environment or in the Windows Azure cloud with very little change.

At this point you will want to run the sample in the local development environment. This can be easily done with the following:

  • Open a command line prompt
  • Type the command 'package create -in="C:\temp\GuestBookUsingTables" -out="C:\temp\GuestBookUsingTables\build" -dev=true'

After running this command your default web browser should open and the GuestBookUsingTables application will be presented to you, running on your local machine.

blog comments powered by Disqus