Loading data into Amazon Redshift via S3 buckets

When you need to load data from your local databases to Amazon Redshift, Semarchy xDI lets you design a simple mapping which will take care of the following steps automatically:

  1. Export data from your database to a file

  2. Send the file to an Amazon S3 bucket

  3. Load the S3 file into Redshift

This article shows how to create the required metadata and how to design the mapping.

Create the Amazon S3 metadata

Create an Amazon S3 Metadata:

load redshift through s3 a s3 metadata creation

Right click to add a bucket and a folder. You can configure each node:

load redshift through s3 b s3 metadata bucket

Create the Amazon Redshift metadata

Create an Amazon Redshift Metadata:

load redshift through s3 c redshift metadata creation

Fill in the JDBC URL and credentials, and reverse your Redshift database schema as you would do with any database.

Once this is done, drag and drop the S3 Folder into the Redshift schema, like this:

load redshift through s3 d redshift metadata configuration

Rename this node as "S3_STAGING". The Load templates will need this name to connect to this folder.

Design the mapping

Design your Mapping using your Redshift table as a target.

Make sure that the LOAD Rdbms to Redshift SQL (with COPY) and INTEGRATION Redshift template are selected.