Reading and Writing Amazon Redshift Spatial Databases

Introduction

As of FME 2020.1, you can read and write data from Amazon Redshift Databases. 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

FME 2020.1 or higher

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 2020.1+

In FME Workbench 2020.1+, navigate to Tools > FME Options (Windows/Linux) For Mac, navigate to FME Workbench > Preferences

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, switch the Database Connection to Redshift 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 encountering an error, please confirm 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 not sure what schema you will be writing to contact your Database Administrator. Ensure that the Feature Operation is set to Insert, and click OK.

Connect the publicart Writer Feature Type to the AttributeManager.

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 the Amazon Redshift parameters, please see 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 on the ellipsis next to Tables and select the table that you want to read in. For this example, we are reading in public.publicart.

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

For information about additional parameters, or how to set a WHERE Clause, please see 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 the Amazon Redshift parameters, please see the documentation.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.