Amazon Redshift

This page describes how to sync data to your Redshift data warehouse.

Getting Started

This guide will walk you through connecting to AWS Redshift as a destination.

If you are trying to use Redshift as a data source (to query data from Redshift and sync to elsewhere), that process is documented separately here: Redshift Data Source

Visit the Destinations page and click + New Destination.
Select Redshift from the menu.
Enter the requested database credentials:

Credential

Description

Hostname

Host name or IP address of database

Port

Port of database (5439 by default for Redshift)

Database Name

Name of database within Redshift to connect to

Username

Username Census will use to connect

Password

Password Census will use to connect

Number of Client Connections

Value between 1 and 8 (default is 1). This is the maximum number of concurrent connections Census will use to connect to database. The default should be fine in most cases, but increasing this value can increase throughput on very large syncs.

🔑 Permissions

To use Redshift as a destination, Census requires permission to write to the desired destination tables, as well as read metadata about the table and database structures.

-- Note that creating a user may be redundant if you're already configured
-- Redshift as a source.

-- Give the census user the ability to sign in with a password
CREATE USER CENSUS WITH PASSWORD '<strong, unique password>';

-- Let the census user see this schema
GRANT USAGE ON SCHEMA "<your schema>" TO CENSUS;

-- Let the census user read all existing tables in this schema
-- Note: this can also be granted to specific tables as well
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "<your schema>" TO CENSUS;

Supported Objects and Sync Behaviors

Object Name

Supported?

Sync Keys

Behaviors

Table

✅

Primary Keys or Columns with Uniqueness Constraints

Update or Create, Update Only, Add

Learn more about all of our sync behaviors in our Syncs documentation.

Advanced Network Configuration

Census can successfully connect to Redshift instances that are using advanced networking controls including region constraints and IP address allow lists. For more information, see our Network Access Controls documentation.

Need help connecting to Redshift?

PreviousAmazon Pinpoint NextAmazon S3

Last updated 9 days ago

Was this helpful?

Getting Started

This guide will walk you through connecting to AWS Redshift as a destination.

If you are trying to use Redshift as a data source (to query data from Redshift and sync to elsewhere), that process is documented separately here: Redshift Data Source

Visit the Destinations page and click + New Destination.

Select Redshift from the menu.

Enter the requested database credentials:

Credential

Description

Hostname

Host name or IP address of database

Port

Port of database (5439 by default for Redshift)

Database Name

Name of database within Redshift to connect to

Username

Username Census will use to connect

Password

Password Census will use to connect

Number of Client Connections

🔑 Permissions

To use Redshift as a destination, Census requires permission to write to the desired destination tables, as well as read metadata about the table and database structures.

-- Note that creating a user may be redundant if you're already configured
-- Redshift as a source.

-- Give the census user the ability to sign in with a password
CREATE USER CENSUS WITH PASSWORD '<strong, unique password>';

-- Let the census user see this schema
GRANT USAGE ON SCHEMA "<your schema>" TO CENSUS;

-- Let the census user read all existing tables in this schema
-- Note: this can also be granted to specific tables as well
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "<your schema>" TO CENSUS;