Pipeworks | Using Azure Table Storage In Pipeworks

Using Azure Table Storage In Pipeworks

1 of 20

Let's get started by getting the list of tables. To do this, we just use Get-AzureTable, and provide a -StorageAccount and -StorageKey.

You only need to do this once, afterwards, every other command will keep using the same -StorageAccount and -StorageKey.

Get-AzureTable -StorageAccount $myStorageAccount -StorageKey $myStorageKey

2 of 20

Let's create a randomly named table

$tableName = "Table$(Get-Random)"            
Add-AzureTable -Name $tableName

3 of 20

Now, let's go ahead and add a few items into the table. First, let's use Set-AzureTable to set a single item. You can put any flat item into Set-AzureTable.

$item = Get-Counter '\Processor(_total)\% Processor Time' | Select-Object -ExpandProperty CounterSamples            
Set-AzureTable -TableName $TableName -PartitionKey PerformanceInfo -RowKey ARow -InputObject $item

4 of 20

When you pipe items into Set-AzureTable without specifying a -RowKey, it will put each item into a new, sequentially numbered row.

Without a -PartitionKey, each item will go into a partition called 'Default'

1..4 |             
    Foreach-Object {            
        New-Object PSObject -Property @{a=$_;b=get-random}             
    } |             
    Set-AzureTable -TableName $tableName

5 of 20

You can also create psuedo objects with named and put them into Azure. The last typename is the only typename put in.

This allows you to quickly construct http://Schema.org items, or otherwise pair information with a view in PowerShell

$articleLink = New-Object PSObject -Property @{            
    Name = 'Brevity is the Soul of Wit'            
    DatePublished = [DateTime]'2/19/2012'            
    Keyword = 'Quote, Irony'            
    Url = 'http://wiki.answers.com/Q/What_does_%27brevity_is_the_soul_of_wit%27_mean'                 
    Description = 'Brevity may the Soul of Wit, but Polonius was never brief'            
}            
$articleLink.pstypenames.clear()            
$articleLink.pstypenames.add('http://Schema.org/Article')            
$articleLink |             
    Set-AzureTable -TableName $tableName -PartitionKey 'Articles' -RowKey ("{0:x}" -f (Get-Random))

6 of 20

Because Get-Web can pull out Microdata from a website, this makes it simple to pull information from the web into azure tables

Get-Web -Url "http://www.imdb.com/title/tt0000001/" -AsMicrodata -ItemType http://Schema.org/Movie |             
    Set-AzureTable -TableName $tableName -PartitionKey Movies -RowKey { [Guid]::NewGuid() }

7 of 20

Now that you've had a brief tour of how to add stuff into a table, let's show how to search for things We can use Search-AzureTable to search items in a given table.

Search-AzureTable -TableName $tableName

8 of 20

By default, each item will also include a TableName, PartitionKey, RowKey, and LastUpdated.

You can exclude these with -ExcludeTableInfo

Search-AzureTable -TableName $tableName -ExcludeTableInfo

9 of 20

Search-AzureTable can also find a specific item with a -Where filter. This is not Where-Object, but it feels a lot like it

Search-AzureTable -TableName $tableName -Where { $_.PartitionKey -eq 'Default' }

10 of 20

One fun way to use this is to pick out items by their type

Search-AzureTable -TableName $tableName -Where { $_.psTypeName -eq 'http://Schema.org/Article' }

11 of 20

To create a filter, create a small script block:

$myTypeName = 'http://Schema.org/Article'            
$ItsMyTypeName = [ScriptBlock]::Create("`$_.psTypeName -eq '$myTypeName'")            
Search-AzureTable -TableName $tableName -Where $ItsMyTypeName

12 of 20

-Select lets you prune properties

Search-AzureTable -TableName $tableName -Where $ItsMyTypeName -Select Name, Url, Timestamp

13 of 20

Using -Select, it's simple to construct a -iLike (Case-Insensitive Like) operator and only pick out items that appeal to us

$term = 'Brevity'            
Search-AzureTable -TableName $tableName -Select Name, Description, RowKey, PartitionKey |            
    Where-Object {            
        $_.Name -ilike "*$term*" -or            
        $_.Description -like "*$Term*"            
    } |            
    Get-AzureTable -TableName $tableName

14 of 20

We can also remove items with Remove-AzureTable.

Search-AzureTable -TableName $tableName -Select Name, Description, RowKey, PartitionKey |            
    Where-Object {            
        $_.Name -ilike "*$term*" -or            
        $_.Description -like "*$Term*"            
    } |            
    Remove-AzureTable

15 of 20

By default, Remove-AzureTable will prompt for confirmation. We can supress this with -Confirm:$false

Search-AzureTable -TableName $tableName -Where { $_.PartitionKey -eq 'PerformanceInfo' } -Select TableName, RowKey, PartitionKey|             
    Remove-AzureTable -Confirm:$False

16 of 20

Remove-AzureTable has 3 parameters that are working behind the scenes to make this happen -TableName, -RowKey, and -PartitionKey Each item is coming from the pipeline by property name. We can also use them at the command line:

Remove-AzureTable -TableName $tableName -PartitionKey Default -RowKey 0

17 of 20

Remove-AzureTable can also clear out an entire table. It also has a nifty feature, -WhatIf, that would show us what we'd do if we removed a thing

Remove-AzureTable -TableName $tableName -WhatIf

18 of 20

Before we remove this table, let's replicate it to another one.

Because Set-AzureTable can take parameters from the pipeline by property name, it can transmute the parameter info to copy the data.

$replicateToTable = "ReplicatedTable$(Get-Random)"            
Add-AzureTable -TableName $replicateToTable            
Search-AzureTable -TableName $tableName | Set-AzureTable -TableName { $replicateToTable }

19 of 20

Now let's remove the original table

Remove-AzureTable -TableName $tableName -Confirm:$false

20 of 20

And clear up any replicated tables

Get-AzureTable |             
    Where-Object { $_.TableName -like "*replicated*" } |             
    Remove-AzureTable -Confirm:$false