Delete certain rows from Azure Table Storage

Question

Hi all,

I have an Azure Table Storege and I store there messages sent to my IoT Hub via MQTT.

I use this table to implement something like a telemetry system but is not important.

As you imagine my table is gowing faster since many devices are sending their data to this table.

Does exist a way to realize something like a job that periodically runs and delete all the records in the table except the most recent for each device:

I try to provide an example, imagine this scenario:

Table before cleaning:

ID	Time	Information
Device_3	2023-07-26 02:00:00 PM	Temperature: 31.0°C
Device_2	2023-07-26 02:13:00 PM	Temperature: 33.4°C
Device_2	2023-07-26 02:12:00 PM	Temperature: 32.9°C
Device_1	2023-07-26 02:00:00 PM	Temperature: 31.4°C
Device_1	2023-07-26 02:01:00 PM	Temperature: 31.7°C

Table after cleaning:


Device_3	2023-07-26 02:00:00 PM	Temperature: 31.0°C
Device_2	2023-07-26 02:12:00 PM	Temperature: 32.9°C
Device_1	2023-07-26 02:01:00 PM	Temperature: 31.7°C

Find a way to do this every n days/hours should be perfect, alternatively what query could I activate on my table to select all but the most recent element of each device in order to delete them?

Answer

Hello @Andrea Previtali ,

the direct answer is to execute deletion logic using eg. a timer-triggered Azure Function or an Automation Runbook:

var query = new TableQuery().Where(TableQuery.GenerateFilterCondition("MyField", QueryComparisons.Equal, "Foo")); 
var rows = groupsTable.ExecuteQuery(query); 
foreach (var row in rows) 
{     
      var tableOperation = TableOperation.Delete(row);     
      table.Execute(tableOperation); 
}

The longer answer is to reconsider Azure Table storage.

Check out other database options like Azure Data Explorer (It sets a retention time by default. There is a free tier), giving you more flexibility.

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.

Share via

Delete certain rows from Azure Table Storage

1 answer

Your answer