Reading and Writing Amazon Redshift Spatial Databases

Introduction

In this article, you will learn how to set up a Database Connection to Amazon Redshift. Following the connection, you will learn to write data to an Amazon Redshift Database in Part 2, and then in Part 3, you will learn how to read that data into FME. Before you begin, please ensure you have met all of the requirements.

Requirements

Amazon Redshift Spatial Database connection

Step-by-Step Instructions

Before we can read or write to Amazon Redshift Spatial Databases, we need to create a connection.

Part 1: Connecting to Amazon Redshift Spatial

1. Open FME Workbench

In FME Workbench, navigate to Tools > FME Options 

2. Create a New Connection

In the FME Options window, switch to Database Connections and then click on the plus sign “+” to create a new connection.

In the Add Database Connection dialog, select Redshift as the Database Connection, and then name your connection.

Fill out your Redshift Database connection details. Once Redshift connection parameters are set, click Test to confirm the connection. If successful, proceed to Part 2. If you encounter an error, please verify the connection parameters.

Part 2: Writing to Amazon Redshift Spatial

1. Add Excel Data

In a blank workspace, add a Microsoft Excel Reader to the canvas. Browse to the PublicArt.xlsx file, set the Coordinate System to LL84, and the Workflow Options to Single Merged Feature Types.

2. Add an AttributeManager

We need to rename the attribute that gets created when the Single Merged Feature Type option is used. Add an AttributeManager to the canvas. In the parameters, rename 'fme_feature_type' to 'neighborhood'.

3. Add an Amazon Redshift Writer

Now we can write out our data. Add an Amazon Redshift (Spatial) Writer to the canvas. For Non-Spatial Data, see the tip at the end of Part 2.

In the writer parameters, select your Amazon Redshift database connection that you previously set up. If you do not have a connection set up, please see Connecting to Amazon Redshift Databases. As a basic example, we do not have any additional parameters to set up. If you would like to explore the parameters further, please see the Amazon Redshift (Spatial) Documentation.

Change the Table Definition to Automatic and then click OK. In the Feature Type dialog, set the Table name to publicart, and then for Table Qualifier, enter the schema you will be writing to; for this example, this is public. If you are unsure of the schema you will be writing to, please contact your Database Administrator. Ensure that the Feature Operation is set to Insert, and click OK.

Connect the PublicArt Writer Feature Type to the Attribute Manager.

4. Run the Workspace

Save the Workspace and then run it. If you get the following error message, ensure that you have the correct schema name, or the credentials used in your connection have sufficient privileges:

Writing Non-Spatial Data

To write non-spatial data to Amazon Redshift, the Amazon Redshift Writer needs to be used. For more details on Amazon Redshift parameters, please refer to the documentation.

Part 3: Reading from Amazon Redshift Spatial

1. Complete Part 2

Before you can read from Amazon Redshift Spatial, ensure that you have completed Part 2 of this tutorial, or you already have data in your database that you can read.

2. Add an Amazon Redshift Spatial Reader

In a blank workspace, add in an Amazon Redshift (Spatial) Reader to the canvas. Select your Amazon Redshift connection that you previously created. If you do not have a connection set up, please see Part1.

In the parameters, click the ellipsis next to "Tables" and select the table you want to read. For this example, we are reading in public.publicart.

Click OK three times to add the reader to the canvas.

For information on additional parameters or setting a WHERE Clause, please refer to the documentation.

3. Continue Workflow

Now that your Amazon Redshift Spatial data has been read in, you can continue building your workspace.

Reading Non-Spatial Data

To read Non-spatial data from Amazon Redshift, the Amazon Redshift reader needs to be used. For more details on Amazon Redshift parameters, please refer to the documentation.