Friday, June 10, 2011

Azure Table Storage - Selecting a partial range with ID's used in Partition and Row Keys

To explain,  let’s say we have a row key comprised of SomeID and SomeOtherID delimited by a underscore, and we have the following set of records in a table:

If we wanted to get all records where SomeID is 1, we would use the “compareto” operator to retrieve a partial matching range.  Sample code shown here:

startRowKey = “1_”
endRowKey = “2_”

        public IList<T> GetAllByPartitionKeyAndRowKeyRange(string partitionKey, string startRowKey, string endRowKey)
            CloudTableQuery<T> query = (from c in this.CreateQuery<T>(TableName)
                                        where c.PartitionKey == partitionKey
                                        && c.RowKey.CompareTo(startRowKey) >= 0
                                        && c.RowKey.CompareTo(endRowKey) < 0
                                        select c).AsTableServiceQuery();
            query.RetryPolicy = this.RetrySettings;
            IEnumerable<T> results = query.Execute();

            return ConvertToList(results);

This would produce the following result set because of azures lexicographically ordering, as is compares one character at a time thus making 10 less than 2:

The only solution I could come up with was to build keys with the same character length and prepend x number of 0’s, or zero padding, for each id (we used 36, as that's a guid length). For ex, when inserting or selecting, an numerical id within a key would look like the following:

This gives our set of records to look like the following:

Which will now produce an accurate result set when queried.

Again this is in the situation that you have ID's in your Partition and Row Keys so using the property bag is not a viable solution to get around this. 

And remember not to do a partial look up on a Partition Key, that results in a regular old table scan.

This was a tough one, hope this helps!

1 comment:

  1. This was extremely helpful, thank you very much.