Skip to main content

​
Installation

Install the Redshift connector: 
npm install @evidence-dev/redshift

​
Configuration

The Redshift connector uses the PostgreSQL adapter, as Redshift is based on PostgreSQL. 
name: redshift
type: redshift
options:
  host: my-cluster.abc123.us-west-2.redshift.amazonaws.com
  port: 5439
  database: analytics
  user: ${REDSHIFT_USER}
  password: ${REDSHIFT_PASSWORD}
  ssl:
    sslmode: require

​
Configuration Parameters

​
host
string
required
Redshift cluster endpoint (e.g., my-cluster.abc123.us-west-2.redshift.amazonaws.com)
​
port
number
default:5439
required
Redshift port (default is 5439)
​
database
string
required
Database name to connect to
​
user
string
required
Database username
​
password
string
required
Database password
​
schema
string
Default schema to use (sets search_path)
​
ssl.sslmode
string
default:"require"
SSL mode: require, verify-ca, or verify-full

​
SSL/TLS Configuration

Redshift requires SSL connections. The connector is configured to use SSL by default:
connection.yaml
name: redshift
type: redshift
options:
  host: my-cluster.abc123.us-west-2.redshift.amazonaws.com
  port: 5439
  database: analytics
  user: ${REDSHIFT_USER}
  password: ${REDSHIFT_PASSWORD}
  ssl:
    sslmode: require
    rejectUnauthorized: true

​
IAM Authentication

For IAM-based authentication, you can use temporary credentials generated by AWS:
connection.yaml
name: redshift
type: redshift
options:
  host: my-cluster.abc123.us-west-2.redshift.amazonaws.com
  port: 5439
  database: analytics
  user: IAM:${AWS_IAM_USER}
  password: ${REDSHIFT_TEMP_PASSWORD}
  ssl:
    sslmode: require
Temporary IAM credentials must be refreshed before expiration. Consider using standard database users for long-running Evidence instances.

​
Features

​
Type Mapping

Redshift types are mapped to Evidence types:
  • Numbers: SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION
  • Strings: VARCHAR, CHAR, TEXT
  • Dates: DATE, TIMESTAMP, TIMESTAMPTZ
  • Booleans: BOOLEAN

​
Redshift-Specific Considerations

  • SUPER type: Complex data returned as strings
  • Column names: Automatically converted to lowercase
  • Distribution keys: Query performance is affected by table distribution; use appropriate sort and distribution keys
  • Workload Management (WLM): Query performance depends on WLM queue configuration

​
Cursor-based Streaming

Results are streamed in batches (default 100,000 rows) for memory-efficient processing.

​
Example Query

Create a SQL file in your Evidence project:
queries/daily_events.sql
SELECT 
  DATE_TRUNC('day', event_timestamp) as date,
  event_type,
  COUNT(*) as event_count,
  COUNT(DISTINCT user_id) as unique_users
FROM events
WHERE event_timestamp >= DATEADD(day, -30, CURRENT_DATE)
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC

​
Performance Optimization

​
Use Distribution Keys

When querying large tables, leverage Redshift’s distribution keys: 
SELECT 
  o.order_date,
  c.customer_name,
  o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - 90

​
Use Sort Keys

Query tables using their sort keys for better performance: 
SELECT *
FROM user_events
WHERE event_date >= '2024-01-01'
ORDER BY event_date DESC

​
Avoid SELECT *

Specify only needed columns to reduce data transfer: 
SELECT 
  user_id,
  event_name,
  created_at
FROM events
LIMIT 10000

​
Network Configuration

​
VPC Security Groups

Ensure your Redshift cluster’s security group allows inbound connections:
  1. Go to AWS Console → Redshift → Clusters → Your Cluster
  2. Click on the VPC security group
  3. Add an inbound rule:
    • Type: Custom TCP
    • Port: 5439
    • Source: Your Evidence instance IP or CIDR block

​
Publicly Accessible

For development, you may need to make your cluster publicly accessible:
  1. Go to Cluster → Actions → Modify publicly accessible setting
  2. Enable “Publicly accessible”
Only make clusters publicly accessible for development. For production, use VPC peering, AWS PrivateLink, or VPN connections.

​
Troubleshooting

  • Verify the cluster endpoint and port are correct
  • Check VPC security group rules allow your IP
  • Ensure the cluster is in “Available” state
  • Verify the cluster is publicly accessible (if connecting from outside AWS)
Redshift requires SSL connections. Ensure SSL is enabled:
ssl:
  sslmode: require
Grant schema usage permissions:
GRANT USAGE ON SCHEMA analytics TO username;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO username;
  • Use EXPLAIN to analyze query plans
  • Check WLM queue configuration
  • Ensure tables use appropriate distribution and sort keys
  • Run VACUUM and ANALYZE on tables regularly

Build docs developers (and LLMs) love

Get started for freeTalk to us