Skill3: Experiment Build and query a data warehouse in Amazon Redshift
Amazon Redshift
Amazon Redshift is a fully managed data warehouse solution from
AWS that allows to store and query large volumes of analytical data.
With AWS Redshift Serverless, users can access and analyze data
without the need for manual configuration of a provisioned data
warehouse.
Objectives
1. Set up an Amazon Redshift cluster.
2. Load structured data into Redshift from Amazon S3.
3. Execute SQL queries to analyze the data.
4. Optimize query performances.
Procedure
Step 1: CREATE IAM ROLE
1. Sign in to AWS Management Console and go to the IAM console.
Click on Roles from the right hand side panel and then click
on Create Role button,
2. In the Use cases for other AWS services select Redshift from the
drop down list. Then click on the Redshift — Customizable radio button
and then click on Next.
3. In the Add permission section, search
for AmazonRedshiftAllCommandsFullAccess policy. Its an AWS
Managed policy so it will be a part of each AWS account. Select this policy
and click on Next
4. On the next page just provide Redshift-IAM-Role-1 as the name for
the IAM role and then click on Create Role button.
Step2: CREATE S3 BCUKET TO SAVE CSV DATA FILES
1. Open the S3 console on your AWS Account and then click on Create
Bucket button and fill in the following details for the same
Bucket Name — aws-redshift-raw-csv
Keep all the other options default and just click on Create Bucket.
2. Once the S3 bucket is created, then click on the bucket and then
create a folder inside the bucket named as nyc-dataset and click
on the Create Folder button.
3. Once the folder is created then click on the folder and upload
the [Link] file.
Step 3: Set Up an Amazon Redshift Cluster
o Navigate to the Amazon Redshift service.
2. Create a Redshift Cluster
o Click on "Create Cluster".
Please fill in the following configurations details on this page
Cluster identifier — redshift-cluster-1
What are you planning to use this cluster for? — Select Production
Node Type — [Link]
Number of Nodes — 1
Admin user name — test-user
Admin user password — Password1234
3. In the Associated IAM roles section, click on Associated IAM
role button and then select the IAM role which created
previously Redshift-IAM-Role-1 and then click on Associated IAM role
button.
4. Keep the Additional Configurations section default and do change
anything over there and then just click on Create Cluster button
5. Cluster will take couple of minutes to get deployed. Have Patience for a
while. Once the cluster is deployed, on the console you will
see Status changed to Available (as shown below)
Step4: CREATE TABLE & LOAD DATA ON REDSHIFT CLUSTER
1. Before you start creating tables, connect to the Redshift cluster
using Query Editor V2. For that click on the Query Editor V2 in the
left hand side panel on the Redshift console.
2. This will open a new tab and will see the Redshift query editor
v2. On this editor you will see the redshift cluster which we created
in the right-hand side Database section.
3. Click on the redshift cluster name and it will automatically connect
to you redshift cluster. If it does not connect automatically, then it
will ask for user name and password for connection. Please enter
the username and password which we used while creating the
cluster
4. Run the following query to Create the table taxi_rides
Create Table [Link].taxi_rides (
id VARCHAR(10),
vendor_id INTEGER,
pickup_datetime TIMESTAMP,
dropoff_datetime TIMESTAMP,
passenger_count INTEGER,
pickup_longitude Decimal(9,6),
pickup_latitude Decimal(9,6),
dropoff_longitude Decimal(9,6),
dropoff_latitude Decimal(9,6),
store_and_fwd_flag VARCHAR(1),
trip_duration INTEGER,
primary key(id))
DISTSTYLE KEY
DISTKEY (id);
5. Run the following COPY command after successful creation of the table.
Make sure that you update the following COPY command with you S3
Bucket name and with the AWS account ID as highlighted below
COPY [Link].taxi_rides
from 's3://aws-redshift-raw-csv/nyc-dataset/[Link]'
iam_role 'arn:aws:iam::284377223973:role/Redshift-IAM-Role-1'
FORMAT AS CSV
IGNOREHEADER 1 ;
6. After the COPY command is successfully executed, just the check the
sample table records if they are properly inserted into the table using a
Select * query
Step 5: Clean Up Resources
1. Delete Tables (if needed)
2. Delete the Redshift Cluster
o Go to Amazon Redshift Console → Select your Cluster →
Delete Cluster.
o Choose "Take Final Snapshot" (optional) or skip snapshot
to delete immediately.