Loading Data into Azure Storage Tables using PowerShell

Summary

I had a customer looking to pump a large amount of data (millions of rows) into Azure Storage Tables.  Here’s how we did it.

Prerequisites

If you’d like to follow along, you are going to need:

Azure Storage Tables

Azure Storage Tables are defined this way:  Table Storage stores structured datasets.  Table Storage is a NoSQL key-attribute data store, which allows for rapid development and fast access to large quantities of data.

How can this data be consumed?  This is out of scope for this article, but some of the ways Azure Storage Tables can be consumed are PowerBI and PowerApps.  Nice quick hits.

Building the Table

There are many ways to get things done within Azure.  I’m going to show you an interactive (by-hand) approach to creating the table, then a PowerShell-based approach to loading the data.

Here’s what we’re going to do:

  • Create a new Storage Account
  • Create an Azure Table within the Storage Account
  • Load Data

To create a new storage account, log into http://portal.azure.com, select New, select Storage Account.  The parameter values are

Name: <storage account name>
Deployment Model Resource Manager
Account Kind General Purpose
Performance Standard
Replication Locally-redundant Storage (LRS)
Subscription: <the name of your subscription>
Resource Group: Create New / <resource group name>
Location: <a location near you>

Just a  note:

I prefer to create a new Resource Group for things that I’m playing with.  It makes it easier to delete them when I’m done.  I just have to delete the Resource Group.

Also note:  the name of the Storage Account must be globally unique.  The Azure Portal will tell you if the name you’ve entered is available.

After the Resource Group and Storage Account have been deployed, open up Visual Studio.Within Visual Studio, open up Cloud Explorer (View/Cloud Explorer).  Select Storage Accounts, then the Storage Account you just provisioned.  Mine is called “mdjblogpost”

Cloud Explorer

Cloud Explorer

Right-click on Tables, and select Create Table, then Name the Table.  I’m going to call mine “mdjblogpost”.

2 - Cloud Explorer

Double-click on the Table and you’ll be presented with this screen:

3 - Table Explorer

Notice that I don’t define any additional columns here.  As mentioned above, Azure Table storage is a NoSQL store and every row can have its own schema (referred to as Properties).

Every table requires at least three “columns” or properties:  PartitionKey, RowKey and Timestamp.  These are referred to as System Properties and are automatically included for every entity in a table.  The names of these properties are reserved and cannot be changed.  Timestamp is automatically populated by the system, the developer is responsible for populating PartitionKey and RowKey.

Tables are partititioned to support load balancing across storage nodes.  A table’s entities are organized by partition.  The PartitionKey is a unique identifier for the partition within a given table and forms the first part of an entities primary key.

The second part of the primary key is the RowKey.  The RowKey is a unique identifier for an entity within a given partition.  Together the PartitionKey and RowKey uniquely identify every entity within a table.

Now I’m going to define the properties available within the table.  Every row or entity within a Table can have it’s own unique set of properties.  This is a major departure from relational tables like the tables you’d find in SQL Server and other relational databases.

For the sake of this post, all entities will have the same properties.  Our sample data was stored in a CSV file.  The entities in the file have the following properties:

  • Column1
  • RowKey
  • FirstName
  • LastName

We’re going to map Column1 to PartitionKey.

Loading the Data

I wrote a quick-and-dirty PowerShell script to open a CSV file and load it into the Azure Table line by line.

function Add-Entity()
{
 [CmdletBinding()]

 param
 (
 $table, 
 [string] $partitionKey, 
 [string] $rowKey, 
 [string] $FirstName, 
 [string] $LastName
 )

 $entity = New-Object -TypeName Microsoft.WindowsAzure.Storage.Table.DynamicTableEntity -ArgumentList $partitionKey, $rowKey 
 $entity.Properties.Add("FirstName", $FirstName)
 $entity.Properties.Add("LastName", $LastName)

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

Clear-Host
$subscriptionName = "Azure Pass"
$resourceGroupName = "mdjblogpost"
$storageAccountName = "mdjblogpost"
$location = "East US"
$containerName = "mdjblogpost"
$tableName = "MyTable"

# Log on to Azure and set the active subscription
Add-AzureRMAccount
Select-AzureRmSubscription -SubscriptionName $subscriptionName

# Get the storage key for the storage account
$storageAccountKey = (Get-AzureRmStorageAccountKey -ResourceGroupName $resourceGroupName -Name $storageAccountName).Value[0]

# Get a storage context
$ctx = New-AzureStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $storageAccountKey

# Get a reference to the table
$table = Get-AzureStorageTable -Name $tableName -Context $ctx 

$csv = Import-CSV {path to your data}

ForEach ($line in $csv)
{
 Add-Entity -Table $table -partitionKey $line.Column1 -rowKey $line.RowKey -FirstName $line.FirstName -LastName $line.LastName
}

 

Sample Data:

As you can see from the sample data (see link below), the CSV has four columns:  Column1, RowKey, FirstName and LastName.  Column1 will be mapped to PartitionKey, RowKey to RowKey and so on.

Once the script has run, return to Visual Studio.  Right click on your table and select “Open Table Editor”.  You’ll see the loaded rows.

4 - Table Editor

Sample Data

Additional Reading:

Understanding the Table Service Data Model

 

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: