Redshift Account

Overview

The Redshift Account allows you to connect to Amazon Redshift data warehouses using JDBC connections. This account supports both provisioned clusters and serverless instances, with options for username/password authentication or IAM role-based authentication.

Account Settings

Note: Learn about the common controls in the Snap settings dialog.
Field/Field set Description
Label

String

Required. Specify a unique label for the account.

Example: Redshift Account

JDBC driver class

String

By default, the com.amazon.redshift.jdbc42.Driver class is bundled with the Snap Pack.

Default value: com.amazon.redshift.jdbc42.Driver

Example: org.postgresql.Driver

JDBC JARs

String

Use this field set to add a list of JDBC JAR files to be loaded. By default, the Redshift account is bundled with the JDBC v2.1.0.29 driver. However, you can add a custom JAR file. Click + to add a new row for each JDBC JAR file.
JDBC driver

String

The Redshift Snap Pack is bundled with the default Redshift JDBC driver v2.1.0.29. Therefore, even if you do not provide a JDBC Driver, the account does not fail.
JDBC URL

String

Enter the URL of the JDBC database.

Example: jdbc:redshift://hostname:port/database

Account properties
Endpoint

String

Required. Enter the server's address to connect to.
Note: The Redshift account determines if the Redshift instance is a provisioned cluster or a serverless instance by checking the Endpoint name.
  • If your endpoint contains .redshift-serverless: It's a Serverless instance
  • If your endpoint contains only .redshift: It's a Provisioned Cluster

The Redshift URL formats:

  • Redshift Serverless: [workgroup-name].[aws-account-number].[region].redshift-serverless.amazonaws.com
  • Redshift Cluster: [cluster-identifier].[random-string].[region].redshift.amazonaws.com

Examples:

  • Provisioned Cluster Endpoint: cluster1.abcdefg.us-west-2.redshift.amazonaws.com
  • Serverless Endpoint: default.123456789012.us-west-2.redshift-serverless.amazonaws.com
Port number

String

Required. Enter the database server's port to connect.

Default value: 5439

Example: 5439

Database name

String

Required. Enter the database name to connect.

Example: snaplogic

Username

String

Enter the username to connect to the database. The username will be used as the default username when retrieving connections. The username must be valid in order to set up the data source.

Example: redshiftadmin

IAM role

Checkbox

Required. Select this checkbox to automatically generate temporary credentials if your Groundplex is configured with IAM role to access the Redshift instance. The Password field is not needed if you select this checkbox.
  • For Redshift clusters, the Username field is required.
  • For Redshift Serverless instances, the Username field is optional when you select this checkbox.

Default status: Deselected
Password

String

 Enter the password to connect to the data source. The password will be used as the default password when retrieving connections. The password must be valid in order to set up the data source. You must provide a valid password to set up the data source unless you select the IAM role checkbox.

Example: p@$$20d*

S3 bucket

String

Required. Enter the external S3 Bucket name residing in an external AWS account to use for staging data onto Redshift.
Note: This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps.

Example: sl-bucket-ca

S3 folder

String

Enter the relative path to a folder in S3 Bucket. This is used as a root folder for staging data onto Redshift.

Example: s3://bucket-name/folder-name

S3 access-key ID

String

Required. Enter the S3 Access key ID part of AWS authentication.
Note: This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps if the IAM role checkbox is not selected.

Example: NAVRGGRV7EDCFVLKJH

S3 secret key

String

 Required. Enter the S3 Secret key part of AWS Authentication.
Note: This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps if the IAM role checkbox is not selected.

Example: 2RGiLmL/6bCujkKLaRuUJHY9uSDEjNYr+ozHRtg

S3 security token

String

Required. Specify the S3 security token part of the AWS Security Token Service (STS) authentication. It is not required unless a specific S3 credential is configured to require it. Note that only global STS regions are supported.
Note: This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps if the IAM role checkbox is not selected.

Example: FwoGYXdzEGUaDFInugbZwNWCiV3uSQBjIr41ZvKGc4TcSknLVYpI2amWKf9z21+7oszQ==

IAM properties
AWS account ID

String

Required. Enter the ID of the Amazon Web Services account to be used for performing the bulk load operation.
Note: This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps if the IAM role checkbox is selected.

Example: AWS-1

IAM role name

String

Required. Enter the name of the IAM role that has been assigned to the Redshift cluster to access the S3 bucket provided above.
Note: This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps if the IAM role checkbox is selected.

Example: rolex

Region name

String

Enter the name of the region the Redshift cluster.

Example: us-east-1

Advanced properties
Auto commit

Checkbox

Select this checkbox to enable the Snap to commit offsets automatically as messages are consumed and sent to the output view.

Default status: Selected
Batch size

Integer

Required. Enter the number of statements to execute at a time. Select queries are not batched.
Note: User-defined types are not supported if a JDBC driver with a version other than 11.2.0.4.0 is set.

Default value: 50

Example: 50

Fetch size

Integer

Required. Enter the number of rows to fetch at a time when executing a query.
Note: User-defined types are not supported if a JDBC driver with a version other than 11.2.0.4.0 is set.

Default value: 100

Example: 100

Max pool size

Integer

Required. Enter the maximum number of connections a pool will maintain at the same time.
Note: Redshift Bulk Load/Bulk Upsert/S3 Upsert Snaps require a minimum of 2 connections per Snap in a pipeline. For example, if a pipeline has a Redshift Bulk Load Snap and an S3 Upsert Snap, then the pool size must be greater than or equal to 4 for successful execution.

Default value: 50

Example: 30

Max lifetime (minutes)

Integer

Required. Enter the maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.

Default value: 30

Example: 10

Idle Timeout (minutes)

Integer

Required. Enter the maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.

Default value: 5

Example: 6

Checkout timeout (milliseconds)

Integer

Required. Enter the number of milliseconds to wait for a connection to be available when the pool is exhausted.
Note: If you enter 0 for this value, the Snap waits infinitely until the connection is available. Therefore, we recommend you not enter 0 for Checkout Timeout.

Default value: 1000

Example: 800

URL properties
URL property name

String

Enter the URL property name.

Example: ssl

URL property value

String

Enter the URL property value.

Example: true