Redshift : Learn how to link Amazon redshift to s3 bucket

Amazon Redshift is a fully-managed, petabyte-scale data warehouse service in the cloud. It allows you to run complex analytical queries against large datasets in a fast and cost-effective way. On the other hand, Amazon S3 is a scalable, durable, and highly available object storage service that allows you to store and retrieve any amount of data from anywhere on the web.

By linking Amazon Redshift to an S3 bucket, you can easily load data into Redshift from S3 and export data from Redshift to S3. In this article, we’ll walk through the steps to set up the S3 bucket and link it to Amazon Redshift.

Step 1: Create an S3 bucket

The first step is to create an S3 bucket if you haven’t already. To create an S3 bucket, log in to your AWS account and navigate to the S3 dashboard. Click on the “Create bucket” button and follow the prompts to create your bucket. Be sure to take note of the bucket name, as we will need it later.

Step 2: Create an IAM role

Next, we need to create an IAM role that Redshift can assume to access the S3 bucket. To do this, navigate to the IAM dashboard and click on the “Roles” tab. Click on the “Create role” button and select “Redshift” as the service that will use the role. Then select “Redshift – Customizable” as the use case.

In the next step, attach the “AmazonS3ReadOnlyAccess” policy to the role. This policy allows Redshift to read data from the S3 bucket. Give the role a name, such as “redshift-s3-role”, and create the role.

Step 3: Create a Redshift cluster

Now we need to create a Redshift cluster if you don’t have one already. To create a Redshift cluster, navigate to the Redshift dashboard and click on the “Create cluster” button. Follow the prompts to create your cluster.

Be sure to select the same VPC as the S3 bucket when you create the Redshift cluster. You also need to specify the IAM role you created in Step 2 in the “Additional configuration” section.

Step 4: Create an external schema

The next step is to create an external schema in Redshift that references the S3 bucket. To create an external schema, open an SQL client and connect to the Redshift cluster. Then run the following SQL statement:

CREATE EXTERNAL SCHEMA freshers_s3_schema
FROM data catalog
DATABASE 'mydatabase'
IAM_ROLE 'arn:aws:iam::7852541258523:role/freshers_redshift-s3-role'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

CREATE EXTERNAL TABLE freshers_s3_schema.mytable (
  col1 varchar(100),
  col2 integer,
  col3 date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION 's3://freshers-in/trainingdata/schema/';

SELECT COUNT(*) FROM freshers_s3_schema.mytable;

UNLOAD ('SELECT * FROM freshers_s3_schema.mytable')
TO 's3://mybucket/export/mytable.csv'
IAM_ROLE 'arn:aws:iam::7852541258523:role/freshers_redshift-s3-role'
DELIMITER ','
ALLOWOVERWRITE;