After reading my previous blog, one of my Kloud colleagues raised a good point:
“What about continuation tokens?”
“Hmm, not sure, let me check. They should just pass through.”
Not so much.
Continuation tokens are used by the Azure Table Storage API as a way to move through a result set which may be larger than the maximum (1000) or has been limited using a $top parameter. Sure enough, a quick check shows that the previous solution is limited to 1000 rows in Excel. It seems Microsoft diverted from the oData spec in this area also which is a shame; but not insurmountable.
It’s time to break out Fiddler again to see what’s happening under the hood. Responses from Azure Table Storage contain HTTP headers x-ms-continuation-NextPartitionKey and x-ms-continuation-NextRowKey.
The intention is that the requesting client will take these HTTP headers and pass them back on a subsequent paging request on the query string as NextPartitionKey and NextRowKey.
oData on the other hand uses a much neater solution. In the returned oData XML, there are entry elements and link elements. The link next elements contain the full URL that the client should use to get the next page of data. The convention is to use a skiptoken but we can actually tunnel any type of token through because it is the whole URL that the oData client will use to get the next page. Here’s how it looks on the NetFlix oData interface which uses an integer skiptoken to page through the dataset.
To bridge the gap between the Azure Table API’s and the oData API we need to look at every response from Azure Table Storage and take the continuation keys from the response headers and form them into a link element to be appended onto the returned data entry elements. This again requires an intermediate proxy between your oData client and the Azure Table Storage which we’ll use Fiddler again for now.
This is pretty easy to implement (with some trial and error) by extending the same Fiddler extension we used earlier and is available as a DLL here or the source code here. Incidentally the same (or similar) code could be reused in a hosted proxy which I might have a go at next time.
Now running Excel over a large data set in Azure Table Storage we see the interaction between Excel and Table Storage as it walks down the pages requesting data using the default maximum page size of 1000 rows (you can elect to use smaller page sizes using the $top=n oData query string)
Now we’ve got more that 1000 rows in our Excel table!
Thanks Dave for reminding me about continuation tokens.