Transferring Data from S3 to Amazon Redshift

Introduction

Prerequisite

  1. AWS account with suitable permission
  2. SQL Workbench/J
  3. Redshift JDBC driver

Dataset

  • User_id: The unique id of a particular user
  • Age: The age of a user who rated a movie
  • Gender: The gender of a user
  • Profession: The users’ profession, i.e., whether they are doctors, artists, engineers, etc.
  • Ratings: The total rating given by a particular user to various movies

Steps

  • Create an IAM Role
  • Create an S3 bucket and upload the data file
  • Create a Redshift Cluster
  • Setup Connection with Redshift Cluster
  • Transfer data from S3 to Redshift

Step 1: Create an IAM Role

  1. Sign in to the AWS Management Console and launch the IAM service.

Step 2: Create an S3 bucket and upload the data file

  1. Launch S3 console and click on Create bucket.
  2. Provide a name for the bucket. It must be globally unique.

Step 3: Create a Redshift Cluster

  1. Search for Redshift in the AWS console and launch the Amazon Redshift console.
  2. Click on Create cluster option.
  3. Provide a name for the cluster. I have given redshift-cluster.
  4. We have 2 options for cluster usage; Production and Free trial. Choose Free trial for the practice purpose.

Step 4: Setup Connection with Redshift Cluster

  1. Download the Amazon Redshift JDBC driver file from here and unzip it. We need the file redshift-jdbc42–2.1.0.10.jar from the unzipped folder.
  2. Download SQL Workbench/J using this link, unzip the file and open SQLWorkbench application. It is a query editor which we’ll use to execute queries for transferring data.
  3. A query editor will open now. Go to the File menu and click on Manage Drivers…

Step 5: Data transfer from S3 to Redshift

  1. Once the connection is successful with the Redshift database, run the below query to create a table user_info with the schema similar to the file u.user uploaded in S3 bucket. Hit the execute button (highlighted in red) on the query editor to run the query.

Conclusion

--

--

Data Engineering and Analytics | AWS Enthusiast

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store