Amazon Redshift ingest data with auto-copy from Amazon S3
Tags: aws, database, tutorial, analytics
Published: 2023-03-12 14:18:12


## Introduction

You may be a data scientist, business analyst or data analyst familiar with loading data from Amazon S3 into Amazon Redshift using the COPY command, at AWS re:invent 2022 to help AWS customers move towards a zero-ETL future without the need for a data engineer to build an ETL pipeline, data movements can be simplified with auto-copy from Amazon S3 into Amazon Redshift.

![drs](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3h91pn0k0om8839g8k29.jpg)

### Lesson Outcome

* In this lesson you will learn how to ingest data with auto-copy from Amazon S3 into Amazon Redshift.

## What is Amazon Redshift Auto-Copy from Amazon S3 ?

Amazon Redshift auto-copy from Amazon S3 simplifies data ingestion from Amazon S3.

### How does this feature work?

Auto-copy from Amazon S3 is a simple, low code data ingestion that automatically loads new files that are [detected](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/) in your S3 bucket into Amazon Redshift.

New files can be [ingested](https://aws.amazon.com/blogs/aws/new-for-amazon-redshift-simplify-data-ingestion-and-make-your-data-warehouse-more-secure-and-reliable/) in the formats csv, json, parquet or avro.

You may store a COPY command in a COPY job in Amazon Redshift which will detect new files stored in Amazon S3 and load the data into your [table](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/).


### What are the benefits?

* You can save time for your team by avoiding manual uploads of new data from an S3 bucket into Amazon Redshift with COPY statements.

* COPY jobs will be able to detect data that was previously loaded in the data ingestion process.

* COPY jobs can prevent [duplicated data](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/) when an automated job is not required.

* COPY jobs can be manually created to [reuse](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/) copy statements

### Solution Architecture

In this high-level architecture, new files are ingested into a single Amazon S3 bucket into separate folders and a copy job  inserts the data into the corresponding Table A and Table B.


![new archit](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oss4hghgooehotiemz4i.jpg)

### Prerequisites

* Download an interesting open source dataset and save it in your directory. This tutorial includes data from Kaggle at this [link](https://www.kaggle.com/datasets/synful/world-happiness-report)

* Login into your AWS account as an IAM Admin user. You may view the [blog](https://dev.to/aws-builders/creating-your-first-iam-admin-user-and-user-group-in-your-aws-account-machine-learning-part-1-3cne) to create an account.

* Create an Amazon S3 bucket. You may view this [blog](https://dev.to/aws-builders/creating-your-first-amazon-s3-bucket-and-new-feature-release-2c03) to create your first S3 bucket.

* Use AWS Glue to create a crawler to inspect the meta data in the data catalogue table. You may follow this [blog](https://dev.to/aws-builders/how-to-etl-with-aws-glue-and-amazon-glue-studio-to-transform-your-data-328b) to learn how to create a crawler.

### Dataset

The World Happiness Report dataset is available to download from Kaggle [here](https://www.kaggle.com/datasets/synful/world-happiness-report). There are five datasets ranging from 2015 to 2019 

**Data Description**

There are 12 columns in each csv file:

* Country
* Region
* Happiness Rank
* Happiness Score
* Standard Error
* Economy - GDP per capita
* Family
* Health - Life Expectancy
* Freedom
* Trust - Government Corruption
* Generosity
* Dystopia

Using AWS Glue to create a crawler to inspect the schema in one file from the data catalogue:


![glue](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jcc9xtku1ois5b7bw9g3.jpg)

### Tutorial 1: Getting started with auto-copy files from Amazon S3(Preview) into Amazon Redshift

For this tutorial we will follow the steps outlined in the [Amazon Redshift Database Developer Guide](https://docs.aws.amazon.com/redshift/latest/dg/loading-data-copy-job.html) 

Step 1: Sign into the AWS Management Console with your [AWS IAM Admin User](https://dev.to/aws-builders/creating-your-first-iam-admin-user-and-user-group-in-your-aws-account-machine-learning-part-1-3cne) account details.

Step 2: Navigate to Amazon Redshift by typing 'Redshift' in the search bar.


![redshift console](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lvrviz6uxwnmohz6cgem.jpg)

Step 3: Create a cluster in Preview by selecting **Provision clusters dashboard**

In this preview, I will use the **AWS region US-East (Ohio) (us-east-2)**.


![provisioned clsuters](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/55hocaz0rk0wiqty2twb.jpg)

Step 4:  Click **Create preview cluster** in the banner.

![create prewview](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ih5ijs0337viw5z8g8ya.jpg)

Step 5: Create a unique name to identify your cluster in lowercase letters.

Next select from the drop-down menu a **Preview track** e.g. *Preview 2022* to allow you to test the new features.

![unique name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vzq7x3o4y3q2uvxyyfg1.jpg)

Step 6: Inspect the size of your data or estimate the size.

![datasets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/28bz3jl4j93yf6ezbnxb.jpg)

Step 7: Enter other configurations for your cluster. I selected **Help me choose**. I have selected additional options to indicate that my estimate is for compressed data in Amazon Redshift and my data is not time-based.


![hep me choosen](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yreobw09ilbpsruyzkrm.jpg)

The **calculated configuration summary** provided recommendations for 2 nodes and compute size using dc2.large.


![calc](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4o2urzh9fgbnzh316rtj.jpg)

Step 8: Under **Database Configurations**, create an admin user password that includes at least one Uppercase letter and a number.


![db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xk9twekfra3y3s2xm4bs.jpg)

Step 9: Under **Cluster Permissions**, I created a new IAM role that has **AmazonRedshiftAllCommandsAccess** policy attached to allow you to perform the following SQL queries such as **COPY**, **UNLOAD** and **SELECT**. The new IAM role can access any S3 bucket and it replaces the default IAM role.


Type 'IAM' in the navigation search bar and click **Roles** create a new role.

Add additional permissions to access Query Editor 2, Amazon S3 and Amazon Redshift Full Access.

Provide a name of the new service role e.g.'redshift-autocopys3-custom-role'


![create custom role](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b42d9kd0afll0gc15m27.jpg)


Add permissions and then click **Create role**.


Confirm that the new service role has been created in IAM.


Step 10: Click **Create cluster** in preview, it will take a few minutes to provision. (Note: do not use the preview cluster for your production workloads).


![create](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wqo629qu3hmguade18ix.jpg)

![Image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7slzw7ris3mvdeh5hdl8.jpg)

The preview cluster has been created successfully.


![cluster preview](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fbf5zbcspffvduzfjrp4.jpg)

Step 11: On the navigation pane on the left, click **Query editor v2**


![query editor](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jlfdrltso08nq26cqig2.jpg)


Step 12: Double click on the preview cluster (i.e.database) and open the arrow to inspect the tables.

You can inspect the new preview cluster that has been created.


![auto copy table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0bqv7qhd94zc2j81iw14.jpg)

Step 13: Create an Amazon S3 bucket with a globally unique name  e.g. world-hapiness-report using default settings and click **Create bucket**.


![world happiness](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oe3z7u2jo35zt8froe89.jpg)

![bucket](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ldsup7ma8imio1g2pypi.jpg)

Step 14: Create a single folder in the Amazon S3 bucket for automated file ingestion e.g. 's3-autocopy-file'

![create folder](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vmc7t75ghhkz84lr48ow.jpg)

Create a subfolder named e.g.'datasets'



Upload five csv files.


![all datasets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3h9h63udy46lxszzxk3j.jpg)

### Tutorial 2: Load data Amazon Redshift and query data.

Step 1: Create a table in Amazon Redshift.

The following SQL code creates the world_happiness table:

```
Create table world_happiness (
country VARCHAR(100),
region VARCHAR(100),
happiness_rank int,
happiness_score float4,
standard_error float4,
economy float4,
family float4,
health float4,
freedomm float4,
trust float4,
generosity float4,
dystopia float4)
;
```

Step 18: Create a COPY job to automatically load csv files from Amazon S3 path into the *world happiness* table in Amazon Redshift.

```
COPY world_happiness
FROM 's3://world-happiness-report/s3-autocopy-file/datasets/published_reports' 
IAM_ROLE 'arn:aws:iam::***********:role/redshift-autocopys3-custom-role'
DELIMITER ','
IGNOREBLANKLINES
REGION 'us-east-1'
IGNOREHEADER 1
JOB CREATE job_world_happiness1 AUTO ON;
```

### Final Notes

* Autocopy files from Amazon S3 to Amazon Redshift should be used for test workloads only during public preview.

* After you have finished testing, please delete your provisioned Amazon Redshift cluster to avoid billing charges.

* IAM role does not support *default* during public preview. You may read other best practice guidelines in this [blog](https://aws.amazon.com/blogs/aws/new-for-amazon-redshift-simplify-data-ingestion-and-make-your-data-warehouse-more-secure-and-reliable/).

### AWS re:Invent 2022

If you missed the keynote from **Dr Swami Sivasubramanian**, VP of Data and Machine Learning at Amazon Web Services you may watch on demand and also keep informed of the other keynotes [here](https://www.youtube.com/playlist?list=PL2yQDdvlhXf8xcKr0-BHEyg_8VB4tWdu1).

If you would like to watch the announcement of the auto-copy files from Amazon S3 (Preview) into Amazon Redshift you may fast forward to the timing at **1 hour** and **18 minutes**.

{% embed https://www.youtube.com/watch?v=TL2HtX-FmiQ&t=9s %}

### Reference

* [AWS News Blog](https://aws.amazon.com/blogs/aws/new-for-amazon-redshift-simplify-data-ingestion-and-make-your-data-warehouse-more-secure-and-reliable/)

* [Simplify data ingestion from Amazon S3 to Amazon Redshift using auto-copy (preview)](https://aws.amazon.com/blogs/big-data/simplify-data-ingestion-from-amazon-s3-to-amazon-redshift-using-auto-copy-preview/)

* [Database developer guide](https://docs.aws.amazon.com/redshift/latest/dg/loading-data-copy-job.html) 

* [Querying the AWS Glue Data Catalog (preview)](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-glue.html)

* [Announcement](https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/)

* [Documentation](https://docs.aws.amazon.com/redshift/latest/dg/loading-data-copy-job.html)

* [Other AWS re:invent 2022 announcements](https://aws.amazon.com/blogs/aws/top-announcements-of-aws-reinvent-2022/)

* [World happiness report dataset](https://www.kaggle.com/datasets/synful/world-happiness-report)

### What's new next week? 🌎

* AWS Innovate - Data and AI/ML edition - [Americas on 14 March including Pi Day celebrations](https://aws.amazon.com/events/innovate-online-conference/americas/) join the party to celebrate 17 years of launching Amazon S3 and have some delicious🥧


![AWS Innovate](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r91xzqxwk3rph7t3lnah.jpg)

Register for AWS Pi Day at this [link](https://pages.awscloud.com/NAMER-field-OE-Pi-Day-2023-reg-event.html?sc_icampaign=aws-pi-day-2023&sc_channel=sm&sc_campaign=Product%20Awareness%20&%20Education=&sc_publisher=TWITTER&sc_geo=GLOBAL&sc_outcome=awareness&trk=c03fe4bd-4899-46d5-80c5-bb4137d18449&linkId=201971315)


![pie day](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y6c59r75xlcjljmbnszy.jpg)

Until the next lesson, happy learning! 🙂