Amazon Redshift

Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse from AWS. Scoop connects directly to Redshift, enabling AI-powered analysis on your data warehouse.

Connection Details

Prerequisites

Before connecting Scoop to Redshift:

1. Amazon Redshift cluster (provisioned or serverless)
2. Read-only user with SELECT permissions
3. VPC security group allowing Scoop's IP addresses

Create a Read-Only User

Connect to your Redshift cluster and run:

-- Create the read-only user
CREATE USER scoop_user PASSWORD 'your_secure_password';

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO scoop_user;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO scoop_user;

-- Grant SELECT on future tables (recommended)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO scoop_user;

Replace the password with a secure value. If using multiple schemas, repeat the GRANT statements for each schema.

Whitelist Scoop IP Addresses

1. Go to Amazon VPC in the AWS Console
2. Find the Security Group attached to your Redshift cluster
3. Edit Inbound Rules
4. Add a rule:
   • Type: Custom TCP
   • Port: 5439
   • Source: Scoop's IP addresses

For Redshift Serverless, configure the VPC security group associated with your workgroup.

Contact Scoop support for the current list of IP addresses to whitelist.

Enable Public Accessibility (if needed)

If your Redshift cluster is in a private subnet:

1. Go to your Redshift cluster in the AWS Console
2. Click Actions > Modify publicly accessible setting
3. Enable Publicly accessible
4. Ensure your cluster is in a subnet with an Internet Gateway

Alternatively, use AWS PrivateLink or a bastion host for secure access.

Connect in Scoop

1. Navigate to Datasets and click New Dataset
2. Select Database as your source
3. Choose Redshift from the database type dropdown
4. Enter your connection details:
   • Server Host: Your cluster endpoint (e.g., mycluster.abc123.us-east-1.redshift.amazonaws.com)
   • Port: 5439
   • Database: Your database name (often dev or prod)
   • Username: scoop_user
   • Password: The user's password
5. Click Test Connection to verify
6. Enter your SQL query and proceed

Connection URL Format

If using a JDBC URL directly:

jdbc:redshift://cluster-endpoint:5439/database_name

With SSL (default):

jdbc:redshift://cluster-endpoint:5439/database_name?ssl=true

Best Practices

• Use distribution keys on large tables to optimize query performance
• Use sort keys on frequently filtered columns
• Run VACUUM and ANALYZE regularly to maintain performance
• Use workload management (WLM) to isolate Scoop queries from production workloads

Troubleshooting

Connection Timeout

• Verify your cluster endpoint is correct
• Check that Scoop's IP addresses are in the security group
• Ensure the cluster is publicly accessible (or use a VPN/bastion)
• Verify the cluster is running (not paused)

Permission Denied

• Verify the user was created successfully: SELECT usename FROM pg_user;
• Check current permissions: SELECT * FROM pg_tables WHERE schemaname = 'public';
• Ensure GRANT statements were executed

SSL Required Error

• Redshift requires SSL by default
• Ensure your connection includes SSL parameters
• Check your cluster's SSL settings in the console