Amazon Redshift is a fast, scalable, secure, and fully managed cloud data warehouse that allows you to analyze your data at scale. Amazon Redshift now enables you to programmatically access Amazon Redshift Advisor recommendations via an API, allowing you to integrate suggestions for improving the performance of your provisioned cluster into your own applications.
Amazon Redshift Advisor provides recommendations to optimize the performance of your Redshift cluster and help you save on operating costs. Advisor develops its recommendations by analyzing performance and usage metrics for your cluster and displays suggestions that should significantly impact performance and operations. With the ability to programmatically access these recommendations through the ListRecommendations API, you can make these recommendations available to implement on-demand or automatically via your internal applications and tools without needing to access the Amazon Redshift console.
This article shows you how to use the ListRecommendations API to set up email notifications for Advisor recommendations on your Redshift cluster. These recommendations, such as identifying tables that should be vacuumed to sort the data or finding table columns that are candidates for compression, can help improve performance and save costs.
How to Access Redshift Advisor Recommendations
To access Advisor recommendations on the Amazon Redshift console, choose Advisor in the navigation pane. You can expand each recommendation to see more details and sort and group recommendations.
You can also use the ListRecommendations API to automate receiving Advisor recommendations and programmatically implement them. The API returns a list of recommended actions that can be parsed and implemented. The API and SDKs also enable you to set up workflows to use Advisor programmatically for automated optimizations. These automated periodic checks of Advisor using cron scheduling along with implementing the changes can help you keep Redshift clusters optimized automatically without manual intervention.
You can also use the list-recommendations command in the AWS Command Line Interface (AWS CLI) to invoke the Advisor recommendations from the command line and automate the workflow through scripts.
Solution Overview
The following diagram illustrates the solution architecture:
The solution workflow consists of the following steps:
- An Amazon EventBridge schedule invokes an AWS Lambda function to retrieve Advisor recommendations.
- Advisor generates recommendations that are accessible through an API.
- Optionally, this solution stores the recommendations in an Amazon Simple Storage Service (Amazon S3) bucket.
- Amazon Simple Notification Service (Amazon SNS) automatically sends notifications to end-users.
Prerequisites
To deploy this solution, you should have the following:
- An AWS account
- A Redshift provisioned cluster
- An SNS topic with an email subscription
- Administrator access to launch the AWS CloudFormation stack
- Optionally, an S3 bucket
Deploy the Solution
Complete the following steps to deploy the solution:
- Choose Launch Stack.
- For Stack name, enter a name for the stack, for example, blog-redshift-advisor-recommendations.
- For SnsTopicArn, enter the SNS topic Amazon Resource Name (ARN) for receiving the email alerts.
- For ClusterIdentifier, enter your Redshift cluster name if you want to receive Advisor notifications for a particular cluster. If you leave it blank, you will receive notifications for all Redshift provisioned clusters in your account.
- For S3Bucket, enter the S3 bucket name to store the detailed Advisor recommendations in a JSON file. If you leave it blank, this step will be skipped.
- For ScheduleExpression, enter the frequency in cron format to receive Advisor recommendation alerts. For this post, we want to receive alerts every Sunday at 14:00 UTC, so we enter *cron(0 14 ? * SUN ).
Make sure to provide the correct cron time expression when deploying the CloudFormation stack to avoid any failures.
Keep all options as default under Configure Stack options and choose Next. Review the settings, select the acknowledge check box, and create the stack. If the CloudFormation stack fails for any reason, refer to Troubleshooting CloudFormation.
Workflow Details
Let’s take a closer look at the Lambda function and the complete workflow:
The input values provided for SnsTopicArn, ClusterIdentifier, and S3Bucket in the CloudFormation stack creation are set as environmental variables in the Lambda function. If the ClusterIdentifier parameter is None, it will invoke the ListRecommendations API to generate Advisor recommendations for all the clusters within the account (same AWS Region). Otherwise, it will pass the ClusterIdentifier value and generate Advisor recommendations only for the given cluster. If the input parameter S3Bucket is provided, the solution creates a folder named RedshiftAdvisorRecommendations and generates the Advisor recommendations file in JSON format within it. If a value for S3Bucket isn’t provided, this step will be skipped.
Next, the function will summarize recommendations by each provisioned cluster (for all clusters in the account or a single cluster, depending on your settings) based on the impact on performance and cost as HIGH, MEDIUM, and LOW categories. An SNS notification email will be sent to the subscribers with the summarized recommendations.
SQL commands are included as part of the Advisor’s recommended action. RecommendedActionType-SQL summarizes the number of SQL actions that can be applied using SQL commands.
If there are no recommendations available for any cluster, the SNS notification email will be sent notifying there are no Advisor recommendations.
An EventBridge rule is created to invoke the Lambda function based on the frequency you provided in the stack parameters. By default, it’s scheduled to run weekly each Sunday at 14:00 UTC.
Clean Up
We recommend deleting the CloudFormation stack if you aren’t going to continue using the solution. This will avoid incurring any additional costs from the resources created as part of the solution.
Conclusion
In this article, we discussed how Redshift Advisor offers you specific recommendations to improve the performance of and decrease the operating costs for your Redshift cluster. We also showed you how to programmatically access these recommendations through an API and implement them on-demand or automatically using your internal tools without having access to the Amazon Redshift console.
By integrating these recommendations into your workflows, you can make informed decisions and implement best practices to optimize the performance and costs of your Redshift clusters, ultimately enhancing the overall efficiency and productivity of your data processing operations.
We encourage you to try out this automated solution to access Advisor recommendations programmatically. If you have any feedback or questions, please leave them in the comments.
