Skip to main content

Redshift Integration for Nodus

1. Introduction to the Redshift Integration​

What Is This Integration?​

The Redshift integration connects your Amazon Redshift data warehouse with Nodus, allowing you to extract, analyze, and transform data from Redshift tables and views. This integration functions as a source connector, bringing your Redshift data into the Nodus ecosystem for advanced analytics and business intelligence.

Prerequisites:​

  • An active Amazon Redshift cluster
  • Redshift database credentials with appropriate permissions
  • Network access to your Redshift cluster (proper security group settings, VPC settings, or publicly accessible endpoint)
  • Access to a database user with sufficient privileges to read from source tables

Connection Overview:​

The integration uses Amazon Redshift's SQL API to extract data and perform queries. Authentication is handled through standard database credentials, and data is extracted based on user-defined configurations that specify which schemas and tables to retrieve.

2. Platform Setup Documentation (Setup Form for Redshift)​

Purpose & Scope​

This section covers how to set up the initial connection between Nodus and Redshift by providing the necessary authentication credentials and cluster details.

Field-by-Field Breakdown:​

Integration Name​

  • Field Name & Label: Integration Name
  • Description & Purpose: A descriptive name to identify this Redshift integration within your Nodus account.
  • Validation Rules & Format: Text string, required field.
  • Examples: "Production Redshift", "Analytics Data Warehouse"
  • Troubleshooting Tips: Use a descriptive name that clearly identifies the specific Redshift cluster or purpose.

Host​

  • Field Name & Label: Host
  • Description & Purpose: The endpoint address of your Redshift cluster.
  • Validation Rules & Format: Text string, required field.
  • Examples: "your-cluster-name.abc123xyz789.us-west-2.redshift.amazonaws.com"
  • Troubleshooting Tips: You can find the cluster endpoint in the AWS Redshift console under the cluster details. Do not include the port number here.

Port​

  • Field Name & Label: Port
  • Description & Purpose: The port number used to connect to your Redshift cluster.
  • Validation Rules & Format: Numeric string, required field.
  • Examples: "5439" (the default Redshift port)
  • Troubleshooting Tips: The default port for Redshift is 5439, but this can be customized during cluster creation.

Database​

  • Field Name & Label: Database
  • Description & Purpose: The name of the Redshift database you want to connect to.
  • Validation Rules & Format: Text string, required field.
  • Examples: "dev", "analytics", "production"
  • Troubleshooting Tips: The database must already exist in your Redshift cluster. The default database in Redshift is usually named "dev".

Username​

  • Field Name & Label: Username
  • Description & Purpose: The username used to authenticate with your Redshift cluster.
  • Validation Rules & Format: Text string, required field.
  • Examples: "awsuser", "redshift_user"
  • Troubleshooting Tips: For security best practices, consider creating a dedicated read-only user for Nodus connections.

Password​

  • Field Name & Label: Password
  • Description & Purpose: The password associated with the username.
  • Validation Rules & Format: Text string, required field, masked for security.
  • Troubleshooting Tips: Passwords are case-sensitive. Ensure the password hasn't expired in your Redshift cluster.

Step-by-Step Guide:​

  1. Prepare your Redshift environment:
    • Ensure your Redshift cluster is running and accessible
    • Verify network connectivity (security groups, VPC settings, etc.)
    • Set up a database user with appropriate permissions
  2. Gather your connection details:
    • Redshift cluster endpoint (hostname)
    • Port number (default is 5439)
    • Database name
    • Username and password
  3. Enter your integration details in the Nodus setup form:
    • Provide a meaningful Integration Name
    • Enter your Redshift Host (endpoint)
    • Enter the Port number
    • Enter the Database name
    • Enter your Username
    • Enter your Password
  4. Save the configuration and test the connection

3. Extraction/Query Configuration (Extraction Form for Redshift)​

Purpose & Overview​

This section explains how to configure data extraction from Redshift. The form allows you to select specific schemas and tables, define extraction strategies, and specify column filters for optimized data extraction.

Template & Field Documentation:​

Source Schema and Table Selection​

  • Field Name & Label: Source Schema / Source Table
  • Description & Purpose: Defines which schema and table to extract data from
  • Validation Rules & Format: Cascading dropdown selection, required field
  • Troubleshooting Tips: The schema list is populated with available schemas in your Redshift database, and the table list is populated based on the selected schema.

Destination Configuration​

  • Field Name & Label: Final Schema / Final table name
  • Description & Purpose: Defines where extracted data will be stored in the Nodus data warehouse
  • Validation Rules & Format: Schema dropdown and table name input, required fields
  • Troubleshooting Tips: Choose an existing schema and provide a name for the destination table where your data will be loaded.

Write Strategy​

  • Field Name & Label: Write Strategy
  • Description & Purpose: Defines how data should be written to the destination
  • Validation Rules & Format: Radio button selection, required field
  • Available Options:
    • Upsert - Update existing records and insert new ones
    • Append - Add new records to the existing table
    • Replace - Drop and recreate the table with new data
  • Troubleshooting Tips: Choose the appropriate strategy based on your use case. Upsert requires a primary key.

Workflow & Examples:​

  1. Select the source schema and table from the cascading dropdowns
  2. Select a destination schema and enter a table name
  3. Choose a write strategy
  4. Execute extraction

Example Use Cases:​

Sales Data Analysis:

  • Source Schema: sales
  • Source Table: transactions
  • Write Strategy: Append

Customer Data Synchronization:

  • Source Schema: public
  • Source Table: customers
  • Write Strategy: Upsert (based on customer_id)

4. Troubleshooting & FAQs for Redshift​

Common Issues & Error Messages​

Authentication Failures​

  • Error: "Could not connect to the server: FATAL: password authentication failed for user"
  • Solution: Verify your username and password. Check if the user account exists in Redshift and that the password is correct.

Connection Issues​

  • Error: "Could not connect to server: Connection timed out"
  • Solution: Check your network settings. Ensure that your Redshift cluster's security group allows connections from the IP address where Nodus is running. Verify VPC and subnet configurations if applicable.

Permission Problems​

  • Error: "Permission denied for schema" or "Permission denied for relation"
  • Solution: Ensure that the user has the necessary permissions on the schema and tables you're trying to access. Grant SELECT privileges on the required objects.
-- Grant schema permissions
GRANT USAGE ON SCHEMA schema_name TO username;
-- Grant table permissions
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

Contact & Support Information​

Best Practices for Redshift Connections​

  • Create a dedicated read-only user for Nodus integrations
  • Set appropriate distribution keys and sort keys on your Redshift tables
  • Monitor and vacuum your tables regularly to maintain performance