In this article
Table of Contents | ||||||
---|---|---|---|---|---|---|
|
Articles in this section
Child pages (Children Display) | ||
---|---|---|
|
Overview
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.
- URL parameters to be specified to ensure SSL connectivity:
- (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. |
Note |
---|
If the Snap fails to connect to the database, it will retry three more times. |
Note |
---|
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
title | Redshift Account |
---|
Redshift Account
Account Settings
Label
Name of the JBDC driver class to use.
Default value: org.postgresql.Driver
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]
Info | ||
---|---|---|
| ||
Use RedshiftJDBC42-1.2.10.1009.jar as the JDBC JAR version in this property when using the Redshift Upsert Snap in the pipeline. |
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]
Required. The database server's port to connect to.
Default value: 5439
Required. The database name to connect to.
Default value: [None]
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 used to connect to the data source. Password wil 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 to use for staging data onto Redshift.
Note |
---|
This field is required if the IAM role checkbox is enabled in the Bulk Load Snap. For optimal performance, this S3 bucket should be located in the same AWS region as the Redshift instance. |
Default value: [None]
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 part of AWS authentication.
Note |
---|
This field is required if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
S3 Secret key part of AWS Authentication.
Note |
---|
This field is required if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
ID of the Amazon Web Services account to be used for performing bulk load operation.
Note |
---|
This field is required if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
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 if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
Name of the region the Redshift cluster belongs to.
Default value: [None]
Required. If selected, then batches are immediately committed after they execute. Therefore, only the current executing batch will be rolled back if the Snap fails.
If not selected, then a transaction is started for the Snap run and committed upon run success. The transaction will be rolled back if the Snap fails.
Default value: Selected
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. |
Note |
---|
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
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
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
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
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
Default value: 10000
URL property name
Default value: [None]
URL property value
Default value: [None]
Account Encryption
If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed.
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
title | Redshift SSL Account |
---|
Redshift SSL Account
Account Settings
Label
Name of the JBDC driver class to use.
Default value: org.postgresql.Driver
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]
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]
Required. The database server's port to connect to.
Default value: 5439
Required. The database name to connect to.
Default value: [None]
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 used to connect to the data source. Password wil 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 to use for staging data onto Redshift.
Note |
---|
This field is required if the IAM role checkbox is enabled in the Bulk Load Snap. For optimal performance, this S3 bucket should be located in the same AWS region as the Redshift instance. |
Default value: [None]
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 part of AWS authentication.
Note |
---|
This field is required if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
S3 Secret key part of AWS Authentication.
Note |
---|
This field is required if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
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]
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]
Default value: [None]
Default value: [None]
ID of the Amazon Web Services account to be used for performing bulk load operation.
Note |
---|
This field is required if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
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 if IAM role checkbox is enabled in the Bulk Load Snap. |
Default value: [None]
Name of the region the Redshift cluster belongs to.
Default value: [None]
Required. If selected, then batches are immediately committed after they execute. Therefore, only the current executing batch will be rolled back if the Snap fails.
If not selected, then a transaction is started for the Snap run and committed upon run success. The transaction will be rolled back if the Snap fails.
Default value: Selected
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. |
Note |
---|
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
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
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
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
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
Default value: 10000
URL property name
Default value: [None]
URL property value
Default value: [None]
Account Encryption
If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed.
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.
borderStyle | none |
---|
Account History
title | Click to view/expand |
---|
4.18 (snapsmrc523)
- No updates made.
4.17 (snapsmrc515)
- No updates made. Automatic rebuild with a platform release.
4.16 (snapsmrc508)
- No updates made. Automatic rebuild with a platform release.
4.15 (snapsmrc500)
- No updates made. Automatic rebuild with a platform release.
4.14 (snapsmrc490)
No updates made. Automatic rebuild with a platform release.
4.10 Patch MULTIPLE4088
- Added SSL support.
4.8.0 Patch redshift2799
- Added the properties, JDBC Driver Class, JDBC jars and JDBC Url to enable the users to upload the Redshift JDBC drivers that can override the default driver.
4.8.0
- Info tab added to accounts.
- Database accounts now invalidate connection pools if account properties are modified and login attempts fail.
4.7.0
Updated the Snap Account Settings with the IAM properties that include AWS account ID, IAM role name, and Region namemust create SnapLogic Redshift Accounts to connect to data sources that you want to use in your pipelines. You can configure your SnapLogic Redshift Accounts using either the Designer or the Manager.
Multiexcerpt include macro | ||||||||
---|---|---|---|---|---|---|---|---|
|
Multiexcerpt include macro | ||||||||
---|---|---|---|---|---|---|---|---|
|
Snap-Account Compatibility
Snaps in the Redshift Snap Pack work with different accounts and protocols per the following table.
Snap | ||||
---|---|---|---|---|
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ | |
✔ | ✔ | ✔ | ✔ |
Configuring Redshift Accounts Using SnapLogic Designer
Drag a Redshift Snap to the Canvas and click the Snap to open its settings. Click the Account tab. You can now either use an existing account or create a new one.
Selecting an existing account
SnapLogic organizes and displays all accounts to which you have access, sorting them by account type and location. To select an existing account:
In the Account tab, click the icon to view the accounts to which you have access, and select the account that you want to use.
Click Save.
Creating an account
In the Account tab, click Add Account below the Account Reference field.
Select the Location in which you want to create the account, select the Account Type, and click Continue. The Add Account dialog window associated with the account type is displayed.
Enter the required account details. For detailed guidance on how to provide information required for each account type, see the following articles:
Child pages (Children Display) Click Validate to verify the account, if the account type supports validation.
Click Apply to complete configuring the Redshift account.
Info |
---|
Enter additional information about this account in the Notes field of the Info tab. This will help you – and other users – understand the purpose of the account, especially if there are multiple accounts of the same type. |
Configuring Redshift Accounts Using SnapLogic Manager
You can use Manager to create accounts without associating them immediately with Pipelines.
Info |
---|
Accounts in SnapLogic are associated with projects. You can use accounts created in other projects only if you have at least Read access to them. |
In the left pane, browse to the project in which you want to create the account and click > Account > <Snap Pack Name>, followed by the appropriate account type. The Create Account dialog associated with the selected account type is displayed.
Repeat the steps numbered 3 through 5 in the Creating an account section.
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.
- URL parameters to be specified to ensure SSL connectivity:
- (Optional) Supply additional information on this account in the Notes field of the Info tab.
- Click Apply.
Info |
---|
Avoid updating account credentials while Pipelines using that account are executing. Doing so may lead to unexpected results, including your account getting locked. |
Note |
---|
|