Amazon Redshift

Hands-On

Demo

In this demo, we will: 

  1. Create an Amazon Redshift cluster
  2. Configure network access and security
  3. Connect to the cluster using Query Editor
  4. Load sample data into Redshift
  5. Perform basic queries and analyze query performance
  6. Copy Command
  7. Clean up resources

Agenda

Create S3 Bucket

and Upload Sample Data

redshift-demo-bucket-468024
sale_id,customer_id,product_id,sale_date,quantity
6,3,1,2023-01-20,2
7,1,2,2023-01-21,1
8,2,3,2023-01-22,3

sales_data.csv

Upload sales_data.csv 

Create Redshift Cluster

Create cluster

redshift-demo-cluster

Load sample data

awsuser
Awsuser123

Database configurations

Database encryption

Cluster permissions

Network and security

Network and security

Create cluster subnet group

cluster-subnet-group-1
cluster-subnet-group-1

Add subnets

Publicly accessible

Database configurations

demodb
5439

Maintenance

Monitoring

Backup

Connect

Connect to Redshift clusters

demodb
awsuser
Awsuser123

Load tickit Sample Data

Load Sample Data

Run Sample Query for tickit database

Load your own data

Create Table

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    signup_date DATE
);

Table Created

INSERT INTO users (user_id, username, email, signup_date)
VALUES 
(1, 'john_doe', 'john@example.com', '2023-01-15'),
(2, 'jane_smith', 'jane@example.com', '2023-02-20'),
(3, 'bob_johnson', 'bob@example.com', '2023-03-10'),
(4, 'alice_brown', 'alice@example.com', '2023-04-05'),
(5, 'charlie_davis', 'charlie@example.com', '2023-05-12');

Insert Query

SELECT * FROM users;

Select Query

SELECT DATE_TRUNC('month', signup_date) AS month, COUNT(*) AS user_count
FROM users
GROUP BY DATE_TRUNC('month', signup_date)
ORDER BY month;

1. The DATE_TRUNC('month', signup_date) function is used to group dates by month.

2. The COUNT(*) function is used to count the number of users for each month.

Aggregation Query

SELECT 
    TO_CHAR(DATE_TRUNC('month', signup_date), 'Month') AS month,
    COUNT(*) AS user_count
FROM users
GROUP BY DATE_TRUNC('month', signup_date)
ORDER BY DATE_TRUNC('month', signup_date);
  1. This query shows how many new users signed up each month.
  2. It takes all the sign-up dates, groups them by month, and counts how many users joined in each month.
  3. The results are ordered from earliest to latest, giving you a clear view of your user growth over time.
  4. It's a simple way to see if your sign-ups are increasing, decreasing, or staying steady month by month, without having to sift through individual user records.

Copy Command

Copy S3 Data Directly to Redshift Table

CREATE TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    sale_date DATE,
    quantity INTEGER
);
COPY sales FROM 's3://redshift-demo-bucket-468024/sales_data.csv'
IAM_ROLE 'arn:aws:iam::651623850282:role/service-role/AmazonRedshift-CommandsAccessRole-20241020T104609'
region 'us-east-1'
CSV
IGNOREHEADER 1;

copy command

select * from sales;

Select query

Connect from Local Machine

Edit inbound rules to Access from local machine

Connecting from local DB Client

Clean Up

Delete Redshift Cluster  

delete

Delete Role

Delete S3 Bucket

🙏

Thanks

for

Watching