In this article

Table of Contents

maxLevel	2
absoluteUrl	true
exclude	Older Versions\|Additional Resources\|Related Links\|Related Information

You can create an account from Designer or Manager. In Designer, when working on pipelines, every Snap that needs an account prompts you to create a new account or use an existing account. The accounts can be created in or used from:

Your private project folder: This folder contains the pipelines that will use the account.
Your Project Space’s shared folder: This folder is accessible to all the users that belong to the Project Space.
The global shared folder: This folder is accessible to all the users within an organization in the SnapLogic instance.

Account Configuration

In Manager, you can navigate to the required folder and create an account in it (see Accounts). To create an account for binary files:

Click Create, then select Redshift > Redshift Account.
Supply an account label.
Supply the necessary information.
- URL parameters to be specified to ensure SSL connectivity:
  - ```
   ssl=true
```
- The S3 Bucket, S3 Access-key ID and S3 Secret key properties are required for Redshift-Bulk Load and Redshift-Bulk Upsert Snaps. The S3 Folder property may be used for the staging file. If the S3 Folder property is left blank, the staging file will be stored in the bucket.
(Optional) Supply additional information on this account in the Notes field of the Info tab.
Click Apply.

Warning
Avoid changing account credentials while pipelines using them are in progress. This may lead to unexpected results, including locking the account.

The list of JDBC jar files to be loaded. The user can upload the Redshift driver(s) that can override the default org.postgresql.Driver driver.

Note

Expand

title	Account Settings

Label

Required. User provided label for the account instance

JDBC Driver Class

Name of the JBDC driver class to use.

Default value: org.postgresql.Driver

JDBC jars

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

Default value: [None]

Info

title	Recommended JDBC JAR Version for Redshift Bulk Upsert Snap

Use RedshiftJDBC42-1.2.10.1009.jar as the JDBC JAR version in this property when using the Redshift Upsert Snap in the pipeline.

JDBC Url

The Url of the JDBC database.

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

Default value: [None]

If the Snap fails to connect to the database, it will retry three more times.
While selecting an Account on a Redshift SSL instance, you can use the JDBC driver v1.2.8.1005 or greater to validate it. However, when not using the Amazon's Redshift JDBC driver v1.2.8.1005 or greater, you would be required to manually import an updated certificate into their TrustStore or, simply use the Redshift SSL Account.

Account Types

Redshift Account

Required. Number of statements to execute at a time.

Account

properties

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

Endpoint

Required. The server's address to connect to.

Default value: [None]

Port number

Required. The database server's port to connect to.

Default value: 5439

Database name

Required. The database name to connect to.

Default value: [None]

Username

Username that is allowed 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.

Default value: [None]

Password

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.

Default value: [None]

S3 Bucket

S3 Bucket name (include the region) 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.

Default value: [None]

S3 Folder

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

Default value: [None]

S3 Access-key ID

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.

Default value: [None]

S3 Secret key

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.

Default value: [None]

IAM properties

AWS account ID

ID of the Amazon Web Services account to be used for performing 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.

Default value: [None]

IAM role name

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.

Default value: [None]

Region name

Name of the region the Redshift cluster belongs to.

Default value: [None]

Advanced properties

Auto commit

When selected, each of the batches is committed immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back.

When deselected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view.

Default value: Selected

Batch size

Note
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.

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

Default value: 50

Fetch size

Required. Number of rows to fetch at a time when executing a query.

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

Default value: 100

Max pool size

Required. Maximum number of connections a pool will maintain at a time.

Note
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.

Default value: 50

Max life time

Required. 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

Idle Timeout

Required. 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

Checkout timeoutRequired. 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.

Default value: 10000

URL PropertiesURL property name

URL property name

Default value: [None]

URL property value

URL property value

Default value: [None]

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.

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

Redshift SSL Account

Expand

title	Account Settings

Label

Required. User provided label for the account instanceJDBC Driver Class

Name of the JBDC driver class to use.

Default value: org.postgresql.Driver

JDBC jars

The list of JDBC jar files to be loaded. The user can upload the Redshift driver(s) that can override the default org.postgresql.Driver driver.

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

Default value: [None]

JDBC Url

The Url of the JDBC database.

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

Default value: [None]

Account properties

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

Endpoint

Required. The server's address to connect to.

Default value: [None]

Port number

Required. The database server's port to connect to.

Default value: 5439

Database name

Required. The database name to connect to.

Default value: [None]

Username

Username that is allowed 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.

Default value: [None]

Password

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.

Default value: [None]

S3 Bucket

S3 Bucket (include the region) 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.

Default value: [None]

S3 Folder

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

Default value: [None]

S3 Access-key ID

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.

Default value: [None]

S3 Secret key

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.

Default value: [None]

KeyStore

The location of the key store file, can be in SLDB or any other unauthenticated endpoint such as https://...

Example: shared/client_keystore.jks

Default value: [None]

TrustStore

The location of the trust store file, can be in SLDB or any other unauthenticated endpoint such as https://...

Example: shared/client_truststore.jks

Default value: [None]

Key/Trust store passwordPassword for key/trust store, it is used for both if both are defined.
Default value: [None]Key aliasDefine the key being used via its alias
Default value: [None]IAM propertiesAWS account ID

ID of the Amazon Web Services account to be used for performing 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.

Default value: [None]

IAM role name

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.

Default value: [None]

Region name

Name of the region the Redshift cluster belongs to.

Default value: [None]

Advanced propertiesAuto commit

When selected, each of the batches is committed immediately after it is executed. If the Snap fails, only the batch being executed at that moment is rolled back.

When deselected, the Snap execution output is committed only after all the batches are executed. If the Snap fails, the entire transaction is rolled back, unless the Snap finds invalid input data before it sends the insert request to the server, and routes the error documents to the Error view.

Default value: Selected

Batch size

Required. Number of statements to execute at a time.

Note
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.

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

Default value: 50

Fetch size

Required. Number of rows to fetch at a time when executing a query.

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

Default value: 100

Max pool size

Required. Maximum number of connections a pool will maintain at a time.

Note
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.

Default value: 50

Max life time

Required. 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

Idle Timeout

Required. 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

Checkout timeoutRequired. 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.

Default value: 10000

URL PropertiesURL property name

URL property name

Default value: [None]

URL property value

URL property value

Default value: [None]

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.

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

Auto Commit with Execute Snaps

For a DB Execute Snap, assume that a stream of documents enters the input view of the Snap and the SQL statement property has JSON paths in the WHERE clause. If the number of documents are large, the Snap executes in more than one batches rather than executing one per each document. Each batch would contain a certain number of WHERE clause values. If Auto commit is turned on, a failure would only roll back the records in the current batch. If Auto commit is turned off, the entire operation would be rolled back. For a single execute statement (with no input view), the setting has no practical effect

.

Insert excerpt

	Redshift Snap Pack
	Redshift Snap Pack
nopanel	true

Versions Compared

Old Version 46

New Version 47

Key

Account Configuration

Account Types

Redshift Account

Account Encryption

Redshift SSL Account

Account Encryption

Auto Commit with Execute Snaps

Page Comparison

Versions Compared

Old Version 46

New Version 47

Key

Account Configuration

Account Types

Redshift Account

Account Encryption

Redshift SSL Account

Account Encryption

Auto Commit with Execute Snaps