Azure Storage Table Batch Insert Operation

Introduction

This blog explains the process to bulk insert entities into Azure Storage Table using batch operations.

In this article, we will be using the Azure function (Timer Triggered), which you can schedule as per your own requirements.

In this article, we are mainly focusing on the batch job (i.e we will be using batch jobs to bulk insert the entities into Azure Table.)

In order to process this, I've created a mock LIST in the program with 250 items (This is only for demo purposes, but the overall logic will be the same for larger data/Entities.)

Environment

Storage Account

Create an Azure Storage account in your Azure subscription.

Follow the steps mentioned in Microsoft docs.

Visual Studio Solution

For example, I've used the Azure Timer function here. You can implement the same as per your project need, the rest of the logical part will be same.

You need to install below NuGet packages for the solution:

Windows.Azure.Storage
Newtonsoft.JSON

Azure Storage Table

All entities in a single batch operation must have all the same PartitionKey. Being the lazy programmer that I am, I only wanted to think about it once and I wanted it to work for anything I threw at it, so I came up with this. If you named your table "TestTable" and you used the tool below to generate the code for your project, this is what the batch insert operation would look like.

Batch Operation

A batch operation is a collection of table operations that are executed by the Storage Service REST API as a single atomic operation, by invoking an Entity Group Transaction. A batch operation may contain up to 100 individual table operations, with the requirement that each operating entity must have the same partition key. A batch with a retrieve operation cannot contain any other operations. Note that the total payload of a batch operation is limited to 4MB.

Step 4.1 - Create EntityClass

Create an entity class as shown below, here "ContactNumber" is my custom field, the same field has been added in the Azure table as Column.

public class UserEntity : TableEntity
{
public UserEntity() { }
public UserEntity(string department, string UserName)
{
PartitionKey = department;
RowKey = UserName;
}
public string ContactNumber { get; set; }
}

Step 4.2 - Authenticate Azure Table

This function helps to authenticate your Azure Storage Table, mention your Azure Storage AccountName and AccountKey.

Refer to the below image:

Azure Storage Table Batch Insert Operation

Also, replace the target table name with your TableName.

private static CloudTable AuthTable()
{
string accountName = "<<YOUR ACCOUNT STORAGE NAME>>";
string accountKey = "<<SORAGE ACCOUNT KEY>>";
string TargetTableName = "TestTable";
try
{
StorageCredentials storageCreds = new StorageCredentials(accountName, accountKey);
CloudStorageAccount cloudStoageAccount = new CloudStorageAccount(storageCreds, useHttps: true);
CloudTableClient cloudTableClient = cloudStoageAccount.CreateCloudTableClient();
CloudTable cloudTable = cloudTableClient.GetTableReference(TargetTableName);
return cloudTable;
}
catch
{
return null;
}
}

Step 4.3 - Create a Mockdata list (You can replace this list with your actual data)

This is mock data and we are using List<string> to store our mock data, you can replace this with your business data

public static List<string> GetMockContactnumbers()
{
Random rand = new Random();
List<string> contactList = new List<string>();
for (int i = 0; i < 250; i++)
{
//rand = new Random();
//contactList = new List<string>();
int randomNumber = rand.Next(8000000, 9999999);
contactList.Add(randomNumber.ToString());
}
return contactList;
}

Step 4.4 - Create Batches for Batch job

Create batches of your data, preferably batches of 100

The azure table will not allow you to batch more than 100 items.

public static IEnumerable<IEnumerable<T>> Batch<T>(this IEnumerable<T> collection, int batchSize)
{
var nextbatch = new List<T>(batchSize);
foreach (T item in collection)
{
nextbatch.Add(item);
if (nextbatch.Count == batchSize)
{
yield return nextbatch;
nextbatch = new List<T>(batchSize);
}
}
if (nextbatch.Count > 0)
{
yield return nextbatch;
}
}

Step 4.5 - Iterate through each batch insert operation

Iterate through each batch and each item inside the batch

//Creating Batches of 100 items in order to insert them into AzureStorage
var batches = contactList.Batch(100);
//Iterating through each batch
foreach (var batch in batches)
{
batchOperationObj = new TableBatchOperation();
//Iterating through each batch entities
foreach (var item in batch)
{
userEntityObj = new UserEntity("HR Department", "rowKey");
{
UserIncrementCounter++;
userEntityObj.PartitionKey = "HR Department";
userEntityObj.RowKey = "User" + UserIncrementCounter.ToString();
userEntityObj.ContactNumber = item;
}
batchOperationObj.InsertOrReplace(userEntityObj);
}
cloudTable.ExecuteBatch(batchOperationObj);
}

All set, after the execution of the code, you can check your table Azure Table Storage Explorer, as shown below.

Summary

In this article, we've seen how to bulk insert entities into an Azure Table. Regardless of the data, you can use List as well as an array to create batches using the same method.

Click here for view above solution on GitHub.