In this lab, you will load data from your choice of S3 bucket, or DyanmoDB table into a running Redshift cluster. You will need to create an IAM role that provides access to the S3 bucket or DynamoDB table and apply it to the Redshift cluster. Some experience with either S3 or DynamoDB will be useful, but we walk through loading data into each of these services.
Learning Objectives
Successfully complete this lab by achieving the following learning objectives:
- Prepare the Source Data
- Visit this lab’s content repo and download the redshift-data.csv file to your local machine.
- Log in to the AWS Management Console with the credentials provided, search and select the S3 service.
- Click create, and name it redshift-import20200923. Select Create once again.
- After creation, click the bucket’s name.
- Select the Upload and Add files buttons and choose the redshift-data.csv file that you downloaded in step 1. Afterward, select Upload.
- Click on Services to find and select the DynamoDB service.
Select Create table:
- Table name: redshift-import
- Primary key: ID (select Number in the drop-down menu)
- Click Create
- Generate an Access Key by clicking on your username, and choose My Security Credentials.
- Under Access key, choose Create Access Key and copy the Access key ID and Secret access key. Keep this, you will need it momentarily.
- Switch to the bastion host’s (cloud server) terminal and download this lab’s content repository.
git clone https://github.com/linuxacademy/content-aws-database-specialty.gitcd content-aws-database-specialty/S06_Additional Database Services/ Configure the AWS CLI client and paste in the Access key ID and Secret access key:
aws configure- Choose the Default region name: us-east-1 and leave the Default output format to None by pressing the return key.
Load the .json into the DynamoDB table:
aws dynamodb batch-write-item --request-items file://redshift-data.json"UnprocessedItems"will appear if successful.- Return to the AWS management console, then choose the DynamoDB service.
- Click on Tables, redshift-import, and the items tab to view the items imported from the JSON file.
- Create IAM Role
In the AWS Management Console search and select for the IAM service.
Select Roles >> Create role >> EC2 >> Next Permissions.
In the search bar type S3 and select AmazonS3ReaOnlyAccess. Additionally, search for DynamoDB and select AmazonDynamoDBReadOnlyAccess.
Choose Next:Tags and type in name (for the key) and redshift-import (for the value). Select Next:Review.
On this Create role page:
- Role name: redshift-import
- Select Create Role.
Search and click on redshift-import, choose Trust relationships, and Edit trust relationship. Change Service: section to Service: "redshift.amazonaws.com".
Select Update trust policy.
In the AWS Management Console search and select for the Redshift service.
Select 1 link under the Cluster.
Choose this cluster, click Actions, and Manage IAM roles.
From the drop-down choose redshift-import, Add IAM role, and Done.
- Load the Data
In the AWS Management Console search and select for the Redshift service.
Click on 1, the cluster name, and copy the Endpoint name.
Select Services and search for and select the IAM service, Roles, redshift-import, and copy the Role ARN. This will be used momentarily.
Switch to the bastion host’s (cloud server) terminal and save the cluster’s import name into a variable. Be sure to erase everything after the
.comin the endpoint (port number andimport-test):export PGHOST=<RedshiftEndpoint>Connect to the cluster and enter in the password, provided in this lab’s description:
psql -U masteruser -p 5439 import-testCreate the tables:
create table s3_users (ID int, Name varchar, Department varchar, ExpenseCode int);create table dynamodb_users (ID int, Name varchar, Department varchar, ExpenseCode int);Import the data from DynamoDB. Be sure to replace the IAM ARN with yours.:
copy dynamodb_users from 'dynamodb://redshift-import' iam_role 'paste in IAM Role ARN' readratio 50;Query the table to ensure rows were inserted:
select * from dynamodb_users ;Import data from the S3 bucket:
copy s3_users from 's3://redshift-import20200923/redshift-data.csv' iam_role 'paste in IAM Role ARN' delimiter ',';Query the table to ensure rows were inserted:
select * from s3_users ;
