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:
1_1
2_3
10_4
100_5

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:
1_1
10_4
100_5

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:
               0...00001

This gives our set of records to look like the following:
0...00001_0...00001
0...00002_0...00003
0...00010_0...00004
0...00100_0...00005

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.

    Chris

    ReplyDelete