Search docs.molo17.com

    Amazon redshift glyph screenshot RedShift agent

    Target RedShift

    Prerequisites

    To have Gluesync working with your Amazon RedShift instance you will need:

    • Valid AWS credentials with permissions to access RedShift and S3;

    • RedShift cluster endpoint or Serverless workgroup URL;

    • Database name and credentials with read/write access to target tables;

    • S3 bucket for staging bulk load files;

    • IAM role or access keys with the following permissions:

      • RedShift: redshift:GetClusterCredentials, redshift:DescribeClusters

      • S3: s3:PutObject, s3:GetObject, s3:DeleteObject on the staging bucket

    Creating an IAM policy for Gluesync

    If you don’t have an IAM user with the required permissions, you can create one using the following IAM policy:

    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Effect": "Allow",
          "Action": [
            "redshift:GetClusterCredentials",
            "redshift:DescribeClusters"
          ],
          "Resource": "*"
        },
        {
          "Effect": "Allow",
          "Action": [
            "s3:PutObject",
            "s3:GetObject",
            "s3:DeleteObject"
          ],
          "Resource": "arn:aws:s3:::your-staging-bucket/*"
        }
      ]
    }

    Creating a RedShift user with the required permissions

    If you don’t have a database user with the required permissions, you can create one by running the following SQL commands in RedShift:

    CREATE USER gluesync_user PASSWORD '<REDACTED>';
    
    -- Grant schema usage and table permissions
    GRANT USAGE ON SCHEMA your_schema TO gluesync_user;
    GRANT CREATE ON SCHEMA your_schema TO gluesync_user;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA your_schema TO gluesync_user;
    
    -- Ensure future tables get permissions automatically
    ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO gluesync_user;

    Setup via Web UI

    • Hostname / IP Address: RedShift cluster endpoint (e.g., cluster-name.region.redshift.amazonaws.com);

    • Port: RedShift port (default: 5439);

    • Database name: Name of your target database;

    • Username: Database username with read & write access to target tables;

    • Password: Password belonging to the given username;

    • AWS Access Key ID: IAM user access key for S3 operations;

    • AWS Secret Access Key: IAM user secret key for S3 operations;

    • S3 Staging Bucket: S3 bucket name for bulk load staging files;

    • S3 Region: AWS region where the S3 bucket is located;

    • Enable TLS: Recommended for secure connections (default: enabled).

    Custom properties

    • Schema: (defaults to public) The target schema where tables will be created/updated;

    • Warehouse: (provisioned clusters only) The RedShift warehouse/cluster name for query execution;

    • Workgroup: (Serverless only) The RedShift Serverless workgroup name;

    • S3 Key Prefix: (optional) Prefix path for staging files in the S3 bucket;

    • Copy Options: (optional) Additional options for the RedShift COPY command (e.g., DELIMITER ',' REMOVEQUOTES);

    Specific configuration

    This agent supports the following specific configuration properties:

    Property Default Description

    s3StagingBucket

    required

    The S3 bucket name used for staging CSV files during bulk loads.

    s3Region

    required

    The AWS region where the S3 bucket is located (e.g., us-east-1).

    s3KeyPrefix

    gluesync-staging/

    Optional prefix path for staging files within the bucket.

    awsAccessKeyId

    required

    AWS IAM access key ID for S3 operations.

    awsSecretAccessKey

    required

    AWS IAM secret access key for S3 operations.

    schema

    public

    The target schema in RedShift.

    workgroup

    (Serverless only)

    RedShift Serverless workgroup name (required for Serverless, omit for provisioned clusters).

    copyOptions

    DELIMITER ',' REMOVEQUOTES ESCAPE

    Additional COPY command options for fine-tuning bulk loads.

    Setup via Rest APIs

    Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

    Connect the agent

    curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
    --header 'Content-Type: application/json' \
    --header 'Authorization: ••••••' \
    --data '{
      "hostCredentials": {
        "connectionName": "myRedshiftAgent",
        "host": "cluster-name.region.redshift.amazonaws.com",
        "port": 5439,
        "databaseName": "my_database",
        "username": "gluesync_user",
        "password": "<REDACTED>",
        "maxConnectionsCount": 50,
        "enableTls": true
      },
      "customHostCredentials": {
        "schema": "public",
        "s3StagingBucket": "my-redshift-staging-bucket",
        "s3Region": "us-east-1",
        "s3KeyPrefix": "gluesync-staging/",
        "awsAccessKeyId": "AKIAIOSFODNN7EXAMPLE",
        "awsSecretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
        "workgroup": "my-workgroup",
        "copyOptions": "DELIMITER ',' REMOVEQUOTES ESCAPE"
      }
    }'

    Bulk load workflow

    The RedShift agent implements the following bulk load workflow:

    1. Data Collection: CDC changes are collected during the mirroring cycle

    2. CSV Generation: Changes are formatted as CSV files

    3. S3 Upload: CSV files are uploaded to the configured S3 staging bucket

    4. COPY Execution: RedShift’s native COPY command loads data from S3 into staging tables

    5. Merge Apply: Staging data is merged into target tables using optimized SQL operations

    6. Cleanup: Staging files are removed from S3 after successful load

    For more details on bulk load patterns, see Bulk load.

    Updates with bulk operations during CDC

    When Use bulk operations during CDC (useBulkOperationsDuringCDC) is on for an entity, the agent does not apply an update as an UPDATE statement. It loads the change into a staging table, deletes the existing row from the target table and inserts the new version of it. With the option off, updates are applied as UPDATE statements.

    For the target table this has the following effects:

    • Columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

    Troubleshooting

    Issue Solution

    S3 access denied errors

    Verify IAM credentials have s3:PutObject, s3:GetObject, and s3:DeleteObject permissions on the staging bucket.

    RedShift connection timeout

    Check security group rules allow inbound traffic from Gluesync on port 5439. Verify the cluster is running (for provisioned clusters).

    COPY command failures

    Review RedShift STL_LOAD_ERRORS table for detailed error messages. Common issues include malformed CSV data or insufficient permissions.

    Slow bulk load performance

    Ensure the S3 bucket is in the same AWS region as the RedShift cluster. Consider using a VPC endpoint for S3 to reduce latency.