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
2$location="uksouth"
3$resourceGroup = "pshtablesrg"
4New-AzResourceGroup -ResourceGroupName $resourceGroup -Location $location
5
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
5
6#Retrieve a reference to the new table
7$storageTable = Get-AzStorageTable -Name $tableName -Context $ctx
8
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}
9}
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}
10}
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