Automate Amazon DynamoDB Integration Tasks from PowerShell

The CData ADO.NET Provider for Amazon DynamoDB is a standard ADO.NET Provider that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to Amazon DynamoDB.

ADO.NET Provider

The ADO.NET Provider provides a SQL interface for Amazon DynamoDB; this tutorial shows how to use the Provider to create, retrieve, update, and delete Amazon DynamoDB data.

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.

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

$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

$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

$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()