Redshift

Last Updated: 2022-01-28

Overview

Grouparoo's Redshift Integration was made specifically to run on Amazon Infrastructure and is configurable as both a Source and a Destination that enables you to import and export customer data to and from one or more Redshift databases. Redshift is a relational database management system (RDBMS). You can use Grouparoo to sync data between a Redshift database and other services to operationalize your data.

Sources

Source NameDescription
Redshift Import TableImport or update Records from a Redshift database table.
Redshift Import QueryImport or update Records via a custom Redshift query.

Destinations

Destination NameDescription
Redshift Export RecordExport Records to a Redshift table. Groups will be exported to a secondary table linked by a foreign key.

Setup

To work with the Redshift Plugin, you must first install it in an existing Grouparoo project. Grouparoo supports syncing with Redshift databases stored on a local machine or remotely, provided that there is no firewall or VPN prohibiting the connection. How you connect depends on how you configure the App in Grouparoo’s Config UI.

App

To connect Redshift to Grouparoo, you create an App to tell Grouparoo how to connect to Postgres. This specifies the connection details for the database you want to sync data to or from.

App Options

In Grouparoo, provide the options that best describe how you would like to connect to Redshift.

Redshift App Options

Option NameDescription
HostThe Redshift Host. This field defaults to ‘localhost’.
PortThe Redshift Port. The default value is ‘5439’.
Database (required)The Redshift Database dame - e.g. ‘data_warehouse’.
SchemaThe Redshift schema used to read tables. This plugin works inside a single schema. This field defaults to ‘public’.
UserThe Redshift User to connect to the database -eg ‘grouparoo_user’. If you are connecting to localhost, leave this field as undefined.
PasswordThe Redshift User’s password. If you don’t have a password, leave this field as undefined.
SSLRequire the use of a SSL connection (default: false). If you need custom SSL certificates paste in their values below.
SSL CertificateThe SSL certificate
SSL KeyThe SSL certificate’s key.
SSL Certificate AuthorityThe SSL certificate authority (CA)

Redshift Import Table

This source reads from a single schema or table in your Redshift database using a primary or foreign key. Properties are generated from this table from row values or aggregations of row values.

Options

There is one option to set for a Redshift Table Import Source. You need to choose the table from your database that will serve as the location for Grouparoo to scan for changes to Properties.

Redshift Import Table Options

Mapping

Defining Mappings is a critical part of the process. It tells Grouparoo which Record to associate with the imported data.

If you are using Redshift Import Table as the primary source, choose the database column that uniquely identifies Records in your Model. This will often be the primary key of the table that represents your Model.

In secondary sources, choose the database column that ties this table to your primary source. This will often be a foreign key in your table.

Properties

Properties populate your Records with data from your Redshift table based on table columns. Create a Property to be able to use row data in Grouparoo.

Options

Redshift Import Table Properties


Filters

If there is a subset of rows data you want to specifically include or exclude from your Redshift database table, create a Filter.

Schedule

Create a schedule to actively pull new data from this Redshift table. If the recurring option is not selected, you are creating a one-time sync. There are also options to confirm records exist when running a schedule and to enable refresh. When Recurring is selected, you will see a field for entering the frequency, in minutes, of recurrence.

Options

Configure a schedule for your project and determine how Grouparoo should check for new data by filling out the options for this Source.

Option NameDescription
column (required)Select which column Grouparoo should check to look for new data. Often, this would be a column like “updated_at” - Grouparoo remembers the most recent values so it can be based on an incremental count as well.

Filters

If there is a subset of rows data you want to specifically include or exclude from your Redshift database table, create a Filter.

Redshift Import Query

A Query Source works differently than a table-based source in that there are no options to choose in setting up the connection to this type of source. A Query Source is a more flexible way to build properties.

This type of Source provides the ability to write custom SQL code to extract data from one or more tables and import the result into Grouparoo.

Redshift Export Record

Redshift Export Record allows you to set up a Grouparoo Project that uses Redshift as a Destination As with Redshift Sources, to set up Redshift as a Destination you will need to have the App installed and configured with connection details.

Sync Modes

A Destination’s sync mode determines what to update in a Destination. Redshift supports the following sync behaviors.

  • Sync: Add, update, and remove Redshift Exported Properties as needed. This option looks for new records, changes to existing records, and deletions.
  • Additive: Add and update Redshift Records as needed, but do not remove any. This option looks for new Records to add to Redshift and changes to existing Redshift Records, but does not keep track of deletions.
  • Enrich: Only update those Records that already exist in Redshift. Do not add or remove Records. This option will only update existing Records in Redshift.

Destination Options

Redshift Export Record Options


Option NameDescription
Table (required)The Redshift table to write records to
Primary Key (required)The Primary key of the table you are writing to
Groups Table (required)The Redshift table to write groups to
Group Foreign Key (required)The foreign key that the groups table uses to reference table
Group Column NameThe column name for where to store the group names

Record Data

Redshift Export Record Properties


Required Properties

In a Redshift Export Record Destination, you must choose the Grouparoo Property to map to the column listed as the primary key in the Redshift database table that this Destination syncs to. Therefore, the Exported Property listed as required will be whatever is designated as the Primary Key in the Redshift table.

Optional Properties

In addition to the required mapping for your table’s primary key, any other columns in the table can also have mappings to Grouparoo properties.

Group Data

You can also use Grouparoo Groups to assign records to a Group in Redshift. This information is recorded in a table that lists the Grouping alongside a foreign key that relates to the associated Record being assigned to that Group in the Redshift database.

Notes

We strive to keep our documentation accurate, easy to use, and up to date. If you notice that something has changed or is not addressed in the process of setting up your Grouparoo connection with Redshift, please let us know by reaching out to help@grouparoo.com.

As always, if you need support using Grouparoo, reach out to community members in our Slack channel. We are always happy to help!

Join us on Slack