I have an Azure Storage Table and it has 3k+ records.
What is the most efficient way to delete all the rows in the table?
5 Answers
25
votes
For 3000 records, easiest way would be to delete the table. However please note that when you delete the table, it is not deleted at that time but is put in some kind of queue to be deleted and is actually deleted some time later. This time depends on the load on the system + number of entities in the table. During this time, you will not be able to recreate this table or use this table.
If it is important for you to keep using the table, the only other option is to delete entities. For faster deletes, you can look at deleting entities using Entity Batch Transactions. But for deleting entities, you would need to first fetch the entities. You can speed up the fetching process by only fetching PartitionKey and RowKey attributes of the entities instead of fetching all attributes as only these two attributes are required for deleting an entity.
2
votes
I use something like this. We partition key by date, your case may be different:
async Task Main()
{
var startDate = new DateTime(2011, 1, 1);
var endDate = new DateTime(2012, 1, 1);
var account = CloudStorageAccount.Parse("connString");
var client = account.CreateCloudTableClient();
var table = client.GetTableReference("TableName");
var dates = Enumerable.Range(0, Math.Abs((startDate.Month - endDate.Month) + 12 * (startDate.Year - endDate.Year)))
.Select(offset => startDate.AddMonths(offset))
.ToList();
foreach (var date in dates)
{
var key = $"{date.ToShortDateString()}";
var query = $"(PartitionKey eq '{key}')";
var rangeQuery = new TableQuery<TableEntity>().Where(query);
var result = table.ExecuteQuery<TableEntity>(rangeQuery);
$"Deleting data from {date.ToShortDateString()}, key {key}, has {result.Count()} records.".Dump();
var allTasks = result.Select(async r =>
{
try
{
await table.ExecuteAsync(TableOperation.Delete(r));
}
catch (Exception e) { $"{r.RowKey} - {e.ToString()}".Dump(); }
});
await Task.WhenAll(allTasks);
}
}
2
votes
For someone finding this later, the problem with the accepted answer "just deleted the table" is that while it works great in the storage emulator, it will fail randomly in production. If your app/service requires regenerating tables regularly then you'll find that you'll have failures due to either conflicts or deletion still in progress.
Instead, I found the fastest and most error proof EF friendly approach to be deleting all rows within a segmented query. Below is a simple drop-in example that I'm using. Pass in your client, table name, and a type that implements ITableEntity.
private async Task DeleteAllRows<T>(string table, CloudTableClient client) where T: ITableEntity, new()
{
// query all rows
CloudTable tableref = client.GetTableReference(table);
var query = new TableQuery<T>();
TableContinuationToken token = null;
do
{
var result = await tableref.ExecuteQuerySegmentedAsync(query, token);
foreach (var row in result)
{
var op = TableOperation.Delete(row);
tableref.ExecuteAsync(op);
}
token = result.ContinuationToken;
} while (token != null);
}
Example Usage:
table = client.GetTableReference("TodayPerformanceSnapshot");
created = await table.CreateIfNotExistsAsync();
if(!created)
{
// not created, table already existed, delete all content
await DeleteAllRows<TodayPerformanceContainer>("TodayPerformanceSnapshot", client);
log.Information("Azure Table:{Table} Purged", table);
}
A batched approach takes significantly more effort since you have to handle both the "only same partition keys in a batch" and "only 100 rows" limitations. The following version of DeleteAllRows does this.
private async Task DeleteAllRows<T>(string table, CloudTableClient client) where T: ITableEntity, new()
{
// query all rows
CloudTable tableref = client.GetTableReference(table);
var query = new TableQuery<T>();
TableContinuationToken token = null;
TableBatchOperation batchops = new TableBatchOperation();
Dictionary<string, Stack<TableOperation>> pendingOperations = new Dictionary<string, Stack<TableOperation>>();
do
{
var result = await tableref.ExecuteQuerySegmentedAsync(query, token);
foreach (var row in result)
{
var op = TableOperation.Delete(row);
if (pendingOperations.ContainsKey(row.PartitionKey))
{
pendingOperations[row.PartitionKey].Push(op);
}
else
{
pendingOperations.Add(row.PartitionKey, new Stack<TableOperation>() );
pendingOperations[row.PartitionKey].Push(op);
}
}
token = result.ContinuationToken;
} while (token != null);
// order by partition key
foreach (var key in pendingOperations.Keys)
{
log.Information($"Deleting:{key}");
var rowStack = pendingOperations[key];
int max = 100;
int current = 0;
while (rowStack.Count != 0)
{
// dequeue in groups of 100
while (current < max && rowStack.Count > 0)
{
var op = rowStack.Pop();
batchops.Add(op);
current++;
}
//execute and reset
_ = await tableref.ExecuteBatchAsync(batchops);
log.Information($"Deleted batch of size:{batchops.Count}");
current = 0;
batchops.Clear();
}
}
}
1
votes
This depends on the structure of your data, but if you can compose a query for all records, you can add each to a TableBatchOperation and execute them all at once.
Here's an example that just gets all the results inside the same partition key, adapted from How to get started with Azure Table storage and Visual Studio connected services.
// query all rows
CloudTable peopleTable = tableClient.GetTableReference("myTableName");
var query = new TableQuery<MyTableEntity>();
var result = await remindersTable.ExecuteQuerySegmentedAsync(query, null);
// Create the batch operation.
TableBatchOperation batchDeleteOperation = new TableBatchOperation();
foreach (var row in result)
{
batchDeleteOperation.Delete(row);
}
// Execute the batch operation.
await remindersTable.ExecuteBatchAsync(batchDeleteOperation);
1
votes
I use the following function to first put all partitions keys in a queue and then loop through the key to delete all rows in batches of 100.
Queue queue = new Queue();
queue.Enqueue("PartitionKeyTodelete1");
queue.Enqueue("PartitionKeyTodelete2");
queue.Enqueue("PartitionKeyTodelete3");
while (queue.Count > 0)
{
string partitionToDelete = (string)queue.Dequeue();
TableQuery<TableEntity> deleteQuery = new TableQuery<TableEntity>()
.Where(TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, partitionToDelete))
.Select(new string[] { "PartitionKey", "RowKey" });
TableContinuationToken continuationToken = null;
do
{
var tableQueryResult = await myTable.ExecuteQuerySegmentedAsync(deleteQuery, continuationToken);
continuationToken = tableQueryResult.ContinuationToken;
// Split into chunks of 100 for batching
List<List<TableEntity>> rowsChunked = tableQueryResult.Select((x, index) => new { Index = index, Value = x })
.Where(x => x.Value != null)
.GroupBy(x => x.Index / 100)
.Select(x => x.Select(v => v.Value).ToList())
.ToList();
// Delete each chunk of 100 in a batch
foreach (List<TableEntity> rows in rowsChunked)
{
TableBatchOperation tableBatchOperation = new TableBatchOperation();
rows.ForEach(x => tableBatchOperation.Add(TableOperation.Delete(x)));
await myTable.ExecuteBatchAsync(tableBatchOperation);
}
}
while (continuationToken != null);
}
