Conduit Connector Amazon Redshift

General

Amazon Redshift connector is one of Conduit plugins. It provides both, a Source and a Destination Amazon Redshift connectors.

Prerequisites

• Go 1.20
• (optional) golangci-lint 1.51.2

How to build it

Run make build.

Testing

Run make test to run all unit and integration tests. To run the integration test, set the Amazon Redshift database DSN to the environment variables as an REDSHIFT_DSN.

Source

Conduit's Redshift source connector allows you to move data from Redshift tables with the specified dsn and tables configuration parameters. Upon starting, the source connector takes a snapshot of given tables in the database, then switches into change data capture (CDC) mode. In CDC mode, the connector will only detect new rows.

Snapshots

At the first launch of the connector, the snapshot mode is enabled and the last value of the orderingColumn is stored to the position, to know the boundary of this mode. The connector reads all rows of a table in batches, using a keyset pagination, limiting the rows by batchSize and ordering by orderingColumn. The connector stores the last processed element value of an orderingColumn in a position, so the snapshot process can be paused and resumed without losing data. Once all rows in that initial snapshot are read the connector switches into CDC mode.

This behavior is enabled by default, but can be turned off by adding "snapshot": false to the Source configuration.

Change Data Capture

In this mode, only rows added after the first launch of the connector are moved in batches. It’s using a keyset pagination, limiting by batchSize and ordering by orderingColumn.

Configuration

[!IMPORTANT] Parameters starting with tables.* are used to configure the orderingColumn and keyColumns for a specific table. The * in the parameter name should be replaced with the table name.

Example

Collections

The following configuration reads records from users and orders table

version: 2.2
pipelines:
  - id: example
    status: running
    connectors:
      - id: example
        type: source
        plugin: redshift
        settings:
          dsn: "sample_dsn"
          # table "users"
          tables.users.orderingColumn: "foo"
          tables.users.keyColumns: "foo,bar"
          # table "orders"
          tables.orders.orderingColumn: "id"

Key handling

The connector builds sdk.Record.Key as sdk.StructuredData. The keys of this field consist of elements of the keyColumns of a specific table. If keyColumns is empty, the connector uses the primary keys of the table; otherwise, if the table has no primary keys, it uses the value of the orderingColumn field of that table. The values of sdk.Record.Key field are taken from sdk.Payload.After by the keys of this field.

Table Name

For each record, the connector adds a opencdc.collection property to the metadata that contains the table name.

Destination

Conduit's Redshift destination connector allows you to move data to a Redshift table with the specified dsn and table configuration parameters. It takes an sdk.Record and parses it into a valid SQL query.

Note: Redshift does not support map or slice types, so they will be stored as marshaled strings.

Configuration Options

Key handling

• When inserting records (i.e. when the CDC operation is create or snapshot) the key is ignored.
• When updating records (i.e. when the CDC operations is update):
  • if the record key exists, it's expected to be structured
  • if the record key doesn't exist, then it will be built from the keyColumns in the payload's After field.
• When deleting records (i.e. when the CDC operation is delete) the key is required to be present.

Table Name

Records are written to the table specified by the opencdc.collection property in the metadata, if it exists. If not, the connector will fall back to the table configured in the connector.

Known limitations

Creating a source or destination connector will fail in the next cases:

• connector does not have access to Redshift;
• user does not have permission;
• table does not exist.

Useful resources

• Quotas and limits in Amazon Redshift