Automate Amazon DynamoDB Integration Tasks from PowerShell

The CData Cmdlets for Amazon DynamoDB are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Amazon DynamoDB.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to Amazon DynamoDB, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete Amazon DynamoDB data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for Amazon DynamoDB. To access Amazon DynamoDB data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for Amazon DynamoDB.

Once you have acquired the necessary connection properties, accessing Amazon DynamoDB data in PowerShell can be enabled in three steps.

The connection to Amazon DynamoDB is made using your AccessKey, SecretKey, and optionally your Domain and Region. Your AccessKey and SecretKey can be obtained on the security credentials page for your Amazon Web Services account. Your Region will be displayed in the upper left-hand corner when you are logged into DynamoDB.

PowerShell

1. Install the module:

   Install-Module AmazonDynamoDBCmdlets

2. Connect:

   $amazondynamodb = Connect-AmazonDynamoDB -Access Key "$Access Key" -Secret Key "$Secret Key" -Domain "$Domain" -Region "$Region"
   

3. Search for and retrieve data:

   $firstname = "Bob"
   $lead = Select-AmazonDynamoDB -Connection $amazondynamodb -Table "Lead" -Where "FirstName = `'$FirstName`'"
   $lead
   

   You can also use the Invoke-AmazonDynamoDB cmdlet to execute SQL commands:

   $lead = Invoke-AmazonDynamoDB -Connection $amazondynamodb -Query 'SELECT * FROM Lead WHERE FirstName = @FirstName' -Params @{'@FirstName'='Bob'}
   

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for Amazon DynamoDB\lib\System.Data.CData.AmazonDynamoDB.dll")
    

2. Connect to Amazon DynamoDB:

    
   $conn= New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBConnection("Access Key=xxx;Secret Key=xxx;Domain=amazonaws.com;Region=OREGON;")
   $conn.Open()
   

3. Instantiate the AmazonDynamoDBDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT Industry, Revenue from Lead"
   
   $da= New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.industry $_.revenue
   }
    

Update Amazon DynamoDB Data

PowerShell

Update-AmazonDynamoDB -Connection $AmazonDynamoDB -Columns @('Industry','Revenue') -Values @('MyIndustry', 'MyRevenue') -Table Lead -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBCommand("UPDATE Lead SET FirstName='Bob' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Amazon DynamoDB Data

PowerShell

Add-AmazonDynamoDB -Connection $AmazonDynamoDB -Table Lead -Columns @("Industry", "Revenue") -Values @("MyIndustry", "MyRevenue") 

ADO.NET

$cmd = New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBCommand("INSERT INTO Lead (FirstName) VALUES (@myFirstName)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBParameter("@myFirstName","Bob")))
$cmd.ExecuteNonQuery()

Delete Amazon DynamoDB Data

PowerShell

Remove-AmazonDynamoDB -Connection $AmazonDynamoDB -Table "Lead" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBCommand("DELETE FROM Lead WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.AmazonDynamoDB.AmazonDynamoDBParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()