Cmdlets for Amazon Redshift

Build 25.0.9434

Establishing a Connection

With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our Redshift Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

Connecting to Amazon Redshift

The following connection properties are usually required to connect to Amazon Redshift.

  • Server: The host name or IP of the server hosting the Amazon Redshift database.
  • Database: The database that you created for your Amazon Redshift cluster.
  • Port (optional): The port of the server hosting the Amazon Redshift database. 5439 by default.

Follow these steps to obtain these 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, obtain the properties from the "Cluster Database Properties" section. The connection property values are the same as the values set in the ODBC URL.

The cmdlet provides secure communication with Amazon Redshift server using SSL encryption. You can optionally turn off SSL encryption by setting UseSSL to false.

You can also leverage SSL authentication to connect to Amazon Redshift data. To do so, specify the following connection properties:

  • SSLClientCert: The name of the certificate store for the client certificate. This is used in the case of 2-way SSL, where truststore and keystore are kept on both the client and server machines.
  • SSLClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
  • SSLClientCertSubject: The subject of the TLS/SSL client certificate. This is used to locate the certificate in the store.
  • SSLClientCertType: The certificate type of the client store.
  • SSLServerCert: The certificate to be accepted from the server.

Example connection string (standard user and password pair and inactive SSL encryption): 

User=username;Password=password;Server=example.us-west-2.redshift.amazonaws.com;Database=your_database;UseSSL=false;"

Authenticating to Amazon Redshift

CData Cmdlets PowerShell Module for Amazon Redshift supports authentication via standard credentials, IAM credentials, ADFS, Ping Federate, Microsoft Entra ID (Azure AD), or Azure AD PKCE.

Standard Authentication

To connect to Amazon Redshift with login credentials, set these properties:

Example connection string: 

AuthScheme=Basic;User=user;Password=password;Server=example.us-west-2.redshift.amazonaws.com;Database=your_database;

IAM Credentials

Set the AuthScheme to IAMCredentials.

Example connection string: 

AuthScheme=IAMCredentials;Server=example.us-west-2.redshift.amazonaws.com;Database=your_database;User=your_user;AWSAccessKey=your_access_key;AWSSecretKey=your_secretkey;

If you are connecting using an IAM role with temporary credentials you are also required to apply AWSSessionToken.

You can optionally apply:

  • AutoCreate: Create a database user with the name specified for User if one does not exist while connecting.
  • DbGroups: Database groups the database user joins for the current session.

ADFS

To connect to ADFS, set these properties:

Example connection string: 

AuthScheme=ADFS;User=username;Password=password;SSOLoginURL='https://sts.company.com';

ADFS Integrated

The ADFS Integrated flow indicates you are connecting with the user credentials of the currently logged in Windows user. To use the ADFS Integrated flow, do not specify the User and Password, but otherwise follow the same steps noted above under ADFS.

PingFederate

To connect to PingFederate, set these properties:

  • AuthScheme: PingFederate.
  • User: The authenticating PingFederate user.
  • Password: The authenticating user's PingFederate password.
  • SSOLoginURL: The SSO provider's login URL.
  • AWSRoleARN (optional): If you have multiple role ARNs, specify the one you want to use for authorization.
  • AWSPrincipalARN (optional): If you have multiple principal ARNs, specify the one you want to use for authorization.
  • SSOProperties (optional): If you want to include your username and password as an authorization header in requests to Amazon S3, set this to Authscheme=Basic.

To enable mutual SSL authentication for SSOLoginURL, the WS-Trust STS endpoint, configure these SSOProperties:

Example connection string: 

Server=redshift-cluster-1.xxxxxxxxxxxx.us-east-1.redshift.amazonaws.com;Database=dev;Port=5439;UseSSL=true;SSLServerCert=*;AuthScheme=PingFederate;AutoCreate=TRUE;SSOLoginURL=https://mycustomserver.com:9033/idp/sts.wst;SSOExchangeUrl=https://us-east-1.signin.aws.amazon.com/platform/saml/acs/764ef411-xxxxxx;User=admin;Password=PassValue;AWSRegion=NORTHERNVIRGINIA;

Microsoft Entra ID (Azure AD)

Note: Microsoft has rebranded Azure AD as Entra ID. In topics that require the user to interact with the Entra ID Admin site, we use the same names Microsoft does. However, there are still CData connection properties whose names or values reference "Azure AD".

To authenticate via Azure AD, you must have a non-B2C Azure tenant and an active Azure AD account.

Before you can authenticate via Azure AD, you must complete the following tasks:

  1. Create and register an OAuth application
  2. Create and configure a Redshift client application
  3. Define a new Identity Provider
  4. Specify connection properties

The following sections describe these tasks in detail.

Create the OAuth Application

To create an custom OAuth application for logging into your Amazon Redshift database via Azure AD:

  1. At the Azure Active Directory Overview page's left navigation bar, click App registrations. The portal displays the App registrations page.
  2. Click New registrations. The portal displays the Register an application page.
  3. Fill in the requested details:
    • For Name, enter a name (for example, "oauth_application").
    • For Redirect URI, choose Public client/native (mobile and desktop) and enter a valid URL (for example, http://localhost:33333/).
  4. To save your details, click Register. The portal displays the details of your newly-created application.
  5. Save the CallbackURL property value for later use.
  6. At the new application's Overview page, move to the left navigation bar.
  7. Click Certificates & secrets.
  8. Click New client secret. The portal displays the Add a client secret window.
  9. Fill in the requested details.
  10. Click Add at the bottom of the window. The portal creates the client secret.
  11. Save your OAuthClientSecret (the Value field of the OAuth secret that is displayed) for later use.
  12. At the left navigation bar, click Expose an API.
  13. Next to the App ID URI, click Set. The Set the App ID URI dialog displays, with the information filled in from registering.
  14. Click Save.
  15. Click Add a scope.
  16. Fill in the requested details.
  17. Click Add scope.
  18. Save the generated Application ID URI and the API scope for future use. These will look similar to api://6256f0b1-2284-43e4-8501-6e53dec4444f and api://6256f0b1-2284-43e4-8501-6e53dec4444f/jdbc_login.

Create the Redshift Client Application

To create another application, which serves as the client application for your Amazon Redshift database:

  1. Navigate to the Azure Active Directory management page.
  2. Click App registrations.
  3. Click New registrations at the top of the page. The portal displays the Register an application page.
  4. Fill in the requested details.
  5. Click Register. The portal creates the Redshift Client Application and displays its Overview page.
  6. At the left navigation bar, click Certificates & secrets.
  7. Click New client secret. The portal displays the Add a client secret window.
  8. Fill in the requested details.
  9. Click Add.
  10. Save your OAuthClientSecret (the Value field of the OAuth secret that is displayed) for future use.
  11. NSavigate to the new Client Application's Management page.
  12. At the left navigation bar, click API permissions.
  13. Click Add a permission.
  14. Choose Microsoft Graph API.
  15. Click Application permissions.
  16. Select Directory > Directory.Read.All.
  17. Click Add.
  18. Click Grant admin consent.
  19. Click Yes.
  20. An the Azure Active Directory left navigation bar, click Groups. The portal displays the Groups page.
  21. Click New group.
  22. Fill in the requested details.
  23. Click No owners selected. The portal displays the Add owners window.
  24. Select the user who should own this application.
  25. Click Create.

Define a New Identity Provider

To define a new Identify Provider:

  1. In the Azure Active Directory Overviewpage, click App registrations from the left navigation pane.
  2. Select the All applications tab and choose the OAuth application you created earlier. The portal displays the OAuth screen.
  3. In the left navigation bar, click Manifest.
  4. Look in the editor for the accessTokenAcceptedVersion. If the value is null, it is a v1.0 token. If the value is set to 2, this is a v2.0 token.
  5. From the Amazon Redshift instance's query box, submit the identity provider query, following the example below: 
        CREATE IDENTITY PROVIDER oauth_standard TYPE azure
    NAMESPACE 'mynamespace'
    PARAMETERS '{
    "issuer":"https://sts.windows.net/your_tenant_here/",
    "client_id":"YourClientId",
    "client_secret":"YourClientSecret",
    "audience":["your_application_id_uri_here"]
    }'

    Terminology Guide

    • Your issuer ID: The issuer ID to trust when a token is received. The unique identifier for the tenant_id is appended to the issuer. If using a v1.0 token, use https://sts.windows.net/<your_tenant_id_here>/. If using a v2.0 token, use https://login.microsoftonline.com/<your_tenant_id_here>/v2.0.
    • Your client_id: The unique, public identifier of the application registered with the identity provider. This is referred to as the application ID. It is the client ID generated for the second application (the Amazon Redshift client application).
    • Your client_secret: A secret identifier, or password, known only to the identity provider and the registered application. This is the secret generated for the second application (the Amazon Redshift application).
    • audience: The Application ID (URI) assigned to the first application (the OAuth application).

    You can use any name you like for the namespace.

  6. In Amazon Redshift, place the CREATE IDENTITY PROVIDER query (like in the example above) into the query text box.
  7. Click Run.
  8. In the query text box, create a role on the Redshift database in this format: 
    create role "mynamespace:myazuregroup";
    Replace mynamespace with your identity provider's namespace provided in the CREATE IDENTITY PROVIDER query and the name of Azure group that you created earlier.
  9. Click Run at the bottom of the query box.
  10. In the query text box, grant table access to this new role as follows: 
    grant select on all tables in schema public to role "mynamespace:myazuregroup";
    Replace the above example with your namespace and Azure group name.
  11. Click Run.

Set Connection Properties

Set these properties:

  • AuthScheme: AzureAD.
  • Server: The name of your Amazon Redshift server endpoint.
  • Database: The name of your Amazon Redshift database that you would like to connect to.
  • User: The name of the authenticating Amazon Redshift user.
  • AzureTenant: The ID of the Azure Tenant that your OAuth and client applications were created under. Find this in the Overview page of one of the applications under Directory (tenant) ID.
  • SSOLoginURL: The value of the Application ID URI, visible on the Overview page of your OAuth application.
  • Scope: For v1.0 OAuth tokens, set this to the Scopes field in the Expose an API page of your OAuth application. For v2.0 OAuth tokens, this will be the same as the OAuth application's Client ID.
  • OAuthClientID: The first OAuth application client ID in the Overview page of the Amazon Redshift client application that you created.
  • OAuthClientSecret: For your first OAuth application, set this to the Value of the OAuth client secret from the client application's Certificates & secrets page.
  • CallbackURL: The callback URL of the OAuth application.

Troubleshooting Note

If you encounter an "Azure JWT token does not have 'upn' field" error:
  1. On the Azure Active Directory management page, navigate to App Registrations and select your OAuth application.
  2. Click Token configuration in the left navigation bar.
  3. Click Add optional claim.
  4. In the Add optional claim screen, under Token type, click Access.
  5. Under the Claim column, select upn.
  6. Click Add at the bottom.
  7. Select Turn on the Microsoft Graph profile permission (required for claims to appear in the token).
  8. Click Add.
  9. Repeat this process for the client application.
  10. Attempt the connection again.

Azure AD PKCE

Note: Microsoft has rebranded Azure AD as Entra ID. In topics that require the user to interact with the Entra ID Admin site, we use the same names Microsoft does. However, there are still CData connection properties whose names or values reference "Azure AD".

Before you can authenticate via Azure AD PKCE, you must complete the following tasks:

  1. Create and register an OAuth application
  2. Create and configure a Redshift client application
  3. Define a new Identity Provider
  4. Specify connection properties

The following sections describe these tasks in detail.

Create and Register an OAuth Application

To create a custom OAuth application for logging into your Amazon Redshift database via Azure AD PKCE:

  1. At the Azure Active Directory Overview page's left navigation bar, click App registrations. The portal displays the App registrations page.
  2. Click New registrations. The portal displays the Register an application page.
  3. Fill in the requested details:
    • For Name, enter a name (for example, "oauth_application").
    • For Redirect URI, choose Public client/native (mobile and desktop) and enter a valid URL (for example, http://localhost:33333/).
  4. To save your details, click Register. The portal displays the details of your newly-created application.
  5. Save the CallbackURL property value for later use.
  6. At the new application's Overview page, move to the left navigation bar.
  7. Click Expose an API.
  8. Next to the Application ID URI, click Set. The Set the App ID URI dialog displays, with the information filled in from registering.
  9. Click Save.
  10. Click Add a scope.
  11. Fill in the requested details.
  12. Click Add scope.
  13. Save the generated Application ID URI and the API scope for future use. These will look similar to api://6256f0b1-2284-43e4-8501-6e53dec4444f and api://6256f0b1-2284-43e4-8501-6e53dec4444f/jdbc_login.

Create the Redshift Client Application

To create another application, which serves as the client application for your Amazon Redshift database:

  1. Navigate to the Azure Active Directory Overview page.
  2. At the left navigation bar, click App registrations.
  3. At the top of the App registrations page, click New registrations. The portal displays the Register an application page.
  4. Fill in the requested details.
  5. Click Register. The portal creates the Redshift Client Application and displays its Overview page.
  6. At the left navigation bar, click Certificates & secrets.
  7. Click New client secret. The portal displays the Add a client secret window.
  8. Fill in the requested details.
  9. Click Add.
  10. Save your OAuthClientSecret (the Value field of the OAuth secret that is displayed).

  11. In the left navigation bar of the client application's management page, click API permissions.
  12. Click Add a permission.
  13. Choose Microsoft Graph API.
  14. Click Application permissions.
  15. Select Directory > Directory.Read.All.
  16. Click Add.
  17. Click Grant admin consent.
  18. Click Yes.
  19. In the Azure Active Directory left navigation bar, click Groups. The portal displays the Groups page.
  20. Click New group.
  21. Fill in the requested details.
  22. Click No owners selected. The portal displays the Add owners window.
  23. Select the user who should own this application.
  24. Click Create.

Define a New Identity Provider

To define a new Identity Provider:

  1. In the Azure Active Directory left navigation bar, click App registrations.
  2. Select the All applications tab and choose the OAuth application you created earlier. The portal displays the OAuth screen.
  3. At the left navigation bar, click Manifest.
  4. Look in the editor for the accessTokenAcceptedVersion. If the value is null or 1, it is a v1.0 token. If the value is 2, this is a v2.0 token.
  5. From the Amazon Redshift instance's query box, submit the identity provider query, following the example below: 
        CREATE IDENTITY PROVIDER oauth_standard TYPE azure
    NAMESPACE 'mynamespace'
    PARAMETERS '{
    "issuer":"https://sts.windows.net/your_tenant_here/",
    "client_id":"YourRedshiftApplicationClientId",
    "client_secret":"YourRedshiftApplicationClientSecret"
    ,"audience":["your_oauth_application_id_uri_here"]
    }'
    For a v2.0 token issuer the value should be: 
        "issuer":"https://login.microsoftonline.com/your_tenant_here/v2.0",
    Terminology Guide

    • Your issuer ID: The issuer ID to trust when a token is received. The unique identifier for the tenant_id is appended to the issuer. If you are using a v1.0 token, specify https://sts.windows.net<your_tenant_id_here>. If you are using a v2.0 token, specify https://login.microsoftonline.com<your_tenant_id_here>/v2.0..
    • Your client_id: The unique, public identifier of the application registered with the identity provider. This is referred to as the application ID. It is the client ID generated for the second application (the Amazon Redshift application).
    • Your client_secret: A secret identifier, or password, known only to the identity provider and the registered application. This is the secret generated for the second application (the Amazon Redshift application).
    • audience: The Application ID (URI) assigned to the first application (the OAuth application).

    You can use any name you like for the namespace.

  6. In Amazon Redshift, place the CREATE IDENTITY PROVIDER query (like in the example above) into the query text box.
  7. Click Run at the bottom of the query box.
  8. In the query text box, create a role on the Redshift database in this format: 
    create role "mynamespace:myazuregroup";
  9. Replace mynamespace with your identity provider's namespace provided in the CREATE IDENTITY PROVIDER query and the name of the Azure group you created earlier.
  10. Click Run.
  11. In the query text box, grant table access to this new role as follows: 
    grant select on all tables in schema public to role "mynamespace:myazuregroup";
  12. Replace the above example with your namespace and Azure group name.
  13. Click Run.

Set Connection Properties

After finishing the above configuration, specify the following properties in the driver to connect to Amazon Redshift:

  • AuthScheme: AzureADPKCE.
  • Server: The name of your Amazon Redshift server endpoint.
  • Database: The name of the Amazon Redshift database to which you want to connect.
  • Scope: For v1.0 OAuth tokens, set this to the Scopes field in the Expose an API page of your OAuth application (for example, api://d3cb3521-6c20-4e41-b16d-e48c8444ee11/jdbc_login). For v2.0 OAuth tokens, this is the same as the OAuth app's Client ID (for example, d3cb3521-6c20-4e41-b16d-e48c8444ee11).
  • OAuthClientID: The Application (client) ID in the Overview page of the OAuth application you created.
  • CallbackURL: The callback URL of the OAuth application.

Troubleshooting Note

If you encounter an "Azure JWT token does not have 'upn' field" error:

  1. On the Azure Active Directory management page, navigate to App Registrations and select your OAuth application.
  2. Select Token configuration > Add Optional claim in the left navigation bar.
  3. In the Add optional claim screen, under "Token type", click Access.
  4. Under the Claim column, select upn.
  5. Click Add at the bottom.
  6. Select Turn on the Microsoft Graph profile permission, which is required for claims to appear in the token.
  7. Click Add.
  8. Repeat this process for the client app.
  9. Attempt the connection again.

Creating a Connection Object

You can then use the Connect-Redshift cmdlet to create a connection object that can be passed to other cmdlets: 

$conn = Connect-Redshift -User 'admin' -Password 'admin' -Server 'examplecluster.my.us-west-2.redshift.amazonaws.com' -Port '5439' -Database 'dev'

Retrieving Data

After you have created a connection, you can use the other cmdlets to perform operations that you would normally expect to be able to perform against a relational database. The Select-Redshift cmdlet provides a native PowerShell interface for retrieving data: 

$results = Select-Redshift -Connection $conn -Table ""sales_db"."public".Orders" -Columns @("ShipName, ShipCity") -Where "ShipCountry='USA'"
The Invoke-Redshift cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file: 

Select-Redshift -Connection $conn -Table "sales_db"."public".Orders -Where "ShipCountry = 'USA'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\my"sales_db"."public".OrdersData.csv -NoTypeInformation

You will notice that we piped the results from Select-Redshift into a Select-Object cmdlet and excluded some properties before piping them into an Export-CSV cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each row object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON: 

 
PS C:\> $conn  = Connect-Redshift -User 'admin' -Password 'admin' -Server 'examplecluster.my.us-west-2.redshift.amazonaws.com' -Port '5439' -Database 'dev'
PS C:\> $row = Select-Redshift -Connection $conn -Table ""sales_db"."public".Orders" -Columns (ShipName, ShipCity) -Where "ShipCountry = 'USA'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  ""sales_db"."public".Orders",
  "Columns":  [

  ],
  "ShipName":  "MyShipName",
  "ShipCity":  "MyShipCity"
}

Deleting Data

The following line deletes any records that match the criteria: 

Select-Redshift -Connection $conn -Table "sales_db"."public".Orders -Where "ShipCountry = 'USA'" | Remove-Redshift

Modifying Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into Amazon Redshift, checking first whether a record already exists and needs to be updated instead of inserted. 

Import-Csv -Path C:\My"sales_db"."public".OrdersUpdates.csv | %{
  $record = Select-Redshift -Connection $conn -Table "sales_db"."public".Orders -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-Redshift -Connection $conn -Table "sales_db"."public".Orders -Columns @("ShipName","ShipCity") -Values @($_.ShipName, $_.ShipCity) -Where "Id  = `'$_.Id`'"
  }else{
    Add-Redshift -Connection $conn -Table "sales_db"."public".Orders -Columns @("ShipName","ShipCity") -Values @($_.ShipName, $_.ShipCity)
  }
}

Copyright (c) 2025 CData Software, Inc. - All rights reserved.
Build 25.0.9434