Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Code examples for Amazon Redshift using AWS SDKs
The following code examples show how to use Amazon Redshift with an AWS software development kit (SDK).
*Basics* are code examples that show you how to perform the essential operations within a service.
*Actions* are code excerpts from larger programs and must be run in context. While actions show you how to call individual service functions, you can see actions in context in their related scenarios.
*Scenarios* are code examples that show you how to accomplish specific tasks by calling multiple functions within a service or combined with other AWS services.
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
**Contents**
+ [Basics](service_code_examples_basics.md)
+ [Hello Amazon Redshift](example_redshift_Hello_section.md)
+ [Learn the basics](example_redshift_Scenario_section.md)
+ [Actions](service_code_examples_actions.md)
+ [`CreateCluster`](example_redshift_CreateCluster_section.md)
+ [`DeleteCluster`](example_redshift_DeleteCluster_section.md)
+ [`DescribeClusters`](example_redshift_DescribeClusters_section.md)
+ [`DescribeStatement`](example_redshift_DescribeStatement_section.md)
+ [`ExecuteStatement`](example_redshift_ExecuteStatement_section.md)
+ [`GetStatementResult`](example_redshift_GetStatementResult_section.md)
+ [`ListDatabases`](example_redshift_ListDatabases_section.md)
+ [`ModifyCluster`](example_redshift_ModifyCluster_section.md)
+ [Scenarios](service_code_examples_scenarios.md)
+ [Create a web application to track Amazon Redshift data](example_cross_RedshiftDataTracker_section.md)
+ [Get started with Redshift Serverless](example_redshift_GettingStarted_038_section.md)
+ [Getting started with Amazon Redshift provisioned clusters](example_redshift_GettingStarted_039_section.md)
# Basic examples for Amazon Redshift using AWS SDKs
The following code examples show how to use the basics of Amazon Redshift with AWS SDKs.
**Contents**
+ [Hello Amazon Redshift](example_redshift_Hello_section.md)
+ [Learn the basics](example_redshift_Scenario_section.md)
+ [Actions](service_code_examples_actions.md)
+ [`CreateCluster`](example_redshift_CreateCluster_section.md)
+ [`DeleteCluster`](example_redshift_DeleteCluster_section.md)
+ [`DescribeClusters`](example_redshift_DescribeClusters_section.md)
+ [`DescribeStatement`](example_redshift_DescribeStatement_section.md)
+ [`ExecuteStatement`](example_redshift_ExecuteStatement_section.md)
+ [`GetStatementResult`](example_redshift_GetStatementResult_section.md)
+ [`ListDatabases`](example_redshift_ListDatabases_section.md)
+ [`ModifyCluster`](example_redshift_ModifyCluster_section.md)
# Hello Amazon Redshift
The following code examples show how to get started using Amazon Redshift.
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Main method to run the Hello Amazon Redshift example.
///
/// Command line arguments (not used).
public static async Task Main(string[] args)
{
var redshiftClient = new AmazonRedshiftClient();
Console.WriteLine("Hello, Amazon Redshift! Let's list available clusters:");
var clusters = new List();
try
{
// Use pagination to retrieve all clusters.
var clustersPaginator = redshiftClient.Paginators.DescribeClusters(new DescribeClustersRequest());
await foreach (var response in clustersPaginator.Responses)
{
if (response.Clusters != null)
clusters.AddRange(response.Clusters);
}
Console.WriteLine($"{clusters.Count} cluster(s) retrieved.");
foreach (var cluster in clusters)
{
Console.WriteLine($"\t{cluster.ClusterIdentifier} (Status: {cluster.ClusterStatus})");
}
}
catch (AmazonRedshiftException ex)
{
Console.WriteLine($"Couldn't list clusters. Here's why: {ex.Message}");
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/DescribeClusters) in *AWS SDK for .NET API Reference*.
------
#### [ Go ]
**SDK for Go V2**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/gov2/redshift#code-examples).
```
package main
import (
"context"
"fmt"
"github.com/aws/aws-sdk-go-v2/aws"
"github.com/aws/aws-sdk-go-v2/config"
"github.com/aws/aws-sdk-go-v2/service/redshift"
)
// main uses the AWS SDK for Go V2 to create a Redshift client
// and list up to 10 clusters in your account.
// This example uses the default settings specified in your shared credentials
// and config files.
func main() {
ctx := context.Background()
sdkConfig, err := config.LoadDefaultConfig(ctx)
if err != nil {
fmt.Println("Couldn't load default configuration. Have you set up your AWS account?")
fmt.Println(err)
return
}
redshiftClient := redshift.NewFromConfig(sdkConfig)
count := 20
fmt.Printf("Let's list up to %v clusters for your account.\n", count)
result, err := redshiftClient.DescribeClusters(ctx, &redshift.DescribeClustersInput{
MaxRecords: aws.Int32(int32(count)),
})
if err != nil {
fmt.Printf("Couldn't list clusters for your account. Here's why: %v\n", err)
return
}
if len(result.Clusters) == 0 {
fmt.Println("You don't have any clusters!")
return
}
for _, cluster := range result.Clusters {
fmt.Printf("\t%v : %v\n", *cluster.ClusterIdentifier, *cluster.ClusterStatus)
}
}
```
+ For API details, see [DescribeClusters](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.DescribeClusters) in *AWS SDK for Go API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
```
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.redshift.RedshiftClient;
import software.amazon.awssdk.services.redshift.paginators.DescribeClustersIterable;
/**
* Before running this Java V2 code example, set up your development
* environment, including your credentials.
*
* For more information, see the following documentation topic:
*
* https://docs.aws.amazon.com/sdk-for-java/latest/developer-guide/get-started.html
*/
public class HelloRedshift {
public static void main(String[] args) {
Region region = Region.US_EAST_1;
RedshiftClient redshiftClient = RedshiftClient.builder()
.region(region)
.build();
listClustersPaginator(redshiftClient);
}
public static void listClustersPaginator(RedshiftClient redshiftClient) {
DescribeClustersIterable clustersIterable = redshiftClient.describeClustersPaginator();
clustersIterable.stream()
.flatMap(r -> r.clusters().stream())
.forEach(cluster -> System.out
.println(" Cluster identifier: " + cluster.clusterIdentifier() + " status = " + cluster.clusterStatus()));
}
}
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/DescribeClusters) in *AWS SDK for Java 2.x API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
import boto3
def hello_redshift(redshift_client):
"""
Use the AWS SDK for Python (Boto3) to create an Amazon Redshift client and list
the clusters in your account. This list might be empty if you haven't created
any clusters.
This example uses the default settings specified in your shared credentials
and config files.
:param redshift_client: A Boto3 Redshift Client object.
"""
print("Hello, Redshift! Let's list your clusters:")
paginator = redshift_client.get_paginator("describe_clusters")
clusters = []
for page in paginator.paginate():
clusters.extend(page["Clusters"])
print(f"{len(clusters)} cluster(s) were found.")
for cluster in clusters:
print(f" {cluster['ClusterIdentifier']}")
if __name__ == "__main__":
hello_redshift(boto3.client("redshift"))
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/DescribeClusters) in *AWS SDK for Python (Boto3) API Reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Learn the basics of Amazon Redshift with an AWS SDK
The following code examples show how to:
+ Create a Redshift cluster.
+ List databases in the cluster.
+ Create a table named Movies.
+ Populate the Movies table.
+ Query the Movies table by year.
+ Modify the Redshift cluster.
+ Delete the Amazon Redshift cluster.
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
Create a Redshift wrapper class to manage operations.
```
///
/// Wrapper class for Amazon Redshift operations.
///
public class RedshiftWrapper
{
private readonly IAmazonRedshift _redshiftClient;
private readonly IAmazonRedshiftDataAPIService _redshiftDataClient;
///
/// Constructor for RedshiftWrapper.
///
/// Amazon Redshift client.
/// Amazon Redshift Data API client.
public RedshiftWrapper(IAmazonRedshift redshiftClient, IAmazonRedshiftDataAPIService redshiftDataClient)
{
_redshiftClient = redshiftClient;
_redshiftDataClient = redshiftDataClient;
}
///
/// Create a new Amazon Redshift cluster.
///
/// The identifier for the cluster.
/// The name of the database.
/// The master username.
/// The master user password.
/// The node type for the cluster.
/// The cluster that was created.
public async Task CreateClusterAsync(string clusterIdentifier, string databaseName,
string masterUsername, string masterUserPassword, string nodeType = "ra3.large")
{
try
{
var request = new CreateClusterRequest
{
ClusterIdentifier = clusterIdentifier,
DBName = databaseName,
MasterUsername = masterUsername,
MasterUserPassword = masterUserPassword,
NodeType = nodeType,
NumberOfNodes = 1,
ClusterType = "single-node"
};
var response = await _redshiftClient.CreateClusterAsync(request);
Console.WriteLine($"Created cluster {clusterIdentifier}");
return response.Cluster;
}
catch (ClusterAlreadyExistsException ex)
{
Console.WriteLine($"Cluster already exists: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't create cluster. Here's why: {ex.Message}");
throw;
}
}
///
/// Describe Amazon Redshift clusters.
///
/// Optional cluster identifier to describe a specific cluster.
/// A list of clusters.
public async Task> DescribeClustersAsync(string? clusterIdentifier = null)
{
try
{
var clusters = new List();
var request = new DescribeClustersRequest();
if (!string.IsNullOrEmpty(clusterIdentifier))
{
request.ClusterIdentifier = clusterIdentifier;
}
var clustersPaginator = _redshiftClient.Paginators.DescribeClusters(request);
await foreach (var response in clustersPaginator.Responses)
{
if (response.Clusters != null)
clusters.AddRange(response.Clusters);
}
Console.WriteLine($"{clusters.Count} cluster(s) retrieved.");
foreach (var cluster in clusters)
{
Console.WriteLine($"\t{cluster.ClusterIdentifier} (Status: {cluster.ClusterStatus})");
}
return clusters;
}
catch (ClusterNotFoundException ex)
{
Console.WriteLine($"Cluster {clusterIdentifier} not found: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't describe clusters. Here's why: {ex.Message}");
throw;
}
}
///
/// Modify an Amazon Redshift cluster.
///
/// The identifier for the cluster.
/// The preferred maintenance window.
/// True if successful.
public async Task ModifyClusterAsync(string clusterIdentifier, string preferredMaintenanceWindow)
{
try
{
var request = new ModifyClusterRequest
{
ClusterIdentifier = clusterIdentifier,
PreferredMaintenanceWindow = preferredMaintenanceWindow
};
var response = await _redshiftClient.ModifyClusterAsync(request);
Console.WriteLine($"The modified cluster was successfully modified and has {response.Cluster.PreferredMaintenanceWindow} as the maintenance window");
return true;
}
catch (ClusterNotFoundException ex)
{
Console.WriteLine($"Cluster {clusterIdentifier} not found: {ex.Message}");
return false;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't modify cluster. Here's why: {ex.Message}");
return false;
}
}
///
/// Delete an Amazon Redshift cluster without a final snapshot.
///
/// The identifier for the cluster.
/// True if successful.
public async Task DeleteClusterWithoutSnapshotAsync(string clusterIdentifier)
{
try
{
var request = new DeleteClusterRequest
{
ClusterIdentifier = clusterIdentifier,
SkipFinalClusterSnapshot = true
};
var response = await _redshiftClient.DeleteClusterAsync(request);
Console.WriteLine($"The {clusterIdentifier} was deleted");
return true;
}
catch (ClusterNotFoundException ex)
{
Console.WriteLine($"Cluster not found: {ex.Message}");
return false;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't delete cluster. Here's why: {ex.Message}");
return false;
}
}
///
/// List databases in a Redshift cluster.
///
/// The cluster identifier.
/// The database user.
/// The database name for authentication.
/// A list of database names.
public async Task> ListDatabasesAsync(string clusterIdentifier, string dbUser, string databaseName)
{
try
{
var request = new ListDatabasesRequest
{
ClusterIdentifier = clusterIdentifier,
DbUser = dbUser,
Database = databaseName
};
var response = await _redshiftDataClient.ListDatabasesAsync(request);
var databases = new List();
foreach (var database in response.Databases)
{
Console.WriteLine($"The database name is : {database}");
databases.Add(database);
}
return databases;
}
catch (Amazon.RedshiftDataAPIService.Model.ValidationException ex)
{
Console.WriteLine($"Validation error: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't list databases. Here's why: {ex.Message}");
throw;
}
}
///
/// Create a table in the Redshift database.
///
/// The cluster identifier.
/// The database name.
/// The database user.
/// The statement ID.
public async Task CreateTableAsync(string clusterIdentifier, string database, string dbUser)
{
try
{
var sqlStatement = @"
CREATE TABLE Movies (
id INTEGER PRIMARY KEY,
title VARCHAR(250) NOT NULL,
year INTEGER NOT NULL
)";
var request = new ExecuteStatementRequest
{
ClusterIdentifier = clusterIdentifier,
Database = database,
DbUser = dbUser,
Sql = sqlStatement
};
var response = await _redshiftDataClient.ExecuteStatementAsync(request);
await WaitForStatementToCompleteAsync(response.Id);
Console.WriteLine("Table created: Movies");
return response.Id;
}
catch (Amazon.RedshiftDataAPIService.Model.ValidationException ex)
{
Console.WriteLine($"Validation error: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't create table. Here's why: {ex.Message}");
throw;
}
}
///
/// Insert a record into the Movies table using parameterized query.
///
/// The cluster identifier.
/// The database name.
/// The database user.
/// The movie ID.
/// The movie title.
/// The movie year.
/// The statement ID.
public async Task InsertMovieAsync(string clusterIdentifier, string database, string dbUser,
int id, string title, int year)
{
try
{
var sqlStatement = "INSERT INTO Movies (id, title, year) VALUES (:id, :title, :year)";
var request = new ExecuteStatementRequest
{
ClusterIdentifier = clusterIdentifier,
Database = database,
DbUser = dbUser,
Sql = sqlStatement,
Parameters = new List
{
new SqlParameter { Name = "id", Value = id.ToString() },
new SqlParameter { Name = "title", Value = title },
new SqlParameter { Name = "year", Value = year.ToString() }
}
};
var response = await _redshiftDataClient.ExecuteStatementAsync(request);
await WaitForStatementToCompleteAsync(response.Id);
Console.WriteLine($"Inserted: {title} ({year})");
return response.Id;
}
catch (Amazon.RedshiftDataAPIService.Model.ValidationException ex)
{
Console.WriteLine($"Validation error: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't insert movie. Here's why: {ex.Message}");
throw;
}
}
///
/// Query movies by year using parameterized query.
///
/// The cluster identifier.
/// The database name.
/// The database user.
/// The year to query.
/// A list of movie titles.
public async Task> QueryMoviesByYearAsync(string clusterIdentifier, string database,
string dbUser, int year)
{
try
{
var sqlStatement = "SELECT title FROM Movies WHERE year = :year";
var request = new ExecuteStatementRequest
{
ClusterIdentifier = clusterIdentifier,
Database = database,
DbUser = dbUser,
Sql = sqlStatement,
Parameters = new List
{
new SqlParameter { Name = "year", Value = year.ToString() }
}
};
var response = await _redshiftDataClient.ExecuteStatementAsync(request);
Console.WriteLine($"The identifier of the statement is {response.Id}");
await WaitForStatementToCompleteAsync(response.Id);
var results = await GetStatementResultAsync(response.Id);
var movieTitles = new List();
foreach (var row in results)
{
if (row.Count > 0)
{
var title = row[0].StringValue;
Console.WriteLine($"The Movie title field is {title}");
movieTitles.Add(title);
}
}
return movieTitles;
}
catch (Amazon.RedshiftDataAPIService.Model.ValidationException ex)
{
Console.WriteLine($"Validation error: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't query movies. Here's why: {ex.Message}");
throw;
}
}
///
/// Describe a statement execution.
///
/// The statement ID.
/// The statement description.
public async Task DescribeStatementAsync(string statementId)
{
try
{
var request = new DescribeStatementRequest
{
Id = statementId
};
var response = await _redshiftDataClient.DescribeStatementAsync(request);
return response;
}
catch (Amazon.RedshiftDataAPIService.Model.ResourceNotFoundException ex)
{
Console.WriteLine($"Statement not found: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't describe statement. Here's why: {ex.Message}");
throw;
}
}
///
/// Get the results of a statement execution.
///
/// The statement ID.
/// A list of result rows.
public async Task>> GetStatementResultAsync(string statementId)
{
try
{
var request = new GetStatementResultRequest
{
Id = statementId
};
var response = await _redshiftDataClient.GetStatementResultAsync(request);
return response.Records;
}
catch (Amazon.RedshiftDataAPIService.Model.ResourceNotFoundException ex)
{
Console.WriteLine($"Statement not found: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't get statement result. Here's why: {ex.Message}");
throw;
}
}
///
/// Wait for a statement to complete execution.
///
/// The statement ID.
/// A task representing the asynchronous operation.
private async Task WaitForStatementToCompleteAsync(string statementId)
{
var status = StatusString.SUBMITTED;
DescribeStatementResponse? response = null;
while (status == StatusString.SUBMITTED || status == StatusString.PICKED || status == StatusString.STARTED)
{
await Task.Delay(1000); // Wait 1 second
response = await DescribeStatementAsync(statementId);
status = response.Status;
Console.WriteLine($"...{status}");
}
if (status == StatusString.FINISHED)
{
Console.WriteLine("The statement is finished!");
}
else
{
var errorMessage = response?.Error ?? "Unknown error";
Console.WriteLine($"The statement failed with status: {status}");
Console.WriteLine($"Error message: {errorMessage}");
}
}
///
/// Wait for a cluster to become available.
///
/// The cluster identifier.
/// A task representing the asynchronous operation.
public async Task WaitForClusterAvailableAsync(string clusterIdentifier)
{
Console.WriteLine($"Wait until {clusterIdentifier} is available. This may take a few minutes.");
var startTime = DateTime.Now;
var clusters = await DescribeClustersAsync(clusterIdentifier);
while (clusters[0].ClusterStatus != "available")
{
var elapsed = DateTime.Now - startTime;
Console.WriteLine($"Elapsed Time: {elapsed:mm\\:ss} - Waiting for cluster...");
await Task.Delay(5000); // Wait 5 seconds
clusters = await DescribeClustersAsync(clusterIdentifier);
}
var totalElapsed = DateTime.Now - startTime;
Console.WriteLine($"Cluster is available! Total Elapsed Time: {totalElapsed:mm\\:ss}");
}
}
```
Run an interactive scenario demonstrating Redshift basics.
```
///
/// Amazon Redshift Getting Started Scenario.
///
public class RedshiftBasics
{
public static bool IsInteractive = true;
public static RedshiftWrapper? Wrapper = null;
public static ILogger logger = null!;
private static readonly string _moviesFilePath = "../../../../../../resources/sample_files/movies.json";
///
/// Main method for the Amazon Redshift Getting Started scenario.
///
/// Command line arguments.
public static async Task Main(string[] args)
{
using var host = Host.CreateDefaultBuilder(args)
.ConfigureServices((_, services) =>
services.AddAWSService()
.AddAWSService()
.AddTransient()
)
.Build();
logger = LoggerFactory.Create(builder => { builder.AddConsole(); })
.CreateLogger();
Wrapper = host.Services.GetRequiredService();
await RunScenarioAsync();
}
///
/// Run the complete Amazon Redshift scenario.
///
public static async Task RunScenarioAsync()
{
// Set all variables to default values
string userName = "awsuser";
string userPassword = "AwsUser1000";
string clusterIdentifier = "redshift-cluster-movies";
var databaseName = "dev";
int recordCount = 50;
int year = 2013;
try
{
Console.WriteLine(
"================================================================================");
Console.WriteLine("Welcome to the Amazon Redshift SDK Getting Started scenario.");
Console.WriteLine(
"This .NET program demonstrates how to interact with Amazon Redshift by using the AWS SDK for .NET.");
Console.WriteLine("Let's get started...");
Console.WriteLine(
"================================================================================");
// Step 1: Get user credentials (if interactive)
if (IsInteractive)
{
Console.WriteLine("Please enter a user name for the cluster (default is awsuser):");
var userInput = Console.ReadLine();
if (!string.IsNullOrEmpty(userInput))
userName = userInput;
Console.WriteLine("================================================================================");
Console.WriteLine("Please enter a user password for the cluster (default is AwsUser1000):");
var passwordInput = Console.ReadLine();
if (!string.IsNullOrEmpty(passwordInput))
userPassword = passwordInput;
Console.WriteLine("================================================================================");
// Step 2: Get cluster identifier
Console.WriteLine("Enter a cluster id value (default is redshift-cluster-movies):");
var clusterInput = Console.ReadLine();
if (!string.IsNullOrEmpty(clusterInput))
clusterIdentifier = clusterInput;
}
else
{
Console.WriteLine($"Using default values: userName={userName}, clusterIdentifier={clusterIdentifier}");
}
// Step 3: Create Redshift cluster
await Wrapper!.CreateClusterAsync(clusterIdentifier, databaseName, userName, userPassword);
Console.WriteLine("================================================================================");
// Step 4: Wait for cluster to become available
Console.WriteLine("================================================================================");
await Wrapper.WaitForClusterAvailableAsync(clusterIdentifier);
Console.WriteLine("================================================================================");
// Step 5: List databases
Console.WriteLine("================================================================================");
Console.WriteLine($" When you created {clusterIdentifier}, the dev database is created by default and used in this scenario.");
Console.WriteLine(" To create a custom database, you need to have a CREATEDB privilege.");
Console.WriteLine(" For more information, see the documentation here: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.html.");
if (IsInteractive)
{
Console.WriteLine("Press Enter to continue...");
Console.ReadLine();
}
Console.WriteLine("================================================================================");
Console.WriteLine("================================================================================");
Console.WriteLine($"List databases in {clusterIdentifier}");
if (IsInteractive)
{
Console.WriteLine("Press Enter to continue...");
Console.ReadLine();
}
await Wrapper.ListDatabasesAsync(clusterIdentifier, userName, databaseName);
Console.WriteLine("================================================================================");
// Step 6: Create Movies table
Console.WriteLine("================================================================================");
Console.WriteLine("Now you will create a table named Movies.");
if (IsInteractive)
{
Console.WriteLine("Press Enter to continue...");
Console.ReadLine();
}
await Wrapper.CreateTableAsync(clusterIdentifier, databaseName, userName);
Console.WriteLine("================================================================================");
// Step 7: Populate the Movies table
Console.WriteLine("================================================================================");
Console.WriteLine("Populate the Movies table using the Movies.json file.");
if (IsInteractive)
{
Console.WriteLine("Specify the number of records you would like to add to the Movies Table.");
Console.WriteLine("Please enter a value between 50 and 200.");
Console.Write("Enter a value: ");
var recordCountInput = Console.ReadLine();
if (int.TryParse(recordCountInput, out var inputCount) && inputCount is >= 50 and <= 200)
{
recordCount = inputCount;
}
else
{
Console.WriteLine($"Invalid input. Using default value of {recordCount}.");
}
}
else
{
Console.WriteLine($"Using default record count: {recordCount}");
}
await PopulateMoviesTableAsync(clusterIdentifier, databaseName, userName, recordCount);
Console.WriteLine($"{recordCount} records were added to the Movies table.");
Console.WriteLine("================================================================================");
// Step 8 & 9: Query movies by year
Console.WriteLine("================================================================================");
Console.WriteLine("Query the Movies table by year. Enter a value between 2012-2014.");
if (IsInteractive)
{
Console.Write("Enter a year: ");
var yearInput = Console.ReadLine();
if (int.TryParse(yearInput, out var inputYear) && inputYear is >= 2012 and <= 2014)
{
year = inputYear;
}
else
{
Console.WriteLine($"Invalid input. Using default value of {year}.");
}
}
else
{
Console.WriteLine($"Using default year: {year}");
}
await Wrapper.QueryMoviesByYearAsync(clusterIdentifier, databaseName, userName, year);
Console.WriteLine("================================================================================");
// Step 10: Modify the cluster
Console.WriteLine("================================================================================");
Console.WriteLine("Now you will modify the Redshift cluster.");
if (IsInteractive)
{
Console.WriteLine("Press Enter to continue...");
Console.ReadLine();
}
await Wrapper.ModifyClusterAsync(clusterIdentifier, "wed:07:30-wed:08:00");
Console.WriteLine("================================================================================");
// Step 11 & 12: Delete cluster confirmation
Console.WriteLine("================================================================================");
if (IsInteractive)
{
Console.WriteLine("Would you like to delete the Amazon Redshift cluster? (y/n)");
var deleteResponse = Console.ReadLine();
if (deleteResponse?.ToLower() == "y")
{
await Wrapper.DeleteClusterWithoutSnapshotAsync(clusterIdentifier);
}
}
else
{
Console.WriteLine("Deleting the Amazon Redshift cluster...");
await Wrapper.DeleteClusterWithoutSnapshotAsync(clusterIdentifier);
}
Console.WriteLine("================================================================================");
Console.WriteLine("================================================================================");
Console.WriteLine("This concludes the Amazon Redshift SDK Getting Started scenario.");
Console.WriteLine("================================================================================");
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred during the scenario: {ex.Message}");
Console.WriteLine("Deleting the Amazon Redshift cluster...");
await Wrapper!.DeleteClusterWithoutSnapshotAsync(clusterIdentifier);
throw;
}
}
///
/// Populate the Movies table with data from the JSON file.
///
/// The cluster identifier.
/// The database name.
/// The database user.
/// Number of records to insert.
private static async Task PopulateMoviesTableAsync(string clusterIdentifier, string database, string dbUser, int recordCount)
{
if (!File.Exists(_moviesFilePath))
{
throw new FileNotFoundException($"Required movies data file not found at: {_moviesFilePath}");
}
var jsonContent = await File.ReadAllTextAsync(_moviesFilePath);
var options = new JsonSerializerOptions
{
PropertyNameCaseInsensitive = true
};
var movies = JsonSerializer.Deserialize>(jsonContent, options);
if (movies == null || movies.Count == 0)
{
throw new InvalidOperationException("Failed to parse movies JSON file or file is empty.");
}
var insertCount = Math.Min(recordCount, movies.Count);
for (int i = 0; i < insertCount; i++)
{
var movie = movies[i];
await Wrapper!.InsertMovieAsync(clusterIdentifier, database, dbUser, i, movie.Title, movie.Year);
}
}
///
/// Movie data model.
///
private class Movie
{
public string Title { get; set; } = string.Empty;
public int Year { get; set; }
}
}
```
+ For API details, see the following topics in *AWS SDK for .NET API Reference*.
+ [CreateCluster](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/CreateCluster)
+ [DescribeClusters](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/DescribeClusters)
+ [DescribeStatement](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/DescribeStatement)
+ [ExecuteStatement](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/ExecuteStatement)
+ [GetStatementResult](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/GetStatementResult)
+ [ListDatabasesPaginator](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/ListDatabasesPaginator)
+ [ModifyCluster](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/ModifyCluster)
------
#### [ Go ]
**SDK for Go V2**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/gov2/redshift#code-examples).
```
package scenarios
import (
"context"
"encoding/json"
"errors"
"fmt"
"log"
"math/rand"
"strings"
"time"
"github.com/aws/aws-sdk-go-v2/aws"
redshift_types "github.com/aws/aws-sdk-go-v2/service/redshift/types"
redshiftdata_types "github.com/aws/aws-sdk-go-v2/service/redshiftdata/types"
"github.com/aws/aws-sdk-go-v2/service/secretsmanager"
"github.com/awsdocs/aws-doc-sdk-examples/gov2/demotools"
"github.com/awsdocs/aws-doc-sdk-examples/gov2/redshift/actions"
"github.com/aws/aws-sdk-go-v2/service/redshift"
"github.com/aws/aws-sdk-go-v2/service/redshiftdata"
)
// IScenarioHelper abstracts input and wait functions from a scenario so that they
// can be mocked for unit testing.
type IScenarioHelper interface {
GetName() string
}
const rMax = 100000
type ScenarioHelper struct {
Prefix string
Random *rand.Rand
}
// GetName returns a unique name formed of a prefix and a random number.
func (helper ScenarioHelper) GetName() string {
return fmt.Sprintf("%v%v", helper.Prefix, helper.Random.Intn(rMax))
}
// RedshiftBasicsScenario separates the steps of this scenario into individual functions so that
// they are simpler to read and understand.
type RedshiftBasicsScenario struct {
sdkConfig aws.Config
helper IScenarioHelper
questioner demotools.IQuestioner
pauser demotools.IPausable
filesystem demotools.IFileSystem
redshiftActor *actions.RedshiftActions
redshiftDataActor *actions.RedshiftDataActions
secretsmanager *SecretsManager
}
// SecretsManager is used to retrieve username and password information from a secure service.
type SecretsManager struct {
SecretsManagerClient *secretsmanager.Client
}
// RedshiftBasics constructs a new Redshift Basics runner.
func RedshiftBasics(sdkConfig aws.Config, questioner demotools.IQuestioner, pauser demotools.IPausable, filesystem demotools.IFileSystem, helper IScenarioHelper) RedshiftBasicsScenario {
scenario := RedshiftBasicsScenario{
sdkConfig: sdkConfig,
helper: helper,
questioner: questioner,
pauser: pauser,
filesystem: filesystem,
secretsmanager: &SecretsManager{SecretsManagerClient: secretsmanager.NewFromConfig(sdkConfig)},
redshiftActor: &actions.RedshiftActions{RedshiftClient: redshift.NewFromConfig(sdkConfig)},
redshiftDataActor: &actions.RedshiftDataActions{RedshiftDataClient: redshiftdata.NewFromConfig(sdkConfig)},
}
return scenario
}
// Movie makes it easier to use Movie objects given in json format.
type Movie struct {
ID int `json:"id"`
Title string `json:"title"`
Year int `json:"year"`
}
// User makes it easier to get the User data back from SecretsManager and use it later.
type User struct {
Username string `json:"userName"`
Password string `json:"userPassword"`
}
// Run runs the RedshiftBasics interactive example that shows you how to use Amazon
// Redshift and how to interact with its common endpoints.
//
// 0. Retrieve username and password information to access Redshift.
// 1. Create a cluster.
// 2. Wait for the cluster to become available.
// 3. List the available databases in the region.
// 4. Create a table named "Movies" in the "dev" database.
// 5. Populate the movies table from the "movies.json" file.
// 6. Query the movies table by year.
// 7. Modify the cluster's maintenance window.
// 8. Optionally clean up all resources created during this demo.
//
// This example creates an Amazon Redshift service client from the specified sdkConfig so that
// you can replace it with a mocked or stubbed config for unit testing.
//
// It uses a questioner from the `demotools` package to get input during the example.
// This package can be found in the ..\..\demotools folder of this repo.
func (runner *RedshiftBasicsScenario) Run(ctx context.Context) {
user := User{}
secretId := "s3express/basics/secrets"
clusterId := "demo-cluster-1"
maintenanceWindow := "wed:07:30-wed:08:00"
databaseName := "dev"
tableName := "Movies"
fileName := "Movies.json"
nodeType := "ra3.xlplus"
clusterType := "single-node"
defer func() {
if r := recover(); r != nil {
log.Println("Something went wrong with the demo.")
_, isMock := runner.questioner.(*demotools.MockQuestioner)
if isMock || runner.questioner.AskBool("Do you want to see the full error message (y/n)?", "y") {
log.Println(r)
}
runner.cleanUpResources(ctx, clusterId, databaseName, tableName, user.Username, runner.questioner)
}
}()
// Retrieve the userName and userPassword from SecretsManager
output, err := runner.secretsmanager.SecretsManagerClient.GetSecretValue(ctx, &secretsmanager.GetSecretValueInput{
SecretId: aws.String(secretId),
})
if err != nil {
log.Printf("There was a problem getting the secret value: %s", err)
log.Printf("Please make sure to create a secret named 's3express/basics/secrets' with keys of 'userName' and 'userPassword'.")
panic(err)
}
err = json.Unmarshal([]byte(*output.SecretString), &user)
if err != nil {
log.Printf("There was a problem parsing the secret value from JSON: %s", err)
panic(err)
}
// Create the Redshift cluster
_, err = runner.redshiftActor.CreateCluster(ctx, clusterId, user.Username, user.Password, nodeType, clusterType, true)
if err != nil {
var clusterAlreadyExistsFault *redshift_types.ClusterAlreadyExistsFault
if errors.As(err, &clusterAlreadyExistsFault) {
log.Println("Cluster already exists. Continuing.")
} else {
log.Println("Error creating cluster.")
panic(err)
}
}
// Wait for the cluster to become available
waiter := redshift.NewClusterAvailableWaiter(runner.redshiftActor.RedshiftClient)
err = waiter.Wait(ctx, &redshift.DescribeClustersInput{
ClusterIdentifier: aws.String(clusterId),
}, 5*time.Minute)
if err != nil {
log.Println("An error occurred waiting for the cluster.")
panic(err)
}
// Get some info about the cluster
describeOutput, err := runner.redshiftActor.DescribeClusters(ctx, clusterId)
if err != nil {
log.Println("Something went wrong trying to get information about the cluster.")
panic(err)
}
log.Println("Here's some information about the cluster.")
log.Printf("The cluster's status is %s", *describeOutput.Clusters[0].ClusterStatus)
log.Printf("The cluster was created at %s", *describeOutput.Clusters[0].ClusterCreateTime)
// List databases
log.Println("List databases in", clusterId)
runner.questioner.Ask("Press Enter to continue...")
err = runner.redshiftDataActor.ListDatabases(ctx, clusterId, databaseName, user.Username)
if err != nil {
log.Printf("Failed to list databases: %v\n", err)
panic(err)
}
// Create the "Movies" table
log.Println("Now you will create a table named " + tableName + ".")
runner.questioner.Ask("Press Enter to continue...")
err = nil
result, err := runner.redshiftDataActor.CreateTable(ctx, clusterId, databaseName, tableName, user.Username, runner.pauser, []string{"title VARCHAR(256)", "year INT"})
if err != nil {
log.Printf("Failed to create table: %v\n", err)
panic(err)
}
describeInput := redshiftdata.DescribeStatementInput{
Id: result.Id,
}
query := actions.RedshiftQuery{
Context: ctx,
Input: describeInput,
Result: result,
}
err = runner.redshiftDataActor.WaitForQueryStatus(query, runner.pauser, true)
if err != nil {
log.Printf("Failed to execute query: %v\n", err)
panic(err)
}
log.Printf("Successfully executed query\n")
// Populate the "Movies" table
runner.PopulateMoviesTable(ctx, clusterId, databaseName, tableName, user.Username, fileName)
// Query the "Movies" table by year
log.Println("Query the Movies table by year.")
year := runner.questioner.AskInt(
fmt.Sprintf("Enter a value between %v and %v:", 2012, 2014),
demotools.InIntRange{Lower: 2012, Upper: 2014})
runner.QueryMoviesByYear(ctx, clusterId, databaseName, tableName, user.Username, year)
// Modify the cluster's maintenance window
runner.redshiftActor.ModifyCluster(ctx, clusterId, maintenanceWindow)
// Delete the Redshift cluster if confirmed
runner.cleanUpResources(ctx, clusterId, databaseName, tableName, user.Username, runner.questioner)
log.Println("Thanks for watching!")
}
// cleanUpResources asks the user if they would like to delete each resource created during the scenario, from most
// impactful to least impactful. If any choice to delete is made, further deletion attempts are skipped.
func (runner *RedshiftBasicsScenario) cleanUpResources(ctx context.Context, clusterId string, databaseName string, tableName string, userName string, questioner demotools.IQuestioner) {
deleted := false
var err error = nil
if questioner.AskBool("Do you want to delete the entire cluster? This will clean up all resources. (y/n)", "y") {
deleted, err = runner.redshiftActor.DeleteCluster(ctx, clusterId)
if err != nil {
log.Printf("Error deleting cluster: %v", err)
}
}
if !deleted && questioner.AskBool("Do you want to delete the dev table? This will clean up all inserted records but keep your cluster intact. (y/n)", "y") {
deleted, err = runner.redshiftDataActor.DeleteTable(ctx, clusterId, databaseName, tableName, userName)
if err != nil {
log.Printf("Error deleting movies table: %v", err)
}
}
if !deleted && questioner.AskBool("Do you want to delete all rows in the Movies table? This will clean up all inserted records but keep your cluster and table intact. (y/n)", "y") {
deleted, err = runner.redshiftDataActor.DeleteDataRows(ctx, clusterId, databaseName, tableName, userName, runner.pauser)
if err != nil {
log.Printf("Error deleting data rows: %v", err)
}
}
if !deleted {
log.Print("Please manually delete any unwanted resources.")
}
}
// loadMoviesFromJSON takes the file and populates a slice of Movie objects.
func (runner *RedshiftBasicsScenario) loadMoviesFromJSON(fileName string, filesystem demotools.IFileSystem) ([]Movie, error) {
file, err := filesystem.OpenFile("../../resources/sample_files/" + fileName)
if err != nil {
return nil, err
}
defer filesystem.CloseFile(file)
var movies []Movie
err = json.NewDecoder(file).Decode(&movies)
if err != nil {
return nil, err
}
return movies, nil
}
// PopulateMoviesTable reads data from the file and inserts records into the "Movies" table.
func (runner *RedshiftBasicsScenario) PopulateMoviesTable(ctx context.Context, clusterId string, databaseName string, tableName string, userName string, fileName string) {
log.Println("Populate the " + tableName + " table using the " + fileName + " file.")
numRecords := runner.questioner.AskInt(
fmt.Sprintf("Enter a value between %v and %v:", 10, 100),
demotools.InIntRange{Lower: 10, Upper: 100})
movies, err := runner.loadMoviesFromJSON(fileName, runner.filesystem)
if err != nil {
log.Printf("Failed to load movies from JSON: %v\n", err)
panic(err)
}
var sqlStatements []string
for i, movie := range movies {
if i >= numRecords {
break
}
sqlStatement := fmt.Sprintf(`INSERT INTO %s (title, year) VALUES ('%s', %d);`,
tableName,
strings.Replace(movie.Title, "'", "''", -1), // Double any single quotes to escape them
movie.Year)
sqlStatements = append(sqlStatements, sqlStatement)
}
input := &redshiftdata.BatchExecuteStatementInput{
ClusterIdentifier: aws.String(clusterId),
Database: aws.String(databaseName),
DbUser: aws.String(userName),
Sqls: sqlStatements,
}
result, err := runner.redshiftDataActor.ExecuteBatchStatement(ctx, *input)
if err != nil {
log.Printf("Failed to execute batch statement: %v\n", err)
panic(err)
}
describeInput := redshiftdata.DescribeStatementInput{
Id: result.Id,
}
query := actions.RedshiftQuery{
Context: ctx,
Result: result,
Input: describeInput,
}
err = runner.redshiftDataActor.WaitForQueryStatus(query, runner.pauser, true)
if err != nil {
log.Printf("Failed to execute batch insert query: %v\n", err)
return
}
log.Printf("Successfully executed batch statement\n")
log.Printf("%d records were added to the Movies table.\n", numRecords)
}
// QueryMoviesByYear retrieves only movies from the "Movies" table which match the given year.
func (runner *RedshiftBasicsScenario) QueryMoviesByYear(ctx context.Context, clusterId string, databaseName string, tableName string, userName string, year int) {
sqlStatement := fmt.Sprintf(`SELECT title FROM %s WHERE year = %d;`, tableName, year)
input := &redshiftdata.ExecuteStatementInput{
ClusterIdentifier: aws.String(clusterId),
Database: aws.String(databaseName),
DbUser: aws.String(userName),
Sql: aws.String(sqlStatement),
}
result, err := runner.redshiftDataActor.ExecuteStatement(ctx, *input)
if err != nil {
log.Printf("Failed to query movies: %v\n", err)
panic(err)
}
log.Println("The identifier of the statement is ", *result.Id)
describeInput := redshiftdata.DescribeStatementInput{
Id: result.Id,
}
query := actions.RedshiftQuery{
Context: ctx,
Input: describeInput,
Result: result,
}
err = runner.redshiftDataActor.WaitForQueryStatus(query, runner.pauser, true)
if err != nil {
log.Printf("Failed to execute query: %v\n", err)
panic(err)
}
log.Printf("Successfully executed query\n")
getResultOutput, err := runner.redshiftDataActor.GetStatementResult(ctx, *result.Id)
if err != nil {
log.Printf("Failed to query movies: %v\n", err)
panic(err)
}
for _, row := range getResultOutput.Records {
for _, col := range row {
title, ok := col.(*redshiftdata_types.FieldMemberStringValue)
if !ok {
log.Println("Failed to parse the field")
} else {
log.Printf("The Movie title field is %s\n", title.Value)
}
}
}
}
```
+ For API details, see the following topics in *AWS SDK for Go API Reference*.
+ [CreateCluster](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.CreateCluster)
+ [DescribeClusters](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.DescribeClusters)
+ [DescribeStatement](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.DescribeStatement)
+ [ExecuteStatement](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.ExecuteStatement)
+ [GetStatementResult](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.GetStatementResult)
+ [ListDatabasesPaginator](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.ListDatabasesPaginator)
+ [ModifyCluster](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.ModifyCluster)
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Run an interactive scenario demonstrating Amazon Redshift features.
```
import com.example.redshift.User;
import com.google.gson.Gson;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.redshift.model.ClusterAlreadyExistsException;
import software.amazon.awssdk.services.redshift.model.CreateClusterResponse;
import software.amazon.awssdk.services.redshift.model.DeleteClusterResponse;
import software.amazon.awssdk.services.redshift.model.ModifyClusterResponse;
import software.amazon.awssdk.services.redshift.model.RedshiftException;
import software.amazon.awssdk.services.redshiftdata.model.ExecuteStatementResponse;
import software.amazon.awssdk.services.redshiftdata.model.RedshiftDataException;
import java.util.Scanner;
import java.util.concurrent.CompletableFuture;
import software.amazon.awssdk.services.secretsmanager.SecretsManagerClient;
import software.amazon.awssdk.services.secretsmanager.model.GetSecretValueRequest;
import software.amazon.awssdk.services.secretsmanager.model.GetSecretValueResponse;
/**
* Before running this Java V2 code example, set up your development
* environment, including your credentials.
*
* For more information, see the following documentation topic:
*
* https://docs.aws.amazon.com/sdk-for-java/latest/developer-guide/get-started.html
*
*
* This example requires an AWS Secrets Manager secret that contains the
* database credentials. If you do not create a
* secret that specifies user name and password, this example will not work. For details, see:
*
* https://docs.aws.amazon.com/secretsmanager/latest/userguide/integrating_how-services-use-secrets_RS.html
*
This Java example performs these tasks:
*
* 1. Prompts the user for a unique cluster ID or use the default value.
* 2. Creates a Redshift cluster with the specified or default cluster Id value.
* 3. Waits until the Redshift cluster is available for use.
* 4. Lists all databases using a pagination API call.
* 5. Creates a table named "Movies" with fields ID, title, and year.
* 6. Inserts a specified number of records into the "Movies" table by reading the Movies JSON file.
* 7. Prompts the user for a movie release year.
* 8. Runs a SQL query to retrieve movies released in the specified year.
* 9. Modifies the Redshift cluster.
* 10. Prompts the user for confirmation to delete the Redshift cluster.
* 11. If confirmed, deletes the specified Redshift cluster.
*/
public class RedshiftScenario {
public static final String DASHES = new String(new char[80]).replace("\0", "-");
private static final Logger logger = LoggerFactory.getLogger(RedshiftScenario.class);
static RedshiftActions redshiftActions = new RedshiftActions();
public static void main(String[] args) throws Exception {
final String usage = """
Usage:
\s
Where:
jsonFilePath - The path to the Movies JSON file (you can locate that file in ../../../resources/sample_files/movies.json)
secretName - The name of the secret that belongs to Secret Manager that stores the user name and password used in this scenario.
""";
if (args.length != 2) {
logger.info(usage);
return;
}
String jsonFilePath = args[0];
String secretName = args[1];
Scanner scanner = new Scanner(System.in);
logger.info(DASHES);
logger.info("Welcome to the Amazon Redshift SDK Basics scenario.");
logger.info("""
This Java program demonstrates how to interact with Amazon Redshift by using the AWS SDK for Java (v2).\s
Amazon Redshift is a fully managed, petabyte-scale data warehouse service hosted in the cloud.
The program's primary functionalities include cluster creation, verification of cluster readiness,\s
list databases, table creation, data population within the table, and execution of SQL statements.
Furthermore, it demonstrates the process of querying data from the Movie table.\s
Upon completion of the program, all AWS resources are cleaned up.
""");
logger.info("Lets get started...");
logger.info("""
First, we will retrieve the user name and password from Secrets Manager.
Using Amazon Secrets Manager to store Redshift credentials provides several security benefits.
It allows you to securely store and manage sensitive information, such as passwords, API keys, and
database credentials, without embedding them directly in your application code.
More information can be found here:
https://docs.aws.amazon.com/secretsmanager/latest/userguide/integrating_how-services-use-secrets_RS.html
""");
Gson gson = new Gson();
User user = gson.fromJson(String.valueOf(getSecretValues(secretName)), User.class);
waitForInputToContinue(scanner);
logger.info(DASHES);
try {
runScenario(user, scanner, jsonFilePath);
} catch (RuntimeException e) {
e.printStackTrace();
} catch (Throwable e) {
throw new RuntimeException(e);
}
}
private static void runScenario(User user, Scanner scanner, String jsonFilePath) throws Throwable {
String databaseName = "dev";
System.out.println(DASHES);
logger.info("Create a Redshift Cluster");
logger.info("A Redshift cluster refers to the collection of computing resources and storage that work together to process and analyze large volumes of data.");
logger.info("Enter a cluster id value or accept the default by hitting Enter (default is redshift-cluster-movies): ");
String userClusterId = scanner.nextLine();
String clusterId = userClusterId.isEmpty() ? "redshift-cluster-movies" : userClusterId;
try {
CompletableFuture future = redshiftActions.createClusterAsync(clusterId, user.getUserName(), user.getUserPassword());
CreateClusterResponse response = future.join();
logger.info("Cluster successfully created. Cluster Identifier {} ", response.cluster().clusterIdentifier());
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof ClusterAlreadyExistsException) {
logger.info("The Cluster {} already exists. Moving on...", clusterId);
} else {
logger.info("An unexpected error occurred: " + rt.getMessage());
}
}
logger.info(DASHES);
logger.info(DASHES);
logger.info("Wait until {} is available.", clusterId);
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.waitForClusterReadyAsync(clusterId);
future.join();
logger.info("Cluster is ready!");
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftException redshiftEx) {
logger.info("Redshift error occurred: Error message: {}, Error code {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: " + rt.getMessage());
}
throw cause;
}
logger.info(DASHES);
logger.info(DASHES);
String databaseInfo = """
When you created $clusteridD, the dev database is created by default and used in this scenario.\s
To create a custom database, you need to have a CREATEDB privilege.\s
For more information, see the documentation here: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.html.
""".replace("$clusteridD", clusterId);
logger.info(databaseInfo);
waitForInputToContinue(scanner);
logger.info(DASHES);
logger.info(DASHES);
logger.info("List databases in {} ",clusterId);
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.listAllDatabasesAsync(clusterId, user.getUserName(), "dev");
future.join();
logger.info("Databases listed successfully.");
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.error("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.error("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
logger.info(DASHES);
logger.info(DASHES);
logger.info("Now you will create a table named Movies.");
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.createTableAsync(clusterId, databaseName, user.getUserName());
future.join();
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
logger.info(DASHES);
logger.info(DASHES);
logger.info("Populate the Movies table using the Movies.json file.");
logger.info("Specify the number of records you would like to add to the Movies Table.");
logger.info("Please enter a value between 50 and 200.");
int numRecords;
do {
logger.info("Enter a value: ");
while (!scanner.hasNextInt()) {
logger.info("Invalid input. Please enter a value between 50 and 200.");
logger.info("Enter a year: ");
scanner.next();
}
numRecords = scanner.nextInt();
} while (numRecords < 50 || numRecords > 200);
try {
redshiftActions.popTableAsync(clusterId, databaseName, user.getUserName(), jsonFilePath, numRecords).join(); // Wait for the operation to complete
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
waitForInputToContinue(scanner);
logger.info(DASHES);
logger.info(DASHES);
logger.info("Query the Movies table by year. Enter a value between 2012-2014.");
int movieYear;
do {
logger.info("Enter a year: ");
while (!scanner.hasNextInt()) {
logger.info("Invalid input. Please enter a valid year between 2012 and 2014.");
logger.info("Enter a year: ");
scanner.next();
}
movieYear = scanner.nextInt();
scanner.nextLine();
} while (movieYear < 2012 || movieYear > 2014);
String id;
try {
CompletableFuture future = redshiftActions.queryMoviesByYearAsync(databaseName, user.getUserName(), movieYear, clusterId);
id = future.join();
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
logger.info("The identifier of the statement is " + id);
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.checkStatementAsync(id);
future.join();
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.getResultsAsync(id);
future.join();
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
waitForInputToContinue(scanner);
logger.info(DASHES);
logger.info(DASHES);
logger.info("Now you will modify the Redshift cluster.");
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.modifyClusterAsync(clusterId);;
future.join();
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
waitForInputToContinue(scanner);
logger.info(DASHES);
logger.info(DASHES);
logger.info("Would you like to delete the Amazon Redshift cluster? (y/n)");
String delAns = scanner.nextLine().trim();
if (delAns.equalsIgnoreCase("y")) {
logger.info("You selected to delete {} ", clusterId);
waitForInputToContinue(scanner);
try {
CompletableFuture future = redshiftActions.deleteRedshiftClusterAsync(clusterId);;
future.join();
} catch (RuntimeException rt) {
Throwable cause = rt.getCause();
if (cause instanceof RedshiftDataException redshiftEx) {
logger.info("Redshift Data error occurred: {} Error code: {}", redshiftEx.getMessage(), redshiftEx.awsErrorDetails().errorCode());
} else {
logger.info("An unexpected error occurred: {}", rt.getMessage());
}
throw cause;
}
} else {
logger.info("The {} was not deleted", clusterId);
}
logger.info(DASHES);
logger.info(DASHES);
logger.info("This concludes the Amazon Redshift SDK Basics scenario.");
logger.info(DASHES);
}
private static SecretsManagerClient getSecretClient() {
Region region = Region.US_EAST_1;
return SecretsManagerClient.builder()
.region(region)
.build();
}
private static void waitForInputToContinue(Scanner scanner) {
while (true) {
System.out.println("");
System.out.println("Enter 'c' followed by to continue:");
String input = scanner.nextLine();
if (input.trim().equalsIgnoreCase("c")) {
System.out.println("Continuing with the program...");
System.out.println("");
break;
} else {
// Handle invalid input.
System.out.println("Invalid input. Please try again.");
}
}
}
// Get the Amazon Redshift credentials from AWS Secrets Manager.
private static String getSecretValues(String secretName) {
SecretsManagerClient secretClient = getSecretClient();
GetSecretValueRequest valueRequest = GetSecretValueRequest.builder()
.secretId(secretName)
.build();
GetSecretValueResponse valueResponse = secretClient.getSecretValue(valueRequest);
return valueResponse.secretString();
}
}
```
A wrapper class for Amazon Redshift SDK methods.
```
public class RedshiftActions {
private static final Logger logger = LoggerFactory.getLogger(RedshiftActions.class);
private static RedshiftDataAsyncClient redshiftDataAsyncClient;
private static RedshiftAsyncClient redshiftAsyncClient;
private static RedshiftAsyncClient getAsyncClient() {
if (redshiftAsyncClient == null) {
SdkAsyncHttpClient httpClient = NettyNioAsyncHttpClient.builder()
.maxConcurrency(100)
.connectionTimeout(Duration.ofSeconds(60))
.readTimeout(Duration.ofSeconds(60))
.writeTimeout(Duration.ofSeconds(60))
.build();
ClientOverrideConfiguration overrideConfig = ClientOverrideConfiguration.builder()
.apiCallTimeout(Duration.ofMinutes(2))
.apiCallAttemptTimeout(Duration.ofSeconds(90))
.retryStrategy(RetryMode.STANDARD)
.build();
redshiftAsyncClient = RedshiftAsyncClient.builder()
.httpClient(httpClient)
.overrideConfiguration(overrideConfig)
.build();
}
return redshiftAsyncClient;
}
private static RedshiftDataAsyncClient getAsyncDataClient() {
if (redshiftDataAsyncClient == null) {
SdkAsyncHttpClient httpClient = NettyNioAsyncHttpClient.builder()
.maxConcurrency(100)
.connectionTimeout(Duration.ofSeconds(60))
.readTimeout(Duration.ofSeconds(60))
.writeTimeout(Duration.ofSeconds(60))
.build();
ClientOverrideConfiguration overrideConfig = ClientOverrideConfiguration.builder()
.apiCallTimeout(Duration.ofMinutes(2))
.apiCallAttemptTimeout(Duration.ofSeconds(90))
.retryStrategy(RetryMode.STANDARD)
.build();
redshiftDataAsyncClient = RedshiftDataAsyncClient.builder()
.httpClient(httpClient)
.overrideConfiguration(overrideConfig)
.build();
}
return redshiftDataAsyncClient;
}
/**
* Creates a new Amazon Redshift cluster asynchronously.
* @param clusterId the unique identifier for the cluster
* @param username the username for the administrative user
* @param userPassword the password for the administrative user
* @return a CompletableFuture that represents the asynchronous operation of creating the cluster
* @throws RuntimeException if the cluster creation fails
*/
public CompletableFuture createClusterAsync(String clusterId, String username, String userPassword) {
CreateClusterRequest clusterRequest = CreateClusterRequest.builder()
.clusterIdentifier(clusterId)
.masterUsername(username)
.masterUserPassword(userPassword)
.nodeType("ra3.4xlarge")
.publiclyAccessible(true)
.numberOfNodes(2)
.build();
return getAsyncClient().createCluster(clusterRequest)
.whenComplete((response, exception) -> {
if (response != null) {
logger.info("Created cluster ");
} else {
throw new RuntimeException("Failed to create cluster: " + exception.getMessage(), exception);
}
});
}
/**
* Waits asynchronously for the specified cluster to become available.
* @param clusterId the identifier of the cluster to wait for
* @return a {@link CompletableFuture} that completes when the cluster is ready
*/
public CompletableFuture waitForClusterReadyAsync(String clusterId) {
DescribeClustersRequest clustersRequest = DescribeClustersRequest.builder()
.clusterIdentifier(clusterId)
.build();
logger.info("Waiting for cluster to become available. This may take a few minutes.");
long startTime = System.currentTimeMillis();
// Recursive method to poll the cluster status.
return checkClusterStatusAsync(clustersRequest, startTime);
}
private CompletableFuture checkClusterStatusAsync(DescribeClustersRequest clustersRequest, long startTime) {
return getAsyncClient().describeClusters(clustersRequest)
.thenCompose(clusterResponse -> {
List clusterList = clusterResponse.clusters();
boolean clusterReady = false;
for (Cluster cluster : clusterList) {
if ("available".equals(cluster.clusterStatus())) {
clusterReady = true;
break;
}
}
if (clusterReady) {
logger.info(String.format("Cluster is available!"));
return CompletableFuture.completedFuture(null);
} else {
long elapsedTimeMillis = System.currentTimeMillis() - startTime;
long elapsedSeconds = elapsedTimeMillis / 1000;
long minutes = elapsedSeconds / 60;
long seconds = elapsedSeconds % 60;
System.out.printf("\rElapsed Time: %02d:%02d - Waiting for cluster...", minutes, seconds);
System.out.flush();
// Wait 1 second before the next status check
return CompletableFuture.runAsync(() -> {
try {
TimeUnit.SECONDS.sleep(1);
} catch (InterruptedException e) {
throw new RuntimeException("Error during sleep: " + e.getMessage(), e);
}
}).thenCompose(ignored -> checkClusterStatusAsync(clustersRequest, startTime));
}
}).exceptionally(exception -> {
throw new RuntimeException("Failed to get cluster status: " + exception.getMessage(), exception);
});
}
/**
* Lists all databases asynchronously for the specified cluster, database user, and database.
* @param clusterId the identifier of the cluster to list databases for
* @param dbUser the database user to use for the list databases request
* @param database the database to list databases for
* @return a {@link CompletableFuture} that completes when the database listing is complete, or throws a {@link RuntimeException} if there was an error
*/
public CompletableFuture listAllDatabasesAsync(String clusterId, String dbUser, String database) {
ListDatabasesRequest databasesRequest = ListDatabasesRequest.builder()
.clusterIdentifier(clusterId)
.dbUser(dbUser)
.database(database)
.build();
// Asynchronous paginator for listing databases.
ListDatabasesPublisher databasesPaginator = getAsyncDataClient().listDatabasesPaginator(databasesRequest);
CompletableFuture future = databasesPaginator.subscribe(response -> {
response.databases().forEach(db -> {
logger.info("The database name is {} ", db);
});
});
// Return the future for asynchronous handling.
return future.exceptionally(exception -> {
throw new RuntimeException("Failed to list databases: " + exception.getMessage(), exception);
});
}
/**
* Creates an asynchronous task to execute a SQL statement for creating a new table.
*
* @param clusterId the identifier of the Amazon Redshift cluster
* @param databaseName the name of the database to create the table in
* @param userName the username to use for the database connection
* @return a {@link CompletableFuture} that completes with the result of the SQL statement execution
* @throws RuntimeException if there is an error creating the table
*/
public CompletableFuture createTableAsync(String clusterId, String databaseName, String userName) {
ExecuteStatementRequest createTableRequest = ExecuteStatementRequest.builder()
.clusterIdentifier(clusterId)
.dbUser(userName)
.database(databaseName)
.sql("CREATE TABLE Movies (" +
"id INT PRIMARY KEY, " +
"title VARCHAR(100), " +
"year INT)")
.build();
return getAsyncDataClient().executeStatement(createTableRequest)
.whenComplete((response, exception) -> {
if (exception != null) {
throw new RuntimeException("Error creating table: " + exception.getMessage(), exception);
} else {
logger.info("Table created: Movies");
}
});
}
/**
* Asynchronously pops a table from a JSON file.
*
* @param clusterId the ID of the cluster
* @param databaseName the name of the database
* @param userName the username
* @param fileName the name of the JSON file
* @param number the number of records to process
* @return a CompletableFuture that completes with the number of records added to the Movies table
*/
public CompletableFuture popTableAsync(String clusterId, String databaseName, String userName, String fileName, int number) {
return CompletableFuture.supplyAsync(() -> {
try {
JsonParser parser = new JsonFactory().createParser(new File(fileName));
JsonNode rootNode = new ObjectMapper().readTree(parser);
Iterator iter = rootNode.iterator();
return iter;
} catch (IOException e) {
throw new RuntimeException("Failed to read or parse JSON file: " + e.getMessage(), e);
}
}).thenCompose(iter -> processNodesAsync(clusterId, databaseName, userName, iter, number))
.whenComplete((result, exception) -> {
if (exception != null) {
logger.info("Error {} ", exception.getMessage());
} else {
logger.info("{} records were added to the Movies table." , result);
}
});
}
private CompletableFuture processNodesAsync(String clusterId, String databaseName, String userName, Iterator iter, int number) {
return CompletableFuture.supplyAsync(() -> {
int t = 0;
try {
while (iter.hasNext()) {
if (t == number)
break;
JsonNode currentNode = iter.next();
int year = currentNode.get("year").asInt();
String title = currentNode.get("title").asText();
// Use SqlParameter to avoid SQL injection.
List parameterList = new ArrayList<>();
String sqlStatement = "INSERT INTO Movies VALUES( :id , :title, :year);";
SqlParameter idParam = SqlParameter.builder()
.name("id")
.value(String.valueOf(t))
.build();
SqlParameter titleParam = SqlParameter.builder()
.name("title")
.value(title)
.build();
SqlParameter yearParam = SqlParameter.builder()
.name("year")
.value(String.valueOf(year))
.build();
parameterList.add(idParam);
parameterList.add(titleParam);
parameterList.add(yearParam);
ExecuteStatementRequest insertStatementRequest = ExecuteStatementRequest.builder()
.clusterIdentifier(clusterId)
.sql(sqlStatement)
.database(databaseName)
.dbUser(userName)
.parameters(parameterList)
.build();
getAsyncDataClient().executeStatement(insertStatementRequest);
logger.info("Inserted: " + title + " (" + year + ")");
t++;
}
} catch (RedshiftDataException e) {
throw new RuntimeException("Error inserting data: " + e.getMessage(), e);
}
return t;
});
}
/**
* Checks the status of an SQL statement asynchronously and handles the completion of the statement.
*
* @param sqlId the ID of the SQL statement to check
* @return a {@link CompletableFuture} that completes when the SQL statement's status is either "FINISHED" or "FAILED"
*/
public CompletableFuture checkStatementAsync(String sqlId) {
DescribeStatementRequest statementRequest = DescribeStatementRequest.builder()
.id(sqlId)
.build();
return getAsyncDataClient().describeStatement(statementRequest)
.thenCompose(response -> {
String status = response.statusAsString();
logger.info("... Status: {} ", status);
if ("FAILED".equals(status)) {
throw new RuntimeException("The Query Failed. Ending program");
} else if ("FINISHED".equals(status)) {
return CompletableFuture.completedFuture(null);
} else {
// Sleep for 1 second and recheck status
return CompletableFuture.runAsync(() -> {
try {
TimeUnit.SECONDS.sleep(1);
} catch (InterruptedException e) {
throw new RuntimeException("Error during sleep: " + e.getMessage(), e);
}
}).thenCompose(ignore -> checkStatementAsync(sqlId)); // Recursively call until status is FINISHED or FAILED
}
}).whenComplete((result, exception) -> {
if (exception != null) {
// Handle exceptions
logger.info("Error: {} ", exception.getMessage());
} else {
logger.info("The statement is finished!");
}
});
}
/**
* Asynchronously retrieves the results of a statement execution.
*
* @param statementId the ID of the statement for which to retrieve the results
* @return a {@link CompletableFuture} that completes when the statement result has been processed
*/
public CompletableFuture getResultsAsync(String statementId) {
GetStatementResultRequest resultRequest = GetStatementResultRequest.builder()
.id(statementId)
.build();
return getAsyncDataClient().getStatementResult(resultRequest)
.handle((response, exception) -> {
if (exception != null) {
logger.info("Error getting statement result {} ", exception.getMessage());
throw new RuntimeException("Error getting statement result: " + exception.getMessage(), exception);
}
// Extract and print the field values using streams if the response is valid.
response.records().stream()
.flatMap(List::stream)
.map(Field::stringValue)
.filter(value -> value != null)
.forEach(value -> System.out.println("The Movie title field is " + value));
return response;
}).thenAccept(response -> {
// Optionally add more logic here if needed after handling the response
});
}
/**
* Asynchronously queries movies by a given year from a Redshift database.
*
* @param database the name of the database to query
* @param dbUser the user to connect to the database with
* @param year the year to filter the movies by
* @param clusterId the identifier of the Redshift cluster to connect to
* @return a {@link CompletableFuture} containing the response ID of the executed SQL statement
*/
public CompletableFuture queryMoviesByYearAsync(String database,
String dbUser,
int year,
String clusterId) {
String sqlStatement = "SELECT * FROM Movies WHERE year = :year";
SqlParameter yearParam = SqlParameter.builder()
.name("year")
.value(String.valueOf(year))
.build();
ExecuteStatementRequest statementRequest = ExecuteStatementRequest.builder()
.clusterIdentifier(clusterId)
.database(database)
.dbUser(dbUser)
.parameters(yearParam)
.sql(sqlStatement)
.build();
return CompletableFuture.supplyAsync(() -> {
try {
ExecuteStatementResponse response = getAsyncDataClient().executeStatement(statementRequest).join(); // Use join() to wait for the result
return response.id();
} catch (RedshiftDataException e) {
throw new RuntimeException("Error executing statement: " + e.getMessage(), e);
}
}).exceptionally(exception -> {
logger.info("Error: {}", exception.getMessage());
return "";
});
}
/**
* Modifies an Amazon Redshift cluster asynchronously.
*
* @param clusterId the identifier of the cluster to be modified
* @return a {@link CompletableFuture} that completes when the cluster modification is complete
*/
public CompletableFuture modifyClusterAsync(String clusterId) {
ModifyClusterRequest modifyClusterRequest = ModifyClusterRequest.builder()
.clusterIdentifier(clusterId)
.preferredMaintenanceWindow("wed:07:30-wed:08:00")
.build();
return getAsyncClient().modifyCluster(modifyClusterRequest)
.whenComplete((clusterResponse, exception) -> {
if (exception != null) {
if (exception.getCause() instanceof RedshiftException) {
logger.info("Error: {} ", exception.getMessage());
} else {
logger.info("Unexpected error: {} ", exception.getMessage());
}
} else {
logger.info("The modified cluster was successfully modified and has "
+ clusterResponse.cluster().preferredMaintenanceWindow() + " as the maintenance window");
}
});
}
/**
* Deletes a Redshift cluster asynchronously.
*
* @param clusterId the identifier of the Redshift cluster to be deleted
* @return a {@link CompletableFuture} that represents the asynchronous operation of deleting the Redshift cluster
*/
public CompletableFuture deleteRedshiftClusterAsync(String clusterId) {
DeleteClusterRequest deleteClusterRequest = DeleteClusterRequest.builder()
.clusterIdentifier(clusterId)
.skipFinalClusterSnapshot(true)
.build();
return getAsyncClient().deleteCluster(deleteClusterRequest)
.whenComplete((response, exception) -> {
if (exception != null) {
// Handle exceptions
if (exception.getCause() instanceof RedshiftException) {
logger.info("Error: {}", exception.getMessage());
} else {
logger.info("Unexpected error: {}", exception.getMessage());
}
} else {
// Handle successful response
logger.info("The status is {}", response.cluster().clusterStatus());
}
});
}
}
```
+ For API details, see the following topics in *AWS SDK for Java 2.x API Reference*.
+ [CreateCluster](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/CreateCluster)
+ [DescribeClusters](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/DescribeClusters)
+ [DescribeStatement](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/DescribeStatement)
+ [ExecuteStatement](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/ExecuteStatement)
+ [GetStatementResult](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/GetStatementResult)
+ [ListDatabasesPaginator](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/ListDatabasesPaginator)
+ [ModifyCluster](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/ModifyCluster)
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftScenario:
"""Runs an interactive scenario that shows how to get started with Redshift."""
def __init__(self, redshift_wrapper, redshift_data_wrapper):
self.redshift_wrapper = redshift_wrapper
self.redshift_data_wrapper = redshift_data_wrapper
def redhift_scenario(self, json_file_path):
database_name = "dev"
print(DASHES)
print("Welcome to the Amazon Redshift SDK Getting Started example.")
print(
"""
This Python program demonstrates how to interact with Amazon Redshift
using the AWS SDK for Python (Boto3).
Amazon Redshift is a fully managed, petabyte-scale data warehouse
service hosted in the cloud.
The program's primary functionalities include cluster creation,
verification of cluster readiness, listing databases, table creation,
populating data within the table, and executing SQL statements.
It also demonstrates querying data from the Movies table.
Upon completion, all AWS resources are cleaned up.
"""
)
if not os.path.isfile(json_file_path):
logging.error(f"The file {json_file_path} does not exist.")
return
print("Let's get started...")
user_name = q.ask("Please enter your user name (default is awsuser):")
user_name = user_name if user_name else "awsuser"
print(DASHES)
user_password = q.ask(
"Please enter your user password (default is AwsUser1000):"
)
user_password = user_password if user_password else "AwsUser1000"
print(DASHES)
print(
"""A Redshift cluster refers to the collection of computing resources and storage that work
together to process and analyze large volumes of data."""
)
cluster_id = q.ask(
"Enter a cluster identifier value (default is redshift-cluster-movies): "
)
cluster_id = cluster_id if cluster_id else "redshift-cluster-movies"
self.redshift_wrapper.create_cluster(
cluster_id, "ra3.4xlarge", user_name, user_password, True, 2
)
print(DASHES)
print(f"Wait until {cluster_id} is available. This may take a few minutes...")
q.ask("Press Enter to continue...")
self.wait_cluster_available(cluster_id)
print(DASHES)
print(
f"""
When you created {cluster_id}, the dev database is created by default and used in this scenario.
To create a custom database, you need to have a CREATEDB privilege.
For more information, see the documentation here:
https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.html.
"""
)
q.ask("Press Enter to continue...")
print(DASHES)
print(DASHES)
print(f"List databases in {cluster_id}")
q.ask("Press Enter to continue...")
databases = self.redshift_data_wrapper.list_databases(
cluster_id, database_name, user_name
)
print(f"The cluster contains {len(databases)} database(s).")
for database in databases:
print(f" Database: {database}")
print(DASHES)
print(DASHES)
print("Now you will create a table named Movies.")
q.ask("Press Enter to continue...")
self.create_table(cluster_id, database_name, user_name)
print(DASHES)
print("Populate the Movies table using the Movies.json file.")
print(
"Specify the number of records you would like to add to the Movies Table."
)
print("Please enter a value between 50 and 200.")
while True:
try:
num_records = int(q.ask("Enter a value: ", q.is_int))
if 50 <= num_records <= 200:
break
else:
print("Invalid input. Please enter a value between 50 and 200.")
except ValueError:
print("Invalid input. Please enter a value between 50 and 200.")
self.populate_table(
cluster_id, database_name, user_name, json_file_path, num_records
)
print(DASHES)
print("Query the Movies table by year. Enter a value between 2012-2014.")
while True:
movie_year = int(q.ask("Enter a year: ", q.is_int))
if 2012 <= movie_year <= 2014:
break
else:
print("Invalid input. Please enter a valid year between 2012 and 2014.")
# Function to query database
sql_id = self.query_movies_by_year(
database_name, user_name, movie_year, cluster_id
)
print(f"The identifier of the statement is {sql_id}")
print("Checking statement status...")
self.wait_statement_finished(sql_id)
result = self.redshift_data_wrapper.get_statement_result(sql_id)
self.display_movies(result)
print(DASHES)
print(DASHES)
print("Now you will modify the Redshift cluster.")
q.ask("Press Enter to continue...")
preferred_maintenance_window = "wed:07:30-wed:08:00"
self.redshift_wrapper.modify_cluster(cluster_id, preferred_maintenance_window)
print(DASHES)
print(DASHES)
delete = q.ask("Do you want to delete the cluster? (y/n) ", q.is_yesno)
if delete:
print(f"You selected to delete {cluster_id}")
q.ask("Press Enter to continue...")
self.redshift_wrapper.delete_cluster(cluster_id)
else:
print(f"Cluster {cluster_id}cluster_id was not deleted")
print(DASHES)
print("This concludes the Amazon Redshift SDK Getting Started scenario.")
print(DASHES)
def create_table(self, cluster_id, database, username):
self.redshift_data_wrapper.execute_statement(
cluster_identifier=cluster_id,
database_name=database,
user_name=username,
sql="CREATE TABLE Movies (statement_id INT PRIMARY KEY, title VARCHAR(100), year INT)",
)
print("Table created: Movies")
def populate_table(self, cluster_id, database, username, file_name, number):
with open(file_name) as f:
data = json.load(f)
i = 0
for record in data:
if i == number:
break
statement_id = i
title = record["title"]
year = record["year"]
i = i + 1
parameters = [
{"name": "statement_id", "value": str(statement_id)},
{"name": "title", "value": title},
{"name": "year", "value": str(year)},
]
self.redshift_data_wrapper.execute_statement(
cluster_identifier=cluster_id,
database_name=database,
user_name=username,
sql="INSERT INTO Movies VALUES(:statement_id, :title, :year)",
parameter_list=parameters,
)
print(f"{i} records inserted into Movies table")
def wait_cluster_available(self, cluster_id):
"""
Waits for a cluster to be available.
:param cluster_id: The cluster identifier.
Note: The cluster_available waiter can also be used.
It is not used in this case to allow an elapsed time message.
"""
cluster_ready = False
start_time = time.time()
while not cluster_ready:
time.sleep(30)
cluster = self.redshift_wrapper.describe_clusters(cluster_id)
status = cluster[0]["ClusterStatus"]
if status == "available":
cluster_ready = True
elif status != "creating":
raise Exception(
f"Cluster {cluster_id} creation failed with status {status}."
)
elapsed_seconds = int(round(time.time() - start_time))
minutes = int(elapsed_seconds // 60)
seconds = int(elapsed_seconds % 60)
print(f"Elapsed Time: {minutes}:{seconds:02d} - status {status}...")
if minutes > 30:
raise Exception(
f"Cluster {cluster_id} is not available after 30 minutes."
)
def query_movies_by_year(self, database, username, year, cluster_id):
sql = "SELECT * FROM Movies WHERE year = :year"
params = [{"name": "year", "value": str(year)}]
response = self.redshift_data_wrapper.execute_statement(
cluster_identifier=cluster_id,
database_name=database,
user_name=username,
sql=sql,
parameter_list=params,
)
return response["Id"]
@staticmethod
def display_movies(response):
metadata = response["ColumnMetadata"]
records = response["Records"]
title_column_index = None
for i in range(len(metadata)):
if metadata[i]["name"] == "title":
title_column_index = i
break
if title_column_index is None:
print("No title column found.")
return
print(f"Found {len(records)} movie(s).")
for record in records:
print(f" {record[title_column_index]['stringValue']}")
def wait_statement_finished(self, sql_id):
while True:
time.sleep(1)
response = self.redshift_data_wrapper.describe_statement(sql_id)
status = response["Status"]
print(f"Statement status is {status}.")
if status == "FAILED":
print(f"The query failed because {response['Error']}. Ending program")
raise Exception("The Query Failed. Ending program")
elif status == "FINISHED":
break
```
Main function showing scenario implementation.
```
def main():
redshift_client = boto3.client("redshift")
redshift_data_client = boto3.client("redshift-data")
redshift_wrapper = RedshiftWrapper(redshift_client)
redshift_data_wrapper = RedshiftDataWrapper(redshift_data_client)
redshift_scenario = RedshiftScenario(redshift_wrapper, redshift_data_wrapper)
redshift_scenario.redhift_scenario(
f"{os.path.dirname(__file__)}/../../../resources/sample_files/movies.json"
)
```
The wrapper functions used in the scenario.
```
def create_cluster(
self,
cluster_identifier,
node_type,
master_username,
master_user_password,
publicly_accessible,
number_of_nodes,
):
"""
Creates a cluster.
:param cluster_identifier: The name of the cluster.
:param node_type: The type of node in the cluster.
:param master_username: The master username.
:param master_user_password: The master user password.
:param publicly_accessible: Whether the cluster is publicly accessible.
:param number_of_nodes: The number of nodes in the cluster.
:return: The cluster.
"""
try:
cluster = self.client.create_cluster(
ClusterIdentifier=cluster_identifier,
NodeType=node_type,
MasterUsername=master_username,
MasterUserPassword=master_user_password,
PubliclyAccessible=publicly_accessible,
NumberOfNodes=number_of_nodes,
)
return cluster
except ClientError as err:
logging.error(
"Couldn't create a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def describe_clusters(self, cluster_identifier):
"""
Describes a cluster.
:param cluster_identifier: The cluster identifier.
:return: A list of clusters.
"""
try:
kwargs = {}
if cluster_identifier:
kwargs["ClusterIdentifier"] = cluster_identifier
paginator = self.client.get_paginator("describe_clusters")
clusters = []
for page in paginator.paginate(**kwargs):
clusters.extend(page["Clusters"])
return clusters
except ClientError as err:
logging.error(
"Couldn't describe a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def execute_statement(
self, cluster_identifier, database_name, user_name, sql, parameter_list=None
):
"""
Executes a SQL statement.
:param cluster_identifier: The cluster identifier.
:param database_name: The database name.
:param user_name: The user's name.
:param sql: The SQL statement.
:param parameter_list: The optional SQL statement parameters.
:return: The SQL statement result.
"""
try:
kwargs = {
"ClusterIdentifier": cluster_identifier,
"Database": database_name,
"DbUser": user_name,
"Sql": sql,
}
if parameter_list:
kwargs["Parameters"] = parameter_list
response = self.client.execute_statement(**kwargs)
return response
except ClientError as err:
logging.error(
"Couldn't execute statement. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def describe_statement(self, statement_id):
"""
Describes a SQL statement.
:param statement_id: The SQL statement identifier.
:return: The SQL statement result.
"""
try:
response = self.client.describe_statement(Id=statement_id)
return response
except ClientError as err:
logging.error(
"Couldn't describe statement. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def get_statement_result(self, statement_id):
"""
Gets the result of a SQL statement.
:param statement_id: The SQL statement identifier.
:return: The SQL statement result.
"""
try:
result = {
"Records": [],
}
paginator = self.client.get_paginator("get_statement_result")
for page in paginator.paginate(Id=statement_id):
if "ColumnMetadata" not in result:
result["ColumnMetadata"] = page["ColumnMetadata"]
result["Records"].extend(page["Records"])
return result
except ClientError as err:
logging.error(
"Couldn't get statement result. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def modify_cluster(self, cluster_identifier, preferred_maintenance_window):
"""
Modifies a cluster.
:param cluster_identifier: The cluster identifier.
:param preferred_maintenance_window: The preferred maintenance window.
"""
try:
self.client.modify_cluster(
ClusterIdentifier=cluster_identifier,
PreferredMaintenanceWindow=preferred_maintenance_window,
)
except ClientError as err:
logging.error(
"Couldn't modify a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def list_databases(self, cluster_identifier, database_name, database_user):
"""
Lists databases in a cluster.
:param cluster_identifier: The cluster identifier.
:param database_name: The database name.
:param database_user: The database user.
:return: The list of databases.
"""
try:
paginator = self.client.get_paginator("list_databases")
databases = []
for page in paginator.paginate(
ClusterIdentifier=cluster_identifier,
Database=database_name,
DbUser=database_user,
):
databases.extend(page["Databases"])
return databases
except ClientError as err:
logging.error(
"Couldn't list databases. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
def delete_cluster(self, cluster_identifier):
"""
Deletes a cluster.
:param cluster_identifier: The cluster identifier.
"""
try:
self.client.delete_cluster(
ClusterIdentifier=cluster_identifier, SkipFinalClusterSnapshot=True
)
except ClientError as err:
logging.error(
"Couldn't delete a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
+ For API details, see the following topics in *AWS SDK for Python (Boto3) API Reference*.
+ [CreateCluster](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/CreateCluster)
+ [DescribeClusters](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/DescribeClusters)
+ [DescribeStatement](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/DescribeStatement)
+ [ExecuteStatement](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/ExecuteStatement)
+ [GetStatementResult](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/GetStatementResult)
+ [ListDatabasesPaginator](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/ListDatabasesPaginator)
+ [ModifyCluster](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/ModifyCluster)
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Actions for Amazon Redshift using AWS SDKs
The following code examples demonstrate how to perform individual Amazon Redshift actions with AWS SDKs. Each example includes a link to GitHub, where you can find instructions for setting up and running the code.
These excerpts call the Amazon Redshift API and are code excerpts from larger programs that must be run in context. You can see actions in context in [Scenarios for Amazon Redshift using AWS SDKs](service_code_examples_scenarios.md).
The following examples include only the most commonly used actions. For a complete list, see the [Amazon Redshift API Reference](https://docs.aws.amazon.com/redshift/latest/APIReference/Welcome.html).
**Topics**
+ [`CreateCluster`](example_redshift_CreateCluster_section.md)
+ [`DeleteCluster`](example_redshift_DeleteCluster_section.md)
+ [`DescribeClusters`](example_redshift_DescribeClusters_section.md)
+ [`DescribeStatement`](example_redshift_DescribeStatement_section.md)
+ [`ExecuteStatement`](example_redshift_ExecuteStatement_section.md)
+ [`GetStatementResult`](example_redshift_GetStatementResult_section.md)
+ [`ListDatabases`](example_redshift_ListDatabases_section.md)
+ [`ModifyCluster`](example_redshift_ModifyCluster_section.md)
# Use `CreateCluster` with an AWS SDK or CLI
The following code examples show how to use `CreateCluster`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code examples:
+ [Learn the basics](example_redshift_Scenario_section.md)
+ [Getting started with Amazon Redshift provisioned clusters](example_redshift_GettingStarted_039_section.md)
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Create a new Amazon Redshift cluster.
///
/// The identifier for the cluster.
/// The name of the database.
/// The master username.
/// The master user password.
/// The node type for the cluster.
/// The cluster that was created.
public async Task CreateClusterAsync(string clusterIdentifier, string databaseName,
string masterUsername, string masterUserPassword, string nodeType = "ra3.large")
{
try
{
var request = new CreateClusterRequest
{
ClusterIdentifier = clusterIdentifier,
DBName = databaseName,
MasterUsername = masterUsername,
MasterUserPassword = masterUserPassword,
NodeType = nodeType,
NumberOfNodes = 1,
ClusterType = "single-node"
};
var response = await _redshiftClient.CreateClusterAsync(request);
Console.WriteLine($"Created cluster {clusterIdentifier}");
return response.Cluster;
}
catch (ClusterAlreadyExistsException ex)
{
Console.WriteLine($"Cluster already exists: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't create cluster. Here's why: {ex.Message}");
throw;
}
}
```
+ For API details, see [CreateCluster](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/CreateCluster) in *AWS SDK for .NET API Reference*.
------
#### [ CLI ]
**AWS CLI**
Create a Cluster with Minimal ParametersThis example creates a cluster with the minimal set of parameters. By default, the output is in JSON format.Command:
```
aws redshift create-cluster --node-type dw.hs1.xlarge --number-of-nodes 2 --master-username adminuser --master-user-password TopSecret1 --cluster-identifier mycluster
```
Result:
```
{
"Cluster": {
"NodeType": "dw.hs1.xlarge",
"ClusterVersion": "1.0",
"PubliclyAccessible": "true",
"MasterUsername": "adminuser",
"ClusterParameterGroups": [
{
"ParameterApplyStatus": "in-sync",
"ParameterGroupName": "default.redshift-1.0"
} ],
"ClusterSecurityGroups": [
{
"Status": "active",
"ClusterSecurityGroupName": "default"
} ],
"AllowVersionUpgrade": true,
"VpcSecurityGroups": \[],
"PreferredMaintenanceWindow": "sat:03:30-sat:04:00",
"AutomatedSnapshotRetentionPeriod": 1,
"ClusterStatus": "creating",
"ClusterIdentifier": "mycluster",
"DBName": "dev",
"NumberOfNodes": 2,
"PendingModifiedValues": {
"MasterUserPassword": "\****"
}
},
"ResponseMetadata": {
"RequestId": "7cf4bcfc-64dd-11e2-bea9-49e0ce183f07"
}
}
```
+ For API details, see [CreateCluster](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/redshift/create-cluster.html) in *AWS CLI Command Reference*.
------
#### [ Go ]
**SDK for Go V2**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/gov2/redshift#code-examples).
```
import (
"context"
"errors"
"log"
"time"
"github.com/aws/aws-sdk-go-v2/aws"
"github.com/aws/aws-sdk-go-v2/service/redshift"
"github.com/aws/aws-sdk-go-v2/service/redshift/types"
)
// RedshiftActions wraps Redshift service actions.
type RedshiftActions struct {
RedshiftClient *redshift.Client
}
// CreateCluster sends a request to create a cluster with the given clusterId using the provided credentials.
func (actor RedshiftActions) CreateCluster(ctx context.Context, clusterId string, userName string, userPassword string, nodeType string, clusterType string, publiclyAccessible bool) (*redshift.CreateClusterOutput, error) {
// Create a new Redshift cluster
input := &redshift.CreateClusterInput{
ClusterIdentifier: aws.String(clusterId),
MasterUserPassword: aws.String(userPassword),
MasterUsername: aws.String(userName),
NodeType: aws.String(nodeType),
ClusterType: aws.String(clusterType),
PubliclyAccessible: aws.Bool(publiclyAccessible),
}
var opErr *types.ClusterAlreadyExistsFault
output, err := actor.RedshiftClient.CreateCluster(ctx, input)
if err != nil && errors.As(err, &opErr) {
log.Println("Cluster already exists")
return nil, nil
} else if err != nil {
log.Printf("Failed to create Redshift cluster: %v\n", err)
return nil, err
}
log.Printf("Created cluster %s\n", *output.Cluster.ClusterIdentifier)
return output, nil
}
```
+ For API details, see [CreateCluster](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.CreateCluster) in *AWS SDK for Go API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Create the cluster.
```
/**
* Creates a new Amazon Redshift cluster asynchronously.
* @param clusterId the unique identifier for the cluster
* @param username the username for the administrative user
* @param userPassword the password for the administrative user
* @return a CompletableFuture that represents the asynchronous operation of creating the cluster
* @throws RuntimeException if the cluster creation fails
*/
public CompletableFuture createClusterAsync(String clusterId, String username, String userPassword) {
CreateClusterRequest clusterRequest = CreateClusterRequest.builder()
.clusterIdentifier(clusterId)
.masterUsername(username)
.masterUserPassword(userPassword)
.nodeType("ra3.4xlarge")
.publiclyAccessible(true)
.numberOfNodes(2)
.build();
return getAsyncClient().createCluster(clusterRequest)
.whenComplete((response, exception) -> {
if (response != null) {
logger.info("Created cluster ");
} else {
throw new RuntimeException("Failed to create cluster: " + exception.getMessage(), exception);
}
});
}
```
+ For API details, see [CreateCluster](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/CreateCluster) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javascriptv3/example_code/redshift#code-examples).
Create the client.
```
import { RedshiftClient } from "@aws-sdk/client-redshift";
// Set the AWS Region.
const REGION = "REGION";
//Set the Redshift Service Object
const redshiftClient = new RedshiftClient({ region: REGION });
export { redshiftClient };
```
Create the cluster.
```
// Import required AWS SDK clients and commands for Node.js
import { CreateClusterCommand } from "@aws-sdk/client-redshift";
import { redshiftClient } from "./libs/redshiftClient.js";
const params = {
ClusterIdentifier: "CLUSTER_NAME", // Required
NodeType: "NODE_TYPE", //Required
MasterUsername: "MASTER_USER_NAME", // Required - must be lowercase
MasterUserPassword: "MASTER_USER_PASSWORD", // Required - must contain at least one uppercase letter, and one number
ClusterType: "CLUSTER_TYPE", // Required
IAMRoleARN: "IAM_ROLE_ARN", // Optional - the ARN of an IAM role with permissions your cluster needs to access other AWS services on your behalf, such as Amazon S3.
ClusterSubnetGroupName: "CLUSTER_SUBNET_GROUPNAME", //Optional - the name of a cluster subnet group to be associated with this cluster. Defaults to 'default' if not specified.
DBName: "DATABASE_NAME", // Optional - defaults to 'dev' if not specified
Port: "PORT_NUMBER", // Optional - defaults to '5439' if not specified
};
const run = async () => {
try {
const data = await redshiftClient.send(new CreateClusterCommand(params));
console.log(
`Cluster ${data.Cluster.ClusterIdentifier} successfully created`,
);
return data; // For unit tests.
} catch (err) {
console.log("Error", err);
}
};
run();
```
+ For API details, see [CreateCluster](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/redshift/command/CreateClusterCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Kotlin ]
**SDK for Kotlin**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/kotlin/services/redshift#code-examples).
Create the cluster.
```
suspend fun createCluster(
clusterId: String?,
masterUsernameVal: String?,
masterUserPasswordVal: String?,
) {
val clusterRequest =
CreateClusterRequest {
clusterIdentifier = clusterId
availabilityZone = "us-east-1a"
masterUsername = masterUsernameVal
masterUserPassword = masterUserPasswordVal
nodeType = "ra3.4xlarge"
publiclyAccessible = true
numberOfNodes = 2
}
RedshiftClient.fromEnvironment { region = "us-east-1" }.use { redshiftClient ->
val clusterResponse = redshiftClient.createCluster(clusterRequest)
println("Created cluster ${clusterResponse.cluster?.clusterIdentifier}")
}
}
```
+ For API details, see [CreateCluster](https://sdk.amazonaws.com/kotlin/api/latest/index.html) in *AWS SDK for Kotlin API reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftWrapper:
"""
Encapsulates Amazon Redshift cluster operations.
"""
def __init__(self, redshift_client):
"""
:param redshift_client: A Boto3 Redshift client.
"""
self.client = redshift_client
def create_cluster(
self,
cluster_identifier,
node_type,
master_username,
master_user_password,
publicly_accessible,
number_of_nodes,
):
"""
Creates a cluster.
:param cluster_identifier: The name of the cluster.
:param node_type: The type of node in the cluster.
:param master_username: The master username.
:param master_user_password: The master user password.
:param publicly_accessible: Whether the cluster is publicly accessible.
:param number_of_nodes: The number of nodes in the cluster.
:return: The cluster.
"""
try:
cluster = self.client.create_cluster(
ClusterIdentifier=cluster_identifier,
NodeType=node_type,
MasterUsername=master_username,
MasterUserPassword=master_user_password,
PubliclyAccessible=publicly_accessible,
NumberOfNodes=number_of_nodes,
)
return cluster
except ClientError as err:
logging.error(
"Couldn't create a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
The following code instantiates the RedshiftWrapper object.
```
client = boto3.client("redshift")
redhift_wrapper = RedshiftWrapper(client)
```
+ For API details, see [CreateCluster](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/CreateCluster) in *AWS SDK for Python (Boto3) API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsh#code-examples).
Create the cluster.
```
TRY.
" Example values: iv_cluster_identifier = 'my-redshift-cluster'
" Example values: iv_node_type = 'ra3.4xlarge'
" Example values: iv_master_username = 'awsuser'
" Example values: iv_master_password = 'AwsUser1000'
" Example values: iv_publicly_accessible = abap_true
" Example values: iv_number_of_nodes = 2
oo_result = lo_rsh->createcluster(
iv_clusteridentifier = iv_cluster_identifier
iv_nodetype = iv_node_type
iv_masterusername = iv_master_username
iv_masteruserpassword = iv_master_password
iv_publiclyaccessible = iv_publicly_accessible
iv_numberofnodes = iv_number_of_nodes
).
MESSAGE 'Redshift cluster created successfully.' TYPE 'I'.
CATCH /aws1/cx_rshclustalrdyexfault.
MESSAGE 'Cluster already exists.' TYPE 'I'.
CATCH /aws1/cx_rshclstquotaexcdfault.
MESSAGE 'Cluster quota exceeded.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [CreateCluster](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `DeleteCluster` with an AWS SDK or CLI
The following code examples show how to use `DeleteCluster`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code example:
+ [Getting started with Amazon Redshift provisioned clusters](example_redshift_GettingStarted_039_section.md)
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Delete an Amazon Redshift cluster without a final snapshot.
///
/// The identifier for the cluster.
/// True if successful.
public async Task DeleteClusterWithoutSnapshotAsync(string clusterIdentifier)
{
try
{
var request = new DeleteClusterRequest
{
ClusterIdentifier = clusterIdentifier,
SkipFinalClusterSnapshot = true
};
var response = await _redshiftClient.DeleteClusterAsync(request);
Console.WriteLine($"The {clusterIdentifier} was deleted");
return true;
}
catch (ClusterNotFoundException ex)
{
Console.WriteLine($"Cluster not found: {ex.Message}");
return false;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't delete cluster. Here's why: {ex.Message}");
return false;
}
}
```
+ For API details, see [DeleteCluster](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/DeleteCluster) in *AWS SDK for .NET API Reference*.
------
#### [ CLI ]
**AWS CLI**
Delete a Cluster with No Final Cluster SnapshotThis example deletes a cluster, forcing data deletion so no final cluster snapshot is created.Command:
```
aws redshift delete-cluster --cluster-identifier mycluster --skip-final-cluster-snapshot
```
Delete a Cluster, Allowing a Final Cluster SnapshotThis example deletes a cluster, but specifies a final cluster snapshot.Command:
```
aws redshift delete-cluster --cluster-identifier mycluster --final-cluster-snapshot-identifier myfinalsnapshot
```
+ For API details, see [DeleteCluster](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/redshift/delete-cluster.html) in *AWS CLI Command Reference*.
------
#### [ Go ]
**SDK for Go V2**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/gov2/redshift#code-examples).
```
import (
"context"
"errors"
"log"
"time"
"github.com/aws/aws-sdk-go-v2/aws"
"github.com/aws/aws-sdk-go-v2/service/redshift"
"github.com/aws/aws-sdk-go-v2/service/redshift/types"
)
// RedshiftActions wraps Redshift service actions.
type RedshiftActions struct {
RedshiftClient *redshift.Client
}
// DeleteCluster deletes the given cluster.
func (actor RedshiftActions) DeleteCluster(ctx context.Context, clusterId string) (bool, error) {
input := redshift.DeleteClusterInput{
ClusterIdentifier: aws.String(clusterId),
SkipFinalClusterSnapshot: aws.Bool(true),
}
_, err := actor.RedshiftClient.DeleteCluster(ctx, &input)
var opErr *types.ClusterNotFoundFault
if err != nil && errors.As(err, &opErr) {
log.Println("Cluster was not found. Where could it be?")
return false, err
} else if err != nil {
log.Printf("Failed to delete Redshift cluster: %v\n", err)
return false, err
}
waiter := redshift.NewClusterDeletedWaiter(actor.RedshiftClient)
err = waiter.Wait(ctx, &redshift.DescribeClustersInput{
ClusterIdentifier: aws.String(clusterId),
}, 5*time.Minute)
if err != nil {
log.Printf("Wait time exceeded for deleting cluster, continuing: %v\n", err)
}
log.Printf("The cluster %s was deleted\n", clusterId)
return true, nil
}
```
+ For API details, see [DeleteCluster](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.DeleteCluster) in *AWS SDK for Go API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Delete the cluster.
```
/**
* Deletes a Redshift cluster asynchronously.
*
* @param clusterId the identifier of the Redshift cluster to be deleted
* @return a {@link CompletableFuture} that represents the asynchronous operation of deleting the Redshift cluster
*/
public CompletableFuture deleteRedshiftClusterAsync(String clusterId) {
DeleteClusterRequest deleteClusterRequest = DeleteClusterRequest.builder()
.clusterIdentifier(clusterId)
.skipFinalClusterSnapshot(true)
.build();
return getAsyncClient().deleteCluster(deleteClusterRequest)
.whenComplete((response, exception) -> {
if (exception != null) {
// Handle exceptions
if (exception.getCause() instanceof RedshiftException) {
logger.info("Error: {}", exception.getMessage());
} else {
logger.info("Unexpected error: {}", exception.getMessage());
}
} else {
// Handle successful response
logger.info("The status is {}", response.cluster().clusterStatus());
}
});
}
```
+ For API details, see [DeleteCluster](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/DeleteCluster) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javascriptv3/example_code/redshift#code-examples).
Create the client.
```
import { RedshiftClient } from "@aws-sdk/client-redshift";
// Set the AWS Region.
const REGION = "REGION";
//Set the Redshift Service Object
const redshiftClient = new RedshiftClient({ region: REGION });
export { redshiftClient };
```
Create the cluster.
```
// Import required AWS SDK clients and commands for Node.js
import { DeleteClusterCommand } from "@aws-sdk/client-redshift";
import { redshiftClient } from "./libs/redshiftClient.js";
const params = {
ClusterIdentifier: "CLUSTER_NAME",
SkipFinalClusterSnapshot: false,
FinalClusterSnapshotIdentifier: "CLUSTER_SNAPSHOT_ID",
};
const run = async () => {
try {
const data = await redshiftClient.send(new DeleteClusterCommand(params));
console.log("Success, cluster deleted. ", data);
return data; // For unit tests.
} catch (err) {
console.log("Error", err);
}
};
run();
```
+ For API details, see [DeleteCluster](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/redshift/command/DeleteClusterCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Kotlin ]
**SDK for Kotlin**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/kotlin/services/redshift#code-examples).
Delete the cluster.
```
suspend fun deleteRedshiftCluster(clusterId: String?) {
val request =
DeleteClusterRequest {
clusterIdentifier = clusterId
skipFinalClusterSnapshot = true
}
RedshiftClient.fromEnvironment { region = "us-west-2" }.use { redshiftClient ->
val response = redshiftClient.deleteCluster(request)
println("The status is ${response.cluster?.clusterStatus}")
}
}
```
+ For API details, see [DeleteCluster](https://sdk.amazonaws.com/kotlin/api/latest/index.html) in *AWS SDK for Kotlin API reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftWrapper:
"""
Encapsulates Amazon Redshift cluster operations.
"""
def __init__(self, redshift_client):
"""
:param redshift_client: A Boto3 Redshift client.
"""
self.client = redshift_client
def delete_cluster(self, cluster_identifier):
"""
Deletes a cluster.
:param cluster_identifier: The cluster identifier.
"""
try:
self.client.delete_cluster(
ClusterIdentifier=cluster_identifier, SkipFinalClusterSnapshot=True
)
except ClientError as err:
logging.error(
"Couldn't delete a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
The following code instantiates the RedshiftWrapper object.
```
client = boto3.client("redshift")
redhift_wrapper = RedshiftWrapper(client)
```
+ For API details, see [DeleteCluster](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/DeleteCluster) in *AWS SDK for Python (Boto3) API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsh#code-examples).
Delete the cluster.
```
TRY.
" Example values: iv_cluster_identifier = 'my-redshift-cluster'
lo_rsh->deletecluster(
iv_clusteridentifier = iv_cluster_identifier
iv_skipfinalclustersnapshot = abap_true
).
MESSAGE 'Redshift cluster deleted successfully.' TYPE 'I'.
CATCH /aws1/cx_rshclustnotfoundfault.
MESSAGE 'Cluster not found.' TYPE 'I'.
CATCH /aws1/cx_rshinvcluststatefault.
MESSAGE 'Invalid cluster state for deletion.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [DeleteCluster](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `DescribeClusters` with an AWS SDK or CLI
The following code examples show how to use `DescribeClusters`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code examples:
+ [Learn the basics](example_redshift_Scenario_section.md)
+ [Getting started with Amazon Redshift provisioned clusters](example_redshift_GettingStarted_039_section.md)
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Describe Amazon Redshift clusters.
///
/// Optional cluster identifier to describe a specific cluster.
/// A list of clusters.
public async Task> DescribeClustersAsync(string? clusterIdentifier = null)
{
try
{
var clusters = new List();
var request = new DescribeClustersRequest();
if (!string.IsNullOrEmpty(clusterIdentifier))
{
request.ClusterIdentifier = clusterIdentifier;
}
var clustersPaginator = _redshiftClient.Paginators.DescribeClusters(request);
await foreach (var response in clustersPaginator.Responses)
{
if (response.Clusters != null)
clusters.AddRange(response.Clusters);
}
Console.WriteLine($"{clusters.Count} cluster(s) retrieved.");
foreach (var cluster in clusters)
{
Console.WriteLine($"\t{cluster.ClusterIdentifier} (Status: {cluster.ClusterStatus})");
}
return clusters;
}
catch (ClusterNotFoundException ex)
{
Console.WriteLine($"Cluster {clusterIdentifier} not found: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't describe clusters. Here's why: {ex.Message}");
throw;
}
}
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/DescribeClusters) in *AWS SDK for .NET API Reference*.
------
#### [ CLI ]
**AWS CLI**
Get a Description of All ClustersThis example returns a description of all clusters for the account. By default, the output is in JSON format.Command:
```
aws redshift describe-clusters
```
Result:
```
{
"Clusters": [
{
"NodeType": "dw.hs1.xlarge",
"Endpoint": {
"Port": 5439,
"Address": "mycluster.coqoarplqhsn.us-east-1.redshift.amazonaws.com"
},
"ClusterVersion": "1.0",
"PubliclyAccessible": "true",
"MasterUsername": "adminuser",
"ClusterParameterGroups": [
{
"ParameterApplyStatus": "in-sync",
"ParameterGroupName": "default.redshift-1.0"
} ],
"ClusterSecurityGroups": [
{
"Status": "active",
"ClusterSecurityGroupName": "default"
} ],
"AllowVersionUpgrade": true,
"VpcSecurityGroups": \[],
"AvailabilityZone": "us-east-1a",
"ClusterCreateTime": "2013-01-22T21:59:29.559Z",
"PreferredMaintenanceWindow": "sat:03:30-sat:04:00",
"AutomatedSnapshotRetentionPeriod": 1,
"ClusterStatus": "available",
"ClusterIdentifier": "mycluster",
"DBName": "dev",
"NumberOfNodes": 2,
"PendingModifiedValues": {}
} ],
"ResponseMetadata": {
"RequestId": "65b71cac-64df-11e2-8f5b-e90bd6c77476"
}
}
```
You can also obtain the same information in text format using the `--output text` option.Command:
`--output text` option.Command:
option.Command:
```
aws redshift describe-clusters --output text
```
Result:
```
dw.hs1.xlarge 1.0 true adminuser True us-east-1a 2013-01-22T21:59:29.559Z sat:03:30-sat:04:00 1 available mycluster dev 2
ENDPOINT 5439 mycluster.coqoarplqhsn.us-east-1.redshift.amazonaws.com
in-sync default.redshift-1.0
active default
PENDINGMODIFIEDVALUES
RESPONSEMETADATA 934281a8-64df-11e2-b07c-f7fbdd006c67
```
+ For API details, see [DescribeClusters](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/redshift/describe-clusters.html) in *AWS CLI Command Reference*.
------
#### [ Go ]
**SDK for Go V2**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/gov2/redshift#code-examples).
```
import (
"context"
"errors"
"log"
"time"
"github.com/aws/aws-sdk-go-v2/aws"
"github.com/aws/aws-sdk-go-v2/service/redshift"
"github.com/aws/aws-sdk-go-v2/service/redshift/types"
)
// RedshiftActions wraps Redshift service actions.
type RedshiftActions struct {
RedshiftClient *redshift.Client
}
// DescribeClusters returns information about the given cluster.
func (actor RedshiftActions) DescribeClusters(ctx context.Context, clusterId string) (*redshift.DescribeClustersOutput, error) {
input, err := actor.RedshiftClient.DescribeClusters(ctx, &redshift.DescribeClustersInput{
ClusterIdentifier: aws.String(clusterId),
})
var opErr *types.AccessToClusterDeniedFault
if errors.As(err, &opErr) {
println("Access to cluster denied.")
panic(err)
} else if err != nil {
println("Failed to describe Redshift clusters.")
return nil, err
}
return input, nil
}
```
+ For API details, see [DescribeClusters](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.DescribeClusters) in *AWS SDK for Go API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Describe the cluster.
```
/**
* Waits asynchronously for the specified cluster to become available.
* @param clusterId the identifier of the cluster to wait for
* @return a {@link CompletableFuture} that completes when the cluster is ready
*/
public CompletableFuture waitForClusterReadyAsync(String clusterId) {
DescribeClustersRequest clustersRequest = DescribeClustersRequest.builder()
.clusterIdentifier(clusterId)
.build();
logger.info("Waiting for cluster to become available. This may take a few minutes.");
long startTime = System.currentTimeMillis();
// Recursive method to poll the cluster status.
return checkClusterStatusAsync(clustersRequest, startTime);
}
private CompletableFuture checkClusterStatusAsync(DescribeClustersRequest clustersRequest, long startTime) {
return getAsyncClient().describeClusters(clustersRequest)
.thenCompose(clusterResponse -> {
List clusterList = clusterResponse.clusters();
boolean clusterReady = false;
for (Cluster cluster : clusterList) {
if ("available".equals(cluster.clusterStatus())) {
clusterReady = true;
break;
}
}
if (clusterReady) {
logger.info(String.format("Cluster is available!"));
return CompletableFuture.completedFuture(null);
} else {
long elapsedTimeMillis = System.currentTimeMillis() - startTime;
long elapsedSeconds = elapsedTimeMillis / 1000;
long minutes = elapsedSeconds / 60;
long seconds = elapsedSeconds % 60;
System.out.printf("\rElapsed Time: %02d:%02d - Waiting for cluster...", minutes, seconds);
System.out.flush();
// Wait 1 second before the next status check
return CompletableFuture.runAsync(() -> {
try {
TimeUnit.SECONDS.sleep(1);
} catch (InterruptedException e) {
throw new RuntimeException("Error during sleep: " + e.getMessage(), e);
}
}).thenCompose(ignored -> checkClusterStatusAsync(clustersRequest, startTime));
}
}).exceptionally(exception -> {
throw new RuntimeException("Failed to get cluster status: " + exception.getMessage(), exception);
});
}
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/DescribeClusters) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javascriptv3/example_code/redshift#code-examples).
Create the client.
```
import { RedshiftClient } from "@aws-sdk/client-redshift";
// Set the AWS Region.
const REGION = "REGION";
//Set the Redshift Service Object
const redshiftClient = new RedshiftClient({ region: REGION });
export { redshiftClient };
```
Describe your clusters.
```
// Import required AWS SDK clients and commands for Node.js
import { DescribeClustersCommand } from "@aws-sdk/client-redshift";
import { redshiftClient } from "./libs/redshiftClient.js";
const params = {
ClusterIdentifier: "CLUSTER_NAME",
};
const run = async () => {
try {
const data = await redshiftClient.send(new DescribeClustersCommand(params));
console.log("Success", data);
return data; // For unit tests.
} catch (err) {
console.log("Error", err);
}
};
run();
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/redshift/command/DescribeClustersCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Kotlin ]
**SDK for Kotlin**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/kotlin/services/redshift#code-examples).
Describe the cluster.
```
suspend fun describeRedshiftClusters() {
RedshiftClient.fromEnvironment { region = "us-west-2" }.use { redshiftClient ->
val clusterResponse = redshiftClient.describeClusters(DescribeClustersRequest {})
val clusterList = clusterResponse.clusters
if (clusterList != null) {
for (cluster in clusterList) {
println("Cluster database name is ${cluster.dbName}")
println("Cluster status is ${cluster.clusterStatus}")
}
}
}
}
```
+ For API details, see [DescribeClusters](https://sdk.amazonaws.com/kotlin/api/latest/index.html) in *AWS SDK for Kotlin API reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftWrapper:
"""
Encapsulates Amazon Redshift cluster operations.
"""
def __init__(self, redshift_client):
"""
:param redshift_client: A Boto3 Redshift client.
"""
self.client = redshift_client
def describe_clusters(self, cluster_identifier):
"""
Describes a cluster.
:param cluster_identifier: The cluster identifier.
:return: A list of clusters.
"""
try:
kwargs = {}
if cluster_identifier:
kwargs["ClusterIdentifier"] = cluster_identifier
paginator = self.client.get_paginator("describe_clusters")
clusters = []
for page in paginator.paginate(**kwargs):
clusters.extend(page["Clusters"])
return clusters
except ClientError as err:
logging.error(
"Couldn't describe a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
The following code instantiates the RedshiftWrapper object.
```
client = boto3.client("redshift")
redhift_wrapper = RedshiftWrapper(client)
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/DescribeClusters) in *AWS SDK for Python (Boto3) API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsh#code-examples).
Describe the cluster.
```
TRY.
" Example values: iv_cluster_identifier = 'my-redshift-cluster' (optional)
oo_result = lo_rsh->describeclusters(
iv_clusteridentifier = iv_cluster_identifier
).
lt_clusters = oo_result->get_clusters( ).
lv_cluster_count = lines( lt_clusters ).
MESSAGE |Retrieved { lv_cluster_count } cluster(s).| TYPE 'I'.
CATCH /aws1/cx_rshclustnotfoundfault.
MESSAGE 'Cluster not found.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [DescribeClusters](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `DescribeStatement` with an AWS SDK
The following code examples show how to use `DescribeStatement`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code example:
+ [Learn the basics](example_redshift_Scenario_section.md)
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Describe a statement execution.
///
/// The statement ID.
/// The statement description.
public async Task DescribeStatementAsync(string statementId)
{
try
{
var request = new DescribeStatementRequest
{
Id = statementId
};
var response = await _redshiftDataClient.DescribeStatementAsync(request);
return response;
}
catch (Amazon.RedshiftDataAPIService.Model.ResourceNotFoundException ex)
{
Console.WriteLine($"Statement not found: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't describe statement. Here's why: {ex.Message}");
throw;
}
}
```
+ For API details, see [DescribeStatement](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/DescribeStatement) in *AWS SDK for .NET API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
```
/**
* Checks the status of an SQL statement asynchronously and handles the completion of the statement.
*
* @param sqlId the ID of the SQL statement to check
* @return a {@link CompletableFuture} that completes when the SQL statement's status is either "FINISHED" or "FAILED"
*/
public CompletableFuture checkStatementAsync(String sqlId) {
DescribeStatementRequest statementRequest = DescribeStatementRequest.builder()
.id(sqlId)
.build();
return getAsyncDataClient().describeStatement(statementRequest)
.thenCompose(response -> {
String status = response.statusAsString();
logger.info("... Status: {} ", status);
if ("FAILED".equals(status)) {
throw new RuntimeException("The Query Failed. Ending program");
} else if ("FINISHED".equals(status)) {
return CompletableFuture.completedFuture(null);
} else {
// Sleep for 1 second and recheck status
return CompletableFuture.runAsync(() -> {
try {
TimeUnit.SECONDS.sleep(1);
} catch (InterruptedException e) {
throw new RuntimeException("Error during sleep: " + e.getMessage(), e);
}
}).thenCompose(ignore -> checkStatementAsync(sqlId)); // Recursively call until status is FINISHED or FAILED
}
}).whenComplete((result, exception) -> {
if (exception != null) {
// Handle exceptions
logger.info("Error: {} ", exception.getMessage());
} else {
logger.info("The statement is finished!");
}
});
}
```
+ For API details, see [DescribeStatement](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/DescribeStatement) in *AWS SDK for Java 2.x API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftDataWrapper:
"""Encapsulates Amazon Redshift data."""
def __init__(self, client):
"""
:param client: A Boto3 RedshiftDataWrapper client.
"""
self.client = client
def describe_statement(self, statement_id):
"""
Describes a SQL statement.
:param statement_id: The SQL statement identifier.
:return: The SQL statement result.
"""
try:
response = self.client.describe_statement(Id=statement_id)
return response
except ClientError as err:
logging.error(
"Couldn't describe statement. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
The following code instantiates the RedshiftDataWrapper object.
```
client = boto3.client("redshift-data")
redshift_data_wrapper = RedshiftDataWrapper(client)
```
+ For API details, see [DescribeStatement](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/DescribeStatement) in *AWS SDK for Python (Boto3) API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsd#code-examples).
```
TRY.
" Example values: iv_statement_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
oo_result = lo_rsd->describestatement(
iv_id = iv_statement_id
).
lv_status = oo_result->get_status( ).
MESSAGE |Statement status: { lv_status }| TYPE 'I'.
CATCH /aws1/cx_rsdresourcenotfoundex.
MESSAGE 'Statement not found.' TYPE 'I'.
CATCH /aws1/cx_rsdinternalserverex.
MESSAGE 'Internal server error.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [DescribeStatement](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `ExecuteStatement` with an AWS SDK
The following code examples show how to use `ExecuteStatement`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code example:
+ [Learn the basics](example_redshift_Scenario_section.md)
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Executes a SQL statement to create a database table.
```
/**
* Creates an asynchronous task to execute a SQL statement for creating a new table.
*
* @param clusterId the identifier of the Amazon Redshift cluster
* @param databaseName the name of the database to create the table in
* @param userName the username to use for the database connection
* @return a {@link CompletableFuture} that completes with the result of the SQL statement execution
* @throws RuntimeException if there is an error creating the table
*/
public CompletableFuture createTableAsync(String clusterId, String databaseName, String userName) {
ExecuteStatementRequest createTableRequest = ExecuteStatementRequest.builder()
.clusterIdentifier(clusterId)
.dbUser(userName)
.database(databaseName)
.sql("CREATE TABLE Movies (" +
"id INT PRIMARY KEY, " +
"title VARCHAR(100), " +
"year INT)")
.build();
return getAsyncDataClient().executeStatement(createTableRequest)
.whenComplete((response, exception) -> {
if (exception != null) {
throw new RuntimeException("Error creating table: " + exception.getMessage(), exception);
} else {
logger.info("Table created: Movies");
}
});
}
```
Executes a SQL statement to insert data into a database table.
```
/**
* Asynchronously pops a table from a JSON file.
*
* @param clusterId the ID of the cluster
* @param databaseName the name of the database
* @param userName the username
* @param fileName the name of the JSON file
* @param number the number of records to process
* @return a CompletableFuture that completes with the number of records added to the Movies table
*/
public CompletableFuture popTableAsync(String clusterId, String databaseName, String userName, String fileName, int number) {
return CompletableFuture.supplyAsync(() -> {
try {
JsonParser parser = new JsonFactory().createParser(new File(fileName));
JsonNode rootNode = new ObjectMapper().readTree(parser);
Iterator iter = rootNode.iterator();
return iter;
} catch (IOException e) {
throw new RuntimeException("Failed to read or parse JSON file: " + e.getMessage(), e);
}
}).thenCompose(iter -> processNodesAsync(clusterId, databaseName, userName, iter, number))
.whenComplete((result, exception) -> {
if (exception != null) {
logger.info("Error {} ", exception.getMessage());
} else {
logger.info("{} records were added to the Movies table." , result);
}
});
}
private CompletableFuture processNodesAsync(String clusterId, String databaseName, String userName, Iterator iter, int number) {
return CompletableFuture.supplyAsync(() -> {
int t = 0;
try {
while (iter.hasNext()) {
if (t == number)
break;
JsonNode currentNode = iter.next();
int year = currentNode.get("year").asInt();
String title = currentNode.get("title").asText();
// Use SqlParameter to avoid SQL injection.
List parameterList = new ArrayList<>();
String sqlStatement = "INSERT INTO Movies VALUES( :id , :title, :year);";
SqlParameter idParam = SqlParameter.builder()
.name("id")
.value(String.valueOf(t))
.build();
SqlParameter titleParam = SqlParameter.builder()
.name("title")
.value(title)
.build();
SqlParameter yearParam = SqlParameter.builder()
.name("year")
.value(String.valueOf(year))
.build();
parameterList.add(idParam);
parameterList.add(titleParam);
parameterList.add(yearParam);
ExecuteStatementRequest insertStatementRequest = ExecuteStatementRequest.builder()
.clusterIdentifier(clusterId)
.sql(sqlStatement)
.database(databaseName)
.dbUser(userName)
.parameters(parameterList)
.build();
getAsyncDataClient().executeStatement(insertStatementRequest);
logger.info("Inserted: " + title + " (" + year + ")");
t++;
}
} catch (RedshiftDataException e) {
throw new RuntimeException("Error inserting data: " + e.getMessage(), e);
}
return t;
});
}
```
Executes a SQL statement to query a database table.
```
/**
* Asynchronously queries movies by a given year from a Redshift database.
*
* @param database the name of the database to query
* @param dbUser the user to connect to the database with
* @param year the year to filter the movies by
* @param clusterId the identifier of the Redshift cluster to connect to
* @return a {@link CompletableFuture} containing the response ID of the executed SQL statement
*/
public CompletableFuture queryMoviesByYearAsync(String database,
String dbUser,
int year,
String clusterId) {
String sqlStatement = "SELECT * FROM Movies WHERE year = :year";
SqlParameter yearParam = SqlParameter.builder()
.name("year")
.value(String.valueOf(year))
.build();
ExecuteStatementRequest statementRequest = ExecuteStatementRequest.builder()
.clusterIdentifier(clusterId)
.database(database)
.dbUser(dbUser)
.parameters(yearParam)
.sql(sqlStatement)
.build();
return CompletableFuture.supplyAsync(() -> {
try {
ExecuteStatementResponse response = getAsyncDataClient().executeStatement(statementRequest).join(); // Use join() to wait for the result
return response.id();
} catch (RedshiftDataException e) {
throw new RuntimeException("Error executing statement: " + e.getMessage(), e);
}
}).exceptionally(exception -> {
logger.info("Error: {}", exception.getMessage());
return "";
});
}
```
+ For API details, see [ExecuteStatement](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/ExecuteStatement) in *AWS SDK for Java 2.x API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsd#code-examples).
```
TRY.
" Example values: iv_cluster_identifier = 'redshift-cluster-movies'
" Example values: iv_database_name = 'dev'
" Example values: iv_user_name = 'awsuser'
" Example values: iv_sql = 'SELECT * FROM movies WHERE year = :year'
" Example values: it_parameter_list - SQL parameters for parameterized queries
" Only pass parameters if the list is not empty
IF it_parameter_list IS NOT INITIAL.
oo_result = lo_rsd->executestatement(
iv_clusteridentifier = iv_cluster_identifier
iv_database = iv_database_name
iv_dbuser = iv_user_name
iv_sql = iv_sql
it_parameters = it_parameter_list
).
ELSE.
oo_result = lo_rsd->executestatement(
iv_clusteridentifier = iv_cluster_identifier
iv_database = iv_database_name
iv_dbuser = iv_user_name
iv_sql = iv_sql
).
ENDIF.
lv_statement_id = oo_result->get_id( ).
MESSAGE |Statement executed. ID: { lv_statement_id }| TYPE 'I'.
CATCH /aws1/cx_rsdexecutestatementex.
MESSAGE 'Statement execution error.' TYPE 'I'.
CATCH /aws1/cx_rsdresourcenotfoundex.
MESSAGE 'Resource not found.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [ExecuteStatement](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `GetStatementResult` with an AWS SDK
The following code examples show how to use `GetStatementResult`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code example:
+ [Learn the basics](example_redshift_Scenario_section.md)
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Get the results of a statement execution.
///
/// The statement ID.
/// A list of result rows.
public async Task>> GetStatementResultAsync(string statementId)
{
try
{
var request = new GetStatementResultRequest
{
Id = statementId
};
var response = await _redshiftDataClient.GetStatementResultAsync(request);
return response.Records;
}
catch (Amazon.RedshiftDataAPIService.Model.ResourceNotFoundException ex)
{
Console.WriteLine($"Statement not found: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't get statement result. Here's why: {ex.Message}");
throw;
}
}
```
+ For API details, see [GetStatementResult](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/GetStatementResult) in *AWS SDK for .NET API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Check the statement result.
```
/**
* Asynchronously retrieves the results of a statement execution.
*
* @param statementId the ID of the statement for which to retrieve the results
* @return a {@link CompletableFuture} that completes when the statement result has been processed
*/
public CompletableFuture getResultsAsync(String statementId) {
GetStatementResultRequest resultRequest = GetStatementResultRequest.builder()
.id(statementId)
.build();
return getAsyncDataClient().getStatementResult(resultRequest)
.handle((response, exception) -> {
if (exception != null) {
logger.info("Error getting statement result {} ", exception.getMessage());
throw new RuntimeException("Error getting statement result: " + exception.getMessage(), exception);
}
// Extract and print the field values using streams if the response is valid.
response.records().stream()
.flatMap(List::stream)
.map(Field::stringValue)
.filter(value -> value != null)
.forEach(value -> System.out.println("The Movie title field is " + value));
return response;
}).thenAccept(response -> {
// Optionally add more logic here if needed after handling the response
});
}
```
+ For API details, see [GetStatementResult](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/GetStatementResult) in *AWS SDK for Java 2.x API Reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftDataWrapper:
"""Encapsulates Amazon Redshift data."""
def __init__(self, client):
"""
:param client: A Boto3 RedshiftDataWrapper client.
"""
self.client = client
def get_statement_result(self, statement_id):
"""
Gets the result of a SQL statement.
:param statement_id: The SQL statement identifier.
:return: The SQL statement result.
"""
try:
result = {
"Records": [],
}
paginator = self.client.get_paginator("get_statement_result")
for page in paginator.paginate(Id=statement_id):
if "ColumnMetadata" not in result:
result["ColumnMetadata"] = page["ColumnMetadata"]
result["Records"].extend(page["Records"])
return result
except ClientError as err:
logging.error(
"Couldn't get statement result. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
The following code instantiates the RedshiftDataWrapper object.
```
client = boto3.client("redshift-data")
redshift_data_wrapper = RedshiftDataWrapper(client)
```
+ For API details, see [GetStatementResult](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/GetStatementResult) in *AWS SDK for Python (Boto3) API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsd#code-examples).
Check the statement result.
```
TRY.
" Example values: iv_statement_id = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'
" Handle pagination for large result sets
DO.
lo_result_page = lo_rsd->getstatementresult(
iv_id = iv_statement_id
iv_nexttoken = lv_next_token
).
" Collect records from this page
lt_page_records = lo_result_page->get_records( ).
APPEND LINES OF lt_page_records TO lt_all_records.
" Check if there are more pages
lv_next_token = lo_result_page->get_nexttoken( ).
IF lv_next_token IS INITIAL.
EXIT. " No more pages
ENDIF.
ENDDO.
" For the last call, set oo_result for return value
oo_result = lo_result_page.
lv_record_count = lines( lt_all_records ).
MESSAGE |Retrieved { lv_record_count } record(s).| TYPE 'I'.
CATCH /aws1/cx_rsdresourcenotfoundex.
MESSAGE 'Statement not found or results not available.' TYPE 'I'.
CATCH /aws1/cx_rsdinternalserverex.
MESSAGE 'Internal server error.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [GetStatementResult](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `ListDatabases` with an AWS SDK
The following code examples show how to use `ListDatabases`.
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// List databases in a Redshift cluster.
///
/// The cluster identifier.
/// The database user.
/// The database name for authentication.
/// A list of database names.
public async Task> ListDatabasesAsync(string clusterIdentifier, string dbUser, string databaseName)
{
try
{
var request = new ListDatabasesRequest
{
ClusterIdentifier = clusterIdentifier,
DbUser = dbUser,
Database = databaseName
};
var response = await _redshiftDataClient.ListDatabasesAsync(request);
var databases = new List();
foreach (var database in response.Databases)
{
Console.WriteLine($"The database name is : {database}");
databases.Add(database);
}
return databases;
}
catch (Amazon.RedshiftDataAPIService.Model.ValidationException ex)
{
Console.WriteLine($"Validation error: {ex.Message}");
throw;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't list databases. Here's why: {ex.Message}");
throw;
}
}
```
+ For API details, see [ListDatabases](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/ListDatabases) in *AWS SDK for .NET API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
```
/**
* Lists all databases asynchronously for the specified cluster, database user, and database.
* @param clusterId the identifier of the cluster to list databases for
* @param dbUser the database user to use for the list databases request
* @param database the database to list databases for
* @return a {@link CompletableFuture} that completes when the database listing is complete, or throws a {@link RuntimeException} if there was an error
*/
public CompletableFuture listAllDatabasesAsync(String clusterId, String dbUser, String database) {
ListDatabasesRequest databasesRequest = ListDatabasesRequest.builder()
.clusterIdentifier(clusterId)
.dbUser(dbUser)
.database(database)
.build();
// Asynchronous paginator for listing databases.
ListDatabasesPublisher databasesPaginator = getAsyncDataClient().listDatabasesPaginator(databasesRequest);
CompletableFuture future = databasesPaginator.subscribe(response -> {
response.databases().forEach(db -> {
logger.info("The database name is {} ", db);
});
});
// Return the future for asynchronous handling.
return future.exceptionally(exception -> {
throw new RuntimeException("Failed to list databases: " + exception.getMessage(), exception);
});
}
```
+ For API details, see [ListDatabases](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/ListDatabases) in *AWS SDK for Java 2.x API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsd#code-examples).
```
TRY.
" Example values: iv_cluster_identifier = 'redshift-cluster-movies'
" Example values: iv_database_name = 'dev'
" Example values: iv_database_user = 'awsuser'
oo_result = lo_rsd->listdatabases(
iv_clusteridentifier = iv_cluster_identifier
iv_database = iv_database_name
iv_dbuser = iv_database_user
).
lt_databases = oo_result->get_databases( ).
lv_db_count = lines( lt_databases ).
MESSAGE |Retrieved { lv_db_count } database(s).| TYPE 'I'.
CATCH /aws1/cx_rsddatabaseconnex.
MESSAGE 'Database connection error.' TYPE 'I'.
CATCH /aws1/cx_rsdresourcenotfoundex.
MESSAGE 'Cluster not found.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [ListDatabases](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Use `ModifyCluster` with an AWS SDK or CLI
The following code examples show how to use `ModifyCluster`.
Action examples are code excerpts from larger programs and must be run in context. You can see this action in context in the following code example:
+ [Learn the basics](example_redshift_Scenario_section.md)
------
#### [ .NET ]
**SDK for .NET (v4)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/dotnetv4/Redshift#code-examples).
```
///
/// Modify an Amazon Redshift cluster.
///
/// The identifier for the cluster.
/// The preferred maintenance window.
/// True if successful.
public async Task ModifyClusterAsync(string clusterIdentifier, string preferredMaintenanceWindow)
{
try
{
var request = new ModifyClusterRequest
{
ClusterIdentifier = clusterIdentifier,
PreferredMaintenanceWindow = preferredMaintenanceWindow
};
var response = await _redshiftClient.ModifyClusterAsync(request);
Console.WriteLine($"The modified cluster was successfully modified and has {response.Cluster.PreferredMaintenanceWindow} as the maintenance window");
return true;
}
catch (ClusterNotFoundException ex)
{
Console.WriteLine($"Cluster {clusterIdentifier} not found: {ex.Message}");
return false;
}
catch (Exception ex)
{
Console.WriteLine($"Couldn't modify cluster. Here's why: {ex.Message}");
return false;
}
}
```
+ For API details, see [ModifyCluster](https://docs.aws.amazon.com/goto/DotNetSDKV4/redshift-2012-12-01/ModifyCluster) in *AWS SDK for .NET API Reference*.
------
#### [ CLI ]
**AWS CLI**
Associate a Security Group with a ClusterThis example shows how to associate a cluster security group with the specified cluster.Command:
```
aws redshift modify-cluster --cluster-identifier mycluster --cluster-security-groups mysecuritygroup
```
Modify the Maintenance Window for a ClusterThis shows how to change the weekly preferred maintenance window for a cluster to be the minimum four hour window starting Sundays at 11:15 PM, and ending Mondays at 3:15 AM.Command:
```
aws redshift modify-cluster --cluster-identifier mycluster --preferred-maintenance-window Sun:23:15-Mon:03:15
```
Change the Master Password for the ClusterThis example shows how to change the master password for a cluster.Command:
```
aws redshift modify-cluster --cluster-identifier mycluster --master-user-password A1b2c3d4
```
+ For API details, see [ModifyCluster](https://awscli.amazonaws.com/v2/documentation/api/latest/reference/redshift/modify-cluster.html) in *AWS CLI Command Reference*.
------
#### [ Go ]
**SDK for Go V2**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/gov2/redshift#code-examples).
```
import (
"context"
"errors"
"log"
"time"
"github.com/aws/aws-sdk-go-v2/aws"
"github.com/aws/aws-sdk-go-v2/service/redshift"
"github.com/aws/aws-sdk-go-v2/service/redshift/types"
)
// RedshiftActions wraps Redshift service actions.
type RedshiftActions struct {
RedshiftClient *redshift.Client
}
// ModifyCluster sets the preferred maintenance window for the given cluster.
func (actor RedshiftActions) ModifyCluster(ctx context.Context, clusterId string, maintenanceWindow string) *redshift.ModifyClusterOutput {
// Modify the cluster's maintenance window
input := &redshift.ModifyClusterInput{
ClusterIdentifier: aws.String(clusterId),
PreferredMaintenanceWindow: aws.String(maintenanceWindow),
}
var opErr *types.InvalidClusterStateFault
output, err := actor.RedshiftClient.ModifyCluster(ctx, input)
if err != nil && errors.As(err, &opErr) {
log.Println("Cluster is in an invalid state.")
panic(err)
} else if err != nil {
log.Printf("Failed to modify Redshift cluster: %v\n", err)
panic(err)
}
log.Printf("The cluster was successfully modified and now has %s as the maintenance window\n", *output.Cluster.PreferredMaintenanceWindow)
return output
}
```
+ For API details, see [ModifyCluster](https://pkg.go.dev/github.com/aws/aws-sdk-go-v2/service/redshift#Client.ModifyCluster) in *AWS SDK for Go API Reference*.
------
#### [ Java ]
**SDK for Java 2.x**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/example_code/redshift#code-examples).
Modify a cluster.
```
/**
* Modifies an Amazon Redshift cluster asynchronously.
*
* @param clusterId the identifier of the cluster to be modified
* @return a {@link CompletableFuture} that completes when the cluster modification is complete
*/
public CompletableFuture modifyClusterAsync(String clusterId) {
ModifyClusterRequest modifyClusterRequest = ModifyClusterRequest.builder()
.clusterIdentifier(clusterId)
.preferredMaintenanceWindow("wed:07:30-wed:08:00")
.build();
return getAsyncClient().modifyCluster(modifyClusterRequest)
.whenComplete((clusterResponse, exception) -> {
if (exception != null) {
if (exception.getCause() instanceof RedshiftException) {
logger.info("Error: {} ", exception.getMessage());
} else {
logger.info("Unexpected error: {} ", exception.getMessage());
}
} else {
logger.info("The modified cluster was successfully modified and has "
+ clusterResponse.cluster().preferredMaintenanceWindow() + " as the maintenance window");
}
});
}
```
+ For API details, see [ModifyCluster](https://docs.aws.amazon.com/goto/SdkForJavaV2/redshift-2012-12-01/ModifyCluster) in *AWS SDK for Java 2.x API Reference*.
------
#### [ JavaScript ]
**SDK for JavaScript (v3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javascriptv3/example_code/redshift#code-examples).
Create the client.
```
import { RedshiftClient } from "@aws-sdk/client-redshift";
// Set the AWS Region.
const REGION = "REGION";
//Set the Redshift Service Object
const redshiftClient = new RedshiftClient({ region: REGION });
export { redshiftClient };
```
Modify a cluster.
```
// Import required AWS SDK clients and commands for Node.js
import { ModifyClusterCommand } from "@aws-sdk/client-redshift";
import { redshiftClient } from "./libs/redshiftClient.js";
// Set the parameters
const params = {
ClusterIdentifier: "CLUSTER_NAME",
MasterUserPassword: "NEW_MASTER_USER_PASSWORD",
};
const run = async () => {
try {
const data = await redshiftClient.send(new ModifyClusterCommand(params));
console.log("Success was modified.", data);
return data; // For unit tests.
} catch (err) {
console.log("Error", err);
}
};
run();
```
+ For API details, see [ModifyCluster](https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/client/redshift/command/ModifyClusterCommand) in *AWS SDK for JavaScript API Reference*.
------
#### [ Kotlin ]
**SDK for Kotlin**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/kotlin/services/redshift#code-examples).
Modify a cluster.
```
suspend fun modifyCluster(clusterId: String?) {
val modifyClusterRequest =
ModifyClusterRequest {
clusterIdentifier = clusterId
preferredMaintenanceWindow = "wed:07:30-wed:08:00"
}
RedshiftClient { region = "us-west-2" }.use { redshiftClient ->
val clusterResponse = redshiftClient.modifyCluster(modifyClusterRequest)
println(
"The modified cluster was successfully modified and has ${clusterResponse.cluster?.preferredMaintenanceWindow} as the maintenance window",
)
}
}
```
+ For API details, see [ModifyCluster](https://sdk.amazonaws.com/kotlin/api/latest/index.html) in *AWS SDK for Kotlin API reference*.
------
#### [ Python ]
**SDK for Python (Boto3)**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/python/example_code/redshift#code-examples).
```
class RedshiftWrapper:
"""
Encapsulates Amazon Redshift cluster operations.
"""
def __init__(self, redshift_client):
"""
:param redshift_client: A Boto3 Redshift client.
"""
self.client = redshift_client
def modify_cluster(self, cluster_identifier, preferred_maintenance_window):
"""
Modifies a cluster.
:param cluster_identifier: The cluster identifier.
:param preferred_maintenance_window: The preferred maintenance window.
"""
try:
self.client.modify_cluster(
ClusterIdentifier=cluster_identifier,
PreferredMaintenanceWindow=preferred_maintenance_window,
)
except ClientError as err:
logging.error(
"Couldn't modify a cluster. Here's why: %s: %s",
err.response["Error"]["Code"],
err.response["Error"]["Message"],
)
raise
```
The following code instantiates the RedshiftWrapper object.
```
client = boto3.client("redshift")
redhift_wrapper = RedshiftWrapper(client)
```
+ For API details, see [ModifyCluster](https://docs.aws.amazon.com/goto/boto3/redshift-2012-12-01/ModifyCluster) in *AWS SDK for Python (Boto3) API Reference*.
------
#### [ SAP ABAP ]
**SDK for SAP ABAP**
There's more on GitHub. Find the complete example and learn how to set up and run in the [AWS Code Examples Repository](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/sap-abap/services/rsh#code-examples).
Modify a cluster.
```
TRY.
" Example values: iv_cluster_identifier = 'my-redshift-cluster'
" Example values: iv_pref_maintenance_wn = 'wed:07:30-wed:08:00'
lo_rsh->modifycluster(
iv_clusteridentifier = iv_cluster_identifier
iv_preferredmaintenancewin00 = iv_pref_maintenance_wn
).
MESSAGE 'Redshift cluster modified successfully.' TYPE 'I'.
CATCH /aws1/cx_rshclustnotfoundfault.
MESSAGE 'Cluster not found.' TYPE 'I'.
CATCH /aws1/cx_rshinvcluststatefault.
MESSAGE 'Invalid cluster state for modification.' TYPE 'I'.
ENDTRY.
```
+ For API details, see [ModifyCluster](https://docs.aws.amazon.com/sdk-for-sap-abap/v1/api/latest/index.html) in *AWS SDK for SAP ABAP API reference*.
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Scenarios for Amazon Redshift using AWS SDKs
The following code examples show you how to implement common scenarios in Amazon Redshift with AWS SDKs. These scenarios show you how to accomplish specific tasks by calling multiple functions within Amazon Redshift or combined with other AWS services. Each scenario includes a link to the complete source code, where you can find instructions on how to set up and run the code.
Scenarios target an intermediate level of experience to help you understand service actions in context.
**Topics**
+ [Create a web application to track Amazon Redshift data](example_cross_RedshiftDataTracker_section.md)
+ [Get started with Redshift Serverless](example_redshift_GettingStarted_038_section.md)
+ [Getting started with Amazon Redshift provisioned clusters](example_redshift_GettingStarted_039_section.md)
# Create an Amazon Redshift item tracker
The following code examples show how to create a web application that tracks and reports on work items using an Amazon Redshift database.
------
#### [ Java ]
**SDK for Java 2.x**
Shows how to create a web application that tracks and reports on work items stored in an Amazon Redshift database.
For complete source code and instructions on how to set up a Spring REST API that queries Amazon Redshift data and for use by a React application, see the full example on [GitHub](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/javav2/usecases/CreatingSpringRedshiftRest).
**Services used in this example**
+ Amazon Redshift
+ Amazon SES
------
#### [ Kotlin ]
**SDK for Kotlin**
Shows how to create a web application that tracks and reports on work items stored in an Amazon Redshift database.
For complete source code and instructions on how to set up a Spring REST API that queries Amazon Redshift data and for use by a React application, see the full example on [GitHub](https://github.com/awsdocs/aws-doc-sdk-examples/tree/main/kotlin/usecases/creating_redshift_application).
**Services used in this example**
+ Amazon Redshift
+ Amazon SES
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Get started with Redshift Serverless using the CLI
The following code example shows how to:
+ Use secrets-manager CreateSecret
+ Use secrets-manager DeleteSecret
+ Use secrets-manager GetSecretValue
+ Use redshift CreateNamespace
+ Use redshift CreateWorkgroup
+ Use redshift DeleteNamespace
+ Use iam CreateRole
------
#### [ Bash ]
**AWS CLI with Bash script**
There's more on GitHub. Find the complete example and learn how to set up and run in the [Sample developer tutorials](https://github.com/aws-samples/sample-developer-tutorials/tree/main/tuts/038-redshift-serverless) repository.
```
#!/bin/bash
# Amazon Redshift Serverless Tutorial Script with Secrets Manager (No jq dependency)
# This script creates a Redshift Serverless environment, loads sample data, and runs queries
# Uses AWS Secrets Manager for secure password management without requiring jq
# Set up logging
LOG_FILE="redshift-serverless-tutorial-v4.log"
exec > >(tee -a "$LOG_FILE") 2>&1
echo "Starting Amazon Redshift Serverless tutorial script at $(date)"
echo "All commands and outputs will be logged to $LOG_FILE"
# Function to check for errors in command output
check_error() {
local output=$1
local cmd=$2
if echo "$output" | grep -i "error\|exception\|fail" > /dev/null; then
echo "ERROR: Command failed: $cmd"
echo "Output: $output"
cleanup_resources
exit 1
fi
}
# Function to generate a secure password that meets Redshift requirements
generate_secure_password() {
# Redshift password requirements:
# - 8-64 characters
# - At least one uppercase letter
# - At least one lowercase letter
# - At least one decimal digit
# - Can contain printable ASCII characters except /, ", ', \, @, space
local password=""
local valid=false
local attempts=0
local max_attempts=10
while [[ "$valid" == false && $attempts -lt $max_attempts ]]; do
# Generate base password with safe characters
local base=$(openssl rand -base64 12 | tr -d '/+=' | head -c 12)
# Ensure we have at least one of each required character type
local upper=$(echo "ABCDEFGHIJKLMNOPQRSTUVWXYZ" | fold -w1 | shuf -n1)
local lower=$(echo "abcdefghijklmnopqrstuvwxyz" | fold -w1 | shuf -n1)
local digit=$(echo "0123456789" | fold -w1 | shuf -n1)
local special=$(echo "!#$%&*()_+-=[]{}|;:,.<>?" | fold -w1 | shuf -n1)
# Combine and shuffle
password="${base}${upper}${lower}${digit}${special}"
password=$(echo "$password" | fold -w1 | shuf | tr -d '\n')
# Validate password meets requirements
if [[ ${#password} -ge 8 && ${#password} -le 64 ]] && \
[[ "$password" =~ [A-Z] ]] && \
[[ "$password" =~ [a-z] ]] && \
[[ "$password" =~ [0-9] ]] && \
[[ ! "$password" =~ [/\"\'\\@[:space:]] ]]; then
valid=true
fi
((attempts++))
done
if [[ "$valid" == false ]]; then
echo "ERROR: Failed to generate valid password after $max_attempts attempts"
exit 1
fi
echo "$password"
}
# Function to create secret in AWS Secrets Manager
create_secret() {
local secret_name=$1
local username=$2
local password=$3
local description=$4
echo "Creating secret in AWS Secrets Manager: $secret_name"
# Create the secret using AWS CLI without jq
local secret_output=$(aws secretsmanager create-secret \
--name "$secret_name" \
--description "$description" \
--secret-string "{\"username\":\"$username\",\"password\":\"$password\"}" 2>&1)
if echo "$secret_output" | grep -i "error\|exception\|fail" > /dev/null; then
echo "ERROR: Failed to create secret: $secret_output"
return 1
fi
echo "Secret created successfully: $secret_name"
return 0
}
# Function to retrieve password from AWS Secrets Manager
get_password_from_secret() {
local secret_name=$1
# Get the secret value and extract password using sed/grep instead of jq
local secret_value=$(aws secretsmanager get-secret-value \
--secret-id "$secret_name" \
--query 'SecretString' \
--output text 2>/dev/null)
if [[ $? -eq 0 ]]; then
# Extract password from JSON using sed
echo "$secret_value" | sed -n 's/.*"password":"\([^"]*\)".*/\1/p'
else
echo ""
fi
}
# Function to wait for a resource to be available
wait_for_resource() {
local resource_type=$1
local resource_name=$2
local max_attempts=$3
local wait_seconds=$4
local check_cmd=$5
echo "Waiting for $resource_type $resource_name to be available..."
for ((i=1; i<=$max_attempts; i++)); do
local output=$($check_cmd 2>/dev/null)
local status=$(echo "$output" | grep -o '"Status": "[^"]*' | cut -d'"' -f4 || echo "")
if [[ "$status" == "AVAILABLE" ]]; then
echo "$resource_type $resource_name is now available"
return 0
fi
echo "Attempt $i/$max_attempts: $resource_type $resource_name status: $status. Waiting $wait_seconds seconds..."
sleep $wait_seconds
done
echo "ERROR: Timed out waiting for $resource_type $resource_name to be available"
return 1
}
# Function to wait for a resource to be deleted
wait_for_resource_deletion() {
local resource_type=$1
local resource_name=$2
local max_attempts=$3
local wait_seconds=$4
local check_cmd=$5
echo "Waiting for $resource_type $resource_name to be deleted..."
for ((i=1; i<=$max_attempts; i++)); do
local output=$($check_cmd 2>&1)
if echo "$output" | grep -i "not found\|does not exist" > /dev/null; then
echo "$resource_type $resource_name has been deleted"
return 0
fi
echo "Attempt $i/$max_attempts: $resource_type $resource_name is still being deleted. Waiting $wait_seconds seconds..."
sleep $wait_seconds
done
echo "ERROR: Timed out waiting for $resource_type $resource_name to be deleted"
return 1
}
# Function to clean up resources
cleanup_resources() {
echo ""
echo "==========================================="
echo "CLEANUP CONFIRMATION"
echo "==========================================="
echo "The following resources were created:"
echo "- Redshift Serverless Workgroup: $WORKGROUP_NAME"
echo "- Redshift Serverless Namespace: $NAMESPACE_NAME"
echo "- IAM Role: $ROLE_NAME"
echo "- Secrets Manager Secret: $SECRET_NAME"
echo ""
echo "Do you want to clean up all created resources? (y/n): "
read -r CLEANUP_CHOICE
if [[ "${CLEANUP_CHOICE,,}" == "y" ]]; then
echo "Cleaning up resources..."
# Delete the workgroup
echo "Deleting Redshift Serverless workgroup $WORKGROUP_NAME..."
WORKGROUP_DELETE_OUTPUT=$(aws redshift-serverless delete-workgroup --workgroup-name "$WORKGROUP_NAME" 2>&1)
echo "$WORKGROUP_DELETE_OUTPUT"
# Wait for workgroup to be deleted before deleting namespace
wait_for_resource_deletion "workgroup" "$WORKGROUP_NAME" 20 30 "aws redshift-serverless get-workgroup --workgroup-name $WORKGROUP_NAME"
# Delete the namespace
echo "Deleting Redshift Serverless namespace $NAMESPACE_NAME..."
NAMESPACE_DELETE_OUTPUT=$(aws redshift-serverless delete-namespace --namespace-name "$NAMESPACE_NAME" 2>&1)
echo "$NAMESPACE_DELETE_OUTPUT"
# Wait for namespace to be deleted
wait_for_resource_deletion "namespace" "$NAMESPACE_NAME" 20 30 "aws redshift-serverless get-namespace --namespace-name $NAMESPACE_NAME"
# Delete the IAM role policy
echo "Deleting IAM role policy..."
POLICY_DELETE_OUTPUT=$(aws iam delete-role-policy --role-name "$ROLE_NAME" --policy-name S3Access 2>&1)
echo "$POLICY_DELETE_OUTPUT"
# Delete the IAM role
echo "Deleting IAM role $ROLE_NAME..."
ROLE_DELETE_OUTPUT=$(aws iam delete-role --role-name "$ROLE_NAME" 2>&1)
echo "$ROLE_DELETE_OUTPUT"
# Delete the secret
echo "Deleting Secrets Manager secret $SECRET_NAME..."
SECRET_DELETE_OUTPUT=$(aws secretsmanager delete-secret --secret-id "$SECRET_NAME" --force-delete-without-recovery 2>&1)
echo "$SECRET_DELETE_OUTPUT"
echo "Cleanup completed."
else
echo "Cleanup skipped. Resources will remain in your AWS account."
fi
}
# Check if required tools are available
if ! command -v openssl &> /dev/null; then
echo "ERROR: openssl is required but not installed. Please install openssl to continue."
exit 1
fi
# Generate unique names for resources
RANDOM_SUFFIX=$(cat /dev/urandom | tr -dc 'a-z0-9' | head -c 6)
NAMESPACE_NAME="rs-namespace-${RANDOM_SUFFIX}"
WORKGROUP_NAME="rs-workgroup-${RANDOM_SUFFIX}"
ROLE_NAME="RedshiftServerlessS3Role-${RANDOM_SUFFIX}"
SECRET_NAME="redshift-serverless-admin-${RANDOM_SUFFIX}"
DB_NAME="dev"
ADMIN_USERNAME="admin"
# Generate secure password
echo "Generating secure password..."
ADMIN_PASSWORD=$(generate_secure_password)
# Create secret in AWS Secrets Manager
create_secret "$SECRET_NAME" "$ADMIN_USERNAME" "$ADMIN_PASSWORD" "Admin credentials for Redshift Serverless namespace $NAMESPACE_NAME"
if [[ $? -ne 0 ]]; then
echo "ERROR: Failed to create secret in AWS Secrets Manager"
exit 1
fi
# Track created resources
CREATED_RESOURCES=()
echo "Using the following resource names:"
echo "- Namespace: $NAMESPACE_NAME"
echo "- Workgroup: $WORKGROUP_NAME"
echo "- IAM Role: $ROLE_NAME"
echo "- Secret: $SECRET_NAME"
echo "- Database: $DB_NAME"
echo "- Admin Username: $ADMIN_USERNAME"
echo "- Admin Password: [STORED IN SECRETS MANAGER]"
# Step 1: Create IAM role for S3 access
echo "Creating IAM role for Redshift Serverless S3 access..."
# Create trust policy document
cat > redshift-trust-policy.json << EOF
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "redshift-serverless.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
EOF
# Create S3 access policy document
cat > redshift-s3-policy.json << EOF
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::redshift-downloads",
"arn:aws:s3:::redshift-downloads/*"
]
}
]
}
EOF
# Create IAM role
echo "Creating IAM role $ROLE_NAME..."
ROLE_OUTPUT=$(aws iam create-role --role-name "$ROLE_NAME" --assume-role-policy-document file://redshift-trust-policy.json 2>&1)
echo "$ROLE_OUTPUT"
check_error "$ROLE_OUTPUT" "aws iam create-role"
CREATED_RESOURCES+=("IAM Role: $ROLE_NAME")
# Attach S3 policy to the role
echo "Attaching S3 access policy to role $ROLE_NAME..."
POLICY_OUTPUT=$(aws iam put-role-policy --role-name "$ROLE_NAME" --policy-name S3Access --policy-document file://redshift-s3-policy.json 2>&1)
echo "$POLICY_OUTPUT"
check_error "$POLICY_OUTPUT" "aws iam put-role-policy"
# Get the role ARN
ROLE_ARN=$(aws iam get-role --role-name "$ROLE_NAME" --query 'Role.Arn' --output text)
echo "Role ARN: $ROLE_ARN"
# Step 2: Create a namespace
echo "Creating Redshift Serverless namespace $NAMESPACE_NAME..."
NAMESPACE_OUTPUT=$(aws redshift-serverless create-namespace \
--namespace-name "$NAMESPACE_NAME" \
--admin-username "$ADMIN_USERNAME" \
--admin-user-password "$ADMIN_PASSWORD" \
--db-name "$DB_NAME" 2>&1)
echo "$NAMESPACE_OUTPUT"
check_error "$NAMESPACE_OUTPUT" "aws redshift-serverless create-namespace"
CREATED_RESOURCES+=("Redshift Serverless Namespace: $NAMESPACE_NAME")
# Wait for namespace to be available
wait_for_resource "namespace" "$NAMESPACE_NAME" 10 30 "aws redshift-serverless get-namespace --namespace-name $NAMESPACE_NAME"
# Associate IAM role with namespace
echo "Associating IAM role with namespace..."
UPDATE_NAMESPACE_OUTPUT=$(aws redshift-serverless update-namespace \
--namespace-name "$NAMESPACE_NAME" \
--iam-roles "$ROLE_ARN" 2>&1)
echo "$UPDATE_NAMESPACE_OUTPUT"
check_error "$UPDATE_NAMESPACE_OUTPUT" "aws redshift-serverless update-namespace"
# Step 3: Create a workgroup
echo "Creating Redshift Serverless workgroup $WORKGROUP_NAME..."
WORKGROUP_OUTPUT=$(aws redshift-serverless create-workgroup \
--workgroup-name "$WORKGROUP_NAME" \
--namespace-name "$NAMESPACE_NAME" \
--base-capacity 8 2>&1)
echo "$WORKGROUP_OUTPUT"
check_error "$WORKGROUP_OUTPUT" "aws redshift-serverless create-workgroup"
CREATED_RESOURCES+=("Redshift Serverless Workgroup: $WORKGROUP_NAME")
# Wait for workgroup to be available
wait_for_resource "workgroup" "$WORKGROUP_NAME" 20 30 "aws redshift-serverless get-workgroup --workgroup-name $WORKGROUP_NAME"
# Get workgroup endpoint
WORKGROUP_ENDPOINT=$(aws redshift-serverless get-workgroup \
--workgroup-name "$WORKGROUP_NAME" \
--query 'workgroup.endpoint.address' \
--output text)
echo "Workgroup endpoint: $WORKGROUP_ENDPOINT"
# Wait additional time for the endpoint to be fully operational
echo "Waiting for endpoint to be fully operational..."
sleep 60
# Step 4: Create tables for sample data
echo "Creating tables for sample data..."
# Create users table
echo "Creating users table..."
USERS_TABLE_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "CREATE TABLE users(
userid INTEGER NOT NULL DISTKEY SORTKEY,
username CHAR(8),
firstname VARCHAR(30),
lastname VARCHAR(30),
city VARCHAR(30),
state CHAR(2),
email VARCHAR(100),
phone CHAR(14),
likesports BOOLEAN,
liketheatre BOOLEAN,
likeconcerts BOOLEAN,
likejazz BOOLEAN,
likeclassical BOOLEAN,
likeopera BOOLEAN,
likerock BOOLEAN,
likevegas BOOLEAN,
likebroadway BOOLEAN,
likemusicals BOOLEAN
);" 2>&1)
echo "$USERS_TABLE_OUTPUT"
check_error "$USERS_TABLE_OUTPUT" "aws redshift-data execute-statement (users table)"
USERS_QUERY_ID=$(echo "$USERS_TABLE_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for query to complete
echo "Waiting for users table creation to complete..."
sleep 5
# Create event table
echo "Creating event table..."
EVENT_TABLE_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "CREATE TABLE event(
eventid INTEGER NOT NULL DISTKEY,
venueid SMALLINT NOT NULL,
catid SMALLINT NOT NULL,
dateid SMALLINT NOT NULL SORTKEY,
eventname VARCHAR(200),
starttime TIMESTAMP
);" 2>&1)
echo "$EVENT_TABLE_OUTPUT"
check_error "$EVENT_TABLE_OUTPUT" "aws redshift-data execute-statement (event table)"
EVENT_QUERY_ID=$(echo "$EVENT_TABLE_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for query to complete
echo "Waiting for event table creation to complete..."
sleep 5
# Create sales table
echo "Creating sales table..."
SALES_TABLE_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "CREATE TABLE sales(
salesid INTEGER NOT NULL,
listid INTEGER NOT NULL DISTKEY,
sellerid INTEGER NOT NULL,
buyerid INTEGER NOT NULL,
eventid INTEGER NOT NULL,
dateid SMALLINT NOT NULL SORTKEY,
qtysold SMALLINT NOT NULL,
pricepaid DECIMAL(8,2),
commission DECIMAL(8,2),
saletime TIMESTAMP
);" 2>&1)
echo "$SALES_TABLE_OUTPUT"
check_error "$SALES_TABLE_OUTPUT" "aws redshift-data execute-statement (sales table)"
SALES_QUERY_ID=$(echo "$SALES_TABLE_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for tables to be created
echo "Waiting for tables to be created..."
sleep 10
# Step 5: Load sample data from Amazon S3
echo "Loading sample data from Amazon S3..."
# Load data into users table
echo "Loading data into users table..."
USERS_LOAD_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "COPY users
FROM 's3://redshift-downloads/tickit/allusers_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
IAM_ROLE '$ROLE_ARN';" 2>&1)
echo "$USERS_LOAD_OUTPUT"
check_error "$USERS_LOAD_OUTPUT" "aws redshift-data execute-statement (load users)"
USERS_LOAD_QUERY_ID=$(echo "$USERS_LOAD_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for data loading to complete
echo "Waiting for users data loading to complete..."
sleep 10
# Load data into event table
echo "Loading data into event table..."
EVENT_LOAD_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "COPY event
FROM 's3://redshift-downloads/tickit/allevents_pipe.txt'
DELIMITER '|'
TIMEFORMAT 'YYYY-MM-DD HH:MI:SS'
IGNOREHEADER 1
IAM_ROLE '$ROLE_ARN';" 2>&1)
echo "$EVENT_LOAD_OUTPUT"
check_error "$EVENT_LOAD_OUTPUT" "aws redshift-data execute-statement (load event)"
EVENT_LOAD_QUERY_ID=$(echo "$EVENT_LOAD_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for data loading to complete
echo "Waiting for event data loading to complete..."
sleep 10
# Load data into sales table
echo "Loading data into sales table..."
SALES_LOAD_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "COPY sales
FROM 's3://redshift-downloads/tickit/sales_tab.txt'
DELIMITER '\t'
TIMEFORMAT 'MM/DD/YYYY HH:MI:SS'
IGNOREHEADER 1
IAM_ROLE '$ROLE_ARN';" 2>&1)
echo "$SALES_LOAD_OUTPUT"
check_error "$SALES_LOAD_OUTPUT" "aws redshift-data execute-statement (load sales)"
SALES_LOAD_QUERY_ID=$(echo "$SALES_LOAD_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for data loading to complete
echo "Waiting for sales data loading to complete..."
sleep 30
# Step 6: Run sample queries
echo "Running sample queries..."
# Query 1: Find top 10 buyers by quantity
echo "Running query: Find top 10 buyers by quantity..."
QUERY1_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
FROM sales
GROUP BY buyerid
ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;" 2>&1)
echo "$QUERY1_OUTPUT"
check_error "$QUERY1_OUTPUT" "aws redshift-data execute-statement (query 1)"
QUERY1_ID=$(echo "$QUERY1_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for query to complete
echo "Waiting for query 1 to complete..."
sleep 10
# Get query 1 results
echo "Getting results for query 1..."
QUERY1_STATUS_OUTPUT=$(aws redshift-data describe-statement --id "$QUERY1_ID" 2>&1)
echo "$QUERY1_STATUS_OUTPUT"
check_error "$QUERY1_STATUS_OUTPUT" "aws redshift-data describe-statement (query 1)"
QUERY1_STATUS=$(echo "$QUERY1_STATUS_OUTPUT" | grep -o '"Status": "[^"]*' | cut -d'"' -f4)
if [ "$QUERY1_STATUS" == "FINISHED" ]; then
QUERY1_RESULTS=$(aws redshift-data get-statement-result --id "$QUERY1_ID" 2>&1)
echo "Query 1 Results:"
echo "$QUERY1_RESULTS"
else
echo "Query 1 is not yet complete. Status: $QUERY1_STATUS"
echo "Waiting additional time for query to complete..."
sleep 20
# Check again
QUERY1_STATUS_OUTPUT=$(aws redshift-data describe-statement --id "$QUERY1_ID" 2>&1)
QUERY1_STATUS=$(echo "$QUERY1_STATUS_OUTPUT" | grep -o '"Status": "[^"]*' | cut -d'"' -f4)
if [ "$QUERY1_STATUS" == "FINISHED" ]; then
QUERY1_RESULTS=$(aws redshift-data get-statement-result --id "$QUERY1_ID" 2>&1)
echo "Query 1 Results:"
echo "$QUERY1_RESULTS"
else
echo "Query 1 is still not complete. Status: $QUERY1_STATUS"
fi
fi
# Query 2: Find events in the 99.9 percentile in terms of all time total sales
echo "Running query: Find events in the 99.9 percentile in terms of all time total sales..."
QUERY2_OUTPUT=$(aws redshift-data execute-statement \
--database "$DB_NAME" \
--workgroup-name "$WORKGROUP_NAME" \
--sql "SELECT eventname, total_price
FROM (SELECT eventid, total_price, ntile(1000) over(order by total_price desc) as percentile
FROM (SELECT eventid, sum(pricepaid) total_price
FROM sales
GROUP BY eventid)) Q, event E
WHERE Q.eventid = E.eventid
AND percentile = 1
ORDER BY total_price desc;" 2>&1)
echo "$QUERY2_OUTPUT"
check_error "$QUERY2_OUTPUT" "aws redshift-data execute-statement (query 2)"
QUERY2_ID=$(echo "$QUERY2_OUTPUT" | grep -o '"Id": "[^"]*' | cut -d'"' -f4)
# Wait for query to complete
echo "Waiting for query 2 to complete..."
sleep 10
# Get query 2 results
echo "Getting results for query 2..."
QUERY2_STATUS_OUTPUT=$(aws redshift-data describe-statement --id "$QUERY2_ID" 2>&1)
echo "$QUERY2_STATUS_OUTPUT"
check_error "$QUERY2_STATUS_OUTPUT" "aws redshift-data describe-statement (query 2)"
QUERY2_STATUS=$(echo "$QUERY2_STATUS_OUTPUT" | grep -o '"Status": "[^"]*' | cut -d'"' -f4)
if [ "$QUERY2_STATUS" == "FINISHED" ]; then
QUERY2_RESULTS=$(aws redshift-data get-statement-result --id "$QUERY2_ID" 2>&1)
echo "Query 2 Results:"
echo "$QUERY2_RESULTS"
else
echo "Query 2 is not yet complete. Status: $QUERY2_STATUS"
echo "Waiting additional time for query to complete..."
sleep 20
# Check again
QUERY2_STATUS_OUTPUT=$(aws redshift-data describe-statement --id "$QUERY2_ID" 2>&1)
QUERY2_STATUS=$(echo "$QUERY2_STATUS_OUTPUT" | grep -o '"Status": "[^"]*' | cut -d'"' -f4)
if [ "$QUERY2_STATUS" == "FINISHED" ]; then
QUERY2_RESULTS=$(aws redshift-data get-statement-result --id "$QUERY2_ID" 2>&1)
echo "Query 2 Results:"
echo "$QUERY2_RESULTS"
else
echo "Query 2 is still not complete. Status: $QUERY2_STATUS"
fi
fi
# Summary
echo ""
echo "==========================================="
echo "TUTORIAL SUMMARY"
echo "==========================================="
echo "You have successfully:"
echo "1. Created a Redshift Serverless namespace and workgroup"
echo "2. Created an IAM role with S3 access permissions"
echo "3. Stored admin credentials securely in AWS Secrets Manager"
echo "4. Created tables for sample data"
echo "5. Loaded sample data from Amazon S3"
echo "6. Run sample queries on the data"
echo ""
echo "Redshift Serverless Resources:"
echo "- Namespace: $NAMESPACE_NAME"
echo "- Workgroup: $WORKGROUP_NAME"
echo "- Database: $DB_NAME"
echo "- Endpoint: $WORKGROUP_ENDPOINT"
echo "- Credentials Secret: $SECRET_NAME"
echo ""
echo "To connect to your Redshift Serverless database using SQL tools:"
echo "- Host: $WORKGROUP_ENDPOINT"
echo "- Database: $DB_NAME"
echo "- Username: $ADMIN_USERNAME"
echo "- Password: Retrieve from AWS Secrets Manager secret '$SECRET_NAME'"
echo ""
echo "To retrieve the password from Secrets Manager (without jq):"
echo "aws secretsmanager get-secret-value --secret-id $SECRET_NAME --query 'SecretString' --output text | sed -n 's/.*\"password\":\"\([^\"]*\)\".*/\1/p'"
echo ""
# Clean up temporary files
rm -f redshift-trust-policy.json redshift-s3-policy.json
# Clean up resources
cleanup_resources
echo "Tutorial completed at $(date)"
```
+ For API details, see the following topics in *AWS CLI Command Reference*.
+ [CreateNamespace](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/CreateNamespace)
+ [CreateRole](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/CreateRole)
+ [CreateSecret](https://docs.aws.amazon.com/goto/aws-cli/secretsmanager-2017-10-17/CreateSecret)
+ [CreateWorkgroup](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/CreateWorkgroup)
+ [DeleteNamespace](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/DeleteNamespace)
+ [DeleteRole](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/DeleteRole)
+ [DeleteRolePolicy](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/DeleteRolePolicy)
+ [DeleteSecret](https://docs.aws.amazon.com/goto/aws-cli/secretsmanager-2017-10-17/DeleteSecret)
+ [DeleteWorkgroup](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/DeleteWorkgroup)
+ [GetNamespace](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/GetNamespace)
+ [GetRole](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/GetRole)
+ [GetSecretValue](https://docs.aws.amazon.com/goto/aws-cli/secretsmanager-2017-10-17/GetSecretValue)
+ [GetWorkgroup](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/GetWorkgroup)
+ [PutRolePolicy](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/PutRolePolicy)
+ [UpdateNamespace](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/UpdateNamespace)
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.
# Getting started with Amazon Redshift provisioned clusters
The following code example shows how to:
+ Create a Redshift cluster
+ Create an IAM role for S3 access
+ Create tables and load data
+ Run example queries
+ Clean up resources
------
#### [ Bash ]
**AWS CLI with Bash script**
There's more on GitHub. Find the complete example and learn how to set up and run in the [Sample developer tutorials](https://github.com/aws-samples/sample-developer-tutorials/tree/main/tuts/039-redshift-provisioned) repository.
```
#!/bin/bash
# Amazon Redshift Provisioned Cluster Tutorial Script
# This script creates a Redshift cluster, loads sample data, runs queries, and cleans up resources
# Version 3: Fixed IAM role usage in COPY commands
# Set up logging
LOG_FILE="redshift_tutorial.log"
exec > >(tee -a "$LOG_FILE") 2>&1
echo "Starting Amazon Redshift tutorial script at $(date)"
echo "All commands and outputs will be logged to $LOG_FILE"
# Function to handle errors
handle_error() {
echo "ERROR: $1"
echo "Resources created so far:"
if [ -n "$CLUSTER_ID" ]; then echo "- Redshift Cluster: $CLUSTER_ID"; fi
if [ -n "$ROLE_NAME" ]; then echo "- IAM Role: $ROLE_NAME"; fi
echo "Attempting to clean up resources..."
cleanup_resources
exit 1
}
# Function to clean up resources
cleanup_resources() {
echo "Cleaning up resources..."
# Delete the cluster if it exists
if [ -n "$CLUSTER_ID" ]; then
echo "Deleting Redshift cluster: $CLUSTER_ID"
aws redshift delete-cluster --cluster-identifier "$CLUSTER_ID" --skip-final-cluster-snapshot
echo "Waiting for cluster deletion to complete..."
aws redshift wait cluster-deleted --cluster-identifier "$CLUSTER_ID"
echo "Cluster deleted successfully."
fi
# Delete the IAM role if it exists
if [ -n "$ROLE_NAME" ]; then
echo "Removing IAM role policy..."
aws iam delete-role-policy --role-name "$ROLE_NAME" --policy-name RedshiftS3Access || echo "Failed to delete role policy"
echo "Deleting IAM role: $ROLE_NAME"
aws iam delete-role --role-name "$ROLE_NAME" || echo "Failed to delete role"
fi
echo "Cleanup completed."
}
# Function to wait for SQL statement to complete
wait_for_statement() {
local statement_id=$1
local max_attempts=30
local attempt=1
local status=""
echo "Waiting for statement $statement_id to complete..."
while [ $attempt -le $max_attempts ]; do
status=$(aws redshift-data describe-statement --id "$statement_id" --query 'Status' --output text)
if [ "$status" == "FINISHED" ]; then
echo "Statement completed successfully."
return 0
elif [ "$status" == "FAILED" ]; then
local error=$(aws redshift-data describe-statement --id "$statement_id" --query 'Error' --output text)
echo "Statement failed with error: $error"
return 1
elif [ "$status" == "ABORTED" ]; then
echo "Statement was aborted."
return 1
fi
echo "Statement status: $status. Waiting... (Attempt $attempt/$max_attempts)"
sleep 10
((attempt++))
done
echo "Timed out waiting for statement to complete."
return 1
}
# Function to check if IAM role is attached to cluster
check_role_attached() {
local role_arn=$1
local max_attempts=10
local attempt=1
echo "Checking if IAM role is attached to the cluster..."
while [ $attempt -le $max_attempts ]; do
local status=$(aws redshift describe-clusters \
--cluster-identifier "$CLUSTER_ID" \
--query "Clusters[0].IamRoles[?IamRoleArn=='$role_arn'].ApplyStatus" \
--output text)
if [ "$status" == "in-sync" ]; then
echo "IAM role is successfully attached to the cluster."
return 0
fi
echo "IAM role status: $status. Waiting... (Attempt $attempt/$max_attempts)"
sleep 30
((attempt++))
done
echo "Timed out waiting for IAM role to be attached."
return 1
}
# Variables to track created resources
CLUSTER_ID="examplecluster"
ROLE_NAME="RedshiftS3Role-$(date +%s)"
DB_NAME="dev"
DB_USER="awsuser"
DB_PASSWORD="Changeit1" # In production, use AWS Secrets Manager to generate and store passwords
echo "=== Step 1: Creating Amazon Redshift Cluster ==="
# Create the Redshift cluster
echo "Creating Redshift cluster: $CLUSTER_ID"
CLUSTER_RESULT=$(aws redshift create-cluster \
--cluster-identifier "$CLUSTER_ID" \
--node-type ra3.4xlarge \
--number-of-nodes 2 \
--master-username "$DB_USER" \
--master-user-password "$DB_PASSWORD" \
--db-name "$DB_NAME" \
--port 5439 2>&1)
# Check for errors
if echo "$CLUSTER_RESULT" | grep -i "error"; then
handle_error "Failed to create Redshift cluster: $CLUSTER_RESULT"
fi
echo "$CLUSTER_RESULT"
echo "Waiting for cluster to become available..."
# Wait for the cluster to be available
aws redshift wait cluster-available --cluster-identifier "$CLUSTER_ID" || handle_error "Timeout waiting for cluster to become available"
# Get cluster status to confirm
CLUSTER_STATUS=$(aws redshift describe-clusters \
--cluster-identifier "$CLUSTER_ID" \
--query 'Clusters[0].ClusterStatus' \
--output text)
echo "Cluster status: $CLUSTER_STATUS"
echo "=== Step 2: Creating IAM Role for S3 Access ==="
# Create trust policy file
echo "Creating trust policy for Redshift"
cat > redshift-trust-policy.json << EOF
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "redshift.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
EOF
# Create IAM role
echo "Creating IAM role: $ROLE_NAME"
ROLE_RESULT=$(aws iam create-role \
--role-name "$ROLE_NAME" \
--assume-role-policy-document file://redshift-trust-policy.json 2>&1)
# Check for errors
if echo "$ROLE_RESULT" | grep -i "error"; then
handle_error "Failed to create IAM role: $ROLE_RESULT"
fi
echo "$ROLE_RESULT"
# Get the role ARN
ROLE_ARN=$(aws iam get-role --role-name "$ROLE_NAME" --query 'Role.Arn' --output text)
echo "Role ARN: $ROLE_ARN"
# Create policy document for S3 access
echo "Creating S3 access policy"
cat > redshift-s3-policy.json << EOF
{
"Version":"2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::redshift-downloads",
"arn:aws:s3:::redshift-downloads/*"
]
}
]
}
EOF
# Attach policy to role
echo "Attaching S3 access policy to role"
POLICY_RESULT=$(aws iam put-role-policy \
--role-name "$ROLE_NAME" \
--policy-name RedshiftS3Access \
--policy-document file://redshift-s3-policy.json 2>&1)
# Check for errors
if echo "$POLICY_RESULT" | grep -i "error"; then
handle_error "Failed to attach policy to role: $POLICY_RESULT"
fi
echo "$POLICY_RESULT"
# Attach role to cluster
echo "Attaching IAM role to Redshift cluster"
ATTACH_ROLE_RESULT=$(aws redshift modify-cluster-iam-roles \
--cluster-identifier "$CLUSTER_ID" \
--add-iam-roles "$ROLE_ARN" 2>&1)
# Check for errors
if echo "$ATTACH_ROLE_RESULT" | grep -i "error"; then
handle_error "Failed to attach role to cluster: $ATTACH_ROLE_RESULT"
fi
echo "$ATTACH_ROLE_RESULT"
# Wait for the role to be attached
echo "Waiting for IAM role to be attached to the cluster..."
if ! check_role_attached "$ROLE_ARN"; then
handle_error "Failed to attach IAM role to cluster"
fi
echo "=== Step 3: Getting Cluster Connection Information ==="
# Get cluster endpoint
CLUSTER_INFO=$(aws redshift describe-clusters \
--cluster-identifier "$CLUSTER_ID" \
--query 'Clusters[0].Endpoint.{Address:Address,Port:Port}' \
--output json)
echo "Cluster endpoint information:"
echo "$CLUSTER_INFO"
echo "=== Step 4: Creating Tables and Loading Data ==="
echo "Creating sales table"
SALES_TABLE_ID=$(aws redshift-data execute-statement \
--cluster-identifier "$CLUSTER_ID" \
--database "$DB_NAME" \
--db-user "$DB_USER" \
--sql "DROP TABLE IF EXISTS sales; CREATE TABLE sales(salesid integer not null, listid integer not null distkey, sellerid integer not null, buyerid integer not null, eventid integer not null, dateid smallint not null sortkey, qtysold smallint not null, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp);" \
--query 'Id' --output text)
echo "Sales table creation statement ID: $SALES_TABLE_ID"
# Wait for statement to complete
if ! wait_for_statement "$SALES_TABLE_ID"; then
handle_error "Failed to create sales table"
fi
echo "Creating date table"
DATE_TABLE_ID=$(aws redshift-data execute-statement \
--cluster-identifier "$CLUSTER_ID" \
--database "$DB_NAME" \
--db-user "$DB_USER" \
--sql "DROP TABLE IF EXISTS date; CREATE TABLE date(dateid smallint not null distkey sortkey, caldate date not null, day character(3) not null, week smallint not null, month character(5) not null, qtr character(5) not null, year smallint not null, holiday boolean default('N'));" \
--query 'Id' --output text)
echo "Date table creation statement ID: $DATE_TABLE_ID"
# Wait for statement to complete
if ! wait_for_statement "$DATE_TABLE_ID"; then
handle_error "Failed to create date table"
fi
echo "Loading data into sales table"
SALES_LOAD_ID=$(aws redshift-data execute-statement \
--cluster-identifier "$CLUSTER_ID" \
--database "$DB_NAME" \
--db-user "$DB_USER" \
--sql "COPY sales FROM 's3://redshift-downloads/tickit/sales_tab.txt' DELIMITER '\t' TIMEFORMAT 'MM/DD/YYYY HH:MI:SS' REGION 'us-east-1' IAM_ROLE '$ROLE_ARN';" \
--query 'Id' --output text)
echo "Sales data load statement ID: $SALES_LOAD_ID"
# Wait for statement to complete
if ! wait_for_statement "$SALES_LOAD_ID"; then
handle_error "Failed to load data into sales table"
fi
echo "Loading data into date table"
DATE_LOAD_ID=$(aws redshift-data execute-statement \
--cluster-identifier "$CLUSTER_ID" \
--database "$DB_NAME" \
--db-user "$DB_USER" \
--sql "COPY date FROM 's3://redshift-downloads/tickit/date2008_pipe.txt' DELIMITER '|' REGION 'us-east-1' IAM_ROLE '$ROLE_ARN';" \
--query 'Id' --output text)
echo "Date data load statement ID: $DATE_LOAD_ID"
# Wait for statement to complete
if ! wait_for_statement "$DATE_LOAD_ID"; then
handle_error "Failed to load data into date table"
fi
echo "=== Step 5: Running Example Queries ==="
echo "Running query: Get definition for the sales table"
QUERY1_ID=$(aws redshift-data execute-statement \
--cluster-identifier "$CLUSTER_ID" \
--database "$DB_NAME" \
--db-user "$DB_USER" \
--sql "SELECT * FROM pg_table_def WHERE tablename = 'sales';" \
--query 'Id' --output text)
echo "Query 1 statement ID: $QUERY1_ID"
# Wait for statement to complete
if ! wait_for_statement "$QUERY1_ID"; then
handle_error "Query 1 failed"
fi
# Get and display results
echo "Query 1 results (first 10 rows):"
aws redshift-data get-statement-result --id "$QUERY1_ID" --max-items 10
echo "Running query: Find total sales on a given calendar date"
QUERY2_ID=$(aws redshift-data execute-statement \
--cluster-identifier "$CLUSTER_ID" \
--database "$DB_NAME" \
--db-user "$DB_USER" \
--sql "SELECT sum(qtysold) FROM sales, date WHERE sales.dateid = date.dateid AND caldate = '2008-01-05';" \
--query 'Id' --output text)
echo "Query 2 statement ID: $QUERY2_ID"
# Wait for statement to complete
if ! wait_for_statement "$QUERY2_ID"; then
handle_error "Query 2 failed"
fi
# Get and display results
echo "Query 2 results:"
aws redshift-data get-statement-result --id "$QUERY2_ID"
echo "=== Tutorial Complete ==="
echo "The following resources were created:"
echo "- Redshift Cluster: $CLUSTER_ID"
echo "- IAM Role: $ROLE_NAME"
echo ""
echo "==========================================="
echo "CLEANUP CONFIRMATION"
echo "==========================================="
echo "Do you want to clean up all created resources? (y/n): "
read -r CLEANUP_CHOICE
if [[ "$CLEANUP_CHOICE" =~ ^[Yy] ]]; then
cleanup_resources
echo "All resources have been cleaned up."
else
echo "Resources were not cleaned up. You can manually delete them later."
echo "To avoid incurring charges, remember to delete the following resources:"
echo "- Redshift Cluster: $CLUSTER_ID"
echo "- IAM Role: $ROLE_NAME"
fi
echo "Script completed at $(date)"
```
+ For API details, see the following topics in *AWS CLI Command Reference*.
+ [CreateCluster](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/CreateCluster)
+ [CreateRole](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/CreateRole)
+ [DeleteCluster](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/DeleteCluster)
+ [DeleteRole](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/DeleteRole)
+ [DeleteRolePolicy](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/DeleteRolePolicy)
+ [DescribeClusters](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/DescribeClusters)
+ [GetRole](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/GetRole)
+ [ModifyClusterIamRoles](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/ModifyClusterIamRoles)
+ [PutRolePolicy](https://docs.aws.amazon.com/goto/aws-cli/iam-2010-05-08/PutRolePolicy)
+ [Wait](https://docs.aws.amazon.com/goto/aws-cli/redshift-2012-12-01/Wait)
------
For a complete list of AWS SDK developer guides and code examples, see [Using this service with an AWS SDK](sdk-general-information-section.md). This topic also includes information about getting started and details about previous SDK versions.