Azure Tables and Table Storage

Microsoft Azure provides a wide range of services to its customers. And, in our previous articles, we came to know that Azure is popular for its storage services. So, in today’s article, we will look at Azure Table Storage which is very popular among users. Let us begin.

About Azure Table Storage

Azure Table Storage is capable of storing a massive amount of structured data. It is a NoSQL datastore service that will take the authenticated information from the Azure Cloud.

Microsoft Azure’s tables are one of the best services available for the users to store structured and non-relational data.

Azure Table Architecture

As we know Microsoft Azure’s Table Storage service is capable of holding a massive volume of data in NoSQL datastores and the users are allowed to query the following structured and non-relational data for reading and writing the tables.

For storing this data Azure table storage service follows a specific architecture as shown below. In a table, the users are allowed to store a minimum of one or multiple entities. Now, these entities will be stored inside a storage account.

The storage account can also consist of one or multiple tables depending upon the capacity of the storage account.

1. Storage Account

Azure’s Storage Account takes the responsibility to provide and manage all the access related tasks in the storage account. Basically, it is the core building block of Azure services.
For transferring or migrating the data from one service in the storage the user should have a storage account as it offers a unique namespace.

It will provide all the required data objects used in Microsoft Azure. They are as follows:

• Blob Storage
• File Storage
• Queues, disks
• Azure Table storage

But, for accessing the Azure Storage Service the users must have a storage account.

2. Table

A table is a set of one or multiple schemaless entities. Azure’s table cannot impose the schema and thus a single table is capable of holding multiple types of entities with different entities.

3. URL

In Microsoft Azure, the storage account is developed with a unique namespace and in the URL format.

4. Entity

One can consider an entity similar to a row in a database with properties set. In Azure Storage, one entity is capable of holding data up to 1MB size and similarly in Azure’s Cosmos Database the size is 2MB.

How to Set Azure Table Storage?

If the user wants to create a table usage they can do it before. But don’t worry if it is not available then Azure’s Table Storage will automatically create for the user if it does not exist.

For setting up the Azure Table Storage as a stare store the user must have the following properties.

The user should click on the ‘New’ button and then enter the table name.

Account Name: The user should enter the storage account name.

Account Key: In this field, the user must enter the Primary or Secondary Storage Key.

Table Name: Here, the user must enter the table name which is used for the Dape state. If there is no table it will be created for the user.

Create Table in Azure

Step 1: The user should simply follow the below-mentioned commands and then paste them into the space. But they must remember to replace the text wherever required such as Account Name.

Step 2: The user must Sign in to their account

$StorageAccountName = "mystorageaccount"
$StorageAccountKey = "mystoragekey"
$Ctx = New-AzureStorageContext $StorageAccountName - StorageAccountKey
$StorageAccountKey

Step 3: In the next step the user should create a new table

$tabName = "Mytablename"
New-AzureStorageTable –Name $tabName –Context $Ctx

In a similar way, the users can retrieve, insert and delete the data inside the table with the help of PowerShell.

Insert Row in Azure Table

Follow below steps to insert row in azure table:

1: The first step is to click on the ‘New’ button.

2: Now, the user should enter the field name.

3: In the next step, the user must choose the data type from the dropdown list and then enter the value.

4: Lastly to check the created rows the user should click on the table name available at the left panel of the window.

How to manage Table with Azure’s Storage Explorer

Follow below steps to manage table with Azure Storage Explorer:

1: In the first step, the user should log in to their Azure account and then go to their storage account.

2: Now, the users should click on the Storage Explorer link.

3: Now, the user must select the ‘Azure Storage Explorer for Windows’ option from the list.

4: Now, the user should run this program on their system and then they should click on the ‘Add Account’ button available at the top section.

5: Now, the user should provide the ‘Storage Account Name’ followed by the ‘Storage Account Key’ and then click on the Test Access.

6: If the user has previously created any tables in the storage then they can check under the ‘Tables’ available in the left pane.

How to manage Azure Tables Using PowerShell

Follow the below steps to manage Azure Tables using Powershell:

1: The user should firstly download and install Windows PowerShell.

2: In the second step the user should right-click on the “Windows PowerShell”

3: Now, select the “Run ISE as Administrator” option.

Azure Table Storage vs Azure Cosmos DB Table API

In Azure, there are Cosmos DB Table API and Azure Table Storage both provide similar functionality, but these two services are not identical.

In the next section of the article, we have mentioned the information about how these services are different from each other.

1. Performance

When the user is using the Azure Table Storage solution there are no upper restrictions on the factors such as latency.

Similarly, the Cosmos DB has the limitations of reading/write latency to ten milliseconds.

With Azure Table, the throughput is restricted to 20k operations per second while in Cosmos DB the supported throughput is up to 10 million operations per second. Also, the Cosmos DB offers users with automatic indexing of properties. And, this can be helpful while querying to increase performance.

2. Global Distribution

Users can make use of Azure Table in a single region with a secondary, read-only region which provides increased availability.

Secondly, with Cosmos DB one can distribute their data across up to thirty regions. Users also get automatic global failover and it provides them options to choose between five consistency levels depending on their desired combination of throughput, latency and availability.

3. Consistent API

Users are allowed to use a similar API for both Azure Table and Cosmos Db. There are also software development kits (SDKs) available for the users to use along with generic REST API.

But with Cosmos DB a superset of functionality is available which can be used for additional techniques. As the API is shared the user can smoothly transfer the data between Azure Table and Cosmos DB.

4. Billing

Azure’s Table Storage Billing is determined depending upon the storage volume used by them. Pricing is done per GB and it is affected by the user’s redundancy level.

The higher level GB they use the cheaper the pricing is. The users will be also billed according to the number of operations each day per 10k operations.

Similarly, Azure’s Cosmos DB is determined by the number of throughput request units (RUs). The user’s database is provisioned at a rate of 100RU per second and they are charged on an hourly basis as they are used. They will be also billed for the storage per GB at a higher rate than Azure’s Table Storage.

Performance Optimization Tips for Azure Table Storage

While using Azure’s Table Storage there are a few tips that the user can apply for optimizing the performance.

The following tips can be helpful to offset a few performance limitations in comparison to Cosmos DB, which allows them to choose a cheaper of two options.

1. Targets for data operations

If the users are expecting an increase in the traffic in their Azure Table Database, they should try to ramp up slowly whenever it is possible.

Also, the service will automatically perform load balances, and if there are sudden bursts of traffic it might cause lag.
Scaling with Azure’s Table Storage is not immediate and their workloads might experience timeouts or throttling while load balancing is adjusted.

2. Network Throughput

While accessing the Table Storage service from on-premises applications or the applications that require high throughput the limitation question is mostly raised with the client.
For avoiding this the user can select larger Azure instances or they can use the clustered machines. It will also provide a greater network capacity.

3. Location

For minimizing the latency, the users should regularly practice placing their clients and the database inside the same Azure region. In this way, it has also added the benefit of eliminating the bandwidth costs as the data transfers within a region are free of cost.

If the users are using the applications that are hosted outside the Azure region then they should try to store the database closest to the region where the applications are hosted.

Similarly, for distributed applications, the users should consider using multiple storage accounts, one per region of distribution.

It is one of the best solutions that work if the user’s data is regionally unique.

4. Unbounded Parallelism

Users can get help from Parallelism which improves the performance level of Azure Table Service.

Users should take care while establishing the limitation of threads as they allow. It includes the limitation of requests for both downloading or uploading the data across multiple items in a single partition or multiple partitions inside the same account.

But, mentioning the limitations on parallelism helps the users to prevent their clients from exceeding their capabilities.

Also, their storage account prevents scaling limitations. It also lowers the possibility of experiencing throttling or increased latency.

5. Client Libraries and Tools

For ensuring the performance level the users can test and use the latest tools and client libraries offered by Azure.
It includes the tools for Azure CLI and PowerShell. Azure libraries are specially designed for improving the performance and they also make sure that they are up to date with the service’s latest version.

Optimizing Azure Table Storage with Azure NetApp Files

Azure NetApp Files is a popular Microsoft Azure File Storage service that is built on the basis of NetApp technology which provides them with file capabilities in Azure even if their core business applications require it.

The users can get enterprise graded data management and storage in Azure so that they can manage their workloads and applications easily. The users can also transfer all their file-based applications to the cloud.

Azure NetApp Files will also issue related to availability and performance for enterprises that wish to transfer the mission-critical applications in the cloud. The workloads include the following:

• HPC
• SAP
• Linux
• Oracle
• SQL Server Workloads
• Windows Virtual Desktops and many more.

Azure NetApp Files will also resolve the availability and performance-based challenges applications to Azure even their business-critical workloads along with extreme file throughput with sub-millisecond response times.

Design for Efficient Reads and Writes

1. Design the Table service solution to be read-efficient

Created for querying in read-intensive applications: When designing your tables, consider the queries (especially the latency-sensitive ones) that will be executed before you consider how you will update your entities. This usually yields an efficient and performant solution.

In your queries, include both PartitionKey and RowKey: These types of point queries are the most efficient table service queries.

Think about storing duplicate copies of entities: Because table storage is inexpensive, consider storing the same entity multiple times (with different keys) to allow for more efficient queries.

Think about denormalizing your data: Because table storage is inexpensive, consider denormalizing your data. Store summary entities, for example, so that queries for aggregate data only need to access a single entity.

Make use of compound key values: PartitionKey and RowKey are the only keys you have. Use compound key values, for example, to enable alternate keyed access paths to entities.
Make use of query projection: By using queries that select only the fields you need; you can reduce the amount of data transferred over the network.

2. Design the Table service solution to be write-efficient

Hot partitions should not be created: Choose keys that allow you to distribute your requests across multiple partitions at any time.
Avoid traffic snarls: Avoid traffic spikes by smoothing traffic over a reasonable period of time.

You don’t have to create a separate table for each type of entity: When atomic transactions across entity types are required, these multiple entity types can be stored in the same partition of the same table.

Consider the maximum throughput required: You must be aware of the Table service’s scalability targets and ensure that your design does not cause you to exceed them.

3. Design for Querying

Table service solutions can be either read-intensive or write-intensive, or a combination of the two.

Typically, a design that is efficient for read operations is also efficient for write operations.

A good place to start when designing your Table service solution to enable efficient data reading is to ask yourself, “What queries will my application need to execute to retrieve the data it requires from the Table service?”

4. Designing for Data Modification

a. Optimizing the performance of insert, update, and delete operations

You must be able to identify an entity using the PartitionKey and RowKey values in order to update or delete it.

In this regard, your selection of PartitionKey and RowKey for modifying entities should adhere to the same criteria as your selection of support point queries, as you want to identify entities as efficiently as possible.

You don’t want to use an inefficient partition or table scan to find an entity and find the PartitionKey and RowKey values you need to update or delete it.

b. Ensuring consistency in your stored entities

Another important consideration in selecting keys for optimizing data modifications is how to ensure consistency through the use of atomic transactions.

An EGT can only operate on entities stored in the same partition.

c. Ensuring the design for efficient modifications facilitates efficient queries

A design for efficient querying results in efficient modifications in many cases, but you should always evaluate whether this is the case for your specific scenario.

Some of the patterns in the article Table Design Patterns explicitly evaluate trade-offs between querying and modifying entities, and the number of each type of operation should always be considered.

Table Design Patterns

In this section of the article, we will discuss patterns for Table service solutions. You will also see how you can address some of the issues and trade-offs discussed in previous Table storage design articles in a practical manner.

In many cases, a design for efficient querying results in efficient modifications, but you should always check to see if this is the case for your specific scenario. Some of the patterns in Table Design Patterns explicitly evaluate trade-offs between querying and modifying entities, and the number of each type of operation should always be taken into account.

Performance and Cost Optimization

Microsoft has created a number of tried-and-true practices for creating high-performance applications using Table storage.

The checklist identifies key practices that developers can implement to improve performance. Keep these best practices in mind as you design your application and throughout the development process.

Scalability and performance goals for Azure Storage include capacity, transaction rate, and bandwidth.

Users must see Scalability and performance targets for standard storage accounts and Scalability and performance targets for Table storage for more information on Azure Storage scalability targets.

Performance

You must consider factors such as performance, scalability, and cost when designing scalable and performant tables.

These considerations will be familiar if you have previously designed schemas for relational databases, but while there are some similarities between the Azure Table service storage model and relational models, there are also significant differences.

These distinctions typically result in designs that appear counterintuitive or incorrect to someone familiar with relational databases, but make sense when designing for a NoSQL key/value store such as the Azure Table service.

Any design differences reflect the fact that the Table service is intended to support cloud-scale applications containing billions of entities (or rows in relational database terminology) of data, as well as datasets requiring high transaction volumes.

As a result, you must reconsider how you store data and understand how the Table service operates.

A well-designed NoSQL data store can allow your solution to scale much further and at a lower cost than a relational database-based solution.

Cost Optimization

Table storage is relatively cheap, but you should factor in cost estimates for both capacity usage and transaction volume when evaluating any Table service solution.

However, storing denormalized or duplicate data in order to improve the performance or scalability of your solution is a valid approach in many scenarios. See Azure Storage Pricing for more information on pricing.

Features of Azure Table Storage

1. Storing Petabytes of Structured Data

Organizations make use of Azure’s Table Storage for storing their petabytes of semi-structured data and also it helps to lower the costs.

Similar to other data stores such as on-premises or cloud-based Azure’s Table Storage will allow the users to scale up without any manual requirement.

Availability is also not an issue with the help of geo-redundant storage; the stored data gets replicated three times within a region and another three times inside another region which is located hundreds of miles away.

2. Supporting Flexible Data Schema

Azure’s Table Storage is an excellent solution for flexible datasets such as web app user data, address books, device information and other metadata which allows the users to easily build cloud applications.

This allows them to not get restricted within one data model or particular schemas. Because in a table there are multiple rows so it can have different structures.

Also, one can evolve their application and the table schema without taking it offline.

3. Build for Enterprise

Azure’s Table Storage is built with a robust consistency model. When the data is inserted or gets updated inside the Azure Storage all the other data access will get the latest updates.

It is important for the systems running with multiple users who are continuously updating the data stores.

4. Designed for Developers

Azure’s Storage service offers a rich client library for building the applications with the following:

• NET
• Java
• Android
• C++
• Node.js
• PHP
• Ruby
• Python

The client libraries also provide an advanced level of capabilities for Table Storage which includes OData support for querying and optimistic locking capabilities.

The Data which is stored inside the Azure Storage is also accessible with the help of REST API which can be called by using any language that makes HTTP/HTTPS requests.

Uses of Azure Table Storage

Below we have mentioned some of the common use cases where the users utilize Table Storage. They are as follows:

• Users use Azure’s table storage to store TBs of structured data to serve web-scale applications.
• Secondly, users use this service to store massive datasets which do not require complex joins, foreign keys or any stored procedures. Thus, it provides faster access.
• Next, it helps to quickly resolve the querying data with the help of a clustered index.
• Lastly, the users can access the data by using OData protocol and LINQ queries with the help of WCF Data Service .NET Libraries.

Data Storage Pricings

 Conclusion

Thus, we are into the last section of our article and we can conclude this article in simple words that Azure’s Table Storage is capable of storing and processing massive sets of structured, non-relational data and the user’s table will scale up and down according to the demand.