Automate Amazon S3 Integration Tasks from PowerShell

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

PowerShell Cmdlets or ADO.NET Provider?

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

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

To authorize Amazon S3 requests, provide the credentials for an administrator account or for an IAM user with custom permissions. Set AccessKey to the access key Id. Set SecretKey to the secret access key.

Note: You can connect as the AWS account administrator, but it is recommended to use IAM user credentials to access AWS services.

For information on obtaining the credentials and other authentication methods, refer to the Getting Started section of the Help documentation.

PowerShell

1. Install the module:

   Install-Module AmazonS3Cmdlets

2. Connect:

   $amazons3 = Connect-AmazonS3 -AccessKey "$AccessKey" -SecretKey "$SecretKey"
   

3. Search for and retrieve data:

   $name = "TestBucket"
   $objectsacl = Select-AmazonS3 -Connection $amazons3 -Table "ObjectsACL" -Where "Name = `'$Name`'"
   $objectsacl
   

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

   $objectsacl = Invoke-AmazonS3 -Connection $amazons3 -Query 'SELECT * FROM ObjectsACL WHERE Name = @Name' -Params @{'@Name'='TestBucket'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Amazon S3:

    
   $conn= New-Object System.Data.CData.AmazonS3.AmazonS3Connection("AccessKey=a123;SecretKey=s123;")
   $conn.Open()
   

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

   $sql="SELECT Name, OwnerId from ObjectsACL"
   
   $da= New-Object System.Data.CData.AmazonS3.AmazonS3DataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.name $_.ownerid
   }