Perform Azure Table storage operations with Azure PowerShell

Tip

The content in this article applies to Azure Table storage. However, there is now a premium offering for table storage, the Azure Cosmos DB Table API that offers throughput-optimized tables, global distribution, and automatic secondary indexes. To learn more and try out the premium experience, please check out Azure Cosmos DB Table API. This article's programming language is not yet supported in the premium offering, but will be added in the future.

Azure Table storage is a NoSQL datastore that you can use to store and query huge sets of structured, non-relational data. The main components of the service are tables, entities, and properties. A table is a collection of entities. An entity is a set of properties. Each entity can have up to 252 properties, which are all name-value pairs. This article assumes that you are already familiar with the Azure Table Storage Service concepts. For detailed information, see Understanding the Table Service Data Model and Get started with Azure Table storage using .NET.

This how-to article covers common Azure Table storage operations. You learn how to:

  • Create a table
  • Retrieve a table
  • Add table entities
  • Query a table
  • Delete table entities
  • Delete a table

This how-to article shows you how to create a new Azure Storage account in a new resource group so you can easily remove it when you're done. If you'd rather use an existing Storage account, you can do that instead.

The examples require Azure PowerShell module AzureRM version 4.4.0 or later. In a PowerShell window, run Get-Module -ListAvailable AzureRM to find the version. If nothing is displayed, or you need to upgrade, see Install Azure PowerShell module.

Important

Using this Azure feature from PowerShell requires that you have the AzureRM module installed. This is an older module only available for Windows PowerShell 5.x that receives critical updates, but no longer gets new features. If you have the Az module installed, you will need to install AzureRM as well. To install AzureRM:

  1. Disable AzureRM aliases in Az (if enabled) with Disable-AzureRMAlias. Running this command without Az installed or with aliases disabled is harmless.
  2. Follow the latest install instructions for AzureRM. Even if you have AzureRM installed, update to the latest version.

After Azure PowerShell is installed or updated, you must install module AzureRmStorageTable, which has the commands for managing the entities. To install this module, run PowerShell as an administrator and use the Install-Module command.

Install-Module AzureRmStorageTable

Sign in to Azure

Log in to your Azure subscription with the Connect-AzureRmAccount command and follow the on-screen directions.

Connect-AzureRmAccount

Retrieve list of locations

If you don't know which location you want to use, you can list the available locations. After the list is displayed, find the one you want to use. These examples use eastus. Store this value in the variable location for future use.

Get-AzureRmLocation | select Location 
$location = "eastus"

Create resource group

Create a resource group with the New-AzureRmResourceGroup command.

An Azure resource group is a logical container into which Azure resources are deployed and managed. Store the resource group name in a variable for future use. In this example, a resource group named pshtablesrg is created in the eastus region.

$resourceGroup = "pshtablesrg"
New-AzureRmResourceGroup -ResourceGroupName $resourceGroup -Location $location

Create storage account

Create a standard general-purpose storage account with locally-redundant storage (LRS) using New-AzureRmStorageAccount. Get the storage account context that defines the storage account to be used. When acting on a storage account, you reference the context instead of repeatedly providing the credentials.

$storageAccountName = "pshtablestorage"
$storageAccount = New-AzureRmStorageAccount -ResourceGroupName $resourceGroup `
  -Name $storageAccountName `
  -Location $location `
  -SkuName Standard_LRS `
  -Kind Storage

$ctx = $storageAccount.Context

Create a new table

To create a table, use the New-AzureStorageTable cmdlet. In this example, the table is called pshtesttable.

$tableName = "pshtesttable"
New-AzureStorageTable –Name $tableName –Context $ctx

Retrieve a list of tables in the storage account

Retrieve a list of tables in the storage account using Get-AzureStorageTable.

Get-AzureStorageTable –Context $ctx | select Name

Retrieve a reference to a specific table

To perform operations on a table, you need a reference to the specific table. Get a reference using Get-AzureStorageTable.

$storageTable = Get-AzureStorageTable –Name $tableName –Context $ctx

Managing table entities

Now that you have a table, let's look at how to manage entities, or rows, in the table.

An entity can have up to 255 properties, including 3 system properties: PartitionKey, RowKey, and Timestamp. You are responsible for inserting and updating the values of PartitionKey and RowKey. The server manages the value of Timestamp, which cannot be modified. Together the PartitionKey and RowKey uniquely identify every entity within a table.

  • PartitionKey: Determines the partition that the entity is stored in.
  • RowKey: Uniquely identifies the entity within the partition.

You may define up to 252 custom properties for an entity.

Add table entities

Add entities to a table using Add-StorageTableRow. These examples use partition keys with values "partition1" and "partition2", and row keys equal to state abbreviations. The properties in each entity are username and userid.

$partitionKey1 = "partition1"
$partitionKey2 = "partition2"

# add four rows 
Add-StorageTableRow `
    -table $storageTable `
    -partitionKey $partitionKey1 `
    -rowKey ("CA") -property @{"username"="Chris";"userid"=1}

Add-StorageTableRow `
    -table $storageTable `
    -partitionKey $partitionKey2 `
    -rowKey ("NM") -property @{"username"="Jessie";"userid"=2}

Add-StorageTableRow `
    -table $storageTable `
    -partitionKey $partitionKey1 `
    -rowKey ("WA") -property @{"username"="Christine";"userid"=3}

Add-StorageTableRow `
    -table $storageTable `
    -partitionKey $partitionKey2 `
    -rowKey ("TX") -property @{"username"="Steven";"userid"=4}

Query the table entities

There are several different ways to query the entities in a table.

Retrieve all entities

To retrieve all entities, use Get-AzureStorageTableRowAll.

Get-AzureStorageTableRowAll -table $storageTable | ft

This command yields results similar to the following table:

userid username partition rowkey
1 Chris partition1 CA
3 Christine partition1 WA
2 Jessie partition2 NM
4 Steven partition2 TX

Retrieve entities for a specific partition

To retrieve all entities in a specific partition, use Get-AzureStorageTableRowByPartitionKey.

Get-AzureStorageTableRowByPartitionKey -table $storageTable -partitionKey $partitionKey1 | ft

The results look similar to the following table:

userid username partition rowkey
1 Chris partition1 CA
3 Christine partition1 WA

Retrieve entities for a specific value in a specific column

To retrieve entities where the value in a specific column equals a particular value, use Get-AzureStorageTableRowByColumnName.

Get-AzureStorageTableRowByColumnName -table $storageTable `
    -columnName "username" `
    -value "Chris" `
    -operator Equal

This query retrieves one record.

field value
userid 1
username Chris
PartitionKey partition1
RowKey CA

Retrieve entities using a custom filter

To retrieve entities using a custom filter, use Get-AzureStorageTableRowByCustomFilter.

Get-AzureStorageTableRowByCustomFilter `
    -table $storageTable `
    -customFilter "(userid eq 1)"

This query retrieves one record.

field value
userid 1
username Chris
PartitionKey partition1
RowKey CA

Updating entities

There are three steps for updating entities. First, retrieve the entity to change. Second, make the change. Third, commit the change using Update-AzureStorageTableRow.

Update the entity with username = 'Jessie' to have username = 'Jessie2'. This example also shows another way to create a custom filter using .NET types.

# Create a filter and get the entity to be updated.
[string]$filter = `
    [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("username",`
    [Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"Jessie")
$user = Get-AzureStorageTableRowByCustomFilter `
    -table $storageTable `
    -customFilter $filter

# Change the entity.
$user.username = "Jessie2" 

# To commit the change, pipe the updated record into the update cmdlet.
$user | Update-AzureStorageTableRow -table $storageTable 

# To see the new record, query the table.
Get-AzureStorageTableRowByCustomFilter -table $storageTable `
    -customFilter "(username eq 'Jessie2')"

The results show the Jessie2 record.

field value
userid 2
username Jessie2
PartitionKey partition2
RowKey NM

Deleting table entities

You can delete one entity or all entities in the table.

Deleting one entity

To delete a single entity, get a reference to that entity and pipe it into Remove-AzureStorageTableRow.

# Set filter.
[string]$filter = `
  [Microsoft.WindowsAzure.Storage.Table.TableQuery]::GenerateFilterCondition("username",`
  [Microsoft.WindowsAzure.Storage.Table.QueryComparisons]::Equal,"Jessie2")

# Retrieve entity to be deleted, then pipe it into the remove cmdlet.
$userToDelete = Get-AzureStorageTableRowByCustomFilter `
    -table $storageTable `
    -customFilter $filter
$userToDelete | Remove-AzureStorageTableRow -table $storageTable 

# Retrieve entities from table and see that Jessie2 has been deleted.
Get-AzureStorageTableRowAll -table $storageTable | ft

Delete all entities in the table

To delete all entities in the table, you retrieve them and pipe the results into the remove cmdlet.

# Get all rows and pipe the result into the remove cmdlet.
Get-AzureStorageTableRowAll `
    -table $storageTable | Remove-AzureStorageTableRow -table $storageTable 

# List entities in the table (there won't be any).
Get-AzureStorageTableRowAll -table $storageTable | ft

Delete a table

To delete a table, use Remove-AzureStorageTable. This cmdlet removes the table, including all of its data.

Remove-AzureStorageTable –Name $tableName –Context $ctx

# Retrieve the list of tables to verify the table has been removed.
Get-AzureStorageTable –Context $Ctx | select Name

Clean up resources

If you created a new resource group and storage account at the beginning of this how-to, you can remove all of the assets you have created in this exercise by removing the resource group. This command deletes all resources contained within the group as well as the resource group itself.

Remove-AzureRmResourceGroup -Name $resourceGroup

Next steps

In this how-to article, you learned about common Azure Table storage operations with PowerShell, including how to:

  • Create a table
  • Retrieve a table
  • Add table entities
  • Query a table
  • Delete table entities
  • Delete a table

For more information, see the following articles