Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Date range queries in Azure Table storage

Hello following on from my question: Windows Azure table access latency Partition keys and row keys selection about the way I have organised data in my Azure storage account. I have a table storage scheme designed to store info about entities.

There are about 4000 - 5000 entities. There are 6 entity types and the types are roughly evenly distributed. so around 800'ish each.

ParitionKey: entityType-Date

Row key: entityId

As the question details I have been sufferring latency issues where querying for multiple partitions seems to take quite a while.

Essentially one possible solution would be to do a query of the following:

PartitionKey > 'EntityType-Date' and PartitionKey < EntityType-HighDate' and RowKey = 'EntityId'

This does not seem to work in Window Azure Storage Explorer. It doesn't seem to recognize the greater than less than, which I'd kind of expect.

My question is exactly how does less than, greater than work and would I be better splitting my entities out by table type. Thus my query would be? Or should I be pursuing a different approach?

PartitionKey > LowDate and PartitionKey < HighDate and RowKey = 'EntityId'

like image 656
Captain John Avatar asked Jan 16 '14 11:01

Captain John


1 Answers

As far as querying syntax is concerned in Azure Storage Explorer, you would need to specify your query like this:

(PartitionKey gt 'EntityType-Date' and PartitionKey lt EntityType-HighDate') and (RowKey eq 'EntityId')

Please see this link for more details: http://msdn.microsoft.com/en-us/library/windowsazure/dd894031.aspx.

For deciding on PartitionKey/RowKey for efficient querying, I would strongly recommend reading up this blog from Storage team: http://blogs.msdn.com/b/windowsazurestorage/archive/2010/11/06/how-to-get-most-out-of-windows-azure-tables.aspx.

like image 76
Gaurav Mantri Avatar answered Sep 30 '22 15:09

Gaurav Mantri