Microsoft Azure - Understanding Azure Tables for Scalable Data Storage

Explore how Azure Tables provide a flexible, highly scalable solution for storing large amounts of unstructured and semi-structured data. Learn the key differences between Azure Tables and SQL Data Services, making it easier to choose the right storage option for your applications.

Microsoft Azure - Tables

In the context of Azure, a table is a storage entity that does not represent a relational database with foreign keys or complex relationships. Instead, Azure Tables provide a highly scalable and flexible way to store large amounts of data that can be easily queried. This makes Azure Tables ideal for scenarios where you need to manage vast amounts of unstructured or semi-structured data.

If you're looking for relational database services, you would use SQL Data Services in Azure. However, Azure Tables are part of Azure's NoSQL offerings, designed for simpler and scalable data storage.

Key Concepts in Azure Tables

1. Tables: Collections of entities, similar to how a database table contains rows.
2. Entities: The individual items stored in a table, akin to rows in a database.
3. Properties: Attributes of entities, similar to columns in a database. Each entity can have up to 252 custom properties and 3 system properties.

Every entity in a table includes three system properties:

• PartitionKey: A unique identifier for a group of entities, used for load balancing.
• RowKey: A unique identifier for an entity within a partition.
• Timestamp: A system-generated property that indicates when the entity was last modified.

Managing Tables Using PowerShell

Creating a Table

1. Run PowerShell as Administrator:
   • Right-click on Windows PowerShell, choose "Run ISE as Administrator".
2. Access Your Account:

   Syntax

   
   $StorageAccountName = "newstorageaccount"
   $StorageAccountKey = "newstoragekey"
   $Ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey
                  

   Copy Code
3. Create a New Table:

   Syntax

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

   Copy Code

   Output

   
   https://newstorageaccount.table.core.windows.net/MyNewTable
                  

Retrieving a Table

To retrieve an existing table, use this command:

$tabName = "MyNewTable"
Get-AzureStorageTable –Name $tabName –Context $Ctx
      

Deleting a Table

To delete a table:

$tabName = "MyNewTable"
Remove-AzureStorageTable –Name $tabName –Context $Ctx
      

Inserting Rows into a Table

To add rows (entities) to a table, you can define a function and use it to insert multiple entities:

function Add-Entity {
[CmdletBinding()]

param(
   $table,
   [String]$partitionKey,
   [String]$rowKey,
   [String]$title,
   [Int]$id,
   [String]$publisher,
   [String]$author
)

$entity = New-Object -TypeName Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity -ArgumentList $partitionKey, $rowKey
$entity.Properties.Add("Title", $title)
$entity.Properties.Add("ID", $id)
$entity.Properties.Add("Publisher", $publisher)
$entity.Properties.Add("Author", $author)

$result = $table.CloudTable.Execute([Microsoft.WindowsAzure.Storage.Table.TableOperation]::Insert($entity))
}

$StorageAccountName = "tutorialsarena"
$StorageAccountKey = Get-AzureStorageKey -StorageAccountName $StorageAccountName
$Ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey.Primary
$TableName = "MyNewTable"

$table = Get-AzureStorageTable –Name $TableName -Context $Ctx -ErrorAction Ignore

# Add multiple entities to the table
Add-Entity -Table $table -PartitionKey "Partition1" -RowKey "Row1" -Title "C#" -Id 1 -Publisher "xyz" -Author "John Doe"
Add-Entity -Table $table -PartitionKey "Partition2" -RowKey "Row2" -Title "Python" -Id 2 -Publisher "xyz" -Author "Jane Smith"
Add-Entity -Table $table -PartitionKey "Partition3" -RowKey "Row3" -Title "JavaScript" -Id 3 -Publisher "abc" -Author "Alice Johnson"
Add-Entity -Table $table -PartitionKey "Partition4" -RowKey "Row4" -Title "Ruby" -Id 4 -Publisher "abc" -Author "Bob Brown"
      

Retrieving Table Data

To retrieve data from a table:

$StorageAccountName = "tutorialsarena"
$StorageAccountKey = Get-AzureStorageKey -StorageAccountName $StorageAccountName
$Ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey.Primary

$TableName = "MyNewTable"

# Get a reference to the table
$table = Get-AzureStorageTable –Name $TableName -Context $Ctx

# Create a table query
$query = New-Object Microsoft.WindowsAzure.Storage.Table.TableQuery

# Define columns to select
$list = New-Object System.Collections.Generic.List[string]
$list.Add("RowKey")
$list.Add("ID")
$list.Add("Title")
$list.Add("Publisher")
$list.Add("Author")

# Set query details
$query.FilterString = "ID gt 0"
$query.SelectColumns = $list
$query.TakeCount = 20

# Execute the query
$entities = $table.CloudTable.ExecuteQuery($query)

# Display entity properties in table format
$entities | Format-Table PartitionKey, RowKey, @{ Label = "Title"; Expression={$_.Properties["Title"].StringValue}}, @{ Label = "ID"; Expression={$_.Properties["ID"].Int32Value}}, @{ Label = "Publisher"; Expression={$_.Properties["Publisher"].StringValue}}, @{ Label = "Author"; Expression={$_.Properties["Author"].StringValue}} -AutoSize
      

Deleting Rows from a Table

To delete a specific row from a table:

$StorageAccountName = "tutorialsarena"
$StorageAccountKey = Get-AzureStorageKey -StorageAccountName $StorageAccountName
$Ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey.Primary

# Retrieve the table
$TableName = "MyNewTable"
$table = Get-AzureStorageTable -Name $TableName -Context $Ctx -ErrorAction Ignore

# If the table exists, start deleting its entities
if ($table -ne $null) {
# Together, PartitionKey and RowKey uniquely identify every entity within a table
$tableResult = $table.CloudTable.Execute([Microsoft.WindowsAzure.Storage.Table.TableOperation]::Retrieve("Partition1", "Row1"))
$entity = $tableResult.Result

if ($entity -ne $null) {
   $table.CloudTable.Execute([Microsoft.WindowsAzure.Storage.Table.TableOperation]::Delete($entity))
}
}
      

This script deletes the first row from the table, identified by Partition1 and Row1.

Managing Tables Using Azure Storage Explorer

Azure Storage Explorer provides a user-friendly interface to manage tables, making it easier for developers compared to using PowerShell scripts.

1. Login to Your Azure Account:
   • Navigate to your storage account in the Azure portal.
2. Access Storage Explorer:
   • Click on the "Storage explorer" link and download the Azure Storage Explorer for Windows.
3. Add Your Account:
   • After installing and running the tool, click "Add Account". Enter your Storage Account Name and Key, and test the access.
4. Create a Table:
   • Click "New" and enter the table name.
5. Insert Rows into the Table:
   • Click "New" to add rows by specifying field names, data types, and values.
6. View Rows:
   • To see the created rows, click on the table name in the left panel.

Azure Storage Explorer offers a basic yet efficient way to manage tables, allowing you to perform actions like creating, deleting, uploading, and downloading tables without writing scripts. This makes it especially useful for developers who prefer graphical interfaces.