Amazon Redshift

The Amazon Redshift Connection

Prerequisites

  • A Redshift cluster
  • A username and password with access to the cluster
  • Connectivity through IP whitelisting or a bastion host (please contact [email protected] for support)

Connection Properties

The following table describes the fields available when creating a new Amazon Redshift connection. Create a new connection using the information below and these step-by-step instructions.

FieldRequiredDescription
Access TypeRequiredThis connection type is Read-Only, Write-Only, or Read-Write.
Connection NameRequiredInput your desired name.
Field NameRequiredThis is a description of the field.
HostRequiredThe endpoint of the Amazon Redshift cluster that the read connector will be linked to. It should be in the following format: clustername.abc123xyz789.exampleserver.redshift.amazonaws.com.)
PortOptionalthe port number that the read connector uses. By default, Ascend.io uses 5439.
Database NameRequiredThe name of the database that you want to access.
Staging DirectoryOptionalThe staging directory is an option on Redshift but ishighly recommended for better performance as even a couple hundred records inserted to Redshift without the COPY command can make the Redshift cluster busy. Also, for the reading side, the staging directory allows for using the UNLOAD command to first export the records to the blob store and then read in the records from there.
Requires CredentialsOptionalCheck this box to create a new credential or select an existing credential.

Credential Properties

The following table describes the fields available when creating a new Amazon Redshift credential.

Field NameRequiredDescription
Credential NameRequiredThe name to identify this credential with. This credential will be available as a selection for future use.
Credential TypeRequiredThis field will automatically populate with Amazon Redshift.
UserRequiredThe Redshift username you need to gain access with this credential.
PasswordRequiredThe Redshift password you need for accessing AWS.

Read Connector Properties

The following table describes the fields available when creating a new Amazon Redshift Read Connector. Create a new Read Connector using the information below and these step-by-step instructions.

Field NameRequiredDescription
Connector NameRequiredProvide a name for your connector. We recommend using lowercase with underscores in place of spaces.
DescriptionOptionalDescribes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
Table NameRequiredThe name of the table used in the read connection.
Schema NameOptionalThe name of the schema of the table entered above that you wish to use specifically.
Replication StrategyOptionalAllows for an exact copy of the master instance of the table to be created. Can be done in parallel, if desired. For more information on replication strategies, refer to our Database Read Strategies documentation.

Write Connector Properties

The following table describes the fields available when creating a new Amazon Redshift Write Connector. Create a new Write Connector using the information below and these step-by-step instructions.

🚧

While it is possible to write the Redshift without an S3 staging directory, we strongly recommend using an S3 staging director because it uses Redshift's COPY command and improves write performance.

Field NameRequiredDescription
Connector NameRequiredProvide a name for your connector. We recommend using lowercase with underscores in place of spaces.
DescriptionOptionalDescribes the connector. We recommend providing a description if you are ingesting information from the same source multiple times for different reasons.
Table NameRequiredThe name of the table that will be written to Redshift.
Write StrategyRequiredThe strategy to copy data into AWS.

The Full Load strategy will replace the entire tables contents each time that the upstream transform has changed.

If the upstream data set contains multiple Ascend partitions and not all partitions are expected to change on each update, then the Incremental By Partition strategy allows for Ascend to only update/insert/delete the partitions of the Amazon Redshift table that have changed in Ascend.
A SQL Statement for Ascend to execute before writingOptionalHere, the user has the option to execute a pre-processing script before writing to final table.
A SQL Statement for Ascend to execute after writingOptionalHere, the user has the option to execute a pre-processing script after writing to final table.

More Reading