Azure Table Storage Sort Order

In this scenario we are writing log data to Azure Table Storage and want to always retrieve the latest entry first (i.e. view the latest logs). Table storage queries lack the equivalent of an SQL “ORDER BY” statement, so the data needs to be stored in a way that it can be retrieved correctly.

This is particularly important when data is pulled back a page at a time. If we have ALL the data in memory we can sort it at the client end, but if we only want the 10 latest rows for example we need that sorting to happen in the Storage Account.

The RowKey is used for this ordering, and there’s a detailed description on Microsoft Learn using C++ examples. In this article I’ll look at how to do the equivalent operation in PowerShell.

Demo environment

First we’ll build a demo environment using PowerShell. This builds a new Resource Group containing a randomly named Storage Account. These are based on the generic examples from Microsoft Learn.

 1# Create a Resource Group
 3$resourceGroup = "pshtablesrg"
 4New-AzResourceGroup -ResourceGroupName $resourceGroup -Location $location
 6# Create Azure Table Storage (LRS)
 7# Using New-Guid to make a random, hopefully globally unique name 
 8$storageAccountName = ([string](New-Guid)).Replace("-","").Substring(0,24)
 9$storageAccount = New-AzStorageAccount -ResourceGroupName $resourceGroup `
10  -Name $storageAccountName `
11  -Location $location `
12  -SkuName Standard_LRS `
13  -Kind Storage
14$ctx = $storageAccount.Context

Next we need to install the AzTable module, create a new table in that Storage Account, and get the necessary references for the following steps.

 1Install-Module AzTable
 2# Create a new table
 3$tableName = "pshtesttable"
 4New-AzStorageTable -Name $tableName -Context $ctx
 6#Retrieve a reference to the new table
 7$storageTable = Get-AzStorageTable -Name $tableName -Context $ctx
 9#Reference the CloudTable property of the table
10$cloudTable = $storageTable.CloudTable

Random-order (default) test

We’re now ready to write some test data. The first set, written to the Partition Key partition1, will contain 9 values (1-9) written in that order but with a randomly assigned RowKey (again using New-Guid to generate unique keys).

1# Fill with data using random RowKey
2$partitionKey1 = "partition1"
3for ($i=1;$i -lt 10;$i++){
4    $rowkey=[string](New-Guid)
5    Add-AzTableRow `
6        -table $cloudTable `
7        -partitionKey $partitionKey1 `
8        -rowKey ($rowkey) -property @{"myvalue"=$i}

When we retrieve this table data we can see the myvalue column is in an apparently random order. The data will always be returned in this same order but it is not the order the rows were written in, it is ordered by the RowKey sorted alphabetically.

1Get-AzTableRow -table $cloudTable -partitionKey $partitionKey1 | ft
 1myvalue PartitionKey RowKey                               TableTimestamp             Etag
 2------- ------------ ------                               --------------             ----
 3      5 partition1   356ec3ef-9c39-48f4-9096-6583e8c0efab 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.0852706Z'"
 4      6 partition1   5d2687ad-9690-456b-bce3-2fcf867df348 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.1172515Z'"
 5      7 partition1   61485ffd-5bba-4cc0-bcc0-156e379c55e0 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.1322438Z'"
 6      2 partition1   63ec7e1d-0efe-412e-9f2b-ec20c5d94d90 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.0392958Z'"
 7      4 partition1   b99d0c51-db42-46d5-be76-6a829fe9cb71 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.0702792Z'"
 8      8 partition1   bfb4f9c7-b43f-4624-914c-35139ced585b 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.148235Z'"
 9      9 partition1   c172ede3-0929-479d-ae0d-eecabe5d2c04 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.1622259Z'"
10      3 partition1   e95b64e4-aec8-487d-b453-553a2cffcc0e 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.0552879Z'"
11      1 partition1   f54839e4-e558-43c3-ae76-f4a24e2ede13 6/6/2024 5:28:26 PM +00:00 W/"datetime'2024-06-06T17%3A28%3A26.0243048Z'"

Reverse-date order

Next we’re going to write some more test data, this time to the Partition key partition2. This will contain the same 9 values, but the RowKey will be specially formatted so that when the data is sorted by RowKey the latest entry is at the top of the output and the oldest at the bottom.

The format of the Rowkey is $MaxTicks-(Get-Date).Ticks which is essentially the number of ticks between now and the latest possible date (1 tick before January 1st 10000). This is padded with zeros to make sure it is always sortable in the right order. Finally, we’re still adding that random GUID onto the end of the RowKey to make sure it is unique.

 1$partitionKey2 = "partition2"
 2#Set MaxTicks to the biggest possible date
 3$MaxTicks=(Get-date "9999-12-31 23:59:59.9999999").Ticks
 4for ($i=1;$i -lt 10;$i++){
 5    $rowkey=([string]($MaxTicks-(Get-Date).Ticks )).PadLeft(20,"0")+ [string](New-Guid)
 6    Add-AzTableRow `
 7        -table $cloudTable `
 8        -partitionKey $partitionKey2 `
 9        -rowKey ($rowkey) -property @{"myvalue"=$i}

Test that this is worked with the following line:

1Get-AzTableRow -table $cloudTable -partitionKey $partitionKey2 | ft

The output should look something like this, with myvalue decreasing from 9 to 1.

 1myvalue PartitionKey RowKey                                                   TableTimestamp             Etag
 2------- ------------ ------                                                   --------------             ----
 3      9 partition2   0251684616358530178734ee0bd1-8f8a-4451-ad20-68f5c66180a9 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.4782038Z'"
 4      8 partition2   02516846163585454674c8a104ce-e35f-4486-a0a5-a8d383b44752 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.4622134Z'"
 5      7 partition2   025168461635856225141be0c3ce-a916-48ec-9bfd-36c5e106180f 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.445223Z'"
 6      6 partition2   0251684616358577413011bea30e-fe45-4fb6-ad56-95fa24f1d897 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.4302312Z'"
 7      5 partition2   02516846163585935784ce4a78e4-b221-4fda-8e4e-5ddafbcb57eb 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.4152409Z'"
 8      4 partition2   02516846163586084563ead4866b-fb5f-4c26-b76b-85bb2eea8286 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.4002484Z'"
 9      3 partition2   02516846163586233661bca2c7b7-9f41-4ed9-a153-d0c7f27abbde 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.384258Z'"
10      2 partition2   02516846163586396832d4b0af51-f0b0-46c8-b462-4af8bdda55ae 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.3682671Z'"
11      1 partition2   02516846163586617410c893077d-3569-4a9b-9000-baa184777209 6/6/2024 5:34:01 PM +00:00 W/"datetime'2024-06-06T17%3A34%3A01.351277Z'"

Tidy Up

Our demo environment can be tidied up by simply removing the Resource Group.

1Remove-AzResourceGroup -Name $resourceGroup -Force