Redshift

Supermetal replicates to Amazon Redshift through COPY loads from an S3 buffer.

Prerequisites

An Amazon Redshift cluster or Serverless workgroup reachable from the Supermetal host on the cluster port (default 5439).
An S3 buffer in the same AWS Region as the cluster. Redshift loads only from S3.
Privileges to create a database user and grant CREATE and TEMP on the database.

Setup

Create a User

CREATE USER supermetal PASSWORD 'strong-password';
GRANT CREATE, TEMP ON DATABASE my_database TO supermetal;

Password security

Replace 'strong-password' with a unique password and store it securely. You will enter it when configuring the target in Supermetal.

CREATE USER supermetal PASSWORD DISABLE;
GRANT CREATE, TEMP ON DATABASE my_database TO supermetal;

Supermetal stores no database password. At connect time it requests temporary credentials from AWS using the identity it runs under, either the instance role or access keys entered in the form. Attach this policy to that identity.

{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": "redshift:GetClusterCredentials",
    "Resource": [
      "arn:aws:redshift:<region>:<account>:dbuser:<cluster>/supermetal",
      "arn:aws:redshift:<region>:<account>:dbname:<cluster>/my_database"
    ]
  }]
}

Redshift logs the session in as IAM:supermetal with the privileges of the user above. Supermetal can instead create the database user on first login, which also needs the redshift:CreateClusterUser permission.

Serverless workgroups

IAM authentication is not available for Redshift Serverless. Use password authentication.

CREATE lets Supermetal create the target schema. CDC replication requires TEMP. If you create the target schema yourself, grant schema privileges instead of CREATE on the database.

GRANT USAGE, CREATE ON SCHEMA my_schema TO supermetal;

Allow Network Access

In the cluster's VPC security group, add an inbound rule for the Supermetal host on the cluster port. Connections from outside the VPC also require the cluster to be publicly accessible.

Configure the S3 Buffer

Select an S3 buffer in the same AWS Region as the cluster. Supermetal loads with the buffer's credentials, so the target needs no extra IAM configuration.

Bucket policies

Redshift fetches staged files through presigned URLs. A bucket policy that blocks presigned URLs, or sets s3:signatureAge below 3,600,000 ms, fails the load.

Transactions

Transactional DML is disabled by default. When enabled, Supermetal applies changes through Redshift transactions and preserves multi table transactions from the source. Changes from a single source transaction apply fully or not at all.

Sort and Distribution Keys

Supermetal sets the sort and distribution keys only when it creates a table, so change this option before the first sync. Tables created ahead of time keep their own layout. Tables without primary keys always use AUTO styles.

Value	Description
On (default)	The first primary key column becomes the distribution key. The full primary key becomes the compound sort key.
Off	Tables are created with AUTO distribution and sort styles, which Redshift can adjust as tables grow.

Identifier Case

Supermetal preserves the case of source table and column names. Redshift lowercases identifiers by default, even quoted ones, so sessions that query mixed case names must enable case sensitivity first.

SET enable_case_sensitive_identifier = true;

To make this the default for every session, set enable_case_sensitive_identifier in the cluster parameter group or serverless workgroup. This changes identifier resolution for existing workloads, so review them first on a shared cluster. Replication does not depend on it either way.

Data Types Mapping

Apache Arrow DataType	Redshift Type	Notes
`Int8`, `Int16`	`SMALLINT`
`Int32`	`INTEGER`
`Int64`	`BIGINT`
`UInt8`, `UInt16`	`INTEGER`
`UInt32`	`BIGINT`
`UInt64`	`DECIMAL(20, 0)`	Exceeds the signed 64 bit range.
`Float16`, `Float32`	`REAL`
`Float64`	`DOUBLE PRECISION`
`Decimal128(p, s)`, `Decimal256(p, s)`	`DECIMAL(p, s)`	When `p <= 38` and `0 <= s <= 37`.
`Decimal128(p, s)`, `Decimal256(p, s)`	`VARCHAR(65535)`	Otherwise.

Apache Arrow DataType	Redshift Type
`Boolean`	`BOOLEAN`

Apache Arrow DataType	Redshift Type	Notes
`Date32`, `Date64`	`DATE`	Values outside `4713-01-01 BC` through `9999-12-31` load as `NULL`.
`Time32`, `Time64`	`VARCHAR(65535)`	Stored as text.
`Timestamp(s\|ms\|us\|ns, tz)`	`TIMESTAMPTZ`	Microsecond resolution. Values outside the `DATE` range load as `NULL`.
`Timestamp(s\|ms\|us\|ns)`	`TIMESTAMP`	Microsecond resolution. Values outside the `DATE` range load as `NULL`.
`Interval`, `Duration`	`VARCHAR(65535)`	Stored as text.

Apache Arrow DataType	Redshift Type	Notes
`Utf8`, `LargeUtf8`, `Utf8View`	`VARCHAR(65535)`	Values over 65,535 bytes are truncated.
`Utf8` JSON extension (`arrow.json`)	`SUPER`	Documents over 65,535 bytes load as `NULL`.
`Null`	`VARCHAR(65535)`

Apache Arrow DataType	Redshift Type
`Binary`, `LargeBinary`, `BinaryView`, `FixedSizeBinary`	`VARBYTE(16777216)`

Apache Arrow DataType	Redshift Type	Notes
`List`, `LargeList`, `FixedSizeList`	`SUPER`	String elements are truncated at 65,535 bytes.
`Struct`, `Map`	`SUPER`	String fields are truncated at 65,535 bytes.