Redshift Account

In this article

Overview

Use this account type to connect Redshift Snaps with data sources.

Prerequisites

Access to the following folders:

  • Private project folder
  • Project Space’s shared folder
  • Global shared folder

Limitations

The default JDBC URL jdbc:postgre:://<hostname>:<port>/<db> does not work as it is not compatible with JDBC 2.x drivers. If you are using 2.x version of Amazon Redshift drivers, you must explicitly provide the JDBC URL as jdbc:redshift://<hostname>:<port>/<db> to configure the account successfully.

Known Issues

None.

Account Settings


Redshift IAM Account Setup

  • If the EC2 plex (where your Pipeline is running with IAM role), Redshift cluster, and S3 bucket are in the same AWS account, then you must use Redshift Account (normal IAM account).
  • If the EC2 plex (where your Pipeline is running with IAM role) is in one account and the Redshift cluster and S3 bucket are in a different AWS account, you must use Redshift Cross-account IAM role Account to run your Pipelines successfully.

This is applicable only for Redshift - Bulk Load, Redshift - Unload, and Redshift - S3 Upsert Snaps.

ParameterData TypeDescriptionDefault ValueExample 
Label*String

Unique user-provided label for the account.

N/ARedshift Account
JDBC Driver Class

String

Name of the JBDC driver class to use.

org.postgresql.Driverorg.postgresql.Driver
JDBC jars

String

The list of JDBC jar files to be loaded.

You can upload the Redshift driver(s) that can override the default org.postgresql.Driver driver.

Set the Batch size property to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar.

N/Aorg.postgresql.Driver
JDBC Url

String

Enter the Url of the JDBC database.

If you are using 2.x version of Amazon Redshift drivers, you must explicitly provide the JDBC URL as jdbc:redshift://<hostname>:<port>/<db> to configure the account successfully. The default JDBC URL, jdbc:postgre://<hostname>:<port>/<db> does not work as it is not compatible with JDBC 2.x drivers.

N/Ajdbc:redshift://hostname:port/database

Account properties


Required. Enter in the information to create a connection to the database.

Endpoint*

String

Enter the server's address to connect to.N/Afieldcluster.c8dwbwr9.us-east-1.redshift.amazonaws.com
Port number*

Numeric


Enter the database server's port to connect.54395439
Database name*

String

Enter the database name to connect.N/Asnaplogic
Username

String

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

String

Enter the password used to connect to the data source. Password will be used as the default password when retrieving connections. The password must be valid in order to set up the data source.N/Ap@$$20d*
S3 BucketString

Enter the external S3 Bucket name residing in an external AWS account, to use for staging data onto Redshift.

This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps. 

N/Asl-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.N/As3://sl-bucket-ca/san-francisco
S3 Access-key IDString

Enter the S3 Access key ID part of AWS authentication.

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. 

N/ANAVRGGRV7EDCFVLKJH
S3 Secret keyString

Enter the S3 Secret key part of AWS Authentication.

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. 

NA2RGiLmL/6bCujkKLaRuUJHY9uSDEjNYr+ozHRtg
IAM properties
Specify the IAM properties information for Redshift to communicate with IAM.

AWS account IDString

Enter the ID of the Amazon Web Services account to be used for performing bulk load operation.

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. 

N/A


AWS-1
IAM role nameString

Enter the name of the IAM role that has been assigned to the Redshift cluster to access the S3 bucket provided above.

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. 

N/Arolex
Region name

String


Enter the name of the region the Redshift cluster.N/Aus-east-1
Advanced properties
Specify advanced properties to support this account.

Auto commit

Checkbox


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

SelectedN/A
Batch size*Integer

Enter the number of statements to execute at a time.

  • Set the Batch size property to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar, in the JDBC jars.
  • Select queries are not batched.
  • User-defined types won't be supported if a JDBC driver with a version other than 11.2.0.4.0 is set.
5050
Fetch size*Integer

Enter the number of rows to fetch at a time when executing a query.

User-defined types won't be supported if a JDBC driver with a version other than 11.2.0.4.0 is set.

100100
Max pool size*Integer

Enter the maximum number of connections a pool will maintain at a time.

Redshift Bulk Load/Bulk Upsert/S3 Upsert Snap requires 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.

5030
Max life time*

Integer

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.3010
Idle Timeout*

Integer

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.56
Checkout timeout*IntegerEnter the number of milliseconds to wait for a connection to be available when the pool is exhausted. Zero waits forever. An exception will be thrown after the wait time has expired.1000800
URL Properties
Specify the URL properties associated with this account.

URL property name

String


Enter the URL property nameN/Assl
URL property value

String


Enter the URL property valueN/Atrue

Troubleshooting

None.


See Also