Redshift Overview
Amazon Redshift is a fully managed, petabyte-scale data warehouse service designed specifically for analytical workloads. At its core, it’s an ANSI SQL-compatible relational database, but it’s engineered for high performance and scalability when dealing with vast amounts of data for business intelligence and reporting.
Key Characteristics and Purpose:
- Data Warehouse Service: Redshift is optimized for analytical processing (OLAP - Online Analytical Processing), contrasting with transactional databases (OLTP - Online Transaction Processing) that handle high volumes of small, frequent read/write operations.
- ANSI SQL Compatible: It supports standard SQL commands, allowing you to use existing SQL-based tools and business intelligence (BI) applications to load and query data. Examples include Redshift Query Editor v2 and various third-party BI tools.
- Fast Performance for Large Data: Designed to handle massive datasets (petabytes) with very high query performance and scalability.
- Flexible Data Ingestion: Supports loading data in various formats, including common ones like CSV and JSON, as well as columnar formats like Parquet and ORC (Optimized Row Columnar).
Architectural Optimizations for Analytics. Redshift achieves its high analytical performance through several key architectural features:
- Columnar Storage:
- Unlike traditional row-oriented databases (common in OLTP), Redshift stores data in a columnar format.
- Benefit: In analytical queries, you often select and aggregate data from only a few columns. Columnar storage significantly reduces the amount of I/O operations because the system only reads the necessary columns from disk, rather than entire rows. This drastically speeds up analytical queries and improves data compression ratios.
- Example: If you have a table with 100 columns but your query only needs
sales_amountandtransaction_date, Redshift only reads those two columns, ignoring the other 98.
- Massive Parallel Processing (MPP):
- Redshift employs an MPP architecture. This means data and query processing are distributed across multiple nodes within a cluster.
- Benefit: Queries are broken down into smaller pieces and executed in parallel across these multiple nodes, enabling extremely fast query execution for large and complex analytical tasks.
- Scalability through Clusters:
- Redshift’s architecture is built around clusters of computing nodes. You can easily resize these clusters (add or remove nodes) as your data volume and performance requirements change, providing elastic scalability.
Additional Features:
- Advanced Compression: Redshift applies advanced compression techniques to data stored in its columnar format, which not only reduces storage footprint but also further enhances query performance by reducing the amount of data that needs to be read from disk.
- Integration with AWS Services: Seamlessly integrates with other AWS services to build comprehensive data pipelines:
- Amazon S3: For data ingestion from data lakes.
- Amazon DynamoDB: For data ingestion.
- AWS Glue: For data cataloging and ETL (Extract, Transform, Load) processes.
- AWS Lambda: For event-driven data processing.
- Security: Includes built-in security features such as:
- Encryption at rest and in transit.
- Database-level object access control.
- Fine-grained access control using IAM roles and policies.
- Integration with VPC security groups for network isolation.
Typical Use Cases. Amazon Redshift is ideal for any use case requiring high-performance analysis of large datasets:
- Data Warehousing: Its core purpose, serving as the central repository for consolidated analytical data.
- Business Intelligence (BI): Powering BI dashboards and reports from tools like Tableau, Power BI, or Amazon QuickSight.
- Log Analysis: Analyzing vast volumes of log data for operational insights or security auditing.
- IoT Data Processing: Storing and analyzing time-series and event data from IoT devices.
- Real-time Data Dashboards: Creating dashboards that require near real-time updates and complex aggregations.
- Customer Analytics: Understanding customer behavior, trends, and segmentation.
Clusters & Nodes
The core infrastructure component of Amazon Redshift is the cluster. A cluster is responsible for executing your analytical workloads, running the Redshift engine, and containing one or more databases. The performance of your Redshift solution is directly tied to how you configure and scale your clusters.
Cluster Characteristics:
- Workload Execution: Clusters are the primary computational units that perform data storage, query processing, and management tasks.
- High Availability & Durability: Redshift clusters are designed with replication and continuous backups to ensure high availability and durability of your data.
- Automatic Recovery: Individual nodes within a cluster can automatically recover from component failures, with Redshift replacing failed components transparently.
Cluster Architecture: Leader Node and Compute Nodes.
/attachments/Pasted-image-20250617221726.png)
A Redshift cluster is typically composed of two main types of nodes that work together:
- Leader Node:
- Orchestration and Coordination: The Leader Node coordinates the entire cluster. It communicates with client applications (via ODBC or JDBC connections).
- Query Planning: When a query is submitted, the Leader Node develops an execution plan. This plan outlines the series of steps necessary to retrieve the results, especially for complex analytical queries.
- Code Distribution: Based on the execution plan, the Leader Node compiles the necessary code and distributes this compiled code to the Compute Nodes.
- Data Assignment: It assigns a specific portion of the data to each Compute Node for processing.
- Result Aggregation: Once Compute Nodes finish their assigned tasks, the Leader Node aggregates their individual results before sending the final aggregated result back to the client application.
- Provisioning: A Leader Node is typically provisioned automatically when you have two or more Compute Nodes in your cluster.
- Exclusive Leader Node Functions: It’s important to note that certain SQL functions in Redshift are designed to run exclusively on the Leader Node. If a query uses any of these functions and also references a table stored on the Compute Nodes, it will return an error. These queries must operate only on data accessible by the Leader Node.
- Compute Nodes:
- Data Storage and Query Execution: Compute Nodes are the workhorses of the cluster. They store the actual data and execute the query execution plans instructed by the Leader Node.
- Dedicated Resources: Each Compute Node has its own dedicated CPU, memory, and disk storage resources.
- Parallel Processing: They transmit data among themselves to serve queries, contributing to Redshift’s Massive Parallel Processing (MPP) capabilities.
- Scalability: You can increase or decrease the compute capacity of your cluster by either:
- Increasing the number of Compute Nodes.
- Changing (upgrading) the node types (which provide different compute, memory, and storage configurations).
- A combination of both.
Redshift Deployment Options. Redshift offers two main deployment options based on your workload characteristics:
- Amazon Redshift Serverless:
- This option allows your data warehouse to scale compute capacity automatically based on demand.
- It’s ideal for unpredictable or fluctuating workloads, as you don’t need to manually provision or manage clusters. You pay only for the compute consumed.
- Provisioned Clusters:
- With this option, you manually provision and configure your cluster (number and type of nodes).
- It provides more granular control over configuration and can be more cost-effective for predictable or steady-state workloads where consistent capacity is known in advance.
Understanding the interaction between the Leader Node and Compute Nodes, along with the available deployment options, is crucial for designing and optimizing your Redshift data warehouse.
Create Redshift Cluster (Hands-on)
This hands-on guide walks you through the process of creating a Redshift cluster with specific settings and highlights key considerations for cost and management.
Important Note on Cost: Creating a Redshift cluster will incur costs. Follow the instructions carefully, especially regarding node type and number of nodes, to minimize expenses. If you are uncomfortable with potential charges, simply follow along without performing the steps yourself. Remember to delete the cluster at the end of the hands-on section to stop incurring costs.
1. Navigate to Amazon Redshift Console:
- Log in to the AWS Management Console.
- Search for “Redshift” and select the service.
- You’ll likely see a prominent promotion for Redshift Serverless. For this exercise, we will provision a cluster.
2. Create a Provisioned Redshift Cluster:
- In the left navigation pane, under “Redshift” → “Analytics,” select “Clusters.”
- Click “Create cluster.”
- Cluster configuration:
- Cluster identifier: Enter a unique name (e.g.,
redshift-training-cluster-1). - Node type: This is crucial for cost.
- Under “Production,” select the DC2 instance family.
- Choose the smallest available node type,
dc2.large. This is a “dense compute” instance, and the.largeversion is the most cost-effective for a single node setup.
- Number of nodes: Set this to
1. This will show you the estimated hourly cost (e.g., $0.25 per hour). - Load sample data: Check “Load sample data.” This provides pre-populated tables for easy querying and demonstration.
- Cluster identifier: Enter a unique name (e.g.,
- Database configurations:
- Admin username: Leave as
awsuser(default). - Admin password: Manually enter a strong password and remember it. You will need this to connect to the database.
- Confirm password: Re-enter the password.
- Admin username: Leave as
- Click “Create cluster.”
3. Monitor Cluster Creation:
- You will be redirected to the “Clusters” overview page.
- Your cluster’s status will initially be “Creating.”
- This process can take several minutes (e.g., 5-10 minutes) as AWS provisions and initializes the cluster resources. Refresh the page periodically to check its status.
- Important: Do not proceed to the next step until the cluster status changes to “Available.”
This completes the initial creation of your Redshift cluster. In subsequent hands-on sections, you will learn how to connect to this cluster, query the sample data, and perform other Redshift operations. Remember to be mindful of the cost while the cluster is running.
Access Redshift Cluster & Query Editor (Hands-on)
This hands-on guide will walk you through accessing your Amazon Redshift cluster and using the Query Editor v2 to explore the loaded sample data.
1. Access the Redshift Cluster Dashboard:
- Go to the AWS Management Console and navigate to the Redshift service.
- In the left navigation pane, under “Analytics,” click “Clusters.”
- You should see your
redshift-training-cluster-1with a status of “Available.” - Click on the cluster identifier (
redshift-training-cluster-1) to view its details.- Here, you can see information like node type, number of nodes, and various endpoints for connection (JDBC/ODBC).
- Note the “Actions” menu, where you can perform various cluster operations, including pausing and deleting the cluster.
2. Access Redshift Query Editor v2:
- From the cluster details page, locate the “Query data” button (usually in the top right corner or under “Actions”).
- Click “Query data.” This will open the Redshift Query Editor v2 in a new browser tab.
3. Connect to Your Redshift Cluster:
- In the Query Editor v2 interface, on the left-hand “Explorer” pane, you should see your cluster listed.
- To connect, you’ll need to provide credentials.
- Click on your cluster name (e.g.,
redshift-training-cluster-1). - Connection Method: Choose “Connect using database user name and password.”
- Database username: Enter
awsuser(the admin username you set during cluster creation). - Database password: Enter the password you set during cluster creation.
- Click “Create connection.”
4. Explore Sample Data:
- Once connected, the “Explorer” pane will expand to show your database.
- Expand the database (it might be named
devor similar if you left defaults). - Expand “Schemas.” You should see a
publicschema. - Expand
public. You will see several sample tables (e.g.,users,events,sales,listing,category,venue,date). These were loaded because you checked “Load sample data” during cluster creation. - View Table Definition:
- Right-click on one of the sample tables (e.g.,
users). - Select “Show table definition.”
- This will open a new query tab with the
CREATE TABLEstatement, showing the table’s schema.
- Right-click on one of the sample tables (e.g.,
- Query a Table:
- Right-click on a sample table (e.g.,
users) again. - Select “Select table.”
- This will populate the query editor with a
SELECT * FROM public.users LIMIT 10;query. - Click the “Run” button (or press
Ctrl + Enter) to execute the query. - The results will appear in the “Results” pane below the query editor.
- Right-click on a sample table (e.g.,
5. (Optional) Pause Your Cluster to Save Costs:
- After exploring the data, if you want to avoid continuous charges and plan to resume your work later:
- Go back to the Redshift Clusters overview page.
- Select your
redshift-training-cluster-1. - Click “Actions” → “Pause.”
- Confirm the pause. The cluster status will change to “Pausing” and then “Paused.”
Important: Pausing the cluster stops compute billing but retains storage. To completely stop all charges, you must delete the cluster. This will be covered in a later hands-on section.
You have now successfully accessed your Redshift cluster and run a basic query using the Redshift Query Editor v2.
Node Types & Storage
In the Redshift architecture, clusters are built from compute nodes, which can be complemented by a leader node for coordination if there are two or more compute nodes. When considering storage and node types, Redshift offers different options optimized for various workloads and scalability needs.
Redshift Managed Storage (RMS)
Redshift Managed Storage (RMS) is a storage architecture primarily used with Ra3 nodes. It offers a hybrid approach to data storage:
- Components: It combines large, high-performance local SSDs for caching frequently accessed data (hot data) with Amazon S3 for more long-term, cost-effective storage of less frequently accessed data (cold data).
- Automatic Data Tiering: As a node’s data footprint grows beyond the local SSD capacity, RMS automatically and transparently offloads data to S3 based on its “temperature.” Hot data remains on the high-performance SSDs, while cold data is moved to S3.
- Consistent Pricing: You pay a single, consistent rate for the data stored, regardless of whether it resides on the local SSDs or in S3. This simplifies cost management and optimizes performance by keeping the most critical data readily accessible.
Redshift Node Types
Redshift offers two primary node types, each with distinct characteristics for compute, storage, and deployment flexibility:
- Ra3 Nodes:
- Uses Redshift Managed Storage (RMS): Ra3 nodes leverage RMS for their storage.
- Decoupled Compute and Storage: This is the key advantage of Ra3 nodes. Compute (CPU, memory) and storage (data capacity) are decoupled and can be scaled independently. This means you can add more storage capacity without necessarily increasing compute power, and vice versa. This offers greater flexibility and cost optimization.
- Multi-Availability Zone (Multi-AZ) Deployment: Ra3 nodes support multi-AZ deployments. You can deploy cluster nodes across different Availability Zones within a region, significantly enhancing high availability and fault tolerance. This capability is not available with DC2 nodes.
- DC2 Nodes (Dense Compute):
- Local SSD Storage: DC2 nodes include local SSD storage that is tightly coupled with the compute resources within the node. The storage is directly attached and integrated into the node itself.
- Coupled Compute and Storage: With DC2 nodes, compute and storage are not decoupled. If your data grows and you need more storage capacity, you must add more DC2 compute nodes, which also increases your compute power whether you need it or not.
- Single Availability Zone Deployment: DC2 nodes are deployed within a single Availability Zone. They cannot be used in a Redshift cluster where nodes are spread across multiple AZs.
- Workload Recommendation: DC2 nodes are recommended for datasets under 1 TB (considering compressed storage) and for workloads that are highly compute-intensive, where the performance benefits of local SSDs are paramount.
The choice between Ra3 and DC2 nodes depends on your specific workload, data growth patterns, cost considerations, and availability requirements. Ra3 offers more flexibility and higher availability options, while DC2 might be suitable for smaller, highly compute-intensive datasets.