# Amazon Redshift

Connect to Amazon Redshift for cloud data warehouse analytics

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

| Setting | Value |
|---------|-------|
| Default Port | 5439 |
| Connection Type | TCP/IP |
| SSL | Required by default |

## 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:

```sql
-- 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