Redshift Cross-Account IAM Role Account

In this article

Overview

Use this account type to connect Redshift Snaps with data sources that use Redshift Cross-Account IAM Role Account.

Prerequisites

Access to the following folders:

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

Limitations

None.

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_Cross_IAM_Account
JDBC Driver Class

String

Enter the JDBC driver class to use in your application.

org.postgresql.Driverorg.postgresql.Driver
JDBC JARs

Specify or upload the JDBC JAR files that you want to use. You can upload the Redshift driver(s) that can override the default org.postgresql.Driver driver. Set the Batch size value to 1 with the JDBC driver version RedshiftJDBC41-1.1.10.1010.jar. Specify each JDBC Driver as a separate row. Click  to add a new row. We recommend, RedshiftJDBC42-1.2.10.1009.jar version when using Redshift Bulk Upsert Snap in your Pipeline.

JDBC DriverString

Select the JDBC driver to use. 

Default JDBC driver will be used on leaving this field blank.

JDBC Url

String

Enter the URL of the JDBC database.N/Ajdbc:redshift://hostname:port/database

Account properties

Configure the information required to establish a database connection with the account.

Endpoint*

String

Enter the server's address to establish a connection.N/Afieldcluster.cvnsysusue7w2.us-east-1.redshift.amazonaws.com
Port number*

Numeric


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

String

Enter the database name to connect.N/Aweqter
Username

String

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

String

Enter the password to connect to the data source. Password is used as the default password when retrieving connections. The password must be valid in order to set up the data source.N/AN/A
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 Bucket RegionString

Enter the name of the region where the S3 bucket belongs.

This field is required if the Redshift cluster and the S3 bucket are in the different regions.

N/Aus-east-2
S3 Bucket Write IAM Role ARNString

Enter the IAM role to write to the S3 bucket which resides in either the same or different AWS account.

  • You need to have write and delete permissions in this role.
  • This field is required when using Bulk load Snap (with input view data source), Bulk Upsert Snap, and Unload Snap.
N/Aarn:aws:iam::133198801419:role/IamRoleWithAllAccessForCanary
External IDStringEnter an optional external ID which is required by the role. This field is applicable only for streaming bulk load.N/A7609567
IAM properties (Redshift Cluster)
Specify the 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.

N/A 


AWS1233961
IAM role nameString

Enter the name of the IAM role associated with the target Redshift cluster to access the S3 bucket. 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.

N/Adas-crossaccount-sp6776

S3 Bucket Read IAM Role

Specify the information required to make Redshift work with IAM instead of Access-key ID and Secret.
IAM Role ARNString

Enter the ARN of the IAM role set on the above S3 bucket.

This field is required only if the S3 bucket is in another AWS account. If S3 bucket is in another AWS account, specify the Cross-Account IAM role to read the S3 bucket. Else, leave this field blank and ensure the IAM role assigned to the Redshift Cluster has permission to read from the S3 bucket. For more information, see Redshift Cross Account IAM Role Setup.

N/Aarn:aws:iam::133198801419:role/cross-account-access-snap6776-das
Advanced propertiesSpecify 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. If the Snap fails, only the batch being executed at that moment is rolled back.

SelectedDeselected
Batch size*Integer

Enter the number of statements to execute at a time.

This field is required when using Redshift Bulk Load, Redshift Bulk Upsert, Redshift S3 Upsert, and Redshift Unload Snaps. User-defined types are not if a JDBC driver with a version other than 11.2.0.4.0 is set.

5070
Fetch size*Integer

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

User-defined types are not supported if a JDBC driver with a version other than 11.2.0.4.0 is set.

10056
Max pool size*Integer

Enter the maximum number of connections that a pool maintains at a time.

Redshift Bulk Load/Bulk Upsert/S3 Upsert Snap requires a minimum of two 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 four for successful execution.

5010
Max life time*

Integer


Enter the maximum lifetime of a connection in the pool.

Ensure that the value you enter is a few seconds lesser 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.

3015
Idle Timeout*

Integer


Enter the maximum amount of time a connection that should remain idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.51
Checkout timeout*Integer


Enter the number of milliseconds to wait for a connection to be available when the pool is exhausted. A value of 0 indicates that the wait time to checkout is infinite. An exception is thrown after the wait time expires.10002000
URL PropertiesSpecify the URL properties associated to this account.

URL property name

Numeric


Enter the URL property name.N/Assl
URL property value

Numeric


Enter the URL property value.N/Atrue

Account Encryption

Standard Encryption

If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed.

Enhanced Encryption

If you have the Enhanced Account Encryption feature, the following describes which fields are encrypted for each sensitivity level selected per each account.

  • High: Password, S3 Access-key ID, S3 Secret key
  • Medium + High: Username, Password, S3 Access-key ID, S3 Secret key
  • Low + Medium + High: Endpoint, Database name, Username, Password, S3 Bucket, S3 Folder, S3 Access-key ID, S3 Secret key, URL property value

Troubleshooting

ErrorReasonResolution
Error copying data from S3 to Redshift.Redshift database user is unable to assume the IAM role.

If the Redshift cluster is in the same AWS account as the S3 bucket, remove the role ARN defined in the S3 Bucket Read IAM role field of the Redshift Cross Account and ensure the role attached to the Redshift cluster has the permission to read from S3 bucket.


See Also