Azure Table storage is a NoSQL datastore and has some differences compared to traditional relational database (RDB) structure. Here are some facts about table storage:
- It’s not the same concept as SQL Server tables.
- It is a NoSQL datastore for storing large amounts of structured data.
- It’s not a RDB. No joint between tables. They are standalone.
- You can create, query, and delete entities. Mandatory properties should be defined for each action.
- Tables can have metadata as well.
- It’s designed for high transactional workloads.
The most important point is that Table storage uses a key/value pair system, which differs from a traditional RDB structure and saves you from all the requirements traditional databases have. As the name implies, a key/value system uses two columns to store the data (key and value). That’s a similar concept to PowerShell Hash tables. A hash table in PowerShell is a collection of name-value pairs where you have a key (left) and value (right).
Table Storage Concept ^
Let’s go back to my previous article and review the concept behind the storage account and relevant sub-components.
Once you create your storage account, you have different storage service options. A table is one of those services and you can create multiple tables under a single storage account. Tables are used to group multiple entities.
Entities are similar to rows in a typical RDB and consist of a primary key and a set of properties. Property, on the other hand, is the actual name/value pair, which is similar to columns.
As this is not a traditional RDB, tables also don’t enforce a fixed scheme. So you can have one row with 5 columns and another row with 2 columns. Or you can store different sets of properties/formats in the same column.
Entity Key Properties: ^
As we said, each entity has a primary key and three default system properties:
- PartitionKey: This property should be populated by the developer for each insert, update, and delete operation. It’s actually used to organize entities in partitions to scale and load-balance tables across storage nodes. Each partition key is a unique identifier and is used to identify the partition that entities belong to. Also, each partition is served from a partition server, which means that you can separate your entities by using different PartitionKeys in multiple partitions, which are served by different servers. Or you can keep them under the same partition to query faster.
- RowKey: Unique strings are used to identify entities within the partitions. PartitionKey and RowKey are usually used together to access particular entities.
- Timestamp: This property is managed by the server itself and tracks the modified time of an entity. It cannot be updated or changed.
Getting Started: ^
First you need a “General Purpose” storage account in order to play with tables.
It’s important to copy your Storage Access Keys as we are going to use them in our script:
Creating your first table is a straightforward process. First you need to create a storage context for your storage account and then pass it to the New-AzureStorageTable cmdlet:
Adding entities with PowerShell ^
Current PowerShell cmdlets are limited when it comes to managing entities. But you can install Storage Client Library packages, which come with Azure SDK for .NET and then you can access .NET classes within PowerShell.
Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity class is part of the Microsoft.WindowsAzure.Storage.Table namespace and helps you to create a PowerShell object.
You then can call the Microsoft.WindowsAzure.Storage.Table.TableOperation.Insert method on this object to add it to the existing table.
First we need to create a connection to our Azure Storage Account and specify table name, storage account name, and access keys.
$StorageAccountName = "storageaccountanil"
$StorageACcessKey = "6nVoPw3yzEEbAEmboxtZ4L4zhg7 +TZX/iFNB7d3Z4KllE2Rg=="
$Context = New-AzureStorageContext $StorageAccountName -StorageAccountKey $StorageACcessKey
$TableName = Get-AzureStorageTable –Name "TestTable01" -Context $Context
Then we need to create a custom object with the DynamicTableEntity class.
$entity = New-Object -TypeName Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity -ArgumentList Partition1, Row1
If you check the entity variable, you will see the assigned values:
I specified Partition1 and Row1 for this entity as these properties are mandatory.
Now we should call the TableOperation.Insert method
$Add = $TableName.CloudTable.Execute([Microsoft.WindowsAzure.Storage.Table.TableOperation]::Insert($entity))
This is a simple example of using .NET classes and methods to manage entities with PowerShell. You can always create a PowerShell function, which accepts parameters like partitionKey, RowKey, Key, and Value and does batch entity adding.
Or you can simply create a PowerShell script, which reads a CSV file and import all the information into the Azure Table.
Querying Table entities with PowerShell ^
In order to query existing entities from a given table, we need to use the Microsoft.WindowsAzure.Storage.Table.TableQuery class.
$queryEntity = New-Object Microsoft.WindowsAzure.Storage.Table.TableQuery
$entities = $TableName.CloudTable.ExecuteQuery($queryEntity)
You can also define additional filter options. A QueryEntity object accepts additional properties, as follows:
You can also easily play with output using a PowerShell format-table:
This has been a quick overview of Azure Table Storage and I wanted to show you how to leverage existing .NET classes to manage tables and entities via PowerShell. In the next part, we will have a look at File storage service.