How to import Amazon Redshift datasets into CARTO

Import Amazon Redshift datasets into your CARTO account.

This tutorial demonstrates how to import Amazon Redshift datasets into CARTO.

Introduction

The CARTO Amazon Redshift connector allows you to import datasets from an Amazon Redshift cloud data warehouse into CARTO. You can then use CARTO to create easy-to-share map dashboards, perform spatial data science analysis or create spatial applications.

Registering a connection to Amazon Redshift

The first thing you need to do is to register a connection to your Redshift cluster. Please read the corresponding section from the Working with Connections tutorial to setup a connection.

Importing your Amazon Redshift dataset

Once you have registered a connection to Redshift, you have two different ways to import a dataset:

• Clicking on the Redshift connection in the “Your connections” tab (Data page)

• Clicking on the “New dataset” button and selecting the Redshift connection from the “From your existing connections” section

In both cases, you will be redirected to a page where you need to specify the SQL query that will be run. The results of this query will be imported into a CARTO dataset. This dataset will be titled according to the name specified in the CARTO dataset input field.

• If your Amazon Redshift table contains a column with GEOMETRY data type, you can import the data just by renaming that field to “the_geom”:

SELECT field1, field2, geometry_field AS the_geom FROM table

• If your Amazon Redshift table contains two columns with longitude and latitude information, you can import the data by creating a Point geometry, setting the spatial reference system ID to 4326 (WGS84) and renaming it to the_geom:

SELECT field1, field2, ST_SetSRID(ST_MakePoint(lng, lat), 4326) AS the_geom FROM table

• When you have specified your SQL query and the name for your new dataset, the “Run SQL Query” button becomes enabled. When you click this button, CARTO will verify the query syntax. If everything is OK, the next screen lets you specify the frequency for updating your CARTO dataset if you’d like to keep it in sync with the Amazon Redshift table.

• Finally, you must click the “CONNECT DATASET” button to begin the import operation. When the operation finishes you will be presented with a preview of the dataset. From this screen you can directly create a map with your imported dataset.

This project has received funding from the European Union’s Horizon 2020 research and innovation programme under grant agreement No 960401.