Warehouse Guide: Redshift

Create a Redshift cluster, open it to Kleene and add a limited user, then connect it as your data warehouse.

• Connector type Warehouse
• Auth method Database credentials

Before you start

• S3 as your filestore — Redshift requires it
• An S3 bucket in the same region as your cluster (recommended)

Set up Redshift as your warehouse

1. Provision your Redshift warehouse

   1. Create a Redshift cluster — see AWS's getting started guide. Choose a size appropriate for your expected data volumes.
   2. Open the AWS Redshift console, select the region your cluster is deployed in, click Clusters in the left menu, and select the cluster Kleene will connect to.
   3. Click the copy icon next to the endpoint to view the full URL. Example: dev-redshift.<clusterID>.eu-west-1.redshift.amazonaws.com:5439/dev
   4. Note the port the database runs on — usually 5439. The host name is the endpoint without the port and database on the end, so in the example above: dev-redshift.<clusterID>.eu-west-1.redshift.amazonaws.com

2. Configure access for Kleene

   1. In the Redshift console, select the cluster and click the Properties tab.
   2. Scroll to Network and security and click the security group to open it.
   3. Click the Inbound rules tab, then Edit inbound rules.
   4. Click Add Rule, select Redshift from the Type dropdown, and add Kleene's IPs as separate rules: 54.78.204.135/32 and 34.242.207.164/32. Click Save rules.
   5. Connect to the cluster using the query editor in the AWS console, connecting to the database you created with the cluster.
   6. Create a limited user for Kleene and grant it the privileges it needs, using the SQL below. Kleene uses password authentication, and creates the schemas and tables it needs itself.

   Create the Kleene user

   -- create the limited user Kleene will connect as
   CREATE USER eltuser PASSWORD with `<password>` createdb;
   
   -- allow it to create schemas and write tables
   GRANT ALL on DATABASE <dbname> to eltuser;
   
   -- check the user was created
   select * from pg_user_info;

   We recommend a limited user rather than the master username, which inherently has CREATE permissions.

3. Add the warehouse in Kleene

   • HostThe cluster endpoint, without the port and database
   • PortFrom the cluster endpoint — usually 5439
   • Database NameThe name you gave the database when creating the cluster
   • Database Usereltuser
   • Database PasswordThe password you assigned to that user
   • S3 detailsYour S3 bucket details — set one in the same region as the cluster

   Kleene can now load data into your warehouse.

Vendor reference: Amazon Redshift management guide