The following describes the steps and inputs needed to configure a Redshift Spectrum destination within your Openbridge account. The process is simple and quick, only requiring minimum information need to be able to connect and write data to your instance.

If you are just creating an Openbridge account you will get presented with the data destination selection screen. Select "Amazon Redshift Spectrum" and then “Activate”.

Provided Redshift and Spectrum Connection Details

The first 4 steps detail your Redshift connection information:

Step 1: Openbridge Storage Name

This is a label that you provide so you can uniquely identify this destination in Openbridge. Some customers may have multiple databases so labeling each will help you identify the system you want us to deliver data into.

Step 2: Name Of Database

This is the name of the Redshift database you want Openbridge to use.

Step 3: Host and Port

Enter the host (or IP) address as well as the port. If you have a firewall setup, you will need to whitelist our IPs:

52.54.227.22
52.2.68.68

Step 4: User and Password

Supply the user and password that Openbridge should use to authorize connections to your system.

Spectrum Connection Details

Next, you will enter the Spectrum connection details. if you have not done so, please check out our Spectrum setup guide if you need help with this.

Step 5: Select Your Desired Region

This is the Amazon Web Service region that wants Amazon Redshift Spectrum configured. Note: It should be the same region you set up your Spectrum S3 bucket.

Step 6: Amazon S3 Bucket

This is the Amazon Web Service S3 Bucket you setup previously to be used with Spectrum

Step 7: Spectrum Database

This is the Spectrum database you want to use. The name should be descriptive of the data being stored. For example, you can use openbridge-database.

Step 8: Spectrum IAM Role ARN

The IAM Role ARN allows us to call the AWS Redshift Spectrum services on your behalf. The ARN is located in the Amazon IAM console. It will look like this arn:aws:iam::1223131231231:role/your-role-name.

Check out our Spectrum setup guide if you need help with this.

Step 9: Spectrum IAM Role ARN

This is the IAM user Access and Secret Key that you set up for Openbridge to connect to Amazon Redshift Spectrum. This info is located in the .csv you downloaded from the Amazon IAM console.

The last thing you need to do is review your information and then "Connect..."
The process can take a few minutes to complete, so give it time to finish. Once it completes your new warehouse destination will be registered in our system!

You can now select Redshift Spectrum as a destination location for any data coming into the system!

Configuring Redshift

This guide will walk you through different commands you can run to make sure that Redshift is set up correctly with the required schema, users, and permissions.

For example, here is a collection of SQL statements that you can use within your Redshift instance to get it set up for us:

/*
You will want to replace {{username}}, {{mydatabase}}, {{userpassword}} with your correct values
*/
create database {{mydatabase}};
\connect {{mydatabase}}
create user {{username}} with password '{{userpassword}}';
grant create on database {{mydatabase}} to {{username}};
create schema if not exists openbridge authorization {{username}};
grant all on schema openbridge to {{username}};