Amazon Redshift Cookbook

Shruti Worlikar, Thiyagarajan Arumugam, Harshida Patel, Eugene Kawamoto

• Publisher: Packt Publishing
• Year: 2021
• ISBN:
  9781800561847
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Modelling & Design
• Index: Computer Science

Discover how to build a cloud-based data warehouse at petabyte-scale that is burstable and built to scale for end-to-end analytical solutionsKey Features• Discover how to translate familiar data warehousing concepts into Redshift implementation• Use impressive Redshift features to optimize development, productionizing, and operations processes• Find out how to use advanced features such as concurrency scaling, Redshift Spectrum, and federated queriesBook DescriptionAmazon Redshift is a fully managed, petabyte-scale AWS cloud data warehousing service. It enables you to build new data warehouse workloads on AWS and migrate on-premises traditional data warehousing platforms to Redshift. This book on Amazon Redshift starts by focusing on Redshift architecture, showing you how to perform database administration tasks on Redshift. You'll then learn how to optimize your data warehouse to quickly execute complex analytic queries against very large datasets. Because of the massive amount of data involved in data warehousing, designing your database for analytical processing lets you take full advantage of Redshift's columnar architecture and managed services. As you advance, you'll discover how to deploy fully automated and highly scalable extract, transform, and load (ETL) processes, which help minimize the operational efforts that you have to invest in managing regular ETL pipelines and ensure the timely and accurate refreshing of your data warehouse. Finally, you'll gain a clear understanding of Redshift use cases, data ingestion, data management, security, and scaling so that you can build a scalable data warehouse platform. By the end of this Redshift book, you'll be able to implement a Redshift-based data analytics solution and have understood the best practice solutions to commonly faced problems.What you will learn• Use Amazon Redshift to build petabyte-scale data warehouses that are agile at scale• Integrate your data warehousing solution with a data lake using purpose-built features and services on AWS• Build end-to-end analytical solutions from data sourcing to consumption with the help of useful recipes• Leverage Redshift's comprehensive security capabilities to meet the most demanding business requirements• Focus on architectural insights and rationale when using analytical recipes• Discover best practices for working with big data to operate a fully managed solutionWho this book is forThis book is for anyone involved in architecting, implementing, and optimizing an Amazon Redshift data warehouse, such as data warehouse developers, data analysts, database administrators, data engineers, and data scientists. Basic knowledge of data warehousing, database systems, and cloud concepts and familiarity with Redshift will be beneficial.

Chapter 1: Getting Started with Amazon Redshift

Amazon Redshift is a fully managed data warehouse service in Amazon Web Services (AWS). You can query all your data, which can scale from gigabytes to petabytes, using SQL. Amazon Redshift integrates into the data lake solution though the lake house architecture, allowing you access all the structured and semi-structured data in one place. Each Amazon Redshift data warehouse is hosted as a cluster (a group of servers or nodes) that consists of one leader node and a collection of one or more compute nodes. Each cluster is a single tenant environment (which can be scaled to a multi-tenant architecture using data sharing), and every node has its own dedicated CPU, memory, and attached disk storage that varies based on the node's type.

This chapter will walk you through the process of creating a sample Amazon Redshift cluster and connecting to it from different clients.

The following recipes will be discussed in this chapter:

• Creating an Amazon Redshift cluster using the AWS console
• Creating an Amazon Redshift cluster using the AWS CLI
• Creating an Amazon Redshift cluster using an AWS CloudFormation template
• Connecting to an Amazon Redshift cluster using the Query Editor
• Connecting to an Amazon Redshift cluster using the SQL Workbench/J client
• Connecting to an Amazon Redshift cluster using a Jupyter Notebook
• Connecting to an Amazon Redshift cluster programmatically using Python
• Connecting to an Amazon Redshift cluster programmatically using Java
• Connecting to an Amazon Redshift cluster programmatically using .NET
• Connecting to an Amazon Redshift cluster using the command line (psql)

Technical requirements

The following are the technical requirements for this chapter:

• An AWS account.
• An AWS administrator should create an IAM user by following Recipe 1 – Creating an IAM user in the Appendix. This IAM user will be used to execute all the recipes in this chapter.
• An AWS administrator should deploy the AWS CloudFormation template to attach the IAM policy to the IAM user, which will give them access to Amazon Redshift, Amazon SageMaker, Amazon EC2, AWS CloudFormation, and AWS Secrets Manager. The template is available here: https://github.com/PacktPublishing/Amazon-Redshift-Cookbook/blob/master/Chapter01/chapter_1_CFN.yaml.
• Client tools such as SQL Workbench/J, an IDE, and a command-line tool.
• You will need to authorize network access from servers or clients to access the Amazon Redshift cluster: https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-authorize-cluster-access.html.
• The code files for this chapter can be found here: https://github.com/PacktPublishing/Amazon-Redshift-Cookbook/tree/master/Chapter01.

Creating an Amazon Redshift cluster using the AWS Console

The AWS Management Console allows you to interactively create an Amazon Redshift cluster via a browser-based user interface. It also recommends the right cluster configuration based on the size of your workload. Once the cluster has been created, you can use the Console to monitor the health of the cluster and diagnose query performance issues from a unified dashboard.

Getting ready

To complete this recipe, you will need the following:

• A new or existing AWS Account. If new AWS accounts need to be created, go to https://portal.aws.amazon.com/billing/signup, enter the necessary information, and follow the steps on the site.
• An IAM user with access to Amazon Redshift.

How to do it…

Follow these steps to create a cluster with minimal parameters:

1. Navigate to the AWS Management Console and select Amazon Redshift: https://console.aws.amazon.com/redshiftv2/.
2. Choose the AWS region (eu-west-1) or corresponding region from the top-right of the screen. Then, click Next.
3. On the Amazon Redshift Dashboard, select CLUSTERS, and then click Create cluster.
4. In the Cluster configuration section, type in any meaningful Cluster identifier, such as myredshiftcluster.
5. Choose either Production or Free trial, depending on what you plan to use this cluster for.
6. Select the Help me choose option for sizing your cluster for the steady state workload. Alternatively, if you know the required size of your cluster (that is, the node type and number of nodes), select I'll choose. For example, you can choose Node type: dc2.large with Nodes: 2.
7. In the Database configurations section, specify values for Database name (optional), Database port (optional...