Automate Redshift Integration Tasks from PowerShell

The CData Cmdlets for Redshift 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 Redshift.

PowerShell Cmdlets or ADO.NET Provider?

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

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

To connect to Redshift, set the following:

• : Set this to the host name or IP address of the cluster hosting the Database you want to connect to.
• : Set this to the port of the cluster.
• : Set this to the name of the database. Or, leave this blank to use the default database of the authenticated user.
• : Set this to the username you want to use to authenticate to the Server.
• : Set this to the password you want to use to authenticate to the Server.

You can obtain the and values in the AWS Management Console:

1. Open the Amazon Redshift console (http://console.aws.amazon.com/redshift).
2. On the Clusters page, click the name of the cluster.
3. On the Configuration tab for the cluster, copy the cluster URL from the connection strings displayed.

PowerShell

1. Install the module:

   Install-Module RedshiftCmdlets

2. Connect:

   $redshift = Connect-Redshift -User "$User" -Password "$Password" -Database "$Database" -Server "$Server" -Port "$Port"
   

3. Search for and retrieve data:

   $shipcountry = "USA"
   $orders = Select-Redshift -Connection $redshift -Table "Orders" -Where "ShipCountry = `'$ShipCountry`'"
   $orders
   

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

   $orders = Invoke-Redshift -Connection $redshift -Query 'SELECT * FROM Orders WHERE ShipCountry = @ShipCountry' -Params @{'@ShipCountry'='USA'}
   

ADO.NET

1. Load the provider's assembly:

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

2. Connect to Redshift:

    
   $conn= New-Object System.Data.CData.Redshift.RedshiftConnection("User=admin;Password=admin;Database=dev;Server=examplecluster.my.us-west-2.redshift.amazonaws.com;Port=5439;")
   $conn.Open()
   

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

   $sql="SELECT ShipName, ShipCity from Orders"
   
   $da= New-Object System.Data.CData.Redshift.RedshiftDataAdapter($sql, $conn)
   $dt= New-Object System.Data.DataTable
   $da.Fill($dt)
   
   $dt.Rows | foreach {
   	Write-Host $_.shipname $_.shipcity
   }
    

Update Redshift Data

PowerShell

Update-Redshift -Connection $Redshift -Columns @('ShipName','ShipCity') -Values @('MyShipName', 'MyShipCity') -Table Orders -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Redshift.RedshiftCommand("UPDATE Orders SET ShipCountry='USA' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Redshift.RedshiftParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()

Insert Redshift Data

PowerShell

Add-Redshift -Connection $Redshift -Table Orders -Columns @("ShipName", "ShipCity") -Values @("MyShipName", "MyShipCity") 

ADO.NET

$cmd = New-Object System.Data.CData.Redshift.RedshiftCommand("INSERT INTO Orders (ShipCountry) VALUES (@myShipCountry)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Redshift.RedshiftParameter("@myShipCountry","USA")))
$cmd.ExecuteNonQuery()

Delete Redshift Data

PowerShell

Remove-Redshift -Connection $Redshift -Table "Orders" -Id "MyId"

ADO.NET

$cmd = New-Object System.Data.CData.Redshift.RedshiftCommand("DELETE FROM Orders WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.Redshift.RedshiftParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()

CodeProject